[IGNITE-14973] Calcite engine. IgniteLimit is pushed down under Exchange invalid

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: sql
• Labels: calcite
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Aleksey Plekhanov
• Reporter: Taras Ledkov
• Created: 23/Jun/21 10:27
• Updated: 23/Nov/21 14:40
• Resolved: 31/Aug/21 14:32

Description

In complex cases IgniteLimit is pushed down under Exchange node.

Steps to reproduce:

CREATE TABLE test (a VARCHAR);
INSERT INTO test VALUES ('aaa'), ('bbb'), ('ccc');
CREATE TABLE test2 (a VARCHAR, b VARCHAR);
INSERT INTO test2 VALUES ('blabla', 'b'), ('blabla2', 'c'), ('blabla3', 'd');

SELECT NULLIF(NULLIF ((SELECT a FROM test ORDER BY a LIMIT 1 offset 1), a), b) FROM test2;

Plan:

IgniteProject(EXPR$0=[CASE(=(CASE(=($4, $2), null:VARCHAR CHARACTER SET "UTF-8", $5), $3), null:VARCHAR CHARACTER SET "UTF-8", CASE(=($4, $2), null:VARCHAR CHARACTER SET "UTF-8", $6))])
  IgniteNestedLoopJoin(condition=[true], joinType=[left])
    IgniteNestedLoopJoin(condition=[true], joinType=[left])
      IgniteNestedLoopJoin(condition=[true], joinType=[left])
        IgniteExchange(distribution=[single])
          IgniteIndexScan(table=[[PUBLIC, TEST2]], index=[_key_PK])
        IgniteExchange(distribution=[single])
          IgniteLimit(offset=[1], fetch=[1])
            IgniteIndexScan(table=[[PUBLIC, TEST]], index=[_key_PK], requiredColumns=[{2}])
      IgniteExchange(distribution=[single])
        IgniteLimit(offset=[1], fetch=[1])
          IgniteIndexScan(table=[[PUBLIC, TEST]], index=[_key_PK], requiredColumns=[{2}])
    IgniteExchange(distribution=[single])
      IgniteLimit(offset=[1], fetch=[1])
        IgniteIndexScan(table=[[PUBLIC, TEST]], index=[_key_PK], requiredColumns=[{2}])

Issue Links

• is part of: IGNITE-14535 Caclite SQL engine capabilities
• relates to: IGNITE-13179 Calcite integration. Fix traits at the IgniteLimit
• links to: GitHub Pull Request #9303