Skip to content

Commit 6110f74

Browse files
authored
fix: avoid pruning timestamp-to-time casts (apache#26452)
## Which issue does this PR close? - Closes apache#26451. ## Rationale for this change Casting timestamp statistics to time-of-day values does not preserve ordering across midnight. This can invert the minimum and maximum used by statistics pruning and incorrectly discard files or row groups that contain matching rows. ## What changes are included in this PR? - Prevent timestamp-to-`Time32` and timestamp-to-`Time64` casts from being used for statistics pruning. - Add pruning-expression coverage for both `CAST` and `TRY_CAST` across all supported time units. - Add a Parquet regression test whose timestamp bounds cross midnight. ## What is the testing strategy for this PR? The regression test verifies equality and range predicates for `Time32` and `Time64`, including the expected row counts and pruning metrics. The full extended workspace test suite also passes. ## Are there any user-facing changes? Queries that cast timestamps to time-of-day values may perform less statistics pruning, but they no longer risk incorrect results when timestamp bounds cross a day boundary.
1 parent 5db4b23 commit 6110f74

3 files changed

Lines changed: 269 additions & 2 deletions

File tree

‎datafusion/core/tests/parquet/row_group_pruning.rs‎

Lines changed: 47 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,8 +7,8 @@
77
//! expected.
88
use std::sync::Arc;
99

10-
use arrow::array::{ArrayRef, Int32Array, RecordBatch};
11-
use arrow_schema::{DataType, Field, Schema};
10+
use arrow::array::{ArrayRef, Int32Array, RecordBatch, TimestampSecondArray};
11+
use arrow_schema::{DataType, Field, Schema, TimeUnit};
1212
use datafusion::prelude::SessionConfig;
1313
use datafusion_common::{DataFusionError, ScalarValue};
1414
use itertools::Itertools;
@@ -293,6 +293,51 @@ async fn prune_timestamps_seconds() {
293293
.await;
294294
}
295295

296+
#[tokio::test]
297+
async fn timestamp_to_time_cast_across_midnight_is_not_pruned() {
298+
let schema = Arc::new(Schema::new(vec![Field::new(
299+
"ts",
300+
DataType::Timestamp(TimeUnit::Second, None),
301+
false,
302+
)]));
303+
let batch = RecordBatch::try_new(
304+
Arc::clone(&schema),
305+
vec![Arc::new(TimestampSecondArray::from(vec![
306+
82_800, 86_400, 90_000,
307+
]))],
308+
)
309+
.unwrap();
310+
311+
for (time_type, op, literal, expected_rows) in [
312+
("Time32(Second)", "=", "00:00:00", 1),
313+
("Time32(Second)", "<", "02:00:00", 2),
314+
("Time64(Nanosecond)", "=", "00:00:00", 1),
315+
("Time64(Nanosecond)", "<", "02:00:00", 2),
316+
] {
317+
let query = format!(
318+
"SELECT ts FROM t WHERE arrow_cast(ts, '{time_type}') {op} \
319+
arrow_cast('{literal}', '{time_type}')"
320+
);
321+
322+
RowGroupPruningTest::new()
323+
.with_scenario(Scenario::Timestamps)
324+
.with_query(&query)
325+
.with_expected_errors(Some(0))
326+
.with_matched_by_stats(Some(1))
327+
.with_fully_matched_by_stats(Some(0))
328+
.with_pruned_by_stats(Some(0))
329+
.with_pruned_files(Some(0))
330+
.with_limit_pruned_row_groups(Some(0))
331+
.with_expected_rows(expected_rows)
332+
.test_row_group_prune_with_custom_data(
333+
Arc::clone(&schema),
334+
vec![batch.clone()],
335+
3,
336+
)
337+
.await;
338+
}
339+
}
340+
296341
#[tokio::test]
297342
async fn prune_date32() {
298343
RowGroupPruningTest::new()

‎datafusion/pruning/src/pruning_predicate.rs‎

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1349,6 +1349,16 @@ fn verify_support_type_for_prune(from_type: &DataType, to_type: &DataType) -> Re
13491349
}
13501350
_ => to_type,
13511351
};
1352+
// Casting a timestamp to a time-of-day wraps at midnight and does not
1353+
// preserve ordering across day boundaries. Casting timestamp min/max
1354+
// statistics could therefore produce invalid bounds and prune matches.
1355+
if matches!(from_type, DataType::Timestamp(_, _))
1356+
&& matches!(to_type, DataType::Time32(_) | DataType::Time64(_))
1357+
{
1358+
return plan_err!(
1359+
"Try Cast/Cast with from type {from_type} to type {to_type} is not supported"
1360+
);
1361+
}
13521362
// If both types are strings or both are not strings (number, timestamp, etc)
13531363
// then we can compare them.
13541364
// PruningPredicate does not support casting of strings to numbers and such.
@@ -4971,6 +4981,56 @@ mod tests {
49714981
Ok(())
49724982
}
49734983

4984+
#[test]
4985+
fn row_group_predicate_cast_timestamp_to_time() {
4986+
// A timezone shifts where the day boundary falls but does not remove
4987+
// it, so a timezone-aware timestamp is just as unsafe to prune on.
4988+
for timestamp_type in [
4989+
DataType::Timestamp(TimeUnit::Second, None),
4990+
DataType::Timestamp(TimeUnit::Second, Some("+05:00".into())),
4991+
DataType::Timestamp(TimeUnit::Nanosecond, Some("UTC".into())),
4992+
] {
4993+
assert_timestamp_to_time_cast_is_not_prunable(timestamp_type);
4994+
}
4995+
}
4996+
4997+
/// Asserts that no comparison of `CAST`/`TRY_CAST` from `timestamp_type` to
4998+
/// a time-of-day produces a pruning expression.
4999+
fn assert_timestamp_to_time_cast_is_not_prunable(timestamp_type: DataType) {
5000+
let schema = Schema::new(vec![Field::new("ts", timestamp_type, false)]);
5001+
5002+
for (time_type, literal) in [
5003+
(
5004+
DataType::Time32(TimeUnit::Second),
5005+
ScalarValue::Time32Second(Some(0)),
5006+
),
5007+
(
5008+
DataType::Time32(TimeUnit::Millisecond),
5009+
ScalarValue::Time32Millisecond(Some(0)),
5010+
),
5011+
(
5012+
DataType::Time64(TimeUnit::Microsecond),
5013+
ScalarValue::Time64Microsecond(Some(0)),
5014+
),
5015+
(
5016+
DataType::Time64(TimeUnit::Nanosecond),
5017+
ScalarValue::Time64Nanosecond(Some(0)),
5018+
),
5019+
] {
5020+
for expr in [
5021+
cast(col("ts"), time_type.clone()).eq(lit(literal.clone())),
5022+
try_cast(col("ts"), time_type).eq(lit(literal)),
5023+
] {
5024+
let predicate_expr = test_build_predicate_expression(
5025+
&expr,
5026+
&schema,
5027+
&mut RequiredColumns::new(),
5028+
);
5029+
assert_eq!(predicate_expr.to_string(), "true");
5030+
}
5031+
}
5032+
}
5033+
49745034
#[test]
49755035
fn row_group_predicate_cast_string_string() -> Result<()> {
49765036
let schema = Schema::new(vec![Field::new("c1", DataType::Utf8View, false)]);

‎datafusion/sqllogictest/test_files/push_down_filter_parquet.slt‎

Lines changed: 162 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1333,6 +1333,168 @@ RESET datafusion.execution.parquet.pushdown_filters;
13331333
statement ok
13341334
drop table t;
13351335

1336+
##########
1337+
# Casting a timestamp to a time-of-day wraps at midnight
1338+
#
1339+
# https://github.com/apache/datafusion/issues/26451
1340+
##########
1341+
1342+
# Pruning must never change a query's answer. A cast whose output does not
1343+
# preserve the ordering of its input can invert the bounds derived from
1344+
# statistics, which makes pruning discard row groups that do contain matching
1345+
# rows. Every predicate below is therefore run twice: once against an
1346+
# in-memory table, which is never pruned and so gives the ground truth, and
1347+
# once against the same rows stored as Parquet. The two must agree.
1348+
1349+
# Three timestamps at 23:00, 00:00 and 01:00, written as a single row group so
1350+
# that the row group's min/max bracket midnight. Casting those bounds to
1351+
# time-of-day turns them into 23:00:00 and 01:00:00, which excludes every value
1352+
# that actually lies between them.
1353+
statement ok
1354+
CREATE TABLE source_data(ts TIMESTAMP) AS VALUES
1355+
(TIMESTAMP '2024-03-10 23:00:00'),
1356+
(TIMESTAMP '2024-03-11 00:00:00'),
1357+
(TIMESTAMP '2024-03-11 01:00:00');
1358+
1359+
statement ok
1360+
COPY (SELECT ts FROM source_data)
1361+
TO 'test_files/scratch/push_down_filter_parquet/timestamp_to_time/timestamps.parquet'
1362+
STORED AS PARQUET
1363+
OPTIONS ('format.max_row_group_size' '3');
1364+
1365+
statement ok
1366+
CREATE EXTERNAL TABLE timestamps
1367+
STORED AS PARQUET
1368+
LOCATION 'test_files/scratch/push_down_filter_parquet/timestamp_to_time/timestamps.parquet';
1369+
1370+
# Equality against the row at midnight.
1371+
query P
1372+
SELECT ts FROM source_data WHERE CAST(ts AS TIME) = TIME '00:00:00' ORDER BY ts;
1373+
----
1374+
2024-03-11T00:00:00
1375+
1376+
query P
1377+
SELECT ts FROM timestamps WHERE CAST(ts AS TIME) = TIME '00:00:00' ORDER BY ts;
1378+
----
1379+
2024-03-11T00:00:00
1380+
1381+
# Range predicate spanning the wrap point.
1382+
query P
1383+
SELECT ts FROM source_data WHERE CAST(ts AS TIME) < TIME '02:00:00' ORDER BY ts;
1384+
----
1385+
2024-03-11T00:00:00
1386+
2024-03-11T01:00:00
1387+
1388+
query P
1389+
SELECT ts FROM timestamps WHERE CAST(ts AS TIME) < TIME '02:00:00' ORDER BY ts;
1390+
----
1391+
2024-03-11T00:00:00
1392+
2024-03-11T01:00:00
1393+
1394+
# The same predicates spelled with arrow_cast, which also reaches Time32.
1395+
query P
1396+
SELECT ts FROM source_data
1397+
WHERE arrow_cast(ts, 'Time32(Second)') = arrow_cast('00:00:00', 'Time32(Second)')
1398+
ORDER BY ts;
1399+
----
1400+
2024-03-11T00:00:00
1401+
1402+
query P
1403+
SELECT ts FROM timestamps
1404+
WHERE arrow_cast(ts, 'Time32(Second)') = arrow_cast('00:00:00', 'Time32(Second)')
1405+
ORDER BY ts;
1406+
----
1407+
2024-03-11T00:00:00
1408+
1409+
query P
1410+
SELECT ts FROM source_data
1411+
WHERE arrow_cast(ts, 'Time32(Second)') < arrow_cast('02:00:00', 'Time32(Second)')
1412+
ORDER BY ts;
1413+
----
1414+
2024-03-11T00:00:00
1415+
2024-03-11T01:00:00
1416+
1417+
query P
1418+
SELECT ts FROM timestamps
1419+
WHERE arrow_cast(ts, 'Time32(Second)') < arrow_cast('02:00:00', 'Time32(Second)')
1420+
ORDER BY ts;
1421+
----
1422+
2024-03-11T00:00:00
1423+
2024-03-11T01:00:00
1424+
1425+
statement ok
1426+
DROP TABLE timestamps;
1427+
1428+
##########
1429+
# The same wrap happens at the local midnight of a timezone-aware column
1430+
##########
1431+
1432+
# The same three local wall-clock times, tagged +05:00, so the instants
1433+
# actually stored are 18:00, 19:00 and 20:00 UTC on 2024-03-10. Those are
1434+
# monotonic and fall inside a single UTC day, so the min/max bounds only
1435+
# invert if the cast honours the column's timezone.
1436+
statement ok
1437+
CREATE TABLE source_data_tz AS
1438+
SELECT arrow_cast(ts, 'Timestamp(Nanosecond, Some("+05:00"))') AS ts
1439+
FROM source_data;
1440+
1441+
statement ok
1442+
DROP TABLE source_data;
1443+
1444+
statement ok
1445+
COPY (SELECT ts FROM source_data_tz)
1446+
TO 'test_files/scratch/push_down_filter_parquet/timestamp_to_time/timestamps_tz.parquet'
1447+
STORED AS PARQUET
1448+
OPTIONS ('format.max_row_group_size' '3');
1449+
1450+
statement ok
1451+
CREATE EXTERNAL TABLE timestamps_tz
1452+
STORED AS PARQUET
1453+
LOCATION 'test_files/scratch/push_down_filter_parquet/timestamp_to_time/timestamps_tz.parquet';
1454+
1455+
# Pin the shape of the data this section depends on: the local times have to
1456+
# straddle a local midnight while the stored instants stay monotonic within a
1457+
# single UTC day. Without this, a change to how a timezone is attached would
1458+
# leave the queries below passing while no longer exercising the wrap at all.
1459+
query TIT
1460+
SELECT
1461+
ts::string AS local_time,
1462+
to_unixtime(ts) AS stored_instant,
1463+
CAST(ts AS TIME)::string AS time_of_day
1464+
FROM timestamps_tz ORDER BY ts;
1465+
----
1466+
2024-03-10T23:00:00+05:00 1710093600 23:00:00
1467+
2024-03-11T00:00:00+05:00 1710097200 00:00:00
1468+
2024-03-11T01:00:00+05:00 1710100800 01:00:00
1469+
1470+
query P
1471+
SELECT ts FROM source_data_tz WHERE CAST(ts AS TIME) = TIME '00:00:00' ORDER BY ts;
1472+
----
1473+
2024-03-11T00:00:00+05:00
1474+
1475+
query P
1476+
SELECT ts FROM timestamps_tz WHERE CAST(ts AS TIME) = TIME '00:00:00' ORDER BY ts;
1477+
----
1478+
2024-03-11T00:00:00+05:00
1479+
1480+
query P
1481+
SELECT ts FROM source_data_tz WHERE CAST(ts AS TIME) < TIME '02:00:00' ORDER BY ts;
1482+
----
1483+
2024-03-11T00:00:00+05:00
1484+
2024-03-11T01:00:00+05:00
1485+
1486+
query P
1487+
SELECT ts FROM timestamps_tz WHERE CAST(ts AS TIME) < TIME '02:00:00' ORDER BY ts;
1488+
----
1489+
2024-03-11T00:00:00+05:00
1490+
2024-03-11T01:00:00+05:00
1491+
1492+
statement ok
1493+
DROP TABLE timestamps_tz;
1494+
1495+
statement ok
1496+
DROP TABLE source_data_tz;
1497+
13361498
# A predicate that the Parquet scan keeps only for pruning
13371499
# (pushdown_filters = false) must not make the scan claim equivalences.
13381500
# Each file is sorted by (a, b). If the scan claims that `a` is constant,

0 commit comments

Comments
 (0)