[ https://issues.apache.org/jira/browse/IGNITE-22204?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Iurii Gerzhedovich updated IGNITE-22204: ---------------------------------------- Priority: Critical (was: Minor) > 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 > Priority: Critical > Labels: ignite-3 > > 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. -- This message was sent by Atlassian Jira (v8.20.10#820010)