[ https://issues.apache.org/jira/browse/CALCITE-5544?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17693639#comment-17693639 ]
libopeng edited comment on CALCITE-5544 at 2/27/23 2:43 AM: ------------------------------------------------------------ [~libenchao] I think these two cases are not the same, although they can be grouped into one kind of problem, but this kind of problem is a little broad, I doubt that these two can be solved together was (Author: JIRAUSER298961): I think these two cases are not the same, although they can be grouped into one kind of problem, but this kind of problem is a little broad, I doubt that these two can be solved together > 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 make mistakes > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > 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)