mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-03 20:02:54 +00:00
56fc77e573
* fix: add missing error display message Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * update sqlness result Signed-off-by: Ruihang Xia <waynestxia@gmail.com> --------- Signed-off-by: Ruihang Xia <waynestxia@gmail.com>
130 lines
2.6 KiB
Plaintext
130 lines
2.6 KiB
Plaintext
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
|
|
|