mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-09 14:52:58 +00:00
288f69a30f
* fix: plan disorder from upgrading datafusion Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * update sqlness Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * update sqlness again Signed-off-by: Ruihang Xia <waynestxia@gmail.com> --------- Signed-off-by: Ruihang Xia <waynestxia@gmail.com>
308 lines
11 KiB
Plaintext
308 lines
11 KiB
Plaintext
-- test if handle aliased sort expr correctly
|
|
CREATE TABLE IF NOT EXISTS lightning (
|
|
collect_time TIMESTAMP(9) NOT NULL,
|
|
collect_time_utc TIMESTAMP(9) NULL,
|
|
peak_current FLOAT NULL,
|
|
TIME INDEX (collect_time)
|
|
)
|
|
ENGINE=mito
|
|
WITH(
|
|
'compaction.twcs.time_window' = '7d',
|
|
'compaction.type' = 'twcs'
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
-- insert some data, with collect_time = collect_time_utc + 8 hour
|
|
INSERT INTO lightning VALUES
|
|
('2025-03-01 16:00:00', '2025-03-01 08:00:00', 1.0),
|
|
('2025-03-01 17:00:00', '2025-03-01 09:00:00', 1.0),
|
|
('2025-03-01 18:00:00', '2025-03-01 10:00:00', 1.0),
|
|
('2025-03-01 19:00:00', '2025-03-01 11:00:00', 1.0),
|
|
('2025-03-01 20:00:00', '2025-03-01 12:00:00', 1.0),
|
|
('2025-03-01 21:00:00', '2025-03-01 13:00:00', 1.0),
|
|
('2025-03-01 22:00:00', '2025-03-01 14:00:00', 1.0),
|
|
('2025-03-01 23:00:00', '2025-03-01 15:00:00', 1.0)
|
|
;
|
|
|
|
Affected Rows: 8
|
|
|
|
-- notice the alias make order by not applicable for window sort
|
|
-- note due to alias there is a tiny difference in the output between standalone/distributed
|
|
-- which is acceptable
|
|
SELECT
|
|
collect_time_utc AS collect_time,
|
|
peak_current,
|
|
FROM
|
|
lightning
|
|
ORDER BY
|
|
collect_time ASC;
|
|
|
|
+---------------------+--------------+
|
|
| collect_time | peak_current |
|
|
+---------------------+--------------+
|
|
| 2025-03-01T08:00:00 | 1.0 |
|
|
| 2025-03-01T09:00:00 | 1.0 |
|
|
| 2025-03-01T10:00:00 | 1.0 |
|
|
| 2025-03-01T11:00:00 | 1.0 |
|
|
| 2025-03-01T12:00:00 | 1.0 |
|
|
| 2025-03-01T13:00:00 | 1.0 |
|
|
| 2025-03-01T14:00:00 | 1.0 |
|
|
| 2025-03-01T15:00:00 | 1.0 |
|
|
+---------------------+--------------+
|
|
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
-- SQLNESS REPLACE (metrics.*) REDACTED
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
EXPLAIN ANALYZE SELECT
|
|
collect_time_utc AS collect_time,
|
|
peak_current,
|
|
FROM
|
|
lightning
|
|
ORDER BY
|
|
collect_time ASC;
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [collect_time@0 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[collect_time@0 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_ProjectionExec: expr=[collect_time_utc@0 as collect_time, peak_current@1 as peak_current] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0} REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 8_|
|
|
+-+-+-+
|
|
|
|
-- also try alias with different name with time index
|
|
SELECT
|
|
collect_time_utc AS collect_time_0,
|
|
peak_current,
|
|
FROM
|
|
lightning
|
|
ORDER BY
|
|
collect_time_0 ASC;
|
|
|
|
+---------------------+--------------+
|
|
| collect_time_0 | peak_current |
|
|
+---------------------+--------------+
|
|
| 2025-03-01T08:00:00 | 1.0 |
|
|
| 2025-03-01T09:00:00 | 1.0 |
|
|
| 2025-03-01T10:00:00 | 1.0 |
|
|
| 2025-03-01T11:00:00 | 1.0 |
|
|
| 2025-03-01T12:00:00 | 1.0 |
|
|
| 2025-03-01T13:00:00 | 1.0 |
|
|
| 2025-03-01T14:00:00 | 1.0 |
|
|
| 2025-03-01T15:00:00 | 1.0 |
|
|
+---------------------+--------------+
|
|
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
-- SQLNESS REPLACE (metrics.*) REDACTED
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
EXPLAIN ANALYZE SELECT
|
|
collect_time_utc AS collect_time_0,
|
|
peak_current,
|
|
FROM
|
|
lightning
|
|
ORDER BY
|
|
collect_time_0 ASC;
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [collect_time_0@0 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[collect_time_0@0 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_ProjectionExec: expr=[collect_time_utc@0 as collect_time_0, peak_current@1 as peak_current] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0} REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 8_|
|
|
+-+-+-+
|
|
|
|
-- try more complex alias with time index
|
|
SELECT
|
|
collect_time AS true_collect_time,
|
|
collect_time_utc AS collect_time,
|
|
peak_current,
|
|
FROM
|
|
lightning
|
|
ORDER BY
|
|
true_collect_time DESC;
|
|
|
|
+---------------------+---------------------+--------------+
|
|
| true_collect_time | collect_time | peak_current |
|
|
+---------------------+---------------------+--------------+
|
|
| 2025-03-01T23:00:00 | 2025-03-01T15:00:00 | 1.0 |
|
|
| 2025-03-01T22:00:00 | 2025-03-01T14:00:00 | 1.0 |
|
|
| 2025-03-01T21:00:00 | 2025-03-01T13:00:00 | 1.0 |
|
|
| 2025-03-01T20:00:00 | 2025-03-01T12:00:00 | 1.0 |
|
|
| 2025-03-01T19:00:00 | 2025-03-01T11:00:00 | 1.0 |
|
|
| 2025-03-01T18:00:00 | 2025-03-01T10:00:00 | 1.0 |
|
|
| 2025-03-01T17:00:00 | 2025-03-01T09:00:00 | 1.0 |
|
|
| 2025-03-01T16:00:00 | 2025-03-01T08:00:00 | 1.0 |
|
|
+---------------------+---------------------+--------------+
|
|
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
-- SQLNESS REPLACE (metrics.*) REDACTED
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
|
|
EXPLAIN ANALYZE SELECT
|
|
collect_time AS true_collect_time,
|
|
collect_time_utc AS collect_time,
|
|
peak_current,
|
|
FROM
|
|
lightning
|
|
ORDER BY
|
|
true_collect_time DESC;
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [true_collect_time@0 DESC] REDACTED
|
|
|_|_|_WindowedSortExec: expr=true_collect_time@0 DESC num_ranges=REDACTED REDACTED
|
|
|_|_|_PartSortExec: expr=true_collect_time@0 DESC num_ranges=REDACTED REDACTED
|
|
|_|_|_ProjectionExec: expr=[collect_time@0 as true_collect_time, collect_time_utc@1 as collect_time, peak_current@2 as peak_current] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0} REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 8_|
|
|
+-+-+-+
|
|
|
|
-- this should also do windowed sort
|
|
SELECT
|
|
collect_time_utc AS collect_time,
|
|
collect_time AS true_collect_time,
|
|
peak_current,
|
|
FROM
|
|
lightning
|
|
ORDER BY
|
|
true_collect_time DESC;
|
|
|
|
+---------------------+---------------------+--------------+
|
|
| collect_time | true_collect_time | peak_current |
|
|
+---------------------+---------------------+--------------+
|
|
| 2025-03-01T15:00:00 | 2025-03-01T23:00:00 | 1.0 |
|
|
| 2025-03-01T14:00:00 | 2025-03-01T22:00:00 | 1.0 |
|
|
| 2025-03-01T13:00:00 | 2025-03-01T21:00:00 | 1.0 |
|
|
| 2025-03-01T12:00:00 | 2025-03-01T20:00:00 | 1.0 |
|
|
| 2025-03-01T11:00:00 | 2025-03-01T19:00:00 | 1.0 |
|
|
| 2025-03-01T10:00:00 | 2025-03-01T18:00:00 | 1.0 |
|
|
| 2025-03-01T09:00:00 | 2025-03-01T17:00:00 | 1.0 |
|
|
| 2025-03-01T08:00:00 | 2025-03-01T16:00:00 | 1.0 |
|
|
+---------------------+---------------------+--------------+
|
|
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
-- SQLNESS REPLACE (metrics.*) REDACTED
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
|
|
EXPLAIN ANALYZE SELECT
|
|
collect_time_utc AS collect_time,
|
|
collect_time AS true_collect_time,
|
|
peak_current,
|
|
FROM
|
|
lightning
|
|
ORDER BY
|
|
true_collect_time DESC;
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [true_collect_time@1 DESC] REDACTED
|
|
|_|_|_WindowedSortExec: expr=true_collect_time@1 DESC num_ranges=REDACTED REDACTED
|
|
|_|_|_PartSortExec: expr=true_collect_time@1 DESC num_ranges=REDACTED REDACTED
|
|
|_|_|_ProjectionExec: expr=[collect_time_utc@1 as collect_time, collect_time@0 as true_collect_time, peak_current@2 as peak_current] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0} REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 8_|
|
|
+-+-+-+
|
|
|
|
DROP TABLE lightning;
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE IF NOT EXISTS `instance_job_metrics` (
|
|
`greptime_timestamp` TIMESTAMP(3) NOT NULL,
|
|
`greptime_value` DOUBLE NULL,
|
|
`instance` STRING NULL,
|
|
`job` STRING NULL,
|
|
TIME INDEX (`greptime_timestamp`),
|
|
PRIMARY KEY (`instance`, `job`)
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO `instance_job_metrics` VALUES
|
|
('2023-10-01 00:00:01.000', 1696118400.0, 'node1', 'job1'),
|
|
('2023-10-01 00:00:02.000', 1696118400.0, 'node2', 'job1'),
|
|
('2023-10-01 00:00:03.000', 1696118400.0, 'node3', 'job2');
|
|
|
|
Affected Rows: 3
|
|
|
|
TQL EVAL('2023-10-01 00:00:00.000'::TIMESTAMP, '2023-10-01 00:00:05.000'::TIMESTAMP, '1s') sum(instance_job_metrics);
|
|
|
|
+---------------------+------------------------------------------+
|
|
| greptime_timestamp | sum(instance_job_metrics.greptime_value) |
|
|
+---------------------+------------------------------------------+
|
|
| 2023-10-01T00:00:01 | 1696118400.0 |
|
|
| 2023-10-01T00:00:02 | 3392236800.0 |
|
|
| 2023-10-01T00:00:03 | 5088355200.0 |
|
|
| 2023-10-01T00:00:04 | 5088355200.0 |
|
|
| 2023-10-01T00:00:05 | 5088355200.0 |
|
|
+---------------------+------------------------------------------+
|
|
|
|
-- SQLNESS REPLACE (metrics.*) REDACTED
|
|
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
TQL ANALYZE('2023-10-01 00:00:00.000'::TIMESTAMP, '2023-10-01 00:00:05.000'::TIMESTAMP, '1s') sum(instance_job_metrics);
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [greptime_timestamp@0 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[greptime_timestamp@0 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[greptime_timestamp@0 as greptime_timestamp], aggr=[sum(instance_job_REDACTED
|
|
|_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[greptime_timestamp@0 as greptime_timestamp], aggr=[sum(instance_job_REDACTED
|
|
|_|_|_ProjectionExec: expr=[greptime_timestamp@0 as greptime_timestamp, greptime_value@1 as greptime_value] REDACTED
|
|
|_|_|_PromInstantManipulateExec: range=[1696118400000..1696118405000], lookback=[300000], interval=[1000], time index=[greptime_timestamp] REDACTED
|
|
|_|_|_PromSeriesDivideExec: tags=["instance", "job"] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeriesScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0}, "distribution":"PerSeries" REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 5_|
|
|
+-+-+-+
|
|
|
|
DROP TABLE IF EXISTS `instance_job_metrics`;
|
|
|
|
Affected Rows: 0
|
|
|