pingcap / pingcap/tidb

`MIN(GREATEST(<datetime string>, DATE)) GROUP BY` over a `(ROW_NUMBER() = 1)` filter view returns DATETIME instead of DATE

Open
#70,503 2 comments 0 reactions 0 assignees View on GitHub
contribution may-affects-25.10 may-affects-26.3 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/planner type/bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.