[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 make mistakes

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Assignee: Unassigned
• Reporter: libopeng
• Created: 24/Feb/23 09:45
• Updated: 27/Feb/23 12:46
• Resolved: 27/Feb/23 12:27

Description

 

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

to rel

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]]) 

line 6, "LogicalJoin condition=[true]" is going to create a Cartesian product

 

 

Issue Links

• duplicates: CALCITE-5418 Nested Queries are not expanded properly