Skip to content

Commit 9fcf3de

Browse files
fix(type-mapping): keep sub-millisecond timestamp digits in direct storage and filters (#531)
* fix(type-mapping): keep sub-millisecond timestamp digits in direct storage and filters Direct storage declares a Parquet timestamp at its unit through the engine version gate, and declares INT96 at microseconds. A filter whose timestamp literal is finer than the column's declared precision returns native Exasol's rows: the adapter rewrites the comparison (workaround for apache/datafusion#22142), the DataFusion dialect keeps nanosecond literal digits, and Iceberg and Delta pruning derive no bound from a literal finer than microseconds. Closes #461 Co-Authored-By: Claude Opus 5.5 <[email protected]> * docs(spec): treat 8.x timestamp filters as the named 8.x limitation Co-Authored-By: Claude Opus 5.5 <[email protected]> --------- Co-authored-by: Claude Opus 5.5 <[email protected]>
1 parent b64069f commit 9fcf3de

22 files changed

Lines changed: 612 additions & 40 deletions

File tree

‎Cargo.lock‎

Lines changed: 2 additions & 2 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎crates/lakehouse-e2e/tests/common/timestamp_precision.rs‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,3 +64,38 @@ pub fn engine_honors_declared_precision(conn: &mut ExaConn) -> bool {
6464
let version = live_engine_version(conn);
6565
leading_version_component(&version).is_none_or(|year| year >= 2025)
6666
}
67+
68+
/// A native copy holds the values Exasol reads, so each filter must select the same `ID`s from
69+
/// it as from the virtual `table`. Holds only where the declaration keeps every source digit:
70+
/// on 8.x the copy holds millisecond-truncated values, not the source data.
71+
pub fn assert_filters_match_native_copy(
72+
conn: &mut ExaConn,
73+
table: &str,
74+
columns: &str,
75+
filters: &[&str],
76+
) {
77+
let native = "TS_FILTER_ORACLE.NATIVE_COPY";
78+
conn.execute("CREATE SCHEMA IF NOT EXISTS TS_FILTER_ORACLE");
79+
conn.execute(&format!(
80+
"CREATE OR REPLACE TABLE {native} AS SELECT ID, {columns} FROM {table}"
81+
));
82+
let ids = |conn: &mut ExaConn, from: &str, filter: &str| -> Vec<String> {
83+
let sql = format!("SELECT TO_CHAR(ID) FROM {from} WHERE {filter} ORDER BY ID");
84+
conn.query_columns(&sql)
85+
.first()
86+
.map(|column| {
87+
column
88+
.iter()
89+
.map(|id| id.as_str().unwrap_or_default().to_owned())
90+
.collect()
91+
})
92+
.unwrap_or_default()
93+
};
94+
for filter in filters {
95+
assert_eq!(
96+
ids(conn, table, filter),
97+
ids(conn, native, filter),
98+
"WHERE {filter} on {table} must select the rows native Exasol selects"
99+
);
100+
}
101+
}

‎crates/lakehouse-e2e/tests/e2e_local/direct_storage.rs‎

Lines changed: 140 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -24,14 +24,17 @@ use crate::common::seed::{
2424
time64_values,
2525
};
2626
use crate::common::stack::{CatalogConnectionPassword, seaweedfs_url_internal};
27+
use crate::common::timestamp_precision::{
28+
assert_filters_match_native_copy, engine_honors_declared_precision,
29+
};
2730
use crate::fixtures::{stack, vs_table};
2831

2932
use arrow::array::{
3033
Array, ArrayRef, BooleanArray, Date32Array, Decimal128Array, Decimal256Array,
3134
DurationMicrosecondArray, Float32Array, Float64Array, Int32Array, Int64Array,
3235
IntervalDayTimeArray, ListBuilder, MapArray, StringArray, StringBuilder, StructArray,
33-
Time32MillisecondArray, TimestampMicrosecondArray, UInt8Array, UInt16Array, UInt32Array,
34-
UInt64Array,
36+
Time32MillisecondArray, TimestampMicrosecondArray, TimestampNanosecondArray, UInt8Array,
37+
UInt16Array, UInt32Array, UInt64Array,
3538
};
3639
use arrow::buffer::{NullBuffer, OffsetBuffer};
3740
use arrow::compute::cast;
@@ -533,10 +536,58 @@ fn all_types_batch() -> RecordBatch {
533536
("c_largebinary", binary_values(&DataType::LargeBinary), true),
534537
("c_fixedsizebinary", fixed_16_values(), true),
535538
("c_struct_binary", struct_binary_values(None), true),
539+
(
540+
"c_ts_ms",
541+
timestamp_unit_values(TimeUnit::Millisecond, None),
542+
true,
543+
),
544+
(
545+
"c_ts_us",
546+
timestamp_unit_values(TimeUnit::Microsecond, None),
547+
true,
548+
),
549+
(
550+
"c_ts_ns",
551+
timestamp_unit_values(TimeUnit::Nanosecond, None),
552+
true,
553+
),
554+
(
555+
"c_tstz_ms",
556+
timestamp_unit_values(TimeUnit::Millisecond, Some("UTC")),
557+
true,
558+
),
559+
(
560+
"c_tstz_us",
561+
timestamp_unit_values(TimeUnit::Microsecond, Some("UTC")),
562+
true,
563+
),
564+
(
565+
"c_tstz_ns",
566+
timestamp_unit_values(TimeUnit::Nanosecond, Some("UTC")),
567+
true,
568+
),
536569
])
537570
.expect("all_types batch")
538571
}
539572

573+
/// 2025-06-01 00:00:00.123456789 UTC: every unit's own digits differ from the coarser units'.
574+
const TIMESTAMP_UNITS_NANOS: i64 = 1_748_736_000_123_456_789;
575+
576+
/// `TIMESTAMP_UNITS_NANOS` truncated to `unit`, the epoch, and NULL.
577+
fn timestamp_unit_values(unit: TimeUnit, tz: Option<&str>) -> ArrayRef {
578+
let divisor = match unit {
579+
TimeUnit::Millisecond => 1_000_000,
580+
TimeUnit::Microsecond => 1_000,
581+
_ => 1,
582+
};
583+
let nanos: ArrayRef = Arc::new(TimestampNanosecondArray::from(vec![
584+
Some(TIMESTAMP_UNITS_NANOS / divisor * divisor),
585+
Some(0),
586+
None,
587+
]));
588+
cast(&nanos, &DataType::Timestamp(unit, tz.map(Into::into))).expect("timestamp unit cast")
589+
}
590+
540591
/// Types only a Parquet logical-type annotation, or its absence, can declare, written with
541592
/// no embedded Arrow schema so the reader sees the annotation alone.
542593
fn annotated_types_bytes() -> Bytes {
@@ -1099,10 +1150,10 @@ fn events_directory_declares_and_returns_mixed_types_across_both_files() {
10991150
"EVENTS.{column}"
11001151
);
11011152
}
1102-
let event_ts = declared_type(&mut conn, VS_DIRECT, "EVENTS", "EVENT_TS");
1103-
assert!(
1104-
event_ts.starts_with("TIMESTAMP"),
1105-
"EVENTS.EVENT_TS must declare a TIMESTAMP of the engine's precision, got {event_ts}"
1153+
assert_eq!(
1154+
declared_type(&mut conn, VS_DIRECT, "EVENTS", "EVENT_TS"),
1155+
TimestampUnitExpectations::for_engine(&mut conn).us.0,
1156+
"EVENTS.EVENT_TS is a microsecond Parquet timestamp"
11061157
);
11071158

11081159
let cols = conn.query_columns(&format!(
@@ -1209,12 +1260,15 @@ fn complex_directory_declares_varchar_and_returns_parseable_json() {
12091260
/// Scenario: Out-of-range Decimal128 falls back to VARCHAR via JSON
12101261
/// Scenario: A Parquet-sourced column maps through the Arrow-input direction
12111262
/// Scenario: The listing declares a binary column
1263+
/// Scenario: A direct-storage Parquet timestamp declares the precision of its unit
1264+
/// Scenario: A timestamp filter compares at full precision
12121265
#[test]
12131266
fn all_types_directories_declare_and_return_their_mapped_values() {
12141267
setup();
12151268
let mut conn = exa_conn();
12161269

12171270
let binary: &[&str] = &["type 'binary'", "#351"];
1271+
let units = TimestampUnitExpectations::for_engine(&mut conn);
12181272
assert_type_matrix(
12191273
&mut conn,
12201274
VS_DIRECT,
@@ -1286,6 +1340,12 @@ fn all_types_directories_declare_and_return_their_mapped_values() {
12861340
VARCHAR_JSON,
12871341
&["member 'c_struct_binary.x'", "type 'binary'", "#351"],
12881342
),
1343+
reads("C_TS_MS", units.ms.0, units.ms.1),
1344+
reads("C_TS_US", units.us.0, units.us.1),
1345+
reads("C_TS_NS", units.ns.0, units.ns.1),
1346+
reads("C_TSTZ_MS", units.ms.0, units.ms.1),
1347+
reads("C_TSTZ_US", units.us.0, units.us.1),
1348+
reads("C_TSTZ_NS", units.ns.0, units.ns.1),
12891349
],
12901350
);
12911351
assert_decimal_orders_numerically(&mut conn, VS_DIRECT, "ALL_TYPES", "C_DECIMAL128_18_0");
@@ -1300,7 +1360,7 @@ fn all_types_directories_declare_and_return_their_mapped_values() {
13001360
refuses("C_UUID", VARCHAR_JSON, &["type 'uuid'", "#351"]),
13011361
reads(
13021362
"C_INT96",
1303-
"TIMESTAMP(3)",
1363+
units.us.0,
13041364
[
13051365
Some("2024-01-15 10:30:45.000000"),
13061366
Some("1970-01-01 00:00:00.000000"),
@@ -1315,6 +1375,79 @@ fn all_types_directories_declare_and_return_their_mapped_values() {
13151375
),
13161376
],
13171377
);
1378+
1379+
if engine_honors_declared_precision(&mut conn) {
1380+
let all_types = vs_table(VS_DIRECT, "ALL_TYPES");
1381+
assert_filters_match_native_copy(
1382+
&mut conn,
1383+
&all_types,
1384+
"C_TS_MS, C_TS_US, C_TS_NS, C_TSTZ_MS, C_TSTZ_NS",
1385+
TIMESTAMP_UNIT_FILTERS,
1386+
);
1387+
conn.execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9'");
1388+
let nanos = conn.query_columns(&format!(
1389+
"SELECT C_TS_NS, C_TSTZ_NS FROM {all_types} WHERE ID = 1"
1390+
));
1391+
for column in &nanos {
1392+
assert_eq!(value_to_string(&column[0]), "2025-06-01 00:00:00.123456789");
1393+
}
1394+
}
1395+
}
1396+
1397+
const TIMESTAMP_UNIT_FILTERS: &[&str] = &[
1398+
"C_TS_MS = TIMESTAMP '2025-06-01 00:00:00.123000000'",
1399+
"C_TS_MS = TIMESTAMP '2025-06-01 00:00:00.123500'",
1400+
"C_TS_MS <> TIMESTAMP '2025-06-01 00:00:00.123500'",
1401+
"NOT (C_TS_MS = TIMESTAMP '2025-06-01 00:00:00.123500')",
1402+
"C_TS_MS < TIMESTAMP '2025-06-01 00:00:00.123500'",
1403+
"C_TS_MS <= TIMESTAMP '2025-06-01 00:00:00.122500'",
1404+
"C_TS_MS > TIMESTAMP '2025-06-01 00:00:00.122500'",
1405+
"C_TS_MS >= TIMESTAMP '2025-06-01 00:00:00.123500'",
1406+
"TIMESTAMP '2025-06-01 00:00:00.123500' > C_TS_MS",
1407+
"C_TS_MS BETWEEN TIMESTAMP '2025-06-01 00:00:00.122500' \
1408+
AND TIMESTAMP '2025-06-01 00:00:00.123500'",
1409+
"C_TS_MS NOT BETWEEN TIMESTAMP '2025-06-01 00:00:00.123100' \
1410+
AND TIMESTAMP '2025-06-01 00:00:00.123500'",
1411+
"C_TS_MS IN (TIMESTAMP '2025-06-01 00:00:00.123500', TIMESTAMP '1970-01-01 00:00:00.000500')",
1412+
"C_TS_MS NOT IN (TIMESTAMP '2025-06-01 00:00:00.123500', TIMESTAMP '1970-01-01 00:00:00')",
1413+
"C_TS_MS IS NULL OR C_TS_MS = TIMESTAMP '2025-06-01 00:00:00.123500'",
1414+
"C_TS_US = TIMESTAMP '2025-06-01 00:00:00.123456'",
1415+
"C_TS_US = TIMESTAMP '2025-06-01 00:00:00.123456789'",
1416+
"C_TS_US < TIMESTAMP '2025-06-01 00:00:00.123456001'",
1417+
"C_TS_US >= TIMESTAMP '2025-06-01 00:00:00.123456001'",
1418+
"C_TS_US IN (TIMESTAMP '2025-06-01 00:00:00.123456789', TIMESTAMP '1970-01-01 00:00:00')",
1419+
"C_TS_NS = TIMESTAMP '2025-06-01 00:00:00.123456789'",
1420+
"C_TS_NS = TIMESTAMP '2025-06-01 00:00:00.123456'",
1421+
"C_TS_NS > TIMESTAMP '2025-06-01 00:00:00.123456'",
1422+
"C_TSTZ_MS = TIMESTAMP '2025-06-01 00:00:00.123500'",
1423+
"C_TSTZ_NS = TIMESTAMP '2025-06-01 00:00:00.123456789'",
1424+
"C_TS_MS = C_TS_NS",
1425+
"C_TS_US < C_TS_NS",
1426+
];
1427+
1428+
type DeclaredAndValues = (&'static str, [Option<&'static str>; 3]);
1429+
1430+
/// Declared type and six-digit wire render of each [`timestamp_unit_values`] unit.
1431+
struct TimestampUnitExpectations {
1432+
ms: DeclaredAndValues,
1433+
us: DeclaredAndValues,
1434+
ns: DeclaredAndValues,
1435+
}
1436+
1437+
impl TimestampUnitExpectations {
1438+
fn for_engine(conn: &mut ExaConn) -> Self {
1439+
let read = |first| [Some(first), Some("1970-01-01 00:00:00.000000"), None];
1440+
let ms = ("TIMESTAMP(3)", read("2025-06-01 00:00:00.123000"));
1441+
if !engine_honors_declared_precision(conn) {
1442+
return Self { ms, us: ms, ns: ms };
1443+
}
1444+
Self {
1445+
ms,
1446+
us: ("TIMESTAMP(6)", read("2025-06-01 00:00:00.123456")),
1447+
// The wire renders six digits; the FF9 check reads the rest.
1448+
ns: ("TIMESTAMP(9)", read("2025-06-01 00:00:00.123456")),
1449+
}
1450+
}
13181451
}
13191452

13201453
#[test]

‎crates/lakehouse-e2e/tests/e2e_local/timestamp_precision.rs‎

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10,8 +10,8 @@ use crate::common::seed::{
1010
};
1111
use crate::common::stack::iceberg_catalog_url;
1212
use crate::common::timestamp_precision::{
13-
ExpectedTimestampPrecision, engine_honors_declared_precision, expected_timestamp_precision,
14-
live_engine_version,
13+
ExpectedTimestampPrecision, assert_filters_match_native_copy, engine_honors_declared_precision,
14+
expected_timestamp_precision, live_engine_version,
1515
};
1616
use crate::fixtures::{assert_pushed_to_scan_udf, emits_clause, stack};
1717
use std::sync::OnceLock;
@@ -106,6 +106,7 @@ fn assert_emits_declares(conn: &mut ExaConn, query_sql: &str, expected: &str) ->
106106
pushdown_sql
107107
}
108108

109+
/// Scenario: A timestamp filter compares at full precision
109110
#[test]
110111
fn iceberg_microsecond_timestamps_round_trip_at_the_declared_precision() {
111112
setup();
@@ -158,6 +159,23 @@ fn iceberg_microsecond_timestamps_round_trip_at_the_declared_precision() {
158159
&format!("COUNT(DISTINCT {column})"),
159160
);
160161
}
162+
163+
if engine_honors_declared_precision(&mut conn) {
164+
assert_filters_match_native_copy(
165+
&mut conn,
166+
&qualified,
167+
"TS, TSTZ, TS_NS",
168+
&[
169+
"TS < TIMESTAMP '2024-01-01 00:00:00.000001001'",
170+
"TS = TIMESTAMP '2024-01-01 00:00:00.000001001'",
171+
"TS <= TIMESTAMP '2024-01-01 00:00:00.123456999'",
172+
"TS > TIMESTAMP '2024-01-01 00:00:00.123456001'",
173+
"TSTZ < TIMESTAMP '2024-01-01 00:00:00.000001001'",
174+
"TS_NS = TIMESTAMP '2024-01-01 00:00:00.000000001'",
175+
"TS_NS < TIMESTAMP '2024-01-01 00:00:00.000000002'",
176+
],
177+
);
178+
}
161179
}
162180

163181
fn qualified_probe() -> String {

‎crates/lakehouse-engine/Cargo.toml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
[package]
22
name = "lakehouse-engine"
3-
version = "0.56.5"
3+
version = "0.56.6"
44
edition = "2024"
55
license.workspace = true
66

‎crates/lakehouse-engine/src/adapter/iceberg_predicate.rs‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@ use iceberg::spec::{Datum, PrimitiveType, Schema};
66
use serde_json::Value as Json;
77

88
use crate::scan::spec::LogicalField;
9+
use crate::types::mapping::timestamp_literal_digits;
910

1011
/// Resolves a filter column through its declared field id to the field the scanned schema carries
1112
/// under that id: bounds are stored by field id, so a rename must not prune by another's bounds.
@@ -73,14 +74,15 @@ fn literal_to_datum(lit: &Json, prim: &PrimitiveType) -> Option<Datum> {
7374
Datum::date_from_str(s).ok()
7475
}
7576

77+
// `Datum` parses at most microseconds; a truncated finer literal would prune matching files.
7678
("literal_timestamp", PrimitiveType::Timestamp | PrimitiveType::TimestampNs) => {
7779
let s = lit.get("value")?.as_str()?;
78-
parse_timestamp_no_tz(s, prim)
80+
(timestamp_literal_digits(s) <= 6).then(|| parse_timestamp_no_tz(s, prim))?
7981
}
8082

8183
("literal_timestamp_utc", PrimitiveType::Timestamptz | PrimitiveType::TimestamptzNs) => {
8284
let s = lit.get("value")?.as_str()?;
83-
parse_timestamptz(s, prim)
85+
(timestamp_literal_digits(s) <= 6).then(|| parse_timestamptz(s, prim))?
8486
}
8587

8688
_ => None,

‎crates/lakehouse-engine/src/adapter/iceberg_predicate_tests.rs‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -208,6 +208,18 @@ fn leaf_date_translates() {
208208
assert!(s.contains("event_date"), "got: {s}");
209209
}
210210

211+
#[test]
212+
fn timestamp_literal_finer_than_microseconds_yields_no_datum() {
213+
for prim in [PrimitiveType::Timestamp, PrimitiveType::TimestampNs] {
214+
let at = |value| json!({"type": "literal_timestamp", "value": value});
215+
assert_eq!(
216+
literal_to_datum(&at("2024-01-15 10:30:00.123456789"), &prim),
217+
None
218+
);
219+
assert!(literal_to_datum(&at("2024-01-15 10:30:00.123456000"), &prim).is_some());
220+
}
221+
}
222+
211223
#[test]
212224
fn and_with_untranslatable_child_keeps_translatable_conjunct() {
213225
let translatable = json!({

0 commit comments

Comments
 (0)