Document the SQL dialect DataFusion attempts to follow

### 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 https://github.com/apache/datafusion/pull/13683#issuecomment-2526344085

### 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_