PostgreSQL compatibility: `extract(second ...)` should preserve fractional seconds

[Dandandan]

Split out from #22247.

Mismatch

extract(second ...) should preserve fractional seconds.

SELECT extract(second from timestamp '2020-01-01 00:00:12.345678');

PostgreSQL:

12.345678

DataFusion:

12

Expected behavior

For PostgreSQL-compatible SQL semantics, DataFusion should either match PostgreSQL's result or raise the same class of domain/semantic error.

[aguilaredu]

take

[kumarUjjawal]

duckdb

memory D   SELECT extract(second FROM TIMESTAMP '2020-01-01 00:00:12.345678');
┌───────────────────────────────────────────────────────────────────────────┐
│ main.date_part('second', CAST('2020-01-01 00:00:12.345678' AS TIMESTAMP)) │
│                                   int64                                   │
├───────────────────────────────────────────────────────────────────────────┤
│                                                                        12 │

DuckDB's date-part design returns integers across all sub-day fields.

[aguilaredu]

The date_part function previously returned float64. It was changed to follow ANSI SQL convention and also as a broader effort to improve ClickBench performance. See #13482 and #13449.

Additionally, this other issue could also be solved if the microsecond calculations were made with f64: #14738. However, it was concluded that the fix should be made in arrow-rs.

I am still working on the change, but wanted to bring it up for discussion.

cc @Dandandan @kumarUjjawal @alamb

[kumarUjjawal]

Duckdb behaviour: except for julian and epoch, which return DOUBLEs, all parts are extracted as integers. Fractional precision is intentionally exposed through separate parts (millisecond, microsecond) rather than as the fractional component of second.

[alamb]

I don't know about this particular issue, but I think we should be careful with changing semantics just to conform more closely to some particular implementation

• Add SQL as a category in breaking API change policy #22179

I tried in the past to get consensus on what semantics to follow but we did not reach it:

• Document the SQL dialect DataFusion attempts to follow #13704
• Document SQL dialect guidance #13706

[kumarUjjawal]

IMO we should stick to the current behavior unless someone has a use case where they would benefit from semantics change like this.

[neilconway]

My two cents, on the topic of Postgres compatibility / SQL dialect in general:

• I think the SQL standard is a good place to start. In most situations, that is what Postgres follows, usually unless there is a good reason to deviate.
• I think that's a reasonable rule of thumb for us as well: follow the SQL spec and/or Postgres in most circumstances / as a reasonable default, but if there is a good reason to deviate, that can be justified.

As far as when to preserve existing behavior, I think DataFusion's current behavior in a lot of corner cases is either unintentional or actively buggy (#14612, #14272, #15161 are a few examples that come to mind in just type coercion!). SQL is a large and complex language and DF's implementation is relatively new -- there will (hopefully!) be many more new users in DataFusion in the coming years than there are today :) So while I think we should be aware of SQL backward compatibility and try to ease migration pain as much as we can (e.g., clear release notes / upgrade guidance, config flags when necessary), I do think evolving the SQL dialect to be more consistent and standards-conformant is a good thing.

[alamb]

I think the SQL standard is a good place to start. In most situations, that is what Postgres follows, usually unless there is a good reason to deviate.

I know people love to bandy around the term "SQL Standard" (and I have a copy of "Information technology — Database languages —SQL — Part 2: Foundation (SQL/Foundation)" !) but it is a pretty bad example of a spec in my opinion and leaves a lot of things "implementations defined"

For example here is an excerpt about extract

7. If is specified, then
   Case:
   a) If is a that does not specify SECOND or is
   not a , then the declared type of the result is an implementation-defined exact
   numeric type with scale 0 (zero).
   b) Otherwise, the declared type of the result is an implementation-defined exact numeric type with scale
   not less than the specified or implied or , as appropriate, of the SECOND of the .

[neilconway]

it is a pretty bad example of a spec in my opinion and leaves a lot of things "implementations defined"

No argument from me that the SQL spec isn't perfect 😊 I do think it's often a good data point to consider, along with Postgres (which usually does a pretty decent job of trying to follow the spec).