You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/10/15 13:16:28 UTC
(datafusion) branch gh-readonly-queue/main/pr-26332-519e4c51a715d4c4152c003b77a8443c497eb847 created (now db425eb58f)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a change to branch gh-readonly-queue/main/pr-26332-519e4c51a715d4c4152c003b77a8443c497eb847
in repository https://gitbox.apache.org/repos/asf/datafusion.git
at db425eb58f test: use WITH ORDER tables for date_bin/date_trunc ordering plan tests (#26332)
This branch includes the following new commits:
new db425eb58f test: use WITH ORDER tables for date_bin/date_trunc ordering plan tests (#26332)
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
(datafusion) 01/01: test: use WITH ORDER tables for date_bin/date_trunc ordering plan tests (#26332)
Posted by gi...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-26332-519e4c51a715d4c4152c003b77a8443c497eb847
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit db425eb58fe2d0715a79b153b2cbfffe9bc8c5b3
Author: Liang-Chi Hsieh <vi...@gmail.com>
AuthorDate: Thu Oct 15 13:09:34 2026 +0000
test: use WITH ORDER tables for date_bin/date_trunc ordering plan tests (#26332)
## Which issue does this PR close?
- Follow-up to #25815, from
https://github.com/apache/datafusion/pull/25815#discussion_r4122735316.
## Rationale for this change
In #25815, @alamb asked why a plan still had a `SortExec` when ordering
propagation should remove it. That sort was the `TopK(fetch=n)` from the
`ORDER BY ... LIMIT n` subquery used to produce ordered input. The outer
sort, the one the test was about, had already been removed. Reading the
input from a table declared sorted makes the plans show this directly:
no sort at all when the ordering propagates, and only the final sort
when it does not.
## What changes are included in this PR?
Test-only. In `datetime/timestamps.slt`, the seven ordering-propagation
`EXPLAIN` tests through `date_bin`, `date_trunc` and `from_unixtime`
(plus the result queries next to them) now read from CSV files written
with `COPY ... TO` and registered with `CREATE EXTERNAL TABLE ... WITH
ORDER`. This follows the pattern in `order.slt`. Each `DataSourceExec`
reports the declared `output_ordering`.
- The five cases where the sort is removed (timezone-free
`from_unixtime`, timezone-free `date_bin`, coarse-precision values
outside the nanosecond range, positive month stride, nested
`date_trunc`) now have no `SortExec`.
- The `America/Goose_Bay` case keeps exactly one `SortExec`, the
required final sort.
- The data is unchanged: `Timestamp(Second)` columns come from
`TIMESTAMP(0)`, the coarse-precision case still uses ±10,000,000,000
seconds, and every query result is unchanged. The Goose Bay result query
moved next to its `EXPLAIN`.
I did not find any other `date_bin`/`date_trunc` ordering plan tests
that use the `ORDER BY ... LIMIT` subquery pattern. The related tests in
`group_by.slt` and `sort_pushdown.slt` already use `WITH ORDER`.
## What is the testing strategy for this PR?
This PR only changes tests. `datetime/timestamps.slt` passes.
## Are there any user-facing changes?
No.
---
.../test_files/datetime/timestamps.slt | 303 ++++++++++++---------
1 file changed, 169 insertions(+), 134 deletions(-)
diff --git a/datafusion/sqllogictest/test_files/datetime/timestamps.slt b/datafusion/sqllogictest/test_files/datetime/timestamps.slt
index ee6bedfeba..162846779a 100644
--- a/datafusion/sqllogictest/test_files/datetime/timestamps.slt
+++ b/datafusion/sqllogictest/test_files/datetime/timestamps.slt
@@ -1679,122 +1679,121 @@ ORDER BY date_trunc('hour', ts);
562129260 562125600
562129259 562129200
-# Typed bounds from a child function must retain named timezone metadata
-query II
-SELECT
- c,
- arrow_cast(
- date_trunc('hour', from_unixtime(c, 'America/Goose_Bay')),
- 'Int64'
- ) AS truncated
-FROM (
- SELECT column1 AS c
- FROM (
- VALUES
- (562129260::bigint),
- (562129259::bigint)
- )
- ORDER BY c
- LIMIT 2
-)
-ORDER BY date_trunc('hour', from_unixtime(c, 'America/Goose_Bay'));
-----
-562129260 562125600
-562129259 562129200
+# The ordering propagation tests below read from tables declared sorted with
+# `WITH ORDER`, so a plan has no sort at all when the ordering propagates, and
+# exactly the final sort when it must not. Each file is written in its
+# declared order.
# Typed bounds allow date_trunc to propagate ordering through timezone-free from_unixtime
+query I
+COPY (VALUES (3600::bigint), (7200::bigint))
+TO 'test_files/scratch/timestamps/ordered_epochs.csv'
+OPTIONS ('format.has_header' 'true');
+----
+2
+
+statement ok
+CREATE EXTERNAL TABLE ordered_epochs (c BIGINT)
+STORED AS CSV
+WITH ORDER (c ASC)
+LOCATION 'test_files/scratch/timestamps/ordered_epochs.csv'
+OPTIONS ('format.has_header' 'true');
+
query TT
EXPLAIN SELECT date_trunc('hour', from_unixtime(c)) AS truncated
-FROM (
- SELECT column1 AS c
- FROM (
- VALUES
- (7200::bigint),
- (3600::bigint)
- )
- ORDER BY c
- LIMIT 2
-)
+FROM ordered_epochs
ORDER BY date_trunc('hour', from_unixtime(c));
----
logical_plan
01)Sort: truncated ASC NULLS LAST
-02)--Projection: date_trunc(Utf8("hour"), from_unixtime(c)) AS truncated
-03)----Sort: c ASC NULLS LAST, fetch=2
-04)------Projection: column1 AS c
-05)--------Values: (Int64(7200)), (Int64(3600))
+02)--Projection: date_trunc(Utf8("hour"), from_unixtime(ordered_epochs.c)) AS truncated
+03)----TableScan: ordered_epochs projection=[c]
physical_plan
-01)ProjectionExec: expr=[date_trunc(hour, from_unixtime(column1@0)) as truncated]
-02)--SortExec: TopK(fetch=2), expr=[column1@0 ASC NULLS LAST], preserve_partitioning=[false]
-03)----DataSourceExec: partitions=1, partition_sizes=[1]
+01)SortPreservingMergeExec: [truncated@0 ASC NULLS LAST]
+02)--ProjectionExec: expr=[date_trunc(hour, from_unixtime(c@0)) as truncated]
+03)----RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1, maintains_sort_order=true
+04)------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/timestamps/ordered_epochs.csv]]}, projection=[c], output_ordering=[c@0 ASC NULLS LAST], file_type=csv, has_header=true
+
+statement ok
+DROP TABLE ordered_epochs;
# Typed bounds allow date_trunc to propagate ordering through timezone-free date_bin
+query I
+COPY (
+ SELECT arrow_cast(column1, 'Timestamp(Second)')
+ FROM (VALUES (3600::bigint), (7200::bigint))
+)
+TO 'test_files/scratch/timestamps/ordered_seconds.csv'
+OPTIONS ('format.has_header' 'true');
+----
+2
+
+statement ok
+CREATE EXTERNAL TABLE ordered_seconds (ts TIMESTAMP(0))
+STORED AS CSV
+WITH ORDER (ts ASC)
+LOCATION 'test_files/scratch/timestamps/ordered_seconds.csv'
+OPTIONS ('format.has_header' 'true');
+
query TT
EXPLAIN SELECT date_trunc('hour', date_bin(INTERVAL '1 minute', ts)) AS truncated
-FROM (
- SELECT arrow_cast(column1, 'Timestamp(Second)') AS ts
- FROM (
- VALUES
- (7200::bigint),
- (3600::bigint)
- )
- ORDER BY ts
- LIMIT 2
-)
+FROM ordered_seconds
ORDER BY date_trunc('hour', date_bin(INTERVAL '1 minute', ts));
----
logical_plan
01)Sort: truncated ASC NULLS LAST
-02)--Projection: date_trunc(Utf8("hour"), date_bin(IntervalMonthDayNano("IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 60000000000 }"), ts)) AS truncated
-03)----Sort: ts ASC NULLS LAST, fetch=2
-04)------Projection: CAST(column1 AS Timestamp(s)) AS ts
-05)--------Values: (Int64(7200)), (Int64(3600))
+02)--Projection: date_trunc(Utf8("hour"), date_bin(IntervalMonthDayNano("IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 60000000000 }"), ordered_seconds.ts)) AS truncated
+03)----TableScan: ordered_seconds projection=[ts]
physical_plan
-01)ProjectionExec: expr=[date_trunc(hour, date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 60000000000 }, ts@0)) as truncated]
-02)--SortExec: TopK(fetch=2), expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[false]
-03)----ProjectionExec: expr=[CAST(column1@0 AS Timestamp(s)) as ts]
-04)------DataSourceExec: partitions=1, partition_sizes=[1]
+01)SortPreservingMergeExec: [truncated@0 ASC NULLS LAST]
+02)--ProjectionExec: expr=[date_trunc(hour, date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 60000000000 }, ts@0)) as truncated]
+03)----RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1, maintains_sort_order=true
+04)------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/timestamps/ordered_seconds.csv]]}, projection=[ts], output_ordering=[ts@0 ASC NULLS LAST], file_type=csv, has_header=true
+
+statement ok
+DROP TABLE ordered_seconds;
# Coarse-precision values outside the nanosecond range are binned without
# overflowing, so date_bin keeps both their values and their ordering.
-query TT
-EXPLAIN SELECT date_trunc('second', date_bin(INTERVAL '1 second', ts)) AS b
-FROM (
- SELECT arrow_cast(column1, 'Timestamp(Second)') AS ts
+query I
+COPY (
+ SELECT arrow_cast(column1, 'Timestamp(Second)')
FROM (VALUES
(-10000000000::bigint),
(0::bigint),
(10000000000::bigint)
)
- ORDER BY ts
- LIMIT 3
)
+TO 'test_files/scratch/timestamps/ordered_wide_seconds.csv'
+OPTIONS ('format.has_header' 'true');
+----
+3
+
+statement ok
+CREATE EXTERNAL TABLE ordered_wide_seconds (ts TIMESTAMP(0))
+STORED AS CSV
+WITH ORDER (ts ASC)
+LOCATION 'test_files/scratch/timestamps/ordered_wide_seconds.csv'
+OPTIONS ('format.has_header' 'true');
+
+query TT
+EXPLAIN SELECT date_trunc('second', date_bin(INTERVAL '1 second', ts)) AS b
+FROM ordered_wide_seconds
ORDER BY b ASC NULLS LAST
----
logical_plan
01)Sort: b ASC NULLS LAST
-02)--Projection: date_trunc(Utf8("second"), date_bin(IntervalMonthDayNano("IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 1000000000 }"), ts)) AS b
-03)----Sort: ts ASC NULLS LAST, fetch=3
-04)------Projection: CAST(column1 AS Timestamp(s)) AS ts
-05)--------Values: (Int64(-10000000000) AS (- Int64(10000000000))), (Int64(0)), (Int64(10000000000))
+02)--Projection: date_trunc(Utf8("second"), date_bin(IntervalMonthDayNano("IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 1000000000 }"), ordered_wide_seconds.ts)) AS b
+03)----TableScan: ordered_wide_seconds projection=[ts]
physical_plan
-01)ProjectionExec: expr=[date_trunc(second, date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 1000000000 }, ts@0)) as b]
-02)--SortExec: TopK(fetch=3), expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[false]
-03)----ProjectionExec: expr=[CAST(column1@0 AS Timestamp(s)) as ts]
-04)------DataSourceExec: partitions=1, partition_sizes=[1]
+01)SortPreservingMergeExec: [b@0 ASC NULLS LAST]
+02)--ProjectionExec: expr=[date_trunc(second, date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 1000000000 }, ts@0)) as b]
+03)----RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1, maintains_sort_order=true
+04)------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/timestamps/ordered_wide_seconds.csv]]}, projection=[ts], output_ordering=[ts@0 ASC NULLS LAST], file_type=csv, has_header=true
query P
SELECT date_trunc('second', date_bin(INTERVAL '1 second', ts)) AS b
-FROM (
- SELECT arrow_cast(column1, 'Timestamp(Second)') AS ts
- FROM (VALUES
- (-10000000000::bigint),
- (0::bigint),
- (10000000000::bigint)
- )
- ORDER BY ts
- LIMIT 3
-)
+FROM ordered_wide_seconds
ORDER BY b ASC NULLS LAST
----
1653-02-10T06:13:20
@@ -1805,74 +1804,83 @@ ORDER BY b ASC NULLS LAST
# range type.
query P
SELECT date_bin(INTERVAL '1 second', date_trunc('second', ts)) AS b
-FROM (
- SELECT arrow_cast(column1, 'Timestamp(Second)') AS ts
- FROM (VALUES
- (-10000000000::bigint),
- (0::bigint),
- (10000000000::bigint)
- )
- ORDER BY ts
- LIMIT 3
-)
+FROM ordered_wide_seconds
ORDER BY b ASC NULLS LAST
----
1653-02-10T06:13:20
1970-01-01T00:00:00
2286-11-20T17:46:40
+statement ok
+DROP TABLE ordered_wide_seconds;
+
# A positive month stride is monotonic, so the final sort is removed.
-query TT
-EXPLAIN SELECT date_bin(INTERVAL '1 month', ts, TIMESTAMP '2023-01-31 00:00:00') AS b
-FROM (
- SELECT arrow_cast(column1, 'Timestamp(Second)') AS ts
+query I
+COPY (
+ SELECT arrow_cast(column1, 'Timestamp(Second)')
FROM (VALUES
(TIMESTAMP '2023-01-01 00:00:00'),
(TIMESTAMP '2023-01-31 00:00:00')
)
- ORDER BY ts
- LIMIT 2
)
+TO 'test_files/scratch/timestamps/ordered_month_seconds.csv'
+OPTIONS ('format.has_header' 'true');
+----
+2
+
+statement ok
+CREATE EXTERNAL TABLE ordered_month_seconds (ts TIMESTAMP(0))
+STORED AS CSV
+WITH ORDER (ts ASC)
+LOCATION 'test_files/scratch/timestamps/ordered_month_seconds.csv'
+OPTIONS ('format.has_header' 'true');
+
+query TT
+EXPLAIN SELECT date_bin(INTERVAL '1 month', ts, TIMESTAMP '2023-01-31 00:00:00') AS b
+FROM ordered_month_seconds
ORDER BY b
----
logical_plan
01)Sort: b ASC NULLS LAST
-02)--Projection: date_bin(IntervalMonthDayNano("IntervalMonthDayNano { months: 1, days: 0, nanoseconds: 0 }"), ts, TimestampNanosecond(1675123200000000000, None)) AS b
-03)----Sort: ts ASC NULLS LAST, fetch=2
-04)------Projection: CAST(column1 AS Timestamp(s)) AS ts
-05)--------Values: (TimestampNanosecond(1672531200000000000, None) AS Utf8("2023-01-01 00:00:00")), (TimestampNanosecond(1675123200000000000, None) AS Utf8("2023-01-31 00:00:00"))
+02)--Projection: date_bin(IntervalMonthDayNano("IntervalMonthDayNano { months: 1, days: 0, nanoseconds: 0 }"), ordered_month_seconds.ts, TimestampNanosecond(1675123200000000000, None)) AS b
+03)----TableScan: ordered_month_seconds projection=[ts]
physical_plan
-01)ProjectionExec: expr=[date_bin(IntervalMonthDayNano { months: 1, days: 0, nanoseconds: 0 }, ts@0, 1675123200000000000) as b]
-02)--SortExec: TopK(fetch=2), expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[false]
-03)----ProjectionExec: expr=[CAST(column1@0 AS Timestamp(s)) as ts]
-04)------DataSourceExec: partitions=1, partition_sizes=[1]
+01)SortPreservingMergeExec: [b@0 ASC NULLS LAST]
+02)--ProjectionExec: expr=[date_bin(IntervalMonthDayNano { months: 1, days: 0, nanoseconds: 0 }, ts@0, 1675123200000000000) as b]
+03)----RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1, maintains_sort_order=true
+04)------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/timestamps/ordered_month_seconds.csv]]}, projection=[ts], output_ordering=[ts@0 ASC NULLS LAST], file_type=csv, has_header=true
query P
SELECT date_bin(INTERVAL '1 month', ts, TIMESTAMP '2023-01-31 00:00:00') AS b
-FROM (
- SELECT arrow_cast(column1, 'Timestamp(Second)') AS ts
- FROM (VALUES
- (TIMESTAMP '2023-01-01 00:00:00'),
- (TIMESTAMP '2023-01-31 00:00:00')
- )
- ORDER BY ts
- LIMIT 2
-)
+FROM ordered_month_seconds
ORDER BY b
----
2022-12-31T00:00:00
2023-01-31T00:00:00
+statement ok
+DROP TABLE ordered_month_seconds;
+
# Default bounds from nested functions retain their resolved output type.
statement ok
SET datafusion.explain.physical_plan_only = true;
+query I
+COPY (
+ SELECT arrow_cast(column1, 'Timestamp(Second)')
+ FROM (VALUES (3601::bigint), (90001::bigint))
+)
+TO 'test_files/scratch/timestamps/nested_trunc_ordering.csv'
+OPTIONS ('format.has_header' 'true');
+----
+2
+
statement ok
-CREATE VIEW nested_trunc_ordering AS
-SELECT arrow_cast(column1, 'Timestamp(Second)') AS ts
-FROM (VALUES (90001::bigint), (3601::bigint))
-ORDER BY ts
-LIMIT 2;
+CREATE EXTERNAL TABLE nested_trunc_ordering (ts TIMESTAMP(0))
+STORED AS CSV
+WITH ORDER (ts ASC)
+LOCATION 'test_files/scratch/timestamps/nested_trunc_ordering.csv'
+OPTIONS ('format.has_header' 'true');
query I
SELECT arrow_cast(date_trunc('day', date_trunc('hour', ts)), 'Int64') AS bucket
@@ -1888,30 +1896,57 @@ FROM nested_trunc_ordering
ORDER BY bucket;
----
physical_plan
-01)ProjectionExec: expr=[date_trunc(day, date_trunc(hour, ts@0)) as bucket]
-02)--SortExec: TopK(fetch=2), expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[false]
-03)----ProjectionExec: expr=[CAST(column1@0 AS Timestamp(s)) as ts]
-04)------DataSourceExec: partitions=1, partition_sizes=[1]
+01)SortPreservingMergeExec: [bucket@0 ASC NULLS LAST]
+02)--ProjectionExec: expr=[date_trunc(day, date_trunc(hour, ts@0)) as bucket]
+03)----RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1, maintains_sort_order=true
+04)------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/timestamps/nested_trunc_ordering.csv]]}, projection=[ts], output_ordering=[ts@0 ASC NULLS LAST], file_type=csv, has_header=true
statement ok
-DROP VIEW nested_trunc_ordering;
+DROP TABLE nested_trunc_ordering;
# Recovering a named timezone must still retain the outer sort.
+query I
+COPY (VALUES (562129259::bigint), (562129260::bigint))
+TO 'test_files/scratch/timestamps/goose_bay_epochs.csv'
+OPTIONS ('format.has_header' 'true');
+----
+2
+
+statement ok
+CREATE EXTERNAL TABLE goose_bay_epochs (c BIGINT)
+STORED AS CSV
+WITH ORDER (c ASC)
+LOCATION 'test_files/scratch/timestamps/goose_bay_epochs.csv'
+OPTIONS ('format.has_header' 'true');
+
+# Typed bounds from a child function must retain named timezone metadata
+query II
+SELECT
+ c,
+ arrow_cast(
+ date_trunc('hour', from_unixtime(c, 'America/Goose_Bay')),
+ 'Int64'
+ ) AS truncated
+FROM goose_bay_epochs
+ORDER BY date_trunc('hour', from_unixtime(c, 'America/Goose_Bay'));
+----
+562129260 562125600
+562129259 562129200
+
query TT
EXPLAIN SELECT date_trunc('hour', from_unixtime(c, 'America/Goose_Bay')) AS bucket
-FROM (
- SELECT column1 AS c
- FROM (VALUES (562129260::bigint), (562129259::bigint))
- ORDER BY c
- LIMIT 2
-)
+FROM goose_bay_epochs
ORDER BY bucket;
----
physical_plan
-01)SortExec: expr=[bucket@0 ASC NULLS LAST], preserve_partitioning=[false]
-02)--ProjectionExec: expr=[date_trunc(hour, from_unixtime(column1@0, America/Goose_Bay)) as bucket]
-03)----SortExec: TopK(fetch=2), expr=[column1@0 ASC NULLS LAST], preserve_partitioning=[false]
-04)------DataSourceExec: partitions=1, partition_sizes=[1]
+01)SortPreservingMergeExec: [bucket@0 ASC NULLS LAST]
+02)--SortExec: expr=[bucket@0 ASC NULLS LAST], preserve_partitioning=[true]
+03)----ProjectionExec: expr=[date_trunc(hour, from_unixtime(c@0, America/Goose_Bay)) as bucket]
+04)------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1, maintains_sort_order=true
+05)--------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/timestamps/goose_bay_epochs.csv]]}, projection=[c], output_ordering=[c@0 ASC NULLS LAST], file_type=csv, has_header=true
+
+statement ok
+DROP TABLE goose_bay_epochs;
statement ok
RESET datafusion.explain.physical_plan_only;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org