pingcap / pingcap/tidb

Unexpected Result Difference with ANY_VALUE and MIN Aggregation in HAVING Clause

Open
#67,012 5 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 behave unexpectedly based on the structure and data of the table. The result should be consistent, but the query returns different outputs depending on whether the table contains a PRIMARY KEY constraint and how the data is inserted. The difference in results is not expected, especially when only the primary key constraint differs.

### 1. Minimal reproduce step (Required)

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

-- Create tables
CREATE TABLE t70(c0 CHAR , PRIMARY KEY(c0));
CREATE TABLE t0 LIKE t70;

-- Insert data into t0 and t70
INSERT IGNORE INTO t0 VALUES (2010304501);
INSERT IGNORE INTO t70(c0) VALUES (true), ('U⏓');

-- Query 1:
SELECT ABS(MIN(b'101010')) AS c2, 1 AS c3
FROM t70 AS tom3
WHERE 1
GROUP BY tom3.c0
HAVING ANY_VALUE(c2);

-- Drop tables and recreate them
DROP TABLE t70;
DROP TABLE t0;

CREATE TABLE t70(c0 CHAR);
CREATE TABLE t0 LIKE t70;

-- Insert the same data into t0 and t70
INSERT IGNORE INTO t0 VALUES (2010304501);
INSERT IGNORE INTO t70(c0) VALUES (true), ('U⏓');

-- Query 2:
SELECT ABS(MIN(b'101010')) AS c2, 1 AS c3
FROM t70 AS tom3
WHERE 1
GROUP BY tom3.c0
HAVING ANY_VALUE(c2);

```
### 2. What did you expect to see? (Required)
I expected both queries to return the same result because the only difference between them is the presence of the PRIMARY KEY constraint on c0. The use of the ANY_VALUE function in the HAVING clause should not affect the results, and the behavior should be consistent.
### 3. What did you see instead (Required)
```sql

Output for Query 1:

+------+----+
| c2 | c3 |
+------+----+
| 42 | 1 |
| 42 | 1 |
+------+----+
2 rows in set (0.00 sec)
Output for Query 2:

Empty set, 2 warnings (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.