[IGNITE-16040] Calcite. Unable to plan query with several correlated sub-queries in select list

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: sql
• Labels: calcite ignite-3
• Epic Link: SQL tests stabilization
• Assignee: Evgeny Stanilovsky
• Reporter: Konstantin Orlov
• Created: 01/Dec/21 15:15
• Updated: 30/Sep/22 10:41
• Resolved: 07/Sep/22 09:06

Description

Currently the query like below can't be planned by calcite-based sql engine:

SELECT a+b*2,
       (a+b+c+d+e)/5,
       (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d),
       (SELECT count(*) FROM t1 AS x WHERE x.b<t1.b),
       abs(b-c),
       a-b
  FROM t1
 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b)
   AND c>d
 ORDER BY 6,5,4,1,3,2

Need to figure it out how to fix this.

Issue Links

• duplicates: IGNITE-15984 Calcite engine. Query plan can't be planned if correlated variable used in both filter and project
• is duplicated by: IGNITE-15595 Calcite. Correlated expressions with subquery can be planned.
• is part of: IGNITE-12248 Apache Calcite based query execution engine
• relates to: CALCITE-4913 Correlated variables in a select list are not deduplicated
• links to: GitHub Pull Request #1042
• links to: GitHub Pull Request #10219