[
https://issues.apache.org/jira/browse/CALCITE-4729?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17399433#comment-17399433
]
duan xiong edited comment on CALCITE-4729 at 8/16/21, 1:05 AM:
---------------------------------------------------------------
This bug does not just show in CTE. I do some tests in PG:
1) join the same table(or different use common column)
{code:java}
select * from emps test1 join emps test2 on test1.name=test2.name{code}
{code:java}
+-----+------+----------+------+----------+-----+------+----------+------+----------+
|empid|deptno|name |salary|commission|empid|deptno|name
|salary|commission|
+-----+------+----------+------+----------+-----+------+----------+------+----------+
|100 |10 |Bill |10000 |1000 |100 |10 |Bill |10000 |1000
|
|200 |20 |Eric |8000 |500 |200 |20 |Eric |8000 |500
|
|150 |10 |Sebastian |7000 |NULL |150 |10 |Sebastian |7000 |NULL
|
|110 |10 |Theodore |11500 |250 |110 |10 |Theodore |11500 |250
|
+-----+------+----------+------+----------+-----+------+----------+------+----------+
{code}
As test. In calcite(when meeting the same column name in the project):
{code:java}
cust_id=100; prod_id=10; cust_id0=100; prod_id0=10
cust_id=150; prod_id=20; cust_id0=150; prod_id0=20{code}
use the column+number to solve the same column. That's different.
was (Author: nobigo):
This bug does not just show in CTE. I do some tests in PG:
1) join the same table(or different use common column)
{code:java}
select * from emps test1 join emps test2 on test1.name=test2.name{code}
{code:java}
+-----+------+----------+------+----------+-----+------+----------+------+----------+
|empid|deptno|name |salary|commission|empid|deptno|name
|salary|commission|
+-----+------+----------+------+----------+-----+------+----------+------+----------+
|100 |10 |Bill |10000 |1000 |100 |10 |Bill |10000 |1000
|
|200 |20 |Eric |8000 |500 |200 |20 |Eric |8000 |500
|
|150 |10 |Sebastian |7000 |NULL |150 |10 |Sebastian |7000 |NULL
|
|110 |10 |Theodore |11500 |250 |110 |10 |Theodore |11500 |250
|
+-----+------+----------+------+----------+-----+------+----------+------+----------+
{code}
As test. In calcite(when meeting the same column name in the project):
{code:java}
cust_id=100; prod_id=10; cust_id0=100; prod_id0=10
cust_id=150; prod_id=20; cust_id0=150; prod_id0=20{code}
use the column+number to solve the same column. That's different.
> Column in CTE should be ambiguous
> ---------------------------------
>
> Key: CALCITE-4729
> URL: https://issues.apache.org/jira/browse/CALCITE-4729
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Lantao Jin
> Assignee: duan xiong
> Priority: Major
>
> {code}
> create table test (a int);
> with t as
> (
> select * from test t1 join test t2 on t1.a=t2.a
> )
> select t.a from t;
> {code}
> The above query fails with 't.a' is ambiguous in Spark/Presto/Postgres. But
> it succeeds in Calcite. Currently, many and many companies use Calcite as a
> query federation layer upon multiple query engines such as Spark and Presto.
> But this behavior confused users.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
