[
https://issues.apache.org/jira/browse/CALCITE-1332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15396770#comment-15396770
]
Julian Hyde edited comment on CALCITE-1332 at 7/28/16 1:37 AM:
---------------------------------------------------------------
This is the same issue as [Magnus Pierre raised in an email
message|http://mail-archives.apache.org/mod_mbox/calcite-dev/201605.mbox/%[email protected]%3E].
(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 {code}SELECT *
FROM foodmart.employee AS employee
INNER JOIN foodmart.department AS department
ON employee.department_id = department.department_id{code}when run against DB2.
was (Author: julianhyde):
This is the same issue as [Magnus Pierre raised in an email
message|http://mail-archives.apache.org/mod_mbox/calcite-dev/201605.mbox/%[email protected]%3E].
(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.
> 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
> 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)
