rows unexpected become greater with a shrink where condition
- 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 DATABASE IF EXISTS repro41_delete;
CREATE DATABASE repro41_delete;
USE repro41_delete;
CREATE TABLE t1 (
c1 INT PRIMARY KEY,
c5 DATE NOT NULL
);
CREATE TABLE t3 (
c1 INT PRIMARY KEY,
c2 INT NOT NULL,
c5 DATETIME NULL
);
INSERT INTO t1 VALUES (1, '2022-04-30');
INSERT INTO t3 VALUES (3, 1, '2019-05-07 15:50:16');
-- original: delete 0 rows
DELETE FROM t3
WHERE c1 = 3
AND c2 = 1
AND EXISTS (
SELECT t1.c5, COALESCE(t1.c5, '2017-10-20 02:16:45'), COALESCE(t1.c5, '2020-08-28 22:54:55')
FROM t1
EXCEPT
SELECT d.k, COALESCE(d.k, '2011-11-27 14:17:47'), COALESCE(d.k, '2010-03-17 16:03:58')
FROM (
SELECT t1.c5 AS k FROM t1
) AS d
);
SELECT 'after original' AS tag, COUNT(*) AS t3_rows FROM t3;
TRUNCATE TABLE t3;
INSERT INTO t3 VALUES (3, 1, '2019-05-07 15:50:16');
-- mutated: delete 1 row
DELETE FROM t3
WHERE c1 = 3
AND c2 = 1
AND EXISTS (
SELECT t1.c5, COALESCE(t1.c5, '2017-10-20 02:16:45'), COALESCE(t1.c5, '2020-08-28 22:54:55')
FROM t1
EXCEPT
SELECT d.k, COALESCE(d.k, '2011-11-27 14:17:47'), COALESCE(d.k, '2010-03-17 16:03:58')
FROM (
SELECT t1.c5 AS k FROM t1
UNION
SELECT t3.c5 AS k FROM t3
) AS d
);
SELECT 'after mutated' AS tag, COUNT(*) AS t3_rows FROM t3;
```
### 2. What did you expect to see? (Required)
mutated rows affected should be the same as the original
```
mysql> -- original: delete 0 rows
mysql> DELETE FROM t3
-> WHERE c1 = 3
-> AND c2 = 1
-> AND EXISTS (
-> SELECT t1.c5, COALESCE(t1.c5, '2017-10-20 02:16:45'), COALESCE(t1.c5, '2020-08-28 22:54:55')
-> FROM t1
-> EXCEPT
-> SELECT d.k, COALESCE(d.k, '2011-11-27 14:17:47'), COALESCE(d.k, '2010-03-17 16:03:58')
-> FROM (
-> SELECT t1.c5 AS k FROM t1
-> ) AS d
-> );
Query OK, 0 rows affected (0.01 sec)
```
### 3. What did you see instead (Required)
```
mysql> -- mutated: delete 1 row
mysql> DELETE FROM t3
-> WHERE c1 = 3
-> AND c2 = 1
-> AND EXISTS (
-> SELECT t1.c5, COALESCE(t1.c5, '2017-10-20 02:16:45'), COALESCE(t1.c5, '2020-08-28 22:54:55')
-> FROM t1
-> EXCEPT
-> SELECT d.k, COALESCE(d.k, '2011-11-27 14:17:47'), COALESCE(d.k, '2010-03-17 16:03:58')
-> FROM (
-> SELECT t1.c5 AS k FROM t1
-> UNION
-> SELECT t3.c5 AS k FROM t3
-> ) AS d
-> );
Query OK, 1 row affected (0.00 sec)
```
### 4. What is your TiDB version? (Required)
```
mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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
Assessment
This issue has not been assessed yet.