Skip to content

Commit f192a07

Browse files
viiryaOmega359
authored andcommitted
fix: avoid invalid date_bin ordering after overflow (apache#25692)
## Which issue does this PR close? - Closes apache#25690. ## Rationale for this change `date_bin` advertises its input ordering even when scaling a coarse timestamp to nanoseconds can overflow and produce a per-row `NULL`. The optimizer can then remove a required sort and return `NULL` values in the wrong position. ## What changes are included in this PR? `date_bin` now reports unordered output for timestamp and time representations whose conversion to nanoseconds can overflow. Nanosecond inputs keep their existing ordering propagation, including the sorted unbounded execution path. ## What is the testing strategy for this PR? The new `timestamps.slt` regression reproduces the missing final sort and wrong `NULLS LAST` result on `main`, and passes with this change. Ablation testing confirmed that the regression fails without the fix. The focused `timestamps.slt` test passes on this branch. ## Are there any user-facing changes? Queries involving coarse-precision `date_bin` overflow now honor requested null ordering. There are no public API changes.
1 parent ec54fb4 commit f192a07

2 files changed

Lines changed: 59 additions & 9 deletions

File tree

‎datafusion/functions/src/datetime/date_bin.rs‎

Lines changed: 13 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,7 @@ use arrow::array::types::{
2424
TimestampSecondType,
2525
};
2626
use arrow::array::{ArrayRef, AsArray, PrimitiveArray};
27-
use arrow::datatypes::DataType::{Time32, Time64, Timestamp};
27+
use arrow::datatypes::DataType::{Null, Time32, Time64, Timestamp};
2828
use arrow::datatypes::IntervalUnit::{DayTime, MonthDayNano};
2929
use arrow::datatypes::TimeUnit::{Microsecond, Millisecond, Nanosecond, Second};
3030
use arrow::datatypes::{
@@ -269,14 +269,23 @@ impl ScalarUDFImpl for DateBinFunc {
269269
}
270270

271271
fn output_ordering(&self, input: &[ExprProperties]) -> Result<SortProperties> {
272-
// The DATE_BIN function preserves the order of its second argument.
273272
let step = &input[0];
274273
let date_value = &input[1];
275274
let reference = input.get(2);
276275

277-
if step.sort_properties.eq(&SortProperties::Singleton)
276+
// Scaling these representations to nanoseconds can overflow and turn
277+
// otherwise valid input rows into NULL. Unknown ranges use the Null
278+
// type and can hide one of these representations. The generated NULLs
279+
// need not have the same placement as the source ordering.
280+
let scale_can_overflow = matches!(
281+
date_value.range.data_type(),
282+
Null | Timestamp(Second | Millisecond | Microsecond, _) | Time64(Microsecond)
283+
);
284+
285+
if !scale_can_overflow
286+
&& step.sort_properties == SortProperties::Singleton
278287
&& reference
279-
.map(|r| r.sort_properties.eq(&SortProperties::Singleton))
288+
.map(|r| r.sort_properties == SortProperties::Singleton)
280289
.unwrap_or(true)
281290
{
282291
Ok(date_value.sort_properties)

‎datafusion/sqllogictest/test_files/datetime/timestamps.slt‎

Lines changed: 46 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1728,7 +1728,7 @@ physical_plan
17281728
02)--SortExec: TopK(fetch=2), expr=[column1@0 ASC NULLS LAST], preserve_partitioning=[false]
17291729
03)----DataSourceExec: partitions=1, partition_sizes=[1]
17301730

1731-
# Typed bounds allow date_trunc to propagate ordering through timezone-free date_bin
1731+
# date_bin remains conservatively unordered because per-row failures become NULL
17321732
query TT
17331733
EXPLAIN SELECT date_trunc('hour', date_bin(INTERVAL '1 minute', ts)) AS truncated
17341734
FROM (
@@ -1750,10 +1750,51 @@ logical_plan
17501750
04)------Projection: CAST(column1 AS Timestamp(s)) AS ts
17511751
05)--------Values: (Int64(7200)), (Int64(3600))
17521752
physical_plan
1753-
01)ProjectionExec: expr=[date_trunc(hour, date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 60000000000 }, ts@0)) as truncated]
1754-
02)--SortExec: TopK(fetch=2), expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[false]
1755-
03)----ProjectionExec: expr=[CAST(column1@0 AS Timestamp(s)) as ts]
1756-
04)------DataSourceExec: partitions=1, partition_sizes=[1]
1753+
01)SortExec: expr=[truncated@0 ASC NULLS LAST], preserve_partitioning=[false]
1754+
02)--ProjectionExec: expr=[date_trunc(hour, date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 60000000000 }, ts@0)) as truncated]
1755+
03)----SortExec: TopK(fetch=2), expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[false]
1756+
04)------ProjectionExec: expr=[CAST(column1@0 AS Timestamp(s)) as ts]
1757+
05)--------DataSourceExec: partitions=1, partition_sizes=[1]
1758+
1759+
# date_bin can return NULL for out-of-range values, so its output does not
1760+
# necessarily preserve the source's NULL ordering.
1761+
query P
1762+
SELECT date_trunc('second', date_bin(INTERVAL '1 second', ts)) AS b
1763+
FROM (
1764+
SELECT arrow_cast(column1, 'Timestamp(Second)') AS ts
1765+
FROM (VALUES
1766+
(-10000000000::bigint),
1767+
(0::bigint),
1768+
(10000000000::bigint)
1769+
)
1770+
ORDER BY ts
1771+
LIMIT 3
1772+
)
1773+
ORDER BY b ASC NULLS LAST
1774+
----
1775+
1970-01-01T00:00:00
1776+
NULL
1777+
NULL
1778+
1779+
# An ordered expression can retain its ordering while its range type is unknown.
1780+
# date_bin must not assume that such an input is safe to scale to nanoseconds.
1781+
query P
1782+
SELECT date_bin(INTERVAL '1 second', date_trunc('second', ts)) AS b
1783+
FROM (
1784+
SELECT arrow_cast(column1, 'Timestamp(Second)') AS ts
1785+
FROM (VALUES
1786+
(-10000000000::bigint),
1787+
(0::bigint),
1788+
(10000000000::bigint)
1789+
)
1790+
ORDER BY ts
1791+
LIMIT 3
1792+
)
1793+
ORDER BY b ASC NULLS LAST
1794+
----
1795+
1970-01-01T00:00:00
1796+
NULL
1797+
NULL
17571798

17581799
statement ok
17591800
CREATE TABLE timestamp_strings(ts varchar)

0 commit comments

Comments
 (0)