This is an automated email from the ASF dual-hosted git repository.
jooger pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new 755a840355 IGNITE-21946: Cover SQL F863(Nested <result offset clause>
in <query expression>) feature by tests (#3732)
755a840355 is described below
commit 755a840355c94ca59358fe4c8b1bf1f26ce0fc9f
Author: Max Zhuravkov <[email protected]>
AuthorDate: Tue May 14 18:28:57 2024 +0300
IGNITE-21946: Cover SQL F863(Nested <result offset clause> in <query
expression>) feature by tests (#3732)
---
.../integrationTest/sql/set/test_except_all.test | 2 +-
.../integrationTest/sql/set/test_intersect.test | 2 +-
.../sql/set/test_query_primary_offset.test | 33 ++++++
.../src/integrationTest/sql/set/test_union.test | 111 ++++++++++++++++++++
.../sql/set/test_union_with_limit.test | 112 +++++++++++++++++++++
.../ignite/internal/sql/engine/util/Commons.java | 4 +
.../sql/engine/planner/LimitOffsetPlannerTest.java | 49 +++++++++
7 files changed, 311 insertions(+), 2 deletions(-)
diff --git
a/modules/sql-engine/src/integrationTest/sql/set/test_except_all.test
b/modules/sql-engine/src/integrationTest/sql/set/test_except_all.test
index 82a4d49467..9847ab9ec8 100644
--- a/modules/sql-engine/src/integrationTest/sql/set/test_except_all.test
+++ b/modules/sql-engine/src/integrationTest/sql/set/test_except_all.test
@@ -1,7 +1,7 @@
# name: test/sql/set/test_except_all.test
# description: EXCEPT ALL table operator
# feature: F304
-# [group] Set operations
+# group: [set]
query II
select x, count(*) from
diff --git a/modules/sql-engine/src/integrationTest/sql/set/test_intersect.test
b/modules/sql-engine/src/integrationTest/sql/set/test_intersect.test
index cfec5fcea8..375c1fc6cd 100644
--- a/modules/sql-engine/src/integrationTest/sql/set/test_intersect.test
+++ b/modules/sql-engine/src/integrationTest/sql/set/test_intersect.test
@@ -1,7 +1,7 @@
# name: test/sql/set/test_intersect.test
# description: INTERSECT [ALL] table operator.
# feature: F302-02
-# [group] Set operations
+# group: [set]
query I
SELECT NULL INTERSECT SELECT NULL
diff --git
a/modules/sql-engine/src/integrationTest/sql/set/test_query_primary_offset.test
b/modules/sql-engine/src/integrationTest/sql/set/test_query_primary_offset.test
new file mode 100644
index 0000000000..110b37f246
--- /dev/null
+++
b/modules/sql-engine/src/integrationTest/sql/set/test_query_primary_offset.test
@@ -0,0 +1,33 @@
+# name: test/sql/set/test_query_primary_offset.test
+# description: F863: Nested <result offset clause>.
+# group: [set]
+
+statement ok
+CREATE TABLE test (a INTEGER)
+
+statement ok
+INSERT INTO test VALUES (1), (2), (3), (4)
+
+query I rowsort
+(SELECT * FROM test ORDER BY a ASC OFFSET 2) UNION SELECT * FROM (VALUES (5),
(6)) t(a);
+----
+3
+4
+5
+6
+
+query I rowsort
+(SELECT * FROM test ORDER BY a ASC OFFSET 2 ROWS) UNION SELECT * FROM (VALUES
(5), (6)) t(a);
+----
+3
+4
+5
+6
+
+query I rowsort
+(SELECT * FROM test ORDER BY a ASC OFFSET 2 ROW) UNION SELECT * FROM (VALUES
(5), (6)) t(a);
+----
+3
+4
+5
+6
diff --git a/modules/sql-engine/src/integrationTest/sql/set/test_union.test
b/modules/sql-engine/src/integrationTest/sql/set/test_union.test
new file mode 100644
index 0000000000..33b1459eff
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/set/test_union.test
@@ -0,0 +1,111 @@
+# name: test/sql/set/test_union.test
+# description: Basic union tests
+# feature: E071-01
+# group: [set]
+
+statement ok
+CREATE TABLE a(i INTEGER);
+
+statement ok
+INSERT INTO a(i) VALUES (100), (NULL);
+
+query I
+SELECT 123 UNION DISTINCT SELECT 123 UNION ALL SELECT 123;
+----
+123
+123
+
+query I
+SELECT 123 UNION ALL SELECT 123 UNION ALL SELECT 123;
+----
+123
+123
+123
+
+query I
+SELECT x FROM table(system_range(123, 123)) UNION DISTINCT SELECT x FROM
table(system_range(123, 123)) UNION ALL SELECT 123;
+----
+123
+123
+
+query I
+SELECT x FROM table(system_range(123, 123)) UNION ALL SELECT x FROM
table(system_range(123, 123)) UNION ALL SELECT 123;
+----
+123
+123
+123
+
+query I
+SELECT NULL UNION DISTINCT SELECT NULL
+----
+NULL
+
+query I
+SELECT NULL UNION ALL SELECT NULL
+----
+NULL
+NULL
+
+query I
+SELECT 'a' UNION DISTINCT SELECT NULL
+----
+a
+NULL
+
+query II
+SELECT NULL, NULL UNION DISTINCT SELECT NULL, NULL FROM a
+----
+NULL NULL
+
+query II
+SELECT NULL, NULL UNION ALL SELECT NULL, NULL FROM a
+----
+NULL NULL
+NULL NULL
+NULL NULL
+
+query II rowsort
+SELECT 1, 2 UNION DISTINCT SELECT i, i FROM a
+----
+NULL NULL
+100 100
+1 2
+
+query II rowsort
+SELECT 1, 2 UNION ALL SELECT i, i FROM a
+----
+NULL NULL
+100 100
+1 2
+
+#Mixed types
+
+query I
+SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
+----
+1.1
+2
+
+query I
+SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
+----
+1
+2.2
+
+query I
+SELECT 1 AS one UNION SELECT 1.0::FLOAT ORDER BY 1;
+----
+1
+
+query I
+SELECT 1.1::FLOAT AS two UNION SELECT 2 UNION SELECT 2.0::FLOAT ORDER BY 1;
+----
+1.1
+2
+
+query I
+SELECT 1.1::FLOAT AS two UNION SELECT 2 UNION ALL SELECT 2.0::FLOAT ORDER BY 1;
+----
+1.1
+2
+2
\ No newline at end of file
diff --git
a/modules/sql-engine/src/integrationTest/sql/set/test_union_with_limit.test
b/modules/sql-engine/src/integrationTest/sql/set/test_union_with_limit.test
new file mode 100644
index 0000000000..cd1b4eddb2
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/set/test_union_with_limit.test
@@ -0,0 +1,112 @@
+# name: test/sql/set/union_with_limit.test
+# description: UNION with nested sort/limit
+# group: [set]
+
+statement ok
+CREATE TABLE test (a INTEGER)
+
+statement ok
+INSERT INTO test VALUES (1), (2), (3), (4)
+
+query I rowsort
+SELECT a FROM
+ (SELECT a FROM test ORDER BY a OFFSET 2) t(a)
+UNION ALL
+ SELECT a FROM test
+----
+1
+2
+3
+3
+4
+4
+
+query I rowsort
+SELECT a FROM test
+UNION ALL
+SELECT a FROM
+ (SELECT a FROM test ORDER BY a OFFSET 2) t(a)
+----
+1
+2
+3
+3
+4
+4
+
+query I rowsort
+SELECT a FROM
+ (SELECT a FROM test ORDER BY a LIMIT 1 OFFSET 1) t(a)
+----
+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
+
+skipif ignite3
+# https://issues.apache.org/jira/browse/IGNITE-22204 incorrect plan
transformation
+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
+
+query I rowsort
+SELECT a FROM
+ (SELECT a FROM test ORDER BY a OFFSET 2) t(a)
+UNION ALL
+SELECT a FROM
+ (SELECT a FROM
+ (SELECT a FROM test ORDER BY a OFFSET 2) i(a)
+ ORDER BY a OFFSET 3
+ ) t(a)
+----
+3
+4
+
+query I rowsort
+SELECT a FROM test
+UNION ALL
+SELECT a FROM
+ (SELECT a FROM
+ (SELECT a FROM test ORDER BY a OFFSET 2) i(a)
+ ORDER BY a OFFSET 3
+ ) t(a)
+----
+1
+2
+3
+4
+
+
+statement ok
+CREATE TABLE test2 (a INTEGER)
+
+statement ok
+INSERT INTO test2 VALUES (1), (2), (3), (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 test2 ORDER BY a LIMIT 3 OFFSET 2) i(a)
+ ORDER BY a OFFSET 1
+ ) t(a);
+----
+2
+4
diff --git
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/Commons.java
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/Commons.java
index a81ca55586..2f7f9b185f 100644
---
a/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/Commons.java
+++
b/modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/util/Commons.java
@@ -154,6 +154,10 @@ public final class Commons {
.executor(new RexExecutorImpl(DataContexts.EMPTY))
.sqlToRelConverterConfig(SqlToRelConverter.config()
.withTrimUnusedFields(true)
+ // Disable `RemoveSortInSubQuery` hint that causes
incorrect plan transformation
+ // because calcite does not distinguish between VIEWs and
nested subqueries.
+ // TODO https://issues.apache.org/jira/browse/IGNITE-22204
+ .withRemoveSortInSubQuery(false)
// currently SqlToRelConverter creates not optimal plan
for both optimization and execution
// so it's better to disable such rewriting right now
// TODO: remove this after IGNITE-14277
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/LimitOffsetPlannerTest.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/LimitOffsetPlannerTest.java
index fe8a0ad364..2b6c802917 100644
---
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/LimitOffsetPlannerTest.java
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/planner/LimitOffsetPlannerTest.java
@@ -193,6 +193,55 @@ public class LimitOffsetPlannerTest extends
AbstractPlannerTest {
.and(hasChildThat(isInstanceOf(IgniteExchange.class)).negate()));
}
+ @Test
+ public void testNestedOffset() throws Exception {
+ // Tests for planner for limit/sort in nested subqueries
+ // See bug https://issues.apache.org/jira/browse/IGNITE-21946
+
+ TableBuilder builder = TestBuilders.table()
+ .name("TEST")
+ .addColumn("A", NativeTypes.INT32)
+ .size(ROW_CNT)
+ .distribution(IgniteDistributions.random());
+
+ IgniteSchema publicSchema = createSchema(builder.build());
+
+ assertPlan("SELECT a FROM (SELECT a FROM test ORDER BY a OFFSET 2)",
publicSchema,
+ isInstanceOf(IgniteLimit.class)
+ .and(s -> doubleFromRex(s.offset(), -1) == 2.0)
+ .and(input(isInstanceOf(IgniteExchange.class))
+
.and(hasChildThat(isInstanceOf(IgniteSort.class)
+ ))));
+
+ assertPlan("SELECT a FROM (SELECT a FROM test ORDER BY a OFFSET 2)
t(a) UNION ALL SELECT a FROM test",
+ publicSchema, isInstanceOf(IgniteUnionAll.class)
+ .and(hasChildThat(isInstanceOf(IgniteLimit.class)
+ .and(s -> doubleFromRex(s.offset(), -1) == 2.0)
+ .and(input(isInstanceOf(IgniteExchange.class))
+
.and(hasChildThat(isInstanceOf(IgniteSort.class)
+ .and(s -> s.offset == null)))))
+ )
+ .and(hasChildThat(isInstanceOf(IgniteExchange.class)
+
.and(input(isInstanceOf(IgniteTableScan.class))))
+ ));
+
+ assertPlan("SELECT a FROM (SELECT a FROM test ORDER BY a OFFSET 2)
t(a) UNION ALL SELECT a FROM test ORDER BY a",
+ publicSchema, isInstanceOf(IgniteSort.class)
+ .and(s -> s.offset == null && s.fetch == null)
+ .and(hasChildThat(isInstanceOf(IgniteUnionAll.class)
+
.and(hasChildThat(isInstanceOf(IgniteLimit.class)
+ .and(s -> doubleFromRex(s.offset(),
-1) == 2.0)
+
.and(input(isInstanceOf(IgniteExchange.class))
+
.and(hasChildThat(isInstanceOf(IgniteSort.class)
+ .and(s -> s.offset ==
null)))))
+ )
+
.and(hasChildThat(isInstanceOf(IgniteExchange.class)
+
.and(input(isInstanceOf(IgniteTableScan.class))))
+ )
+ ))
+ );
+ }
+
/**
* Creates PUBLIC schema with one TEST table.
*/
