[jira] [Updated] (IGNITE-22204) Sql. Set operation. Incorrect query transformation for a query with limit / offset that uses the same table

["Maksim Zhuravkov (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/IGNITE-22204?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Maksim Zhuravkov updated IGNITE-22204:
--------------------------------------
    Description: 

IGNITE-16013  incorrectly handles Sort(offset, fetch) transformation
It transforms  Sort(ordering=abc, offset=o, fetch=f) into Limit (offset=o, fetch=f)  -> Sort(ordering=abc, offset=o, fetch=f), which is not correct, since fetch and offset are applied twice.


  was:
Combination of LIMIT / OFFSET and set operator results in incorrect transformation of a plan tree:

{noformat}
statement ok
CREATE TABLE test (a INTEGER);

statement ok
INSERT INTO test VALUES (1), (2), (3), (4);

# query 1
query I rowsort
SELECT a FROM
  (SELECT a FROM test ORDER BY a LIMIT 1 OFFSET 1) t(a)
----
2

# query 2
query I rowsort
SELECT a FROM
  (SELECT a FROM
    (SELECT a FROM test ORDER BY a LIMIT 3 OFFSET 2) i(a)
    ORDER BY a OFFSET 1
  ) t(a)
----
4

# combined query should return 2, 4
# but it returns 2
query I rowsort
SELECT a FROM
  (SELECT a FROM test ORDER BY a LIMIT 1 OFFSET 1) t(a)
UNION ALL
SELECT a FROM
  (SELECT a FROM
    (SELECT a FROM test ORDER BY a LIMIT 3 OFFSET 2) i(a)
    ORDER BY a OFFSET 1
  ) t(a)
----
2
4

{noformat}


Query 1
{noformat}
SELECT a FROM
  (SELECT a FROM test ORDER BY a LIMIT 1 OFFSET 1) t(a)

 Limit(offset=[1], fetch=[1]), id = 80
    Exchange(distribution=[single]), id = 79
       Sort(sort0=[$0], dir0=[ASC], offset=[1], fetch=[1]), id = 78
         TableScan(table=[[PUBLIC, TEST]], requiredColumns=[{0}]), id = 50
{noformat}

Query 2

{noformat}
SELECT a FROM
  (SELECT a FROM
    (SELECT a FROM test ORDER BY a LIMIT 3 OFFSET 2) i(a)
    ORDER BY a OFFSET 1
  ) t(a)

 Limit(offset=[1]), id = 201
   Limit(offset=[2], fetch=[3]), id = 200
     Exchange(distribution=[single]), id = 199
       Sort(sort0=[$0], dir0=[ASC], offset=[2], fetch=[3]), id = 198
         TableScan(table=[[PUBLIC, TEST]], requiredColumns=[{0}]), id = 168
{noformat}

Combine queries using UNION ALL

{noformat}
SELECT a FROM
  (SELECT a FROM test ORDER BY a LIMIT 1 OFFSET 1) t(a)
UNION ALL
SELECT a FROM
  (SELECT a FROM
    (SELECT a FROM test ORDER BY a LIMIT 3 OFFSET 2) i(a)
    ORDER BY a OFFSET 1
  ) t(a)

UnionAll(all=[true]), id = 403
  Limit(offset=[1], fetch=[1]), id = 400
    Exchange(distribution=[single]), id = 399 # subtree is duplicated in another part of a query
      Sort(sort0=[$0], dir0=[ASC], offset=[1], fetch=[1]), id = 398 # 
        TableScan(table=[[PUBLIC, TEST]], requiredColumns=[{0}]), id = 345
  Limit(offset=[1]), id = 402
    Limit(offset=[2], fetch=[3]), id = 401
      Exchange(distribution=[single]), id = 399 # duplicate
        Sort(sort0=[$0], dir0=[ASC], offset=[1], fetch=[1]), id = 398
          TableScan(table=[[PUBLIC, TEST]], requiredColumns=[{0}]), id = 345
{noformat}



When tables are different, results are correct.




> Sql. Set operation. Incorrect query transformation for a query with limit / offset that uses the same table
> -----------------------------------------------------------------------------------------------------------
>
>                 Key: IGNITE-22204
>                 URL: https://issues.apache.org/jira/browse/IGNITE-22204
>             Project: Ignite
>          Issue Type: Bug
>          Components: sql
>    Affects Versions: 3.0.0-beta2
>            Reporter: Maksim Zhuravkov
>            Assignee: Maksim Zhuravkov
>            Priority: Critical
>              Labels: ignite-3
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> IGNITE-16013  incorrectly handles Sort(offset, fetch) transformation
> It transforms  Sort(ordering=abc, offset=o, fetch=f) into Limit (offset=o, fetch=f)  -> Sort(ordering=abc, offset=o, fetch=f), which is not correct, since fetch and offset are applied twice.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)