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

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.9.0
    • core
    • 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

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

            Dates

              Created:
              Updated:
              Resolved: