Document the SQL dialect DataFusion attempts to follow

[alamb]

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

[findepi]

Document the SQL dialect DataFusion attempts to follow

💯
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]

I though this is covered by #12357

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

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.

I know and I agree it is a very important usecase. I will try and update the wording in #13706 to reflect this sentiment

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.

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]

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]

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]

AFAIK the SQL spec is underspecified here

Absolutely right

it has been suggested that ordering and comparison could use different ordering

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.

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).

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!