Bug #119080 SELECT vs. TLP partitioning query returns different cardinalities (cardinality mismatch)
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;
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.