dolthub / dolthub/dolt

Date-valued window arithmetic appends a zero-time suffix

Open
#11,505 0 comments 0 reactions 0 assignees View on GitHub
bug correctness customer issue good repro sql
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.