[
https://issues.apache.org/jira/browse/IGNITE-22204?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maksim Zhuravkov updated IGNITE-22204:
--------------------------------------
Description:
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
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.
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
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, result 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
> Priority: Minor
> 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
> 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)
