create table t ( ts timestamp time index, host string primary key, not_pk string, val double, ) with (append_mode='true'); Affected Rows: 0 insert into t values (0, 'a', '🌕', 1.0), (1, 'b', '🌖', 2.0), (2, 'a', '🌗', 3.0), (3, 'c', '🌘', 4.0), (4, 'a', '🌑', 5.0), (5, 'b', '🌒', 6.0), (6, 'a', '🌓', 7.0), (7, 'c', '🌔', 8.0), (8, 'd', '🌕', 9.0); Affected Rows: 9 admin flush_table('t'); +------------------------+ | ADMIN flush_table('t') | +------------------------+ | 0 | +------------------------+ select first_value(host order by ts), first_value(not_pk order by ts), first_value(val order by ts) from t group by host order by host; +----------------------------------------------------+------------------------------------------------------+---------------------------------------------------+ | first_value(t.host) ORDER BY [t.ts ASC NULLS LAST] | first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST] | first_value(t.val) ORDER BY [t.ts ASC NULLS LAST] | +----------------------------------------------------+------------------------------------------------------+---------------------------------------------------+ | a | 🌕 | 1.0 | | b | 🌖 | 2.0 | | c | 🌘 | 4.0 | | d | 🌕 | 9.0 | +----------------------------------------------------+------------------------------------------------------+---------------------------------------------------+ -- repeat the query again, ref: https://github.com/GreptimeTeam/greptimedb/issues/4650 select first_value(host order by ts), first_value(not_pk order by ts), first_value(val order by ts) from t group by host order by host; +----------------------------------------------------+------------------------------------------------------+---------------------------------------------------+ | first_value(t.host) ORDER BY [t.ts ASC NULLS LAST] | first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST] | first_value(t.val) ORDER BY [t.ts ASC NULLS LAST] | +----------------------------------------------------+------------------------------------------------------+---------------------------------------------------+ | a | 🌕 | 1.0 | | b | 🌖 | 2.0 | | c | 🌘 | 4.0 | | d | 🌕 | 9.0 | +----------------------------------------------------+------------------------------------------------------+---------------------------------------------------+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED explain select first_value(host order by ts), first_value(not_pk order by ts), first_value(val order by ts) from t group by host; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| MergeScan [is_placeholder=false, remote_input=[_| |_| Projection: first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]_| |_|_Aggregate: groupBy=[[t.host]], aggr=[[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]]] | |_|_TableScan: t_| |_| ]]_| | physical_plan | CooperativeExec_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (elapsed_compute.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (metrics.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED -- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED explain analyze select first_value(host order by ts), first_value(not_pk order by ts), first_value(val order by ts) from t group by host; +-+-+-+ | stage | node | plan_| +-+-+-+ | 0_| 0_|_CooperativeExec REDACTED |_|_|_MergeScanExec: REDACTED |_|_|_| | 1_| 0_|_ProjectionExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@1 as first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST]@2 as first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]@3 as first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_AggregateExec: mode=FinalPartitioned, gby=[host@0 as host], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[host@1 as host], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_UnorderedScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":0, "files":1, "file_ranges":1} REDACTED |_|_|_| |_|_| Total rows: 4_| +-+-+-+ select first_value(ts order by ts) from t; +--------------------------------------------------+ | first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST] | +--------------------------------------------------+ | 1970-01-01T00:00:00 | +--------------------------------------------------+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED explain select first_value(ts order by ts) from t; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| MergeScan [is_placeholder=false, remote_input=[_| |_| Projection: first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]_| |_|_Aggregate: groupBy=[[]], aggr=[[first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]]] | |_|_TableScan: t_| |_| ]]_| | physical_plan | CooperativeExec_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (elapsed_compute.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (metrics.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED -- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED explain analyze select first_value(ts order by ts) from t; +-+-+-+ | stage | node | plan_| +-+-+-+ | 0_| 0_|_CooperativeExec REDACTED |_|_|_MergeScanExec: REDACTED |_|_|_| | 1_| 0_|_AggregateExec: mode=Final, gby=[], aggr=[first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalescePartitionsExec REDACTED |_|_|_AggregateExec: mode=Partial, gby=[], aggr=[first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_UnorderedScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":0, "files":1, "file_ranges":1} REDACTED |_|_|_| |_|_| Total rows: 1_| +-+-+-+ drop table t; Affected Rows: 0 create table t ( ts timestamp time index, host string primary key, not_pk string, val double, ) PARTITION ON COLUMNS (host) ( host < 'b', host >= 'b' AND host < 'd', host >= 'd' ); Affected Rows: 0 insert into t values (0, 'a', '🌕', 1.0), (1, 'b', '🌖', 2.0), (2, 'a', '🌗', 3.0), (3, 'c', '🌘', 4.0), (4, 'a', '🌑', 5.0), (5, 'b', '🌒', 6.0), (6, 'a', '🌓', 7.0), (7, 'c', '🌔', 8.0), (8, 'd', '🌕', 9.0); Affected Rows: 9 select first_value(host order by ts) as ordered_host, first_value(not_pk order by ts), first_value(val order by ts) from t group by host order by ordered_host; +--------------+------------------------------------------------------+---------------------------------------------------+ | ordered_host | first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST] | first_value(t.val) ORDER BY [t.ts ASC NULLS LAST] | +--------------+------------------------------------------------------+---------------------------------------------------+ | a | 🌕 | 1.0 | | b | 🌖 | 2.0 | | c | 🌘 | 4.0 | | d | 🌕 | 9.0 | +--------------+------------------------------------------------------+---------------------------------------------------+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED explain select first_value(host order by ts) as ordered_host, first_value(not_pk order by ts), first_value(val order by ts) from t group by host order by ordered_host; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| MergeSort: ordered_host ASC NULLS LAST_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Sort: ordered_host ASC NULLS LAST_| |_|_Projection: first_value(t.host) ORDER BY [t.ts ASC NULLS LAST] AS ordered_host, first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]_| |_|_Aggregate: groupBy=[[t.host]], aggr=[[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]]] | |_|_TableScan: t_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [ordered_host@0 ASC NULLS LAST]_| |_|_CooperativeExec_| |_|_CooperativeExec_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (elapsed_compute.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (metrics.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED -- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED -- might write to different partitions -- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED explain analyze select first_value(host order by ts) as ordered_host, first_value(not_pk order by ts), first_value(val order by ts) from t group by host order by ordered_host; +-+-+-+ | stage | node | plan_| +-+-+-+ | 0_| 0_|_SortPreservingMergeExec: [ordered_host@0 ASC NULLS LAST] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_CooperativeExec REDACTED |_|_|_MergeScanExec: REDACTED |_|_|_| | 1_| 0_|_ProjectionExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@0 as ordered_host, first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST]@1 as first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]@2 as first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_SortPreservingMergeExec: [first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@0 ASC NULLS LAST] REDACTED |_|_|_SortExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@0 ASC NULLS LAST], preserve_REDACTED |_|_|_ProjectionExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@1 as first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST]@2 as first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]@3 as first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_AggregateExec: mode=FinalPartitioned, gby=[host@0 as host], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[host@1 as host], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 1_|_ProjectionExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@0 as ordered_host, first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST]@1 as first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]@2 as first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_SortPreservingMergeExec: [first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@0 ASC NULLS LAST] REDACTED |_|_|_SortExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@0 ASC NULLS LAST], preserve_REDACTED |_|_|_ProjectionExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@1 as first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST]@2 as first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]@3 as first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_AggregateExec: mode=FinalPartitioned, gby=[host@0 as host], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[host@1 as host], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 2_|_ProjectionExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@0 as ordered_host, first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST]@1 as first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]@2 as first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_SortPreservingMergeExec: [first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@0 ASC NULLS LAST] REDACTED |_|_|_SortExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@0 ASC NULLS LAST], preserve_REDACTED |_|_|_ProjectionExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@1 as first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST]@2 as first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]@3 as first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_AggregateExec: mode=FinalPartitioned, gby=[host@0 as host], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[host@1 as host], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.not_pk) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| |_|_| Total rows: 4_| +-+-+-+ select first_value(ts order by ts) from t; +--------------------------------------------------+ | first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST] | +--------------------------------------------------+ | 1970-01-01T00:00:00 | +--------------------------------------------------+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED explain select first_value(ts order by ts) from t; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Aggregate: groupBy=[[]], aggr=[[__first_value_merge(__first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]) AS first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]]] | |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Aggregate: groupBy=[[]], aggr=[[__first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]]]_| |_|_TableScan: t_| |_| ]]_| | physical_plan | AggregateExec: mode=Final, gby=[], aggr=[first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]]_| |_|_CoalescePartitionsExec_| |_|_AggregateExec: mode=Partial, gby=[], aggr=[first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]]_| |_|_CooperativeExec_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (elapsed_compute.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (metrics.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED -- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED -- might write to different partitions -- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED explain analyze select first_value(ts order by ts) from t; +-+-+-+ | stage | node | plan_| +-+-+-+ | 0_| 0_|_AggregateExec: mode=Final, gby=[], aggr=[first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalescePartitionsExec REDACTED |_|_|_AggregateExec: mode=Partial, gby=[], aggr=[first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_MergeScanExec: REDACTED |_|_|_| | 1_| 0_|_AggregateExec: mode=Final, gby=[], aggr=[__first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalescePartitionsExec REDACTED |_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 1_|_AggregateExec: mode=Final, gby=[], aggr=[__first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalescePartitionsExec REDACTED |_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 2_|_AggregateExec: mode=Final, gby=[], aggr=[__first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalescePartitionsExec REDACTED |_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| |_|_| Total rows: 1_| +-+-+-+ select first_value(host order by ts) as ordered_host, first_value(val order by ts), first_value(ts order by ts), date_bin('5ms'::INTERVAL, ts) as time_window from t group by time_window order by time_window, ordered_host; +--------------+---------------------------------------------------+--------------------------------------------------+-------------------------+ | ordered_host | first_value(t.val) ORDER BY [t.ts ASC NULLS LAST] | first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST] | time_window | +--------------+---------------------------------------------------+--------------------------------------------------+-------------------------+ | a | 1.0 | 1970-01-01T00:00:00 | 1970-01-01T00:00:00 | | b | 6.0 | 1970-01-01T00:00:00.005 | 1970-01-01T00:00:00.005 | +--------------+---------------------------------------------------+--------------------------------------------------+-------------------------+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED explain select first_value(host order by ts) as ordered_host, first_value(val order by ts), first_value(ts order by ts), date_bin('5ms'::INTERVAL, ts) as time_window from t group by time_window order by time_window, ordered_host; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: time_window ASC NULLS LAST, ordered_host ASC NULLS LAST_| |_|_Projection: first_value(t.host) ORDER BY [t.ts ASC NULLS LAST] AS ordered_host, first_value(t.val) ORDER BY [t.ts ASC NULLS LAST], first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST], date_bin(Utf8("5 milliseconds"),t.ts) AS time_window_| |_|_Aggregate: groupBy=[[date_bin(Utf8("5 milliseconds"),t.ts)]], aggr=[[__first_value_merge(__first_value_state(t.host) ORDER BY [t.ts ASC NULLS LAST]) AS first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], __first_value_merge(__first_value_state(t.val) ORDER BY [t.ts ASC NULLS LAST]) AS first_value(t.val) ORDER BY [t.ts ASC NULLS LAST], __first_value_merge(__first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]) AS first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]]] | |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Aggregate: groupBy=[[date_bin(CAST(Utf8("5 milliseconds") AS Interval(MonthDayNano)), t.ts)]], aggr=[[__first_value_state(t.host) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.val) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]]]_| |_|_TableScan: t_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [time_window@3 ASC NULLS LAST, ordered_host@0 ASC NULLS LAST]_| |_|_SortExec: expr=[time_window@3 ASC NULLS LAST, ordered_host@0 ASC NULLS LAST], preserve_REDACTED |_|_ProjectionExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@1 as ordered_host, first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]@2 as first_value(t.val) ORDER BY [t.ts ASC NULLS LAST], first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]@3 as first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST], date_bin(Utf8("5 milliseconds"),t.ts)@0 as time_window]_| |_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("5 milliseconds"),t.ts)@0 as date_bin(Utf8("5 milliseconds"),t.ts)], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST], first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]]_| |_|_CoalesceBatchesExec: target_batch_size=8192_| |_|_RepartitionExec: REDACTED |_|_AggregateExec: mode=Partial, gby=[date_bin(Utf8("5 milliseconds"),t.ts)@0 as date_bin(Utf8("5 milliseconds"),t.ts)], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST], first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]]_| |_|_CooperativeExec_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (elapsed_compute.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (metrics.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED -- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED -- might write to different partitions -- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED explain analyze select first_value(host order by ts) as ordered_host, first_value(val order by ts), first_value(ts order by ts), date_bin('5ms'::INTERVAL, ts) as time_window from t group by time_window order by time_window, ordered_host; +-+-+-+ | stage | node | plan_| +-+-+-+ | 0_| 0_|_SortPreservingMergeExec: [time_window@3 ASC NULLS LAST, ordered_host@0 ASC NULLS LAST] REDACTED |_|_|_SortExec: expr=[time_window@3 ASC NULLS LAST, ordered_host@0 ASC NULLS LAST], preserve_REDACTED |_|_|_ProjectionExec: expr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST]@1 as ordered_host, first_value(t.val) ORDER BY [t.ts ASC NULLS LAST]@2 as first_value(t.val) ORDER BY [t.ts ASC NULLS LAST], first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]@3 as first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST], date_bin(Utf8("5 milliseconds"),t.ts)@0 as time_window] REDACTED |_|_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("5 milliseconds"),t.ts)@0 as date_bin(Utf8("5 milliseconds"),t.ts)], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST], first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[date_bin(Utf8("5 milliseconds"),t.ts)@0 as date_bin(Utf8("5 milliseconds"),t.ts)], aggr=[first_value(t.host) ORDER BY [t.ts ASC NULLS LAST], first_value(t.val) ORDER BY [t.ts ASC NULLS LAST], first_value(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_MergeScanExec: REDACTED |_|_|_| | 1_| 0_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("5 milliseconds"),t.ts)@0 as date_bin(Utf8("5 milliseconds"),t.ts)], aggr=[__first_value_state(t.host) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.val) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 5000000 }, ts@0) as date_bin(Utf8("5 milliseconds"),t.ts)], aggr=[__first_value_state(t.host) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.val) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 1_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("5 milliseconds"),t.ts)@0 as date_bin(Utf8("5 milliseconds"),t.ts)], aggr=[__first_value_state(t.host) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.val) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 5000000 }, ts@0) as date_bin(Utf8("5 milliseconds"),t.ts)], aggr=[__first_value_state(t.host) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.val) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 2_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("5 milliseconds"),t.ts)@0 as date_bin(Utf8("5 milliseconds"),t.ts)], aggr=[__first_value_state(t.host) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.val) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 5000000 }, ts@0) as date_bin(Utf8("5 milliseconds"),t.ts)], aggr=[__first_value_state(t.host) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.val) ORDER BY [t.ts ASC NULLS LAST], __first_value_state(t.ts) ORDER BY [t.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| |_|_| Total rows: 2_| +-+-+-+ drop table t; Affected Rows: 0 CREATE TABLE phy (ts timestamp time index, val double, host string primary key) PARTITION ON COLUMNS (host) ( host < 'b', host >= 'b' AND host < 'd', host >= 'd' ) engine=metric with ("physical_metric_table" = ""); Affected Rows: 0 CREATE TABLE t1 (ts timestamp time index, val double, host string primary key) engine = metric with ("on_physical_table" = "phy"); Affected Rows: 0 insert into t1(ts, val, host) values (0, 1.0, 'a'), (1, 2.0, 'b'), (2, 3.0, 'a'), (3, 4.0, 'c'), (4, 5.0, 'a'), (5, 6.0, 'b'), (6, 7.0, 'a'), (7, 8.0, 'c'), (8, 9.0, 'd'); Affected Rows: 9 select first_value(ts order by ts) from t1; +----------------------------------------------------+ | first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST] | +----------------------------------------------------+ | 1970-01-01T00:00:00 | +----------------------------------------------------+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED explain select first_value(ts order by ts) from t1; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Aggregate: groupBy=[[]], aggr=[[__first_value_merge(__first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]) AS first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]]] | |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Aggregate: groupBy=[[]], aggr=[[__first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]]]_| |_|_TableScan: t1_| |_| ]]_| | physical_plan | AggregateExec: mode=Final, gby=[], aggr=[first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]]_| |_|_CoalescePartitionsExec_| |_|_AggregateExec: mode=Partial, gby=[], aggr=[first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]]_| |_|_CooperativeExec_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (elapsed_compute.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (metrics.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED -- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED -- might write to different partitions -- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED explain analyze select first_value(ts order by ts) from t1; +-+-+-+ | stage | node | plan_| +-+-+-+ | 0_| 0_|_AggregateExec: mode=Final, gby=[], aggr=[first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalescePartitionsExec REDACTED |_|_|_AggregateExec: mode=Partial, gby=[], aggr=[first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_MergeScanExec: REDACTED |_|_|_| | 1_| 0_|_AggregateExec: mode=Final, gby=[], aggr=[__first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalescePartitionsExec REDACTED |_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 1_|_AggregateExec: mode=Final, gby=[], aggr=[__first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalescePartitionsExec REDACTED |_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 2_|_AggregateExec: mode=Final, gby=[], aggr=[__first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalescePartitionsExec REDACTED |_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| |_|_| Total rows: 1_| +-+-+-+ select first_value(host order by ts) as ordered_host, first_value(val order by ts) from t1 group by host order by ordered_host; +--------------+-----------------------------------------------------+ | ordered_host | first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST] | +--------------+-----------------------------------------------------+ | a | 1.0 | | b | 2.0 | | c | 4.0 | | d | 9.0 | +--------------+-----------------------------------------------------+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED explain select first_value(host order by ts) as ordered_host, first_value(val order by ts) from t1 group by host order by ordered_host; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| MergeSort: ordered_host ASC NULLS LAST_| |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Sort: ordered_host ASC NULLS LAST_| |_|_Projection: first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST] AS ordered_host, first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]_| |_|_Aggregate: groupBy=[[t1.host]], aggr=[[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]]] | |_|_TableScan: t1_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [ordered_host@0 ASC NULLS LAST]_| |_|_CooperativeExec_| |_|_CooperativeExec_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (elapsed_compute.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (metrics.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED -- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED -- might write to different partitions -- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED explain analyze select first_value(host order by ts) as ordered_host, first_value(val order by ts) from t1 group by host order by ordered_host; +-+-+-+ | stage | node | plan_| +-+-+-+ | 0_| 0_|_SortPreservingMergeExec: [ordered_host@0 ASC NULLS LAST] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_CooperativeExec REDACTED |_|_|_MergeScanExec: REDACTED |_|_|_| | 1_| 0_|_ProjectionExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@0 as ordered_host, first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]@1 as first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_SortPreservingMergeExec: [first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@0 ASC NULLS LAST] REDACTED |_|_|_SortExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@0 ASC NULLS LAST], preserve_REDACTED |_|_|_ProjectionExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@1 as first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]@2 as first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_AggregateExec: mode=FinalPartitioned, gby=[host@0 as host], aggr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[host@0 as host], aggr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 1_|_ProjectionExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@0 as ordered_host, first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]@1 as first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_SortPreservingMergeExec: [first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@0 ASC NULLS LAST] REDACTED |_|_|_SortExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@0 ASC NULLS LAST], preserve_REDACTED |_|_|_ProjectionExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@1 as first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]@2 as first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_AggregateExec: mode=FinalPartitioned, gby=[host@0 as host], aggr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[host@0 as host], aggr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 2_|_ProjectionExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@0 as ordered_host, first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]@1 as first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_SortPreservingMergeExec: [first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@0 ASC NULLS LAST] REDACTED |_|_|_SortExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@0 ASC NULLS LAST], preserve_REDACTED |_|_|_ProjectionExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@1 as first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]@2 as first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_AggregateExec: mode=FinalPartitioned, gby=[host@0 as host], aggr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[host@0 as host], aggr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| |_|_| Total rows: 4_| +-+-+-+ select first_value(host order by ts) as ordered_host, first_value(val order by ts), first_value(ts order by ts), date_bin('5ms'::INTERVAL, ts) as time_window from t1 group by time_window order by time_window, ordered_host; +--------------+-----------------------------------------------------+----------------------------------------------------+-------------------------+ | ordered_host | first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST] | first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST] | time_window | +--------------+-----------------------------------------------------+----------------------------------------------------+-------------------------+ | a | 1.0 | 1970-01-01T00:00:00 | 1970-01-01T00:00:00 | | b | 6.0 | 1970-01-01T00:00:00.005 | 1970-01-01T00:00:00.005 | +--------------+-----------------------------------------------------+----------------------------------------------------+-------------------------+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED explain select first_value(host order by ts) as ordered_host, first_value(val order by ts), first_value(ts order by ts), date_bin('5ms'::INTERVAL, ts) as time_window from t1 group by time_window order by time_window, ordered_host; +-+-+ | plan_type_| plan_| +-+-+ | logical_plan_| Sort: time_window ASC NULLS LAST, ordered_host ASC NULLS LAST_| |_|_Projection: first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST] AS ordered_host, first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST], date_bin(Utf8("5 milliseconds"),t1.ts) AS time_window_| |_|_Aggregate: groupBy=[[date_bin(Utf8("5 milliseconds"),t1.ts)]], aggr=[[__first_value_merge(__first_value_state(t1.host) ORDER BY [t1.ts ASC NULLS LAST]) AS first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], __first_value_merge(__first_value_state(t1.val) ORDER BY [t1.ts ASC NULLS LAST]) AS first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST], __first_value_merge(__first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]) AS first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]]] | |_|_MergeScan [is_placeholder=false, remote_input=[_| |_| Aggregate: groupBy=[[date_bin(CAST(Utf8("5 milliseconds") AS Interval(MonthDayNano)), t1.ts)]], aggr=[[__first_value_state(t1.host) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.val) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]]]_| |_|_TableScan: t1_| |_| ]]_| | physical_plan | SortPreservingMergeExec: [time_window@3 ASC NULLS LAST, ordered_host@0 ASC NULLS LAST]_| |_|_SortExec: expr=[time_window@3 ASC NULLS LAST, ordered_host@0 ASC NULLS LAST], preserve_REDACTED |_|_ProjectionExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@1 as ordered_host, first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]@2 as first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]@3 as first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST], date_bin(Utf8("5 milliseconds"),t1.ts)@0 as time_window]_| |_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("5 milliseconds"),t1.ts)@0 as date_bin(Utf8("5 milliseconds"),t1.ts)], aggr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]]_| |_|_CoalesceBatchesExec: target_batch_size=8192_| |_|_RepartitionExec: REDACTED |_|_AggregateExec: mode=Partial, gby=[date_bin(Utf8("5 milliseconds"),t1.ts)@0 as date_bin(Utf8("5 milliseconds"),t1.ts)], aggr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]]_| |_|_CooperativeExec_| |_|_MergeScanExec: REDACTED |_|_| +-+-+ -- SQLNESS REPLACE (-+) - -- SQLNESS REPLACE (\s\s+) _ -- SQLNESS REPLACE (elapsed_compute.*) REDACTED -- SQLNESS REPLACE (peers.*) REDACTED -- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED -- SQLNESS REPLACE (metrics.*) REDACTED -- SQLNESS REPLACE (partitioning.*) REDACTED -- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED -- might write to different partitions -- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED explain analyze select first_value(host order by ts) as ordered_host, first_value(val order by ts), first_value(ts order by ts), date_bin('5ms'::INTERVAL, ts) as time_window from t1 group by time_window order by time_window, ordered_host; +-+-+-+ | stage | node | plan_| +-+-+-+ | 0_| 0_|_SortPreservingMergeExec: [time_window@3 ASC NULLS LAST, ordered_host@0 ASC NULLS LAST] REDACTED |_|_|_SortExec: expr=[time_window@3 ASC NULLS LAST, ordered_host@0 ASC NULLS LAST], preserve_REDACTED |_|_|_ProjectionExec: expr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST]@1 as ordered_host, first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST]@2 as first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]@3 as first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST], date_bin(Utf8("5 milliseconds"),t1.ts)@0 as time_window] REDACTED |_|_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("5 milliseconds"),t1.ts)@0 as date_bin(Utf8("5 milliseconds"),t1.ts)], aggr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[date_bin(Utf8("5 milliseconds"),t1.ts)@0 as date_bin(Utf8("5 milliseconds"),t1.ts)], aggr=[first_value(t1.host) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.val) ORDER BY [t1.ts ASC NULLS LAST], first_value(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_MergeScanExec: REDACTED |_|_|_| | 1_| 0_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("5 milliseconds"),t1.ts)@0 as date_bin(Utf8("5 milliseconds"),t1.ts)], aggr=[__first_value_state(t1.host) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.val) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 5000000 }, ts@1) as date_bin(Utf8("5 milliseconds"),t1.ts)], aggr=[__first_value_state(t1.host) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.val) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 1_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("5 milliseconds"),t1.ts)@0 as date_bin(Utf8("5 milliseconds"),t1.ts)], aggr=[__first_value_state(t1.host) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.val) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 5000000 }, ts@1) as date_bin(Utf8("5 milliseconds"),t1.ts)], aggr=[__first_value_state(t1.host) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.val) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| | 1_| 2_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("5 milliseconds"),t1.ts)@0 as date_bin(Utf8("5 milliseconds"),t1.ts)], aggr=[__first_value_state(t1.host) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.val) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED |_|_|_RepartitionExec: REDACTED |_|_|_AggregateExec: mode=Partial, gby=[date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 5000000 }, ts@1) as date_bin(Utf8("5 milliseconds"),t1.ts)], aggr=[__first_value_state(t1.host) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.val) ORDER BY [t1.ts ASC NULLS LAST], __first_value_state(t1.ts) ORDER BY [t1.ts ASC NULLS LAST]] REDACTED |_|_|_CooperativeExec REDACTED |_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED |_|_|_| |_|_| Total rows: 2_| +-+-+-+ drop table t1; Affected Rows: 0 drop table phy; Affected Rows: 0