pingcap / pingcap/tidb

GROUP BY NULL HAVING (column IS NULL) returns error results

Open
#65,944 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

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```

drop table t0;
drop table t1;

CREATE TABLE t0(c0 CHAR DEFAULT '0' , PRIMARY KEY(c0));
CREATE TABLE t1 LIKE t0;

INSERT INTO t1(c0) VALUES ('0') ON DUPLICATE KEY UPDATE c0='~';
INSERT INTO t0 VALUES ('1'), ('~') ON DUPLICATE KEY UPDATE c0=t0.c0;
INSERT INTO t1(c0) VALUES ('-');
INSERT INTO t0 VALUES ('0'), ('4'), ('B');
INSERT INTO t0(c0) VALUES (NULL), ('8'), ('2') ON DUPLICATE KEY UPDATE c0='0';
INSERT INTO t0(c0) VALUES ('a') ON DUPLICATE KEY UPDATE c0=t0.c0;

SELECT /*+ MPP_1PHASE_AGG()*/t1.c0 FROM t1 NATURAL RIGHT JOIN t0 GROUP BY NULL HAVING ((t1.c0) IS NULL) ORDER BY '?[';

```

### 2. What did you expect to see? (Required)
Resulsts from mariadb:
```
mysql> SELECT /*+ MPP_1PHASE_AGG()*/t1.c0 FROM t1 NATURAL RIGHT JOIN t0GROUP BY NULL HAVING ((t1.c0) IS NULL) ORDER BY '?[';
Empty set (0.00 sec)

mysql> select version();
+-----------------+
| version() |
+-----------------+
| 11.4.10-MariaDB |
+-----------------+
1 row in set (0.00 sec)

```

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

```
mysql> SELECT /*+ MPP_1PHASE_AGG()*/t1.c0 FROM t1 NATURAL RIGHT JOIN t0 GROUP BY NULL HAVING ((t1.c0) IS NULL) ORDER BY '?[';
+------+
| c0 |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

mysql> select version();
+------------------------------------------+
| version() |
+------------------------------------------+
| 8.0.11-TiDB-v8.4.0-this-is-a-placeholder |
+------------------------------------------+
1 row in set (0.00 sec)
```
### 4. What is your TiDB version? (Required)

```
| 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.6
Race Enabled: false
Check Table Before Drop: false
Store: unistore
Kernel Type: Classic |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 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.