pingcap / pingcap/tidb

New ONLY_FULL_GROUP_BY FD checker treats hash-colliding CAST expressions as equivalent and persists arbitrary non-grouped values

Open
#70,983 0 comments 0 reactions 0 assignees View on GitHub
affects-9.0 component/expression found-by-ai 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

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```sql
CREATE TABLE t_c (id INT PRIMARY KEY, dt DATETIME);
CREATE TABLE t_d LIKE t_c;
INSERT INTO t_c VALUES
(1, '2024-01-15 11:00:00'),
(2, '2024-01-15 09:00:00'),
(3, '2024-01-15 10:00:00');
INSERT INTO t_d VALUES
(1, '2024-01-15 09:00:00'),
(2, '2024-01-15 11:00:00'),
(3, '2024-01-15 10:00:00');

SET SESSION tidb_enable_new_only_full_group_by_check = ON;

SELECT CAST(dt AS DATETIME) FROM t_c GROUP BY CAST(dt AS DATE);
-- actual: 2024-01-15 11:00:00

SELECT CAST(dt AS DATETIME) FROM t_d GROUP BY CAST(dt AS DATE);
-- actual: 2024-01-15 09:00:00
```

The same group key `2024-01-15` produces different selected datetimes solely because a different row has the lowest primary key. That proves the value is not determined by the group key.

Persistent consequence:

```sql
CREATE TABLE out_c (x DATETIME);
INSERT INTO out_c
SELECT CAST(dt AS DATETIME) FROM t_c GROUP BY CAST(dt AS DATE);
SELECT x FROM out_c; -- 2024-01-15 11:00:00, persisted instead of 1055
```

### 2. What did you expect to see? (Required)

With the default SQL mode (which contains `ONLY_FULL_GROUP_BY`) and the supported optimizer switch `tidb_enable_new_only_full_group_by_check=ON`, TiDB accepts a select expression that is not functionally dependent on the group key:

```sql
SELECT CAST(dt AS DATETIME)
FROM t
GROUP BY CAST(dt AS DATE);
```

The two `CAST` expressions are semantically different, but they share an ordinary `Expression.HashCode()` because `ScalarFunction.ReHashCode` appends only the broad return `EvalType` for `CAST`; both `DATE` and `DATETIME` use `ETDatetime`.

`LogicalAggregation.ExtractFD` registers the `GROUP BY` expression under that hash. `LogicalProjection.ExtractFD` then sees the same hash for the select expression and adds an equivalence between the group-by scalar ID and the projection output. The new only-full-group-by checker treats the output as functionally dependent, so the statement succeeds and the executor returns an arbitrary row's datetime per group. `INSERT ... SELECT` persists the arbitrary value.

The bug requires the new checker switch (default OFF); default SQL mode already contains `ONLY_FULL_GROUP_BY`, so no custom SQL mode is needed.

### 3. What did you see instead (Required)

new checker ON accepts SELECT CAST(dt AS DATETIME) FROM t GROUP BY CAST(dt AS DATE); same group key yields 2024-01-15 11:00:00 vs 2024-01-15 09:00:00 depending on which row has the lowest PK; INSERT ... SELECT stores both values. Controls: non-colliding CAST(dt AS CHAR) errors 1055; legacy checker OFF errors 1055.

### 4. What is your TiDB version? (Required)

```text
Release Version: v8.4.0-this-is-a-placeholder
Edition: Community
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.25.12
Race Enabled: false
Check Table Before Drop: false
Store: unistore
Kernel Type: Classic
```

Built from source commit `a514a92784c9654502686e6ee6efc9e0aeda8afa` (pingcap/tidb master, 2026-09-07).

### 5. Root cause (optional)

- pkg/planner/core/operator/logicalop/logical_aggregation.go:373-395 LogicalAggregation.ExtractFD registers GROUP BY scalar hash
- pkg/planner/core/operator/logicalop/logical_projection.go:429-447 LogicalProjection.ExtractFD adds equivalence on hash match
- pkg/planner/core/logical_plan_builder.go:1899 new ONLY_FULL_GROUP_BY checker consumes projection FD
- pkg/expression/scalar_function.go:765-791 ReHashCode appends only EvalType for CAST

Contributor guide

Open the contributing guide

Research direction

Reproduce the queries with tidb_enable_new_only_full_group_by_check enabled, then read LogicalAggregation.ExtractFD in pkg/planner/core/operator/logicalop/logical_aggregation.go, LogicalProjection.ExtractFD in logical_projection.go, and the checker in pkg/planner/core/logical_plan_builder.go. Compare these with CAST hashing in pkg/expression/scalar_function.go. Done means the colliding CAST expressions are not treated as equivalent and the supplied query is rejected with error 1055, while the control cases remain valid.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.