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.
      
      

        Attachments

          Activity

            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: