[jira] [Commented] (CALCITE-5583) JDBC adapter generates 'SELECT *', which is invalid SQL if there are duplicate column names
[
https://issues.apache.org/jira/browse/CALCITE-5583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17756255#comment-17756255
]
Jiajun Xie commented on CALCITE-5583:
-
[~leepb] , Thank you for your PR.
I have left some comments.
> JDBC adapter generates 'SELECT *', which is invalid SQL if there are
> duplicate column names
> ---
>
> Key: CALCITE-5583
> URL: https://issues.apache.org/jira/browse/CALCITE-5583
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> JDBC adapter generates 'SELECT *', which is invalid SQL if there are
> duplicate column names
> {code:java}
> LogicalProject(c0=[$0], c00=[$1])
> LogicalJoin(condition=[=($0, $1)], joinType=[inner])
> LogicalProject(c0=[$0])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalProject(c0=[$0])
> LogicalTableScan(table=[[scott, EMP]]){code}
> to sql
> {code:java}
> SELECT *
> FROM (SELECT "EMPNO" AS "c0"
> FROM "scott"."EMP") AS "t"
> INNER JOIN (SELECT "EMPNO" AS "c0"
> FROM "scott"."EMP") AS "t0" ON "t"."c0" = "t0"."c0" {code}
> When this sql is a subquery, it can be wrong
> {code:java}
> select *
> from "EMPNO" t1
> left join (
> SELECT * FROM (SELECT "EMPNO" AS "c0" FROM "scott"."EMP") AS "t" INNER JOIN
> (SELECT "EMPNO" AS "c0" FROM "scott"."EMP") AS "t0" ON "t"."c0" = "t0"."c0"
> ) t2
> on t1.EMPNO=t2."c0"{code}
> {color:#ff}fault: 'column reference "c0" is ambiguous' error when
> executing in postgresql{color}
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5583) JDBC adapter generates 'SELECT *', which is invalid SQL if there are duplicate column names
[
https://issues.apache.org/jira/browse/CALCITE-5583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17755945#comment-17755945
]
libopeng commented on CALCITE-5583:
---
I would appreciate it if you could help me review this
[PR|https://github.com/apache/calcite/pull/3375]
> JDBC adapter generates 'SELECT *', which is invalid SQL if there are
> duplicate column names
> ---
>
> Key: CALCITE-5583
> URL: https://issues.apache.org/jira/browse/CALCITE-5583
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> JDBC adapter generates 'SELECT *', which is invalid SQL if there are
> duplicate column names
> {code:java}
> LogicalProject(c0=[$0], c00=[$1])
> LogicalJoin(condition=[=($0, $1)], joinType=[inner])
> LogicalProject(c0=[$0])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalProject(c0=[$0])
> LogicalTableScan(table=[[scott, EMP]]){code}
> to sql
> {code:java}
> SELECT *
> FROM (SELECT "EMPNO" AS "c0"
> FROM "scott"."EMP") AS "t"
> INNER JOIN (SELECT "EMPNO" AS "c0"
> FROM "scott"."EMP") AS "t0" ON "t"."c0" = "t0"."c0" {code}
> When this sql is a subquery, it can be wrong
> {code:java}
> select *
> from "EMPNO" t1
> left join (
> SELECT * FROM (SELECT "EMPNO" AS "c0" FROM "scott"."EMP") AS "t" INNER JOIN
> (SELECT "EMPNO" AS "c0" FROM "scott"."EMP") AS "t0" ON "t"."c0" = "t0"."c0"
> ) t2
> on t1.EMPNO=t2."c0"{code}
> {color:#ff}fault: 'column reference "c0" is ambiguous' error when
> executing in postgresql{color}
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5583) JDBC adapter generates 'SELECT *', which is invalid SQL if there are duplicate column names
[
https://issues.apache.org/jira/browse/CALCITE-5583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17755699#comment-17755699
]
Julian Hyde commented on CALCITE-5583:
--
While the names of a RelNode are arbitrary, the names of a table are not,
because the table is external. If the tables EMP and DEPT each have a column
called DEPTNO, then the JDBC adapter cannot generate 'select * from EMP, DEPT'.
In a sense the JDBC adapter is not relying on the field name. We are looking at
the RexInputRefs to determine which fields of the table are being referenced.
> JDBC adapter generates 'SELECT *', which is invalid SQL if there are
> duplicate column names
> ---
>
> Key: CALCITE-5583
> URL: https://issues.apache.org/jira/browse/CALCITE-5583
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> JDBC adapter generates 'SELECT *', which is invalid SQL if there are
> duplicate column names
> {code:java}
> LogicalProject(c0=[$0], c00=[$1])
> LogicalJoin(condition=[=($0, $1)], joinType=[inner])
> LogicalProject(c0=[$0])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalProject(c0=[$0])
> LogicalTableScan(table=[[scott, EMP]]){code}
> to sql
> {code:java}
> SELECT *
> FROM (SELECT "EMPNO" AS "c0"
> FROM "scott"."EMP") AS "t"
> INNER JOIN (SELECT "EMPNO" AS "c0"
> FROM "scott"."EMP") AS "t0" ON "t"."c0" = "t0"."c0" {code}
> When this sql is a subquery, it can be wrong
> {code:java}
> select *
> from "EMPNO" t1
> left join (
> SELECT * FROM (SELECT "EMPNO" AS "c0" FROM "scott"."EMP") AS "t" INNER JOIN
> (SELECT "EMPNO" AS "c0" FROM "scott"."EMP") AS "t0" ON "t"."c0" = "t0"."c0"
> ) t2
> on t1.EMPNO=t2."c0"{code}
> {color:#ff}fault: 'column reference "c0" is ambiguous' error when
> executing in postgresql{color}
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5583) JDBC adapter generates 'SELECT *', which is invalid SQL if there are duplicate column names
[
https://issues.apache.org/jira/browse/CALCITE-5583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=1776#comment-1776
]
libopeng commented on CALCITE-5583:
---
[~julianhyde] I'm sorry to bring up this issue again after such a long time, I
saw this [CALCITE-528].
{quote}Basically, yes. Rules should just use ordinals.
The row-type is a struct type, and therefore we have to give the fields names,
and those names have to be unique. I suppose that we could make sure that the
field names are always $0, $1 etc. But it's traditional, and useful, to try to
preserve meaningful field names for the benefit of a human debugging a plan.
(To do this, we sometimes have to assign field names like $f0 (where we can't
derive a field name from source), and we sometimes need to add suffixes to
ensure that field names are unique.)
I like that tradition, and don't feel a strong need to change it. But
developers need to remember that the field names cannot be relied upon.
{quote}
I agree. Can we rely on the field name to determine 'star'?
> JDBC adapter generates 'SELECT *', which is invalid SQL if there are
> duplicate column names
> ---
>
> Key: CALCITE-5583
> URL: https://issues.apache.org/jira/browse/CALCITE-5583
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> JDBC adapter generates 'SELECT *', which is invalid SQL if there are
> duplicate column names
> {code:java}
> LogicalProject(c0=[$0], c00=[$1])
> LogicalJoin(condition=[=($0, $1)], joinType=[inner])
> LogicalProject(c0=[$0])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalProject(c0=[$0])
> LogicalTableScan(table=[[scott, EMP]]){code}
> to sql
> {code:java}
> SELECT *
> FROM (SELECT "EMPNO" AS "c0"
> FROM "scott"."EMP") AS "t"
> INNER JOIN (SELECT "EMPNO" AS "c0"
> FROM "scott"."EMP") AS "t0" ON "t"."c0" = "t0"."c0" {code}
> When this sql is a subquery, it can be wrong
> {code:java}
> select *
> from "EMPNO" t1
> left join (
> SELECT * FROM (SELECT "EMPNO" AS "c0" FROM "scott"."EMP") AS "t" INNER JOIN
> (SELECT "EMPNO" AS "c0" FROM "scott"."EMP") AS "t0" ON "t"."c0" = "t0"."c0"
> ) t2
> on t1.EMPNO=t2."c0"{code}
> {color:#ff}fault: 'column reference "c0" is ambiguous' error when
> executing in postgresql{color}
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
