[CALCITE-1332] JDBC adapter for DB2 should always use aliases for tables: x.y.z AS z

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.9.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: MinJi Kim
• Created: 27/Jul/16 18:37
• Updated: 27/Feb/24 22:23
• Resolved: 29/Jul/16 06:18

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.