[
https://issues.apache.org/jira/browse/CALCITE-4693?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17381254#comment-17381254
]
duan xiong edited comment on CALCITE-4693 at 7/19/21, 3:13 AM:
---------------------------------------------------------------
In PostgreSQL:
The DATA:
DEPT:
{code:java}
+------+--------------+
|deptno|dname |
+------+--------------+
|10 | 'Sales' |
|20 | 'Marketing' |
|30 | 'Engineering'|
|40 | 'Empty' |
|NULL |NULL |
+------+--------------+{code}
EMP:
{code:java}
+-----+------+------+
|ename|deptno|gender|
+-----+------+------+
|Jane |10 |F |
|Bob |10 |M |
|Eric |20 |M |
|Susan|30 |F |
|Alice|30 |F |
|Adam |50 |M |
|Eve |50 |F |
|Grace|60 |F |
|Wilma|NULL |F |
+-----+------+------+
{code}
Test SQL:
{code:java}
SELECT K.deptno,
K.deptno - t2.totalAmount AS amountDue
FROM dept AS K,
LATERAL (
SELECT sum(emp.deptno) AS totalAmount
FROM emp
WHERE emp.deptno = K.deptno
) AS t2{code}
The Result In PG:
{code:java}
+------+---------+
|deptno|amountdue|
+------+---------+
|10 |-10 |
|20 |0 |
|30 |-30 |
|40 |NULL |
|NULL |NULL |
+------+---------+{code}
Same data in Calcite,The result is:
{code:java}
deptno=20; amountDue=0
deptno=10; amountDue=-10
deptno=30; amountDue=-30{code}
When I change the Calcite join from inner join to left join, can get the same
data as PG.
was (Author: nobigo):
In PostgreSQL:
The DATA:
DEPT:
{code:java}
+------+--------------+
|deptno|dname |
+------+--------------+
|10 | 'Sales' |
|20 | 'Marketing' |
|30 | 'Engineering'|
|40 | 'Empty' |
|NULL |NULL |
+------+--------------+{code}
EMP:
{code:java}
+-----+------+------+
|ename|deptno|gender|
+-----+------+------+
|Jane |10 |F |
|Bob |10 |M |
|Eric |20 |M |
|Susan|30 |F |
|Alice|30 |F |
|Adam |50 |M |
|Eve |50 |F |
|Grace|60 |F |
|Wilma|NULL |F |
+-----+------+------+
{code}
Test SQL:
{code:java}
SELECT K.deptno,
K.deptno - t2.totalAmount AS amountDue
FROM dept AS K,
LATERAL (
SELECT sum(emp.deptno) AS totalAmount
FROM emp
WHERE emp.deptno = K.deptno
) AS t2{code}
The Result In PG:
{code:java}
+------+---------+
|deptno|amountdue|
+------+---------+
|10 |-10 |
|20 |0 |
|30 |-30 |
|40 |NULL |
|NULL |NULL |
+------+---------+{code}
Same data in Calcite,The result is:
{code:java}
deptno=20; amountDue=0
deptno=10; amountDue=-10
deptno=30; amountDue=-30{code}
When I change the Calcite join from inner join to left join, can get the same
data as PG.
> Query with Lateral Join is converted to Inner Join instead of Left Join
> -----------------------------------------------------------------------
>
> Key: CALCITE-4693
> URL: https://issues.apache.org/jira/browse/CALCITE-4693
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.26.0
> Reporter: Shiven Dvrk
> Assignee: duan xiong
> Priority: Major
>
> The following query:
> {code:java}
> SELECT
> K.ID,
> K.C1 - t2.totalAmount AS amountDue
> FROM
> T895 AS K,
> LATERAL (
> SELECT
> sum(C201 + C202) AS totalAmount
> FROM T902
> WHERE
> C200 = K.ID
> ) AS t2{code}
>
> is converted to following RelNode( this is innerJoin):
>
> {code:java}
> LogicalProject(ID=[$0], amountDue=[-($2, $4)])
> LogicalJoin(condition=[=($0, $3)], joinType=[inner])
> LogicalTableScan(table=[[T895]])
> LogicalAggregate(group=[{0}], totalAmount=[SUM($1)])
> LogicalProject(C200=[$0], $f0=[+($1, $2)])
> LogicalTableScan(table=[[T902]]) {code}
> we can convert this relnode back to SQL again, it clearly shows that it is
> inner join.
> Tried the Lateral join query on Postgres, the results clearly show that it is
> a left join.
> is there any specific reason behind this behavior ? it seems to be a bug.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
