Bug #119080 SELECT vs. TLP partitioning query returns different cardinalities (cardinality mismatch)

Submitted: 27 Sep 2025 8:16 Modified: 29 Sep 2025 17:31
Reporter: ruifeng wang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.43 OS:Any
Assigned to: CPU Architecture:Any

[27 Sep 2025 8:16] ruifeng wang
Description:
When running the queries below in MySQL 8.0.43, the baseline SELECT query and a TLP-style three-branch partitioning query (P OR NOT P OR P IS NULL) return different numbers of rows.
Logically, the three-branch union should cover all rows in the table, and thus the row counts should always match the baseline query. However, in practice, the baseline query returns 2 rows, while the partitioned query returns only 1 row.

How to repeat:
DROP DATABASE IF EXISTS databasetest;
CREATE DATABASE databasetest;
USE databasetest;

CREATE TABLE IF NOT EXISTS t0(c0 TEXT  STORAGE MEMORY COMMENT 'asdf'  NULL COLUMN_FORMAT FIXED, c1 DOUBLE ZEROFILL  COMMENT 'asdf' , c2 INT(213) ZEROFILL  PRIMARY KEY UNIQUE COLUMN_FORMAT FIXED NOT NULL STORAGE DISK COMMENT 'asdf' ) ;
INSERT HIGH_PRIORITY INTO t0(c2, c0, c1) VALUES(0.6756249056315148, NULL, NULL);
REPLACE LOW_PRIORITY INTO t0(c2, c0) VALUES(0.052922524507772684, 0.5575357180725545);
SELECT ALL t0.c0 AS ref0, t0.c2 AS ref1 FROM t0;-- cardinality: 2\n\n
SELECT ALL t0.c0 AS ref0, t0.c2 AS ref1 FROM t0 WHERE (+ ((GREATEST(t0.c2, t0.c0)) || ((+ (t0.c2))))) UNION ALL SELECT ALL t0.c0 AS ref0, t0.c2 AS ref1 FROM t0 WHERE (! ((+ ((GREATEST(t0.c2, t0.c0)) || ((+ (t0.c2))))))) UNION ALL SELECT ALL t0.c0 AS ref0, t0.c2 AS ref1 FROM t0 WHERE ((+ ((GREATEST(t0.c2, t0.c0)) || ((+ (t0.c2)))))) IS NULL;\n-- cardinality: 1\n;
[29 Sep 2025 17:31] MySQL Verification Team
Thanks for the report and test case.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.