Maksim Zhuravkov created IGNITE-22392: -----------------------------------------
Summary: Sql. Set operation. Incorrect query transformation for a query with limit / offset that uses the same table (When RemoveSortInSubQuery is enabled) Key: IGNITE-22392 URL: https://issues.apache.org/jira/browse/IGNITE-22392 Project: Ignite Issue Type: Bug Components: sql Reporter: Maksim Zhuravkov Combination of LIMIT / OFFSET and set operator results in incorrect transformation of a plan tree. This issue is caused by incorrect by incorrect handling of the `RemoveSortInSubQuery` flag inside SqlToRelConverter internals. ATM this issue is migrated by disabling that flag. {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. -- This message was sent by Atlassian Jira (v8.20.10#820010)