Repository navigation
Document the SQL dialect DataFusion attempts to follow #13704
Description
Is your feature request related to a problem or challenge?
Determining what is the "correct behavior" comes frequently deciding how a function should behave and there is disagreement across implementations (e.g. postgres does something different than spark and/or duckdb)
This happened most recently here with @comphead and @jayzhan211 and @Kimahriman in #13683 (comment)
Describe the solution you'd like
I don't think our codebase is super consistent, but I think it would help to at least document our shared understanding of what we are aiming for.
Describe alternatives you've considered
No response
Additional context
No response
Reacted by Oleks V and riziles
Activity
findepi commentedon Dec 10, 2024
💯
I though this is covered by #12357
(eg do we accept features based on "but it works in PostgresQL" or "but it works in my database")
However, this should pertain the DataFusion frontend only.
People are building database systems based on DataFusion where frontend is not DataFusion and SQL dialect is not DF's either. For example DataFusion Ballista effectively implements Spark SQL. SDF (not OSS) implements various SQL dialects on top of DF, etc.
Thus, while it's important to be explicit what the frontend is meant to accept, it's also important to limit impact of this decision to DataFusion frontend only. It cannot trickle down across whole codebase.
(this comment obviously relates to #12723)
alamb commentedon Dec 10, 2024
I could have included it in the same PR, but I thought this might generate some more discussion so might be better kept as a separate PR / issue
I know and I agree it is a very important usecase. I will try and update the wording in #13706 to reflect this sentiment
Yes, 100% agree. I also think it is worth thinking how we can keep any "postgres specific" stuff localized (or localize it even more0 to make it easier to override for systems that have different needs
findepi commentedon Jul 23, 2025
For floating point numbers semantics I propose that we follow Trino, which in turn follows IEEE 754 -- translating floating point standard primitive operations into as-consistent-as-possible SQL-world behavior.
See #16745 (comment) and #16745 (comment) for more context.
cc @berkaysynnada
tustvold commentedon Jul 23, 2025
FWIW arrow-rs consistently follows the totalOrder predicate as defined in the IEEE 754 (2008 revision) floating point standard. This is pretty fundamental to things like the row format and would be a substantial effort to change, if even possible.
Aside from being an actual standard, as opposed to following what some databases happen to do, having a consistent total order is IMO quite compelling. AFAIK the SQL spec is underspecified here, or at the very least lots of databases do different things.
FWIW there is some ongoing discussion around adding this total ordering to parquet as a way to order statistics (the current approach makes statistics unusable for floats).
Edit: it has been suggested that ordering and comparison could use different ordering, I think this would be extremely confusing and personally think it would be a bad idea.
findepi commentedon Jul 23, 2025
Absolutely right
That's indeed what databases do.
In fact, it's impossible to satisfy two following goals at once: have total ordering AND have any comparison involving NaN return false.
That' a good point.
In fact, eg Trino has some conservative treatment of floating point types (like here https://github.com/trinodb/trino/blob/da94e0726fa48bd2e31a91ba26ff7d72aa01085a/core/trino-main/src/main/java/io/trino/sql/planner/DomainTranslator.java#L221-L229).
Improving parquet spec (and then Iceberg) around this is a great idea, especially once all writer implementation catch up with this!