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 26bd72f9e1 IGNITE-21947: Cover SQL T121(WITH (excluding RECURSIVE) in
query expression) feature by tests (#3730)
26bd72f9e1 is described below
commit 26bd72f9e1f12470d512b790ed6a64a6ba64d08b
Author: ygerzhedovich <[email protected]>
AuthorDate: Mon May 13 08:42:53 2024 +0300
IGNITE-21947: Cover SQL T121(WITH (excluding RECURSIVE) in query
expression) feature by tests (#3730)
---
.../src/integrationTest/sql/cte/test_cte.test | 111 +++++++++++++++++++++
.../integrationTest/sql/cte/test_cte_in_cte.test | 54 ++++++++++
2 files changed, 165 insertions(+)
diff --git a/modules/sql-engine/src/integrationTest/sql/cte/test_cte.test
b/modules/sql-engine/src/integrationTest/sql/cte/test_cte.test
new file mode 100644
index 0000000000..689df3f18d
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/cte/test_cte.test
@@ -0,0 +1,111 @@
+# name: test/sql/cte/test_cte.test
+# description: Test Common Table Expressions (CTE)
+# feature: T121(WITH (excluding RECURSIVE) in query expression).
+# group: [cte]
+
+statement ok
+create table a(i bigint);
+
+statement ok
+insert into a values (42);
+
+query I
+with cte1 as (Select i as j from a) select * from cte1;
+----
+42
+
+query I
+with cte1 as (Select i as j from a) select x from cte1 t1(x);
+----
+42
+
+query I
+with cte1(xxx) as (Select i as j from a) select xxx from cte1;
+----
+42
+
+query I
+with cte1(xxx) as (Select i as j from a) select x from cte1 t1(x);
+----
+42
+
+query II
+with cte1 as (Select i as j from a), cte2 as (select ref.j as k from cte1 as
ref), cte3 as (select ref2.j+1 as i from cte1 as ref2) select * from cte2 ,
cte3;
+----
+42 43
+
+query I
+with cte1 as (select i as j from a), cte2 as (select ref.j as k from cte1 as
ref), cte3 as (select ref2.j+1 as i from cte1 as ref2) select * from cte2 union
all select * FROM cte3;
+----
+42
+43
+
+# duplicate CTE alias
+skipif ignite3
+# https://issues.apache.org/jira/browse/IGNITE-22199
+statement error
+with cte1 as (select 42), cte1 as (select 43) select * FROM cte1;
+----
+
+# reference to CTE before its actually defined
+statement error
+with cte3 as (select ref2.j as i from cte1 as ref2), cte1 as (Select i as j
from a), cte2 as (select ref.j+1 as k from cte1 as ref) select * from cte2
union all select * FROM cte3;
+----
+
+# multiple uses of same CTE
+query II
+with cte1 as (Select i as j from a) select * from cte1 cte11, cte1 cte12;
+----
+42 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
+
+# multi-column name alias
+query II
+with cte1(x, y) as (select 42 a, 84 b) select zzz, y from cte1 t1(zzz,y);
+----
+42 84
+
+# cte in set operation node
+skipif ignite3
+# https://issues.apache.org/jira/browse/IGNITE-22200
+query I
+SELECT 1 UNION ALL (WITH cte AS (SELECT 42) SELECT * FROM cte);
+----
+1
+42
+
+query I
+WITH my_list(val) AS (VALUES (1), (2), (3))
+ SELECT * FROM my_list LIMIT 0 OFFSET 1
+----
+
+statement ok
+create table orders(ordered_at int);
+
+statement ok
+create table stg_orders(ordered_at int);
+
+statement ok
+insert into orders values (1);
+
+statement ok
+insert into stg_orders values (1);
+
+query I
+with
+orders as (
+ select * from stg_orders
+ where ordered_at >= (select max(ordered_at) from orders)
+),
+some_more_logic as (
+ select *
+ from orders
+)
+select * from some_more_logic;
+----
+1
diff --git
a/modules/sql-engine/src/integrationTest/sql/cte/test_cte_in_cte.test
b/modules/sql-engine/src/integrationTest/sql/cte/test_cte_in_cte.test
new file mode 100644
index 0000000000..f369b8b8f7
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/cte/test_cte_in_cte.test
@@ -0,0 +1,54 @@
+# name: test/sql/cte/test_cte_in_cte.test
+# description: Test Nested Common Table Expressions (CTE)
+# feature: T121(WITH (excluding RECURSIVE) in query expression).
+# group: [cte]
+
+statement ok
+create table a(i bigint);
+
+statement ok
+insert into a values (42);
+
+query I
+with cte1 as (Select i as j from a) select * from cte1;
+----
+42
+
+query I
+with cte1 as (with b as (Select i as j from a) Select j from b) select x from
cte1 t1(x);
+----
+42
+
+query I
+with cte1(xxx) as (with ncte(yyy) as (Select i as j from a) Select yyy from
ncte) select xxx from cte1;
+----
+42
+
+query II
+with cte1 as (with b as (Select i as j from a) select j from b), cte2 as (with
c as (select ref.j+1 as k from cte1 as ref) select k from c) select * from cte1
, cte2;
+----
+42 43
+
+
+# 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
+
+# self-refer to non-existent cte
+statement error
+with cte as (select * from cte) select * from cte
+----
