`MIN(GREATEST(<datetime string>, DATE)) GROUP BY` over a `(ROW_NUMBER() = 1)` filter view returns DATETIME instead of DATE
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
### 1. Minimal reproduce step (Required)
Not sure if this is expected behavior:
```sql
CREATE TABLE t (id BIGINT, d DATE);
INSERT INTO t VALUES (1, '1999-12-31');
CREATE VIEW v AS SELECT id, d FROM (
SELECT id, d, (ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) = 1) AS q FROM t
) x WHERE q;
SELECT MIN(GREATEST('2016-05-04 10:10:10.100000', d)) FROM v GROUP BY id;
-- Expected: '2016-05-04'
-- Actual: '2016-05-04 10:10:10.100000'
```
However, the same aggregate without the equivalent table is correct:
```sql
CREATE TABLE t (id BIGINT, d DATE);
INSERT INTO t VALUES (1, '1999-12-31');
SELECT MIN(GREATEST('2016-05-04 10:10:10.100000', d)) FROM t GROUP BY id;
-- Expected/actual: '2016-05-04'
```
### 2. What did you expect to see? (Required)
Please see above
### 3. What did you see instead (Required)
Please see above
### 4. What is your TiDB version? (Required)
TiDB v9.0.0-beta.2.pre-2051-g3bea8196a5 @3bea8196 (unistore)
Contributor guide
Research direction
Start by running the supplied SQL reproduction against TiDB v9.0.0-beta.2.pre-2051-g3bea8196a5, then trace type handling across the ROW_NUMBER-filtered view, GREATEST expression, and MIN aggregate. Done means the grouped query returns DATE-compatible output like the equivalent direct-table query, with a regression test covering the reproduction.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100