pingcap / pingcap/tidb

Unexpected Result Difference After CREATE INDEX and ANALYZE TABLE with MAKETIME and CONVERT_TZ

Open
#67,010 2 comments 0 reactions 0 assignees View on GitHub
contribution may-affects-7.1 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

It seems that the query results are inconsistent depending on whether an index is created and analyzed on the table. This behavior is unexpected since the only difference between the two queries is the creation and analysis of an index. The query should return the same result regardless of the presence of the index.

### 1. Minimal reproduce step (Required)

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

-- Create table
CREATE TABLE t0 (c0 CHAR);
INSERT IGNORE INTO t0 VALUES (1);

-- Create index and analyze table
CREATE INDEX i0 ON t0(c0 ASC);
ANALYZE TABLE t0 INDEX i0;

-- Query 1:
SELECT MAKETIME(42, 42, SUM(b'101010')) AS c22
FROM t0 AS tom13
WHERE CONVERT_TZ('2025-12-31 14:30:00', 'Europe/Amsterdam', '-04:30');

-- Drop table and recreate without index
DROP TABLE t0;
CREATE TABLE t0 (c0 CHAR);
INSERT IGNORE INTO t0 VALUES (1);

-- Query 2:
SELECT MAKETIME(42, 42, SUM(b'101010')) AS c22
FROM t0 AS tom13
WHERE CONVERT_TZ('2025-12-31 14:30:00', 'Europe/Amsterdam', '-04:30');

```
### 2. What did you expect to see? (Required)
I expected both queries to return the same result, as the only difference between them is the creation and analysis of an index on the c0 column. The presence or absence of the index should not affect the result of the MAKETIME function or the CONVERT_TZ function.

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

Output for Query 1:

+-----------------+
| c22 |
+-----------------+
| 42:42:42.000000 |
+-----------------+
1 row in set (0.01 sec)
Output for Query 2:

+-----------------+
| c22 |
+-----------------+
| 42:42:00.000000 |
+-----------------+
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.