Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1332

JDBC adapter for DB2 should always use aliases for tables: x.y.z AS z

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.9.0
    • Component/s: core
    • Labels:
      None

      Description

      I tried joining tables in DB2 and it looks like DB2 dialect requires the fully qualified (with db name, table name, and column name) in the join condition. Also, if quoted, the identifiers need to be separated, e.g. "MYDB.MYTABLE" will not work, but "MYDB"."MYTABLE" will.

      db2 => select * from mydb.mytable inner join mydb.mytable2 on mytable.id = mytable2.id
      SQL0206N  "MYTABLE.ID" is not valid in the context where it is used. 
      db2 => select * from mydb.mytable inner join mydb.mytable2 on "mydb.mytable".id = "mydb.mytable2".id
      SQL0206N  "mydb.mytable.ID" is not valid in the context where it is used.  
      SQLSTATE=42703
      db2 => select name from mydb.mytable inner join mydb.mytable2 on mydb.mytable.id = mydb.mytable2.id
      
      NAME                                              
      --------------------------------------------------
      Steven                                            
      
      db2 => select * from "MYDB"."MYTABLE" inner join "MYDB"."MYTABLE2" on "MYTABLE"."ID" = "MYTABLE2"."ID"
      SQL0206N  "MYTABLE.ID" is not valid in the context where it is used. 
      SQLSTATE=42703
      db2 => select * from "MYDB"."MYTABLE" inner join "MYDB"."MYTABLE2" on "MYDB.MYTABLE"."ID" = "MYDB.MYTABLE2"."ID"
      SQL0206N  "MYDB.MYTABLE.ID" is not valid in the context where it is used. 
      SQLSTATE=42703
      db2 => select * from "MYDB"."MYTABLE" inner join "MYDB"."MYTABLE2" on "MYDB"."MYTABLE"."ID" = "MYDB"."MYTABLE2"."ID"
      
      ID          NAME                                               FN                                                                                                   LN                                                                                                   ID         
      ----------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
              100 Steven                                             steven                                                                                               even                                                                                                         100
      
        1 record(s) selected.
      
      

        Activity

        Hide
        minjikim MinJi Kim added a comment -

        Julian Hyde I am sure if this is the best way to solve this problem. Please take a look and let me know if you have any suggestions or comments. Thanks!

        https://github.com/apache/calcite/pull/261

        Show
        minjikim MinJi Kim added a comment - Julian Hyde I am sure if this is the best way to solve this problem. Please take a look and let me know if you have any suggestions or comments. Thanks! https://github.com/apache/calcite/pull/261
        Hide
        julianhyde Julian Hyde added a comment -

        Putting the database (or any other qualifier like schema) inside the double-quotes is always wrong. Remember that a table name can legitimately contain a '.', so we would not be able to tell where the database name ended and the table name started.

        I'm pretty sure that the solution is to use a table alias, i.e.

        select *
        from "MYDB"."MYTABLE" AS "T1"
        inner join "MYDB"."MYTABLE2" AS "T2" on "T1"."ID" = "T2"."ID"
        

        Your solution would not work if the same table occurs more than once in the FROM clause (i.e. a self-join). So let's use aliases all the time. We'll obviously need to use aliases for other occurrences of the columns, in say the SELECT and WHERE clauses.

        Show
        julianhyde Julian Hyde added a comment - Putting the database (or any other qualifier like schema) inside the double-quotes is always wrong. Remember that a table name can legitimately contain a '.', so we would not be able to tell where the database name ended and the table name started. I'm pretty sure that the solution is to use a table alias, i.e. select * from "MYDB" . "MYTABLE" AS "T1" inner join "MYDB" . "MYTABLE2" AS "T2" on "T1" . "ID" = "T2" . "ID" Your solution would not work if the same table occurs more than once in the FROM clause (i.e. a self-join). So let's use aliases all the time. We'll obviously need to use aliases for other occurrences of the columns, in say the SELECT and WHERE clauses.
        Hide
        minjikim MinJi Kim added a comment -

        Thanks, Julian Hyde! I think the alias definitely makes things cleaner. I will give it a try and upload a new patch for you to review.

        Show
        minjikim MinJi Kim added a comment - Thanks, Julian Hyde ! I think the alias definitely makes things cleaner. I will give it a try and upload a new patch for you to review.
        Hide
        julianhyde Julian Hyde added a comment - - edited

        This is the same issue as Magnus Pierre raised in an email message. (I wish he'd logged a JIRA case, as I asked.)

        Calcite assumes that FROM x.y.z is equivalent to FROM x.y.z AS z; i.e. a table gets an implicit alias. DB2 is the only database I know that doesn't do this. The remedy is to create an explicit alias in DB2 queries.

        Thus your test case should generate

        SELECT *
        FROM foodmart.employee AS employee
        INNER JOIN foodmart.department AS department
        ON employee.department_id = department.department_id

        when run against DB2.

        Show
        julianhyde Julian Hyde added a comment - - edited This is the same issue as Magnus Pierre raised in an email message . (I wish he'd logged a JIRA case, as I asked.) Calcite assumes that FROM x.y.z is equivalent to FROM x.y.z AS z ; i.e. a table gets an implicit alias. DB2 is the only database I know that doesn't do this. The remedy is to create an explicit alias in DB2 queries. Thus your test case should generate SELECT * FROM foodmart.employee AS employee INNER JOIN foodmart.department AS department ON employee.department_id = department.department_id when run against DB2.
        Hide
        minjikim MinJi Kim added a comment -

        Julian Hyde I am still working on a few cases with filters and joins. I will update patch when it is ready.

        Show
        minjikim MinJi Kim added a comment - Julian Hyde I am still working on a few cases with filters and joins. I will update patch when it is ready.
        Hide
        minjikim MinJi Kim added a comment -

        Julian Hyde I have made updates to the patch for you to review. Thanks!

        Show
        minjikim MinJi Kim added a comment - Julian Hyde I have made updates to the patch for you to review. Thanks!
        Hide
        julianhyde Julian Hyde added a comment -
        Show
        julianhyde Julian Hyde added a comment - Thanks for the PR, MinJi Kim ! Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/b4df7c97 .
        Hide
        jcamachorodriguez Jesus Camacho Rodriguez added a comment -

        Resolved in release 1.9.0 (2016-09-22)

        Show
        jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.9.0 (2016-09-22)

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            minjikim MinJi Kim
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development