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 db949749e9 IGNITE-21949: Cover SQL T122(WITH (excluding RECURSIVE) in
subquery) feature by tests (#3741)
db949749e9 is described below
commit db949749e9dcf192b632c79cee52f0b0cb07fbc4
Author: ygerzhedovich <[email protected]>
AuthorDate: Mon May 13 12:36:21 2024 +0300
IGNITE-21949: Cover SQL T122(WITH (excluding RECURSIVE) in subquery)
feature by tests (#3741)
---
.../sql/cte/test_cte_in_subquery.test | 34 ++++++
.../scalar/test_correlated_subquery_cte.test | 19 +++
.../test_correlated_subquery_cte.test_ignore | 133 ---------------------
.../subquery/scalar/test_scalar_subquery_cte.test | 1 +
4 files changed, 54 insertions(+), 133 deletions(-)
diff --git
a/modules/sql-engine/src/integrationTest/sql/cte/test_cte_in_subquery.test
b/modules/sql-engine/src/integrationTest/sql/cte/test_cte_in_subquery.test
new file mode 100644
index 0000000000..0fe40280ef
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/cte/test_cte_in_subquery.test
@@ -0,0 +1,34 @@
+# name: test/sql/cte/test_cte_in_subquery.test
+# description: Test Common Table Expressions (CTE)
+# feature: SQL T122(WITH (excluding RECURSIVE) in subquery)
+# group: [cte]
+
+statement ok
+create table a(i bigint);
+
+statement ok
+insert into a values (42);
+
+# refer to CTE in subquery
+query I
+with cte1 as (Select i as j from a) select * from cte1 where j = (select
max(j) from cte1 as cte2);
+----
+42
+
+# refer to CTE in subquery tableref
+query I
+with cte1 as (Select i as j from a) select * from (with cte2 as (select max(j)
as j from cte1) select * from cte2) f
+----
+42
+
+# refer to CTE in subquery expression
+query I
+with cte1 as (Select i as j from a) select * from cte1 where j = (with cte2 as
(select max(j) as j from cte1) select j from cte2);
+----
+42
+
+# refer to same-named CTE in a subquery expression
+query I
+with cte as (Select i as j from a) select * from cte where j = (with cte as
(select max(j) as j from cte) select j from cte);
+----
+42
diff --git
a/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_correlated_subquery_cte.test
b/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_correlated_subquery_cte.test
index 85d444f021..18004afafa 100644
---
a/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_correlated_subquery_cte.test
+++
b/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_correlated_subquery_cte.test
@@ -1,5 +1,6 @@
# name: test/sql/subquery/scalar/test_correlated_subquery_cte.test
# description: Test correlated subqueries
+# feature: SQL T122(WITH (excluding RECURSIVE) in subquery)
# group: [scalar]
statement ok
@@ -20,6 +21,24 @@ SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j) SELECT j FROM
i2) AS j FROM integers
3 45
NULL NULL
+skipif ignite3
+#https://issues.apache.org/jira/browse/IGNITE-15995
+query I
+SELECT * FROM (SELECT 1) ORDER BY (WITH t AS (SELECT 1) SELECT * FROM t)
+----
+1
+
+# ORDER BY correlated subquery
+skipif ignite3
+#https://issues.apache.org/jira/browse/IGNITE-15995
+query I
+SELECT i FROM integers i1 ORDER BY (WITH i2 AS (SELECT 100-i1.i as j) SELECT j
FROM i2);
+----
+3
+2
+1
+NULL
+
# subquery returning multiple results
statement error
SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j FROM integers) SELECT j FROM i2) AS
j FROM integers i1 ORDER BY i;
diff --git
a/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_correlated_subquery_cte.test_ignore
b/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_correlated_subquery_cte.test_ignore
deleted file mode 100644
index 82076bf109..0000000000
---
a/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_correlated_subquery_cte.test_ignore
+++ /dev/null
@@ -1,133 +0,0 @@
-# name: test/sql/subquery/scalar/test_correlated_subquery_cte.test
-# description: Test correlated subqueries
-# group: [scalar]
-# Ignore https://issues.apache.org/jira/browse/IGNITE-15995
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE integers(i INTEGER)
-
-statement ok
-INSERT INTO integers VALUES (1), (2), (3), (NULL)
-
-# scalar select with correlation
-query II
-SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j) SELECT j FROM i2) AS j FROM
integers i1 ORDER BY i;
-----
-NULL NULL
-1 43
-2 44
-3 45
-
-# ORDER BY correlated subquery
-query I
-SELECT i FROM integers i1 ORDER BY (WITH i2 AS (SELECT 100-i1.i as j) SELECT j
FROM i2);
-----
-NULL
-3
-2
-1
-
-# subquery returning multiple results
-statement error
-SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j FROM integers) SELECT j FROM i2) AS
j FROM integers i1 ORDER BY i;
-
-# subquery with LIMIT
-query II
-SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j FROM integers) SELECT j FROM i2
LIMIT 1) AS j FROM integers i1 ORDER BY i;
-----
-NULL NULL
-1 43
-2 44
-3 45
-
-# subquery with LIMIT 0
-query II
-SELECT i, (WITH i2 AS (SELECT 42+i1.i AS j FROM integers) SELECT j FROM i2
LIMIT 0) AS j FROM integers i1 ORDER BY i;
-----
-NULL NULL
-1 NULL
-2 NULL
-3 NULL
-
-# subquery with WHERE clause that is always FALSE
-query II
-SELECT i, (WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i) SELECT i
FROM i2) AS j FROM integers i1 ORDER BY i NULLS FIRST;
-----
-NULL NULL
-1 NULL
-2 NULL
-3 NULL
-
-# correlated EXISTS with WHERE clause that is always FALSE
-query IT
-SELECT i, EXISTS(WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i)
SELECT i FROM i2) AS j FROM integers i1 ORDER BY i;
-----
-NULL false
-1 false
-2 false
-3 false
-
-# correlated ANY with WHERE clause that is always FALSE
-query IT
-SELECT i, i=ANY(WITH i2 AS (SELECT i FROM integers WHERE 1=0 AND i1.i=i)
SELECT i FROM i2) AS j FROM integers i1 ORDER BY i;
-----
-NULL false
-1 false
-2 false
-3 false
-
-# subquery with OFFSET is not supported
-statement error
-SELECT i, (WITH i2 AS (SELECT i+i1.i FROM integers LIMIT 1 OFFSET 1) SELECT *
FROM i2) AS j FROM integers i1 ORDER BY i;
-
-# subquery with ORDER BY is not supported
-statement error
-SELECT i, (WITH i2 AS (SELECT i+i1.i FROM integers ORDER BY 1 LIMIT 1 OFFSET
1) SELECT * FROM i2) AS j FROM integers i1 ORDER BY i;
-
-# correlated filter without FROM clause
-statement error
-SELECT i, (WITH i2 AS (SELECT 42 WHERE i1.i>2) SELECT * FROM i2) AS j FROM
integers i1 ORDER BY i;
-
-# correlated filter with matching entry on NULL
-statement error
-SELECT i, (WITH i2 AS (SELECT 42 WHERE i1.i IS NULL) SELECT * FROM i2) AS j
FROM integers i1 ORDER BY i;
-
-# scalar select with correlation in projection
-query II
-SELECT i, (WITH i2 AS (SELECT i+i1.i FROM integers WHERE i=1) SELECT * FROM
i2) AS j FROM integers i1 ORDER BY i;
-----
-NULL NULL
-1 2
-2 3
-3 4
-
-# scalar select with correlation in filter
-query II
-SELECT i, (WITH i2 AS (SELECT i FROM integers WHERE i=i1.i) SELECT * FROM i2)
AS j FROM integers i1 ORDER BY i;
-----
-NULL NULL
-1 1
-2 2
-3 3
-
-# scalar select with operation in projection
-query II
-SELECT i, (WITH i2 AS (SELECT i+1 FROM integers WHERE i=i1.i) SELECT * FROM
i2) AS j FROM integers i1 ORDER BY i;
-----
-NULL NULL
-1 2
-2 3
-3 4
-
-# correlated scalar select with constant in projection
-query II
-SELECT i, (WITH i2 AS (SELECT 42 FROM integers WHERE i=i1.i) SELECT * FROM i2)
AS j FROM integers i1 ORDER BY i;
-----
-NULL NULL
-1 42
-2 42
-3 42
-
diff --git
a/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_scalar_subquery_cte.test
b/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_scalar_subquery_cte.test
index eeddea07e2..b541013d27 100644
---
a/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_scalar_subquery_cte.test
+++
b/modules/sql-engine/src/integrationTest/sql/subquery/scalar/test_scalar_subquery_cte.test
@@ -1,5 +1,6 @@
# name: test/sql/subquery/scalar/test_scalar_subquery_cte.test
# description: Test subqueries with CTEs
+# feature: SQL T122(WITH (excluding RECURSIVE) in subquery)
# group: [scalar]
statement ok
