Repository navigation
SELECT ... ORDER BY query fails on data with int64 timestamp and timezone field #959
Description
Describe the bug
When trying to query a Parquet file produced by Apache Flink I get an error:
ArrowError(InvalidArgumentError("column types must match schema types, expected Timestamp(Millisecond, Some(\"UTC\")) but found Timestamp(Millisecond, None) at column index 0"))Output of Java parquet-schema:
message Row {
optional int64 system_time (TIMESTAMP(MILLIS,true));
optional int64 reported_date (TIMESTAMP(MILLIS,true));
optional binary province (STRING);
optional int64 total_daily;
}To Reproduce
Download and extract the sample data: data.tar.gz.
Run:
use datafusion::arrow::util::pretty::print_batches;
use datafusion::prelude::*;
#[tokio::main]
async fn main() -> datafusion::error::Result<()> {
let mut ctx = ExecutionContext::new();
ctx.register_parquet("test", "flink.parquet")?;
let df = ctx.table("test")?;
//let df = ctx.sql("select * from test")?;
let df = ctx.sql("select * from test order by reported_date desc")?;
let records = df.collect().await?;
print_batches(&records)?;
Ok(())
}Note that simple select works fine, but ORDER BY fails.
Expected behavior
Query executes without errors.
Reacted by Christopher Watford
Activity
jorgecarleitao commentedon Aug 30, 2021
This is due to a limitation in arrow-rs that does not support timestamps with timezones: every timestamp that enters arrow-rs and is transformed is outputted as a timestamp without timezone. Because
Timestamp(a,Some(b)) != Timestamp(a,None), theRecordBatchrefuses to accept the transformed columns (as it expects one with timestamp).A solution in
DataFusionis to apply an extra cast after every operator from arrow to the expected schema, thereby converting the array back to the expected logical type (until the next operator hits). This implies not using operators that returnRecordBatchsuch asbatch_filter, etc. (since afaik they also error inarrow-rs).sergiimk commentedon Aug 31, 2021
Thanks for the prompt reply @jorgecarleitao.
I followed your advise of re-casting and this query works:
Linking the corresponding arrow issue: apache/arrow-rs#393
I wonder if the impact of not having timezone support in Arrow can be minimized somehow, e.g. by treating
Timestamp(a, None)as equal toTimestamp(a, Some("UTC")).When reading parquet files produced by Spark the timestamps are being encoded as plain
INT96with no logical types, so it seems DataFusion already defaults to UTC?alamb commentedon Aug 31, 2021
Here is a related discussion on the mailing list: https://lists.apache.org/thread.html/ra4c0842067342056a64a8a13e03755d4be58e8dddfbb064ca92ed5a3%40%3Cdev.arrow.apache.org%3E
[-]SELECT ... ORDER BY query fails on data with int64 timestamp field[/-][+]SELECT ... ORDER BY query fails on data with int64 timestamp and timezone field[/+]sergiimk commentedon Dec 20, 2022
Update: Latest
datafusionno longer errors out on timestamps with time zones, but when printing out the column values it displays something like:alamb commentedon Dec 20, 2022
Thanks @sergiimk -- I wonder if we need to update the arrow pretty printing code to handle that better 🤔
Jefffrey commentedon Jan 1, 2024
Seems to be working as expected now on latest main.
Via datafusion-cli:
Can see it properly displays timezone with Z now
cc @alamb @sergiimk
alamb commentedon Jan 1, 2024
Thanks @Jefffrey
I also verified the query runs correctly using an explicitly created external table as well
chore: clarify tarball installation (apache#959)
Add datafusion.extract (apache#959)