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