[
https://issues.apache.org/jira/browse/CALCITE-1332?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
MinJi Kim updated CALCITE-1332:
-------------------------------
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.
{code}
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
{code}
{code}
db2 => select name from mydb.mytable inner join mydb.mytable2 on
mydb.mytable.id = mydb.mytable2.id
NAME
--------------------------------------------------
Steven
{code}
{code}
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.
{code}
was:
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.
{code}
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
{code}
{code}
db2 => select name from mydb.mytable inner join mydb.mytable2 on
mydb.mytable.id = mydb.mytable2.id
NAME
--------------------------------------------------
Steven
{code}
{code}
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"
WORKS!
{code}
> DB2 requires fully qualified names in join condition
> ----------------------------------------------------
>
> Key: CALCITE-1332
> URL: https://issues.apache.org/jira/browse/CALCITE-1332
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: MinJi Kim
> Assignee: Julian Hyde
>
> 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.
> {code}
> 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
> {code}
> {code}
> db2 => select name from mydb.mytable inner join mydb.mytable2 on
> mydb.mytable.id = mydb.mytable2.id
> NAME
> --------------------------------------------------
> Steven
> {code}
> {code}
> 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.
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
