Date-valued window arithmetic appends a zero-time suffix
- Dominant language
- Go
- Stars
- 24.5k
- Forks
- 873
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 120
Description
## What happened
Dolt exhibits this behavior: date-valued window arithmetic appends a zero-time suffix. Not sure whether this is intended.
## Environment
Dolt latest `main` commit `59fb843bf6a4b653d7c8b6d997a603b10cf279d9` (`dolt version 2.2.3`, ICU build).
## How to reproduce
```sql
CREATE TABLE t(id INT PRIMARY KEY, d DATE);
INSERT INTO t VALUES (1, '2024-01-01'), (2, '2024-01-02');
SELECT id,
FIRST_VALUE(d) OVER (ORDER BY id) + 0 AS actual
FROM t
ORDER BY id;
```
## Expected result
MySQL 8.0.43 fresh run:
```text
id actual
1 20240101
2 20240101
```
MySQL's numeric context converts a DATE to its exact `YYYYMMDD` integer.
## Actual result
```text
id actual
1 20240101000000
2 20240101000000
```
The 14-digit datetime form is returned on every fresh replay. Related exact integer operators (`-`, `DIV`, and `MOD`) exhibit the same conversion, while the `+ 0` witness is the minimal representative.
Contributor guide
No contributing guide indexed for this repository
Research direction
Run the supplied SQL reproduction on Dolt and compare its DATE arithmetic with the expected MySQL result. Trace the numeric conversion used by window expressions and the related -, DIV, and MOD operators; done means + 0 returns the exact YYYYMMDD value without a zero-time suffix.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100