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