lets-order-some-fries opened a new pull request, #67238:
URL: https://github.com/apache/doris/pull/67238
### What problem does this PR solve?
Issue Number: close #66120
Related PR: #64127
Problem Summary:
`DATEDIFF` and the other eleven `*_diff` scalar functions silently return
off-by-one (or off-by-N) results when (1) an argument is a string-typed
**non-literal** — a varchar column, subquery projection slot, or UNION-ALL
output slot — and (2) the session `time_zone` is not UTC.
All 12 functions list their TIMESTAMPTZ signature first in `SIGNATURES`. The
timezone-coercion penalty in `SearchSignature.doMatchTypes` only fires for
literals it can inspect (`ExpressionUtils.getLiteralAfterUnwrapNullable`, added
in #64127), so for a varchar `SlotReference` every candidate signature ties and
the tie-break keeps the first-listed candidate — TIMESTAMPTZ. The varchar value
is then cast through `timestamptz(6)`, which treats it as session-local time
and shifts it to UTC, so day-number arithmetic runs on the shifted value.
Fix, as proposed by @Baymine in #66120: move the TimeStampTz signature to
**last** in each of the 12 `*Diff` files. Literal-with-timezone arguments still
route to TIMESTAMPTZ via the `timeZoneCoersionScore` branch; typed TIMESTAMPTZ
/ DATETIMEV2 / DATEV2 arguments still bind their identical-match signatures.
Only the previously order-decided tie for non-literal string arguments changes:
TIMESTAMPTZ → DATETIMEV2 (wall-clock semantics, independent of session time
zone).
New unit test `DiffFunctionSignatureTest` covers all 12 functions in both
directions: varchar slots must bind DATETIMEV2 (fails on master before this
change) and TIMESTAMPTZ slots must still bind TIMESTAMPTZ. Happy to also add a
SQL regression suite mirroring the issue's reproducer (varchar column /
subquery slot / UNION-ALL slot under `+08:00`) if wanted — omitted here because
the `.out` expected-files need a cluster run to generate.
### Release note
Fixed DATEDIFF and the other *_diff functions returning wrong results on
varchar columns and subquery slots when the session time_zone is not UTC.
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [ ] Regression test
- [x] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [ ] No.
- [x] Yes. <!-- Explain the behavior change --> For `*_diff` calls whose
string-typed argument is NOT a literal, the argument now binds to DATETIMEV2
(wall-clock) instead of TIMESTAMPTZ (UTC-shifting). That re-bind is the fix;
literal arguments and typed date/datetime/timestamptz arguments are unaffected.
- Does this need documentation?
- [x] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
