mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-14 12:00:40 +00:00
d1151b665b
* feat: flow tql cte Signed-off-by: discord9 <discord9@163.com> * fix: creating flow TQL CTE source tables lose cte part in query Signed-off-by: discord9 <discord9@163.com> * test: update sqlness result Signed-off-by: discord9 <discord9@163.com> * chore Signed-off-by: discord9 <discord9@163.com> * fix: properly canonicalize ident Signed-off-by: discord9 <discord9@163.com> * feat: even stricter check Signed-off-by: discord9 <discord9@163.com> * chore: sqlness update Signed-off-by: discord9 <discord9@163.com> * chore: after rebase fix Signed-off-by: discord9 <discord9@163.com> --------- Signed-off-by: discord9 <discord9@163.com>
85 lines
2.5 KiB
SQL
85 lines
2.5 KiB
SQL
CREATE TABLE metric_cte (
|
|
ts timestamp(3) time index,
|
|
val DOUBLE,
|
|
);
|
|
|
|
INSERT INTO TABLE metric_cte VALUES
|
|
(0::Timestamp, 0),
|
|
(5000::Timestamp, 1),
|
|
(10000::Timestamp, 2),
|
|
(15000::Timestamp, 3);
|
|
|
|
CREATE FLOW calc_cte SINK TO metric_cte_sink EVAL INTERVAL '1m' AS
|
|
WITH tql (ts, the_value) AS (
|
|
TQL EVAL (now() - now(), now() - (now() - '15s'::interval), '5s') metric_cte
|
|
)
|
|
SELECT * FROM tql;
|
|
|
|
CREATE TABLE tql (
|
|
ts timestamp(3) time index,
|
|
val DOUBLE,
|
|
);
|
|
|
|
INSERT INTO TABLE tql VALUES
|
|
(0::Timestamp, 10),
|
|
(5000::Timestamp, 20),
|
|
(10000::Timestamp, 30),
|
|
(15000::Timestamp, 40);
|
|
|
|
-- Fail due to case sensitivity of CTE name
|
|
CREATE FLOW calc_cte_case SINK TO metric_cte_join_sink EVAL INTERVAL '1m' AS
|
|
WITH "TQL"(ts, the_value) AS (
|
|
TQL EVAL (now() - now(), now() - (now() - '15s'::interval), '5s') metric_cte
|
|
)
|
|
SELECT * FROM TQL;
|
|
|
|
CREATE FLOW calc_cte_non_star SINK TO metric_cte_non_star_sink EVAL INTERVAL '1m' AS
|
|
WITH tql(ts, the_value) AS (
|
|
TQL EVAL (now() - now(), now() - (now() - '15s'::interval), '5s') metric_cte
|
|
)
|
|
SELECT ts FROM tql;
|
|
|
|
CREATE FLOW calc_cte_filter SINK TO metric_cte_filter_sink EVAL INTERVAL '1m' AS
|
|
WITH tql(ts, the_value) AS (
|
|
TQL EVAL (now() - now(), now() - (now() - '15s'::interval), '5s') metric_cte
|
|
)
|
|
SELECT * FROM tql WHERE ts > 0::timestamp;
|
|
|
|
CREATE FLOW calc_cte_mixed SINK TO metric_cte_mixed_sink EVAL INTERVAL '1m' AS
|
|
WITH s1 AS (SELECT 1),
|
|
tql(ts, the_value) AS (
|
|
TQL EVAL (now() - now(), now() - (now() - '15s'::interval), '5s') metric_cte
|
|
)
|
|
SELECT * FROM tql;
|
|
|
|
CREATE FLOW calc_cte_case SINK TO metric_cte_join_sink EVAL INTERVAL '1m' AS
|
|
WITH "TQL"(ts, the_value) AS (
|
|
TQL EVAL (now() - now(), now() - (now() - '15s'::interval), '5s') metric_cte
|
|
)
|
|
SELECT * FROM "TQL";
|
|
|
|
SHOW CREATE TABLE metric_cte_sink;
|
|
|
|
SELECT source_table_names FROM information_schema.flows WHERE flow_name = 'calc_cte';
|
|
|
|
SHOW CREATE TABLE metric_cte_join_sink;
|
|
|
|
SELECT source_table_names FROM information_schema.flows WHERE flow_name = 'calc_cte_case';
|
|
|
|
-- SQLNESS REPLACE (ADMIN\sFLUSH_FLOW\('\w+'\)\s+\|\n\+-+\+\n\|\s+)[0-9]+\s+\| $1 FLOW_FLUSHED |
|
|
ADMIN FLUSH_FLOW('calc_cte');
|
|
|
|
SELECT * FROM metric_cte_sink ORDER BY ts;
|
|
|
|
-- SQLNESS REPLACE (ADMIN\sFLUSH_FLOW\('\w+'\)\s+\|\n\+-+\+\n\|\s+)[0-9]+\s+\| $1 FLOW_FLUSHED |
|
|
ADMIN FLUSH_FLOW('calc_cte_case');
|
|
|
|
SELECT * FROM metric_cte_join_sink ORDER BY ts;
|
|
|
|
DROP FLOW calc_cte;
|
|
DROP FLOW calc_cte_case;
|
|
DROP TABLE metric_cte;
|
|
DROP TABLE tql;
|
|
DROP TABLE metric_cte_sink;
|
|
DROP TABLE metric_cte_join_sink;
|