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;
