create table a(i integer, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

insert into a values (42, 1);

Affected Rows: 1

with cte1 as (Select i as j from a) select * from cte1;

+----+
| j  |
+----+
| 42 |
+----+

with cte1 as (Select i as j from a) select x from cte1 t1(x);

+----+
| x  |
+----+
| 42 |
+----+

with cte1(xxx) as (Select i as j from a) select xxx from cte1;

+-----+
| xxx |
+-----+
| 42  |
+-----+

with cte1(xxx) as (Select i as j from a) select x from cte1 t1(x);

+----+
| x  |
+----+
| 42 |
+----+

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;

+----+----+
| k  | i  |
+----+----+
| 42 | 43 |
+----+----+

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 order by 1;

+----+
| k  |
+----+
| 42 |
| 43 |
+----+

with cte1 as (select 42), cte1 as (select 42) select * FROM cte1;

Error: 3000(PlanQuery), Failed to plan SQL: sql parser error: WITH query name "cte1" specified more than once

-- reference to CTE before its actually defined, it's not supported by datafusion
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;

Error: 3000(PlanQuery), Failed to plan SQL: Error during planning: Table not found: greptime.public.cte1

with cte1 as (Select i as j from a) select * from cte1 cte11, cte1 cte12;

+----+----+
| j  | j  |
+----+----+
| 42 | 42 |
+----+----+

with cte1 as (Select i as j from a) select * from cte1 where j = (select max(j) from cte1 as cte2);

+----+
| j  |
+----+
| 42 |
+----+

with cte1(x, y) as (select 42 a, 84 b) select zzz, y from cte1 t1(zzz, y);

+-----+----+
| zzz | y  |
+-----+----+
| 42  | 84 |
+-----+----+

SELECT 1 UNION ALL (WITH cte AS (SELECT 42) SELECT * FROM cte) order by 1;

+----------+
| Int64(1) |
+----------+
| 1        |
| 42       |
+----------+

-- Recursive CTEs are not supported in datafusion
WITH RECURSIVE cte(d) AS (
		SELECT 1
	UNION ALL
		(WITH c(d) AS (SELECT * FROM cte)
			SELECT d + 1
			FROM c
			WHERE FALSE
		)
)
SELECT max(d) FROM cte;

Error: 3000(PlanQuery), Failed to plan SQL: This feature is not implemented: Recursive CTEs are not supported

-- Nested aliases is not supported in datafusion
with cte (a) as (
    select 1
)
select
    a as alias1,
    alias1 as alias2
from cte
where alias2 > 0;

Error: 3000(PlanQuery), Failed to plan SQL: No field named alias2. Valid fields are cte.a.

drop table a;

Affected Rows: 0