You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2016/07/28 01:37:20 UTC
[jira] [Comment Edited] (CALCITE-1332) DB2 requires fully qualified
names in join condition
[ 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/%3CCE9BFE72-92DC-419C-9260-8624D33E8477@apache.org%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/%3CCE9BFE72-92DC-419C-9260-8624D33E8477@apache.org%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)