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 0edec7a349 IGNITE-21944: Cover SQL F851(<order by clause> in
subqueries) feature by tests (#3773)
0edec7a349 is described below
commit 0edec7a3497fc9d3b3b2be8a1f5165765eaebce4
Author: ygerzhedovich <41903880+ygerzhedov...@users.noreply.github.com>
AuthorDate: Thu May 16 16:34:08 2024 +0300
IGNITE-21944: Cover SQL F851(<order by clause> in subqueries) feature by
tests (#3773)
---
.../integrationTest/sql/order/test_order_by.test | 24 ++-
.../sql/order/test_order_by.test_ignore | 174 ---------------------
.../sql/order/test_order_by_in_subquery.test | 60 +++++++
3 files changed, 78 insertions(+), 180 deletions(-)
diff --git
a/modules/sql-engine/src/integrationTest/sql/order/test_order_by.test
b/modules/sql-engine/src/integrationTest/sql/order/test_order_by.test
index 5ad4666470..810c26745b 100644
--- a/modules/sql-engine/src/integrationTest/sql/order/test_order_by.test
+++ b/modules/sql-engine/src/integrationTest/sql/order/test_order_by.test
@@ -106,6 +106,13 @@ SELECT b, a FROM test WHERE a < 13 ORDER BY b DESC;
22 11
21 12
+# order by expression
+query IR
+SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY b % 2;
+----
+0 24
+1 12
+
# order by expression that is not in SELECT
query II
SELECT b % 2 AS f, a FROM test ORDER BY b % 4, a;
@@ -114,10 +121,15 @@ SELECT b % 2 AS f, a FROM test ORDER BY b % 4, a;
0 11
0 13
-# ORDER BY after union
-query I
-SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY k;
+# ORDER BY alias
+query IR
+SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY f;
----
-1
-2
-3
+0 24
+1 12
+
+query IR
+SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY 1;
+----
+0 24
+1 12
diff --git
a/modules/sql-engine/src/integrationTest/sql/order/test_order_by.test_ignore
b/modules/sql-engine/src/integrationTest/sql/order/test_order_by.test_ignore
deleted file mode 100644
index fdc6a5b0db..0000000000
--- a/modules/sql-engine/src/integrationTest/sql/order/test_order_by.test_ignore
+++ /dev/null
@@ -1,174 +0,0 @@
-# name: test/sql/order/test_order_by.test
-# description: Test ORDER BY keyword
-# group: [order]
-# Ignored: https://issues.apache.org/jira/browse/IGNITE-14885
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE test (a INTEGER, b INTEGER);
-
-statement ok
-INSERT INTO test VALUES (11, 22), (12, 21), (13, 22);
-
-# simple ORDER BY
-query I
-SELECT b FROM test ORDER BY a DESC;
-----
-22
-21
-22
-
-query II
-SELECT a, b FROM test ORDER BY a;
-----
-11 22
-12 21
-13 22
-
-query II
-SELECT a, b FROM test ORDER BY a DESC;
-----
-13 22
-12 21
-11 22
-
-# ORDER BY on multiple columns
-query II
-SELECT a, b FROM test ORDER BY b, a;
-----
-12 21
-11 22
-13 22
-
-# ORDER BY using select indices
-query II
-SELECT a, b FROM test ORDER BY 2, 1;
-----
-12 21
-11 22
-13 22
-
-query II
-SELECT a, b FROM test ORDER BY b DESC, a;
-----
-11 22
-13 22
-12 21
-
-query II
-SELECT a, b FROM test ORDER BY b, a DESC;
-----
-12 21
-13 22
-11 22
-
-# TOP N queries
-query II
-SELECT a, b FROM test ORDER BY b, a DESC LIMIT 1;
-----
-12 21
-
-# Offset
-query II
-SELECT a, b FROM test ORDER BY b, a DESC LIMIT 1 OFFSET 1;
-----
-13 22
-
-# Offset without limit
-query II
-SELECT a, b FROM test ORDER BY b, a DESC OFFSET 1;
-----
-13 22
-11 22
-
-query II
-SELECT a, b FROM test WHERE a < 13 ORDER BY b;
-----
-12 21
-11 22
-
-query II
-SELECT a, b FROM test WHERE a < 13 ORDER BY 2;
-----
-12 21
-11 22
-
-query II
-SELECT a, b FROM test WHERE a < 13 ORDER BY b DESC;
-----
-11 22
-12 21
-
-query II
-SELECT b, a FROM test WHERE a < 13 ORDER BY b DESC;
-----
-22 11
-21 12
-
-# order by expression
-query IR
-SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY b % 2;
-----
-0 24.000000
-1 12.000000
-
-# order by expression that is not in SELECT
-query II
-SELECT b % 2 AS f, a FROM test ORDER BY b % 4, a;
-----
-1 12
-0 11
-0 13
-
-# ORDER BY alias
-query IR
-SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY f;
-----
-0 24.000000
-1 12.000000
-
-query IR
-SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY 1;
-----
-0 24.000000
-1 12.000000
-
-# ORDER BY after union
-query I
-SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY k;
-----
-1
-2
-3
-
-# ORDER BY on alias in right-most query
-# CONTROVERSIAL: SQLite allows both "k" and "l" to be referenced here,
Postgres and MonetDB give an error.
-query I
-SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY l;
-----
-1
-2
-3
-
-# computations with aliases are not allowed though
-statement error
-SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY 1-k;
-
-# but ordering on computation elements should work
-query I
-SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY a-10;
-----
-1
-2
-3
-
-query I
-SELECT a-10 AS k FROM test UNION SELECT a-11 AS l FROM test ORDER BY a-11;
-----
-0
-1
-2
-3
-
diff --git
a/modules/sql-engine/src/integrationTest/sql/order/test_order_by_in_subquery.test
b/modules/sql-engine/src/integrationTest/sql/order/test_order_by_in_subquery.test
new file mode 100644
index 0000000000..355358f792
--- /dev/null
+++
b/modules/sql-engine/src/integrationTest/sql/order/test_order_by_in_subquery.test
@@ -0,0 +1,60 @@
+# name: test/sql/order/test_order_by_in_subquery.test
+# description: SQL F851(<order by clause> in subqueries)
+# feature: F851
+# group: [order]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE test (a BIGINT, b BIGINT);
+
+statement ok
+INSERT INTO test VALUES (11, 22), (12, 21), (13, 22);
+
+# ORDER BY in subquery
+query II
+SELECT t1.a, (SELECT b FROM test WHERE a > t1.a ORDER BY b LIMIT 1) FROM test
t1 ORDER BY t1.a
+----
+11 21
+12 22
+13 NULL
+
+# ORDER BY after union
+query I
+SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY k;
+----
+1
+2
+3
+
+# ORDER BY on alias in right-most query should fail due to SQL standard say
that ORDER by will be apply to whole query in this case.
+statement error
+SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY l
+
+# ORDER BY on alias in right-most query but related just to the subquery.
+query I
+SELECT a-10 AS k FROM test UNION (SELECT a-10 AS l FROM test ORDER BY l)
+----
+1
+2
+3
+
+# computations with aliases are allowed
+query I
+SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY 1-k;
+----
+3
+2
+1
+
+# ORDER BY in left query should fail with parse error.
+statement error
+SELECT a-10 AS k FROM test ORDER by k UNION SELECT a-10 AS l FROM test;
+
+# ordering on computation elements with absent element in result query should
fail
+statement error
+SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY a-10;
+
+statement error
+SELECT a-10 AS k FROM test UNION SELECT a-11 AS l FROM test ORDER BY a-11;
