greptimedb/tests/cases/standalone/common/cte/cte.result

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 |
+----+----+

-- SQLNESS SORT_RESULT 3 1
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: Error during planning: 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: 4001(TableNotFound), Failed to plan SQL: 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       |
+----------+

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;

+------------+
| max(cte.d) |
+------------+
| 1          |
+------------+

-- the recursive term is re-executed once per iteration, so its scans must be re-runnable
WITH RECURSIVE t AS (
		SELECT 0 AS depth
	UNION ALL
		SELECT t.depth + 1 AS depth
		FROM t, information_schema.tables
		WHERE table_catalog = 'greptime'
			AND table_schema = 'information_schema'
			AND table_name = 'tables'
			AND t.depth < 2
)
SELECT depth FROM t ORDER BY depth;

+-------+
| depth |
+-------+
| 0     |
| 1     |
| 2     |
+-------+

-- 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

CREATE TABLE grpc_latencies
(
    ts      TIMESTAMP TIME INDEX,
    host    VARCHAR(255),
    latency FLOAT,
    PRIMARY KEY (host),
);

Affected Rows: 0

INSERT INTO grpc_latencies
VALUES ('2023-10-01 10:00:00', 'host1', 120),
       ('2023-10-01 10:00:00', 'host2', 150),
       ('2023-10-01 10:00:05', 'host1', 130);

Affected Rows: 3

WITH latencies AS (SELECT ts,
                          host,
                          AVG(latency) RANGE '2s' AS avg_latency
                   FROM grpc_latencies ALIGN '2s' BY (host) FILL PREV
    )
SELECT latencies.ts,
       AVG(latencies.avg_latency)
FROM latencies
GROUP BY latencies.ts ORDER BY latencies.ts;

+---------------------+----------------------------+
| ts                  | avg(latencies.avg_latency) |
+---------------------+----------------------------+
| 2023-10-01T10:00:00 | 135.0                      |
| 2023-10-01T10:00:02 | 120.0                      |
| 2023-10-01T10:00:04 | 130.0                      |
+---------------------+----------------------------+

DROP TABLE grpc_latencies;

Affected Rows: 0