You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "libopeng (Jira)" <ji...@apache.org> on 2023/02/24 09:46:00 UTC
[jira] [Updated] (CALCITE-5544) SCALAR_QUERY is nested within SCALAR_QUERY, and the inner SCALAR_QUERY is an associative subquery that references a variable of the external query, sql to rel will fail
[ https://issues.apache.org/jira/browse/CALCITE-5544?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
libopeng updated CALCITE-5544:
------------------------------
Description:
{code:java}
SELECT
*,
(
SELECT SUM
( (SELECT SUM ( order_amt ) FROM tb_order t2 WHERE t2.product_id = t0.product_id ) )
FROM
tb_order t1
WHERE
t1.product_id = t0.product_id
)
FROM
tb_order t0{code}
to rel
{code:java}
LogicalProject(order_id=[$0], order_amt=[$1], order_date=[$2], desc=[$3], buyer_id=[$4], seller_id=[$5], product_id=[$6], express_id=[$7], EXPR$8=[$9])
LogicalJoin(condition=[=($6, $8)], joinType=[left])
LogicalTableScan(table=[[tb_order]])
LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)])
LogicalProject(product_id0=[$8], EXPR$0=[$9])
LogicalJoin(condition=[true], joinType=[left])
LogicalFilter(condition=[IS NOT NULL($6)])
LogicalTableScan(table=[[tb_order]])
LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)])
LogicalProject(product_id=[$6], order_amt=[$1])
LogicalFilter(condition=[IS NOT NULL($6)])
LogicalTableScan(table=[[tb_order]]) {code}
line 6, "LogicalJoin condition=[true]" is going to create a Cartesian product
was:
{code:java}
SELECT
*,
(
SELECT SUM
( (SELECT SUM ( order_amt ) FROM tb_order t2 WHERE t2.product_id = t0.product_id ) )
FROM
tb_order t1
WHERE
t1.product_id = t0.product_id
)
FROM
tb_order t0{code}
to rel
{code:java}
LogicalProject(order_id=[$0], order_amt=[$1], order_date=[$2], desc=[$3], buyer_id=[$4], seller_id=[$5], product_id=[$6], express_id=[$7], EXPR$8=[$9])
LogicalJoin(condition=[=($6, $8)], joinType=[left])
LogicalTableScan(table=[[tb_order]])
LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)])
LogicalProject(product_id0=[$8], EXPR$0=[$9])
LogicalJoin(condition=[true], joinType=[left])
LogicalFilter(condition=[IS NOT NULL($6)])
LogicalTableScan(table=[[tb_order]])
LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)])
LogicalProject(product_id=[$6], order_amt=[$1])
LogicalFilter(condition=[IS NOT NULL($6)])
LogicalTableScan(table=[[tb_order]]) {code}
line 6, "LogicalJoin condition=[true]" is going to create a Cartesian product
> SCALAR_QUERY is nested within SCALAR_QUERY, and the inner SCALAR_QUERY is an associative subquery that references a variable of the external query, sql to rel will fail
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5544
> URL: https://issues.apache.org/jira/browse/CALCITE-5544
> Project: Calcite
> Issue Type: Bug
> Reporter: libopeng
> Priority: Major
>
>
> {code:java}
> SELECT
> *,
> (
> SELECT SUM
> ( (SELECT SUM ( order_amt ) FROM tb_order t2 WHERE t2.product_id = t0.product_id ) )
> FROM
> tb_order t1
> WHERE
> t1.product_id = t0.product_id
> )
> FROM
> tb_order t0{code}
> to rel
> {code:java}
> LogicalProject(order_id=[$0], order_amt=[$1], order_date=[$2], desc=[$3], buyer_id=[$4], seller_id=[$5], product_id=[$6], express_id=[$7], EXPR$8=[$9])
> LogicalJoin(condition=[=($6, $8)], joinType=[left])
> LogicalTableScan(table=[[tb_order]])
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)])
> LogicalProject(product_id0=[$8], EXPR$0=[$9])
> LogicalJoin(condition=[true], joinType=[left])
> LogicalFilter(condition=[IS NOT NULL($6)])
> LogicalTableScan(table=[[tb_order]])
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)])
> LogicalProject(product_id=[$6], order_amt=[$1])
> LogicalFilter(condition=[IS NOT NULL($6)])
> LogicalTableScan(table=[[tb_order]]) {code}
> line 6, "LogicalJoin condition=[true]" is going to create a Cartesian product
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)