[ https://issues.apache.org/jira/browse/IGNITE-22392?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Maksim Zhuravkov updated IGNITE-22392: -------------------------------------- Description: Combination of LIMIT / OFFSET and set operator results in incorrect transformation of a plan tree. This issue is caused by incorrect handling of the `RemoveSortInSubQuery` flag inside SqlToRelConverter. 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. Reproducible in vanila calcite: {noformat} EnumerableUnion(all=[true]) > EnumerableCalc(expr#0..1=[{inputs}], A=[$t0]) > EnumerableLimit(offset=[1], fetch=[1]) > EnumerableSort(sort0=[$0], dir0=[ASC]) > EnumerableTableScan(table=[[BLANK, TEST]]) > EnumerableCalc(expr#0..1=[{inputs}], A=[$t0]) > EnumerableLimit(offset=[2], fetch=[3]) > EnumerableSort(sort0=[$0], dir0=[ASC]) > EnumerableTableScan(table=[[BLANK, TEST]]) {noformat} was: 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. Reproducible in vanila calcite: {noformat} EnumerableUnion(all=[true]) > EnumerableCalc(expr#0..1=[{inputs}], A=[$t0]) > EnumerableLimit(offset=[1], fetch=[1]) > EnumerableSort(sort0=[$0], dir0=[ASC]) > EnumerableTableScan(table=[[BLANK, TEST]]) > EnumerableCalc(expr#0..1=[{inputs}], A=[$t0]) > EnumerableLimit(offset=[2], fetch=[3]) > EnumerableSort(sort0=[$0], dir0=[ASC]) > EnumerableTableScan(table=[[BLANK, TEST]]) {noformat} > 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 > Priority: Minor > Labels: ignite-3 > > Combination of LIMIT / OFFSET and set operator results in incorrect > transformation of a plan tree. This issue is caused by incorrect handling of > the `RemoveSortInSubQuery` flag inside SqlToRelConverter. 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. > Reproducible in vanila calcite: > {noformat} > EnumerableUnion(all=[true]) > > EnumerableCalc(expr#0..1=[{inputs}], A=[$t0]) > > EnumerableLimit(offset=[1], fetch=[1]) > > EnumerableSort(sort0=[$0], dir0=[ASC]) > > EnumerableTableScan(table=[[BLANK, TEST]]) > > EnumerableCalc(expr#0..1=[{inputs}], A=[$t0]) > > EnumerableLimit(offset=[2], fetch=[3]) > > EnumerableSort(sort0=[$0], dir0=[ASC]) > > EnumerableTableScan(table=[[BLANK, TEST]]) > {noformat} -- This message was sent by Atlassian Jira (v8.20.10#820010)