pingcap / pingcap/tidb

Unexpected Result Difference After Changing Table Structure with Different Collations

Open
#67,011 4 comments 0 reactions 1 assignee Claimed by @xhebox View on GitHub
contribution may-affects-7.1 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/sql-infra type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

It seems that the only difference between the two queries is the change in the table structure, specifically the collation of the c0 column, yet this difference causes the results to diverge unexpectedly. The query should return consistent results regardless of the collation or primary key constraints on the column. This suggests that the collation setting may be affecting the behavior of the query.

### 1. Minimal reproduce step (Required)

```sql

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

-- Create table with PRIMARY KEY constraint
CREATE TABLE t2(c0 CHAR , PRIMARY KEY(c0));
REPLACE INTO t2(c0) VALUES (false);

-- Query 1:
SELECT RADIANS(MAX(b'1')) AS c0
FROM t2 AS tom0
GROUP BY tom0.c0;

-- Drop table and recreate with different collation (utf8mb4_bin)
DROP TABLE t2;
CREATE TABLE t2(c0 CHAR );
REPLACE INTO t2(c0) VALUES (false);

-- Query 2:
SELECT RADIANS(MAX(b'1')) AS c0
FROM t2 AS tom0
GROUP BY tom0.c0;

```

### 2. What did you expect to see? (Required)
I expected both queries to return the same result, as the only difference between the two queries is the change in collation and the primary key constraint. The collation should not affect the result of the RADIANS function or the aggregation.
### 3. What did you see instead (Required)
```sql

Output for Query 1:

+----------------------+
| c0 |
+----------------------+
| 0.017453292519943295 |
+----------------------+
1 row in set (0.00 sec)
Output for Query 2:

+------+
| c0 |
+------+
| 0 |
+------+
1 row in set, 1 warning (0.01 sec)
```

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

```sql
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.5.5
Edition: Community
Git Commit Hash: 1fa258b833ff113883beeba40bc130be7ce66610
Git Branch: HEAD
UTC Build Time: 2026-03-12 08:34:59
GoVersion: go1.25.5
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.