Description:
CREATE TABLE IF NOT EXISTS t0(c0 FLOAT, c1 DOUBLE);
CREATE INDEX i0 ON t0(c0);
INSERT INTO t0(c0) VALUES(''), (1), (2), (3), (4), (5), (6), (7), (8), (9);
INSERT INTO t0(c0) VALUES(NULL);
INSERT INTO t0(c0) VALUES(10), (NULL);
INSERT INTO t0(c0) VALUES(NULL), (11), (12);
INSERT INTO t0(c0) VALUES(13);
INSERT INTO t0(c0) VALUES(NULL);
ANALYZE TABLE t0 UPDATE HISTOGRAM ON c0;
EXPLAIN FORMAT=JSON SELECT t0.c0 FROM t0; -- rows_examined_per_scan: 3, rows_produced_per_join: 3
EXPLAIN FORMAT=JSON SELECT t0.c0 FROM t0 WHERE (t0.c0) IS NULL; -- rows_examined_per_scan: 4, rows_produced_per_join: 4
The second query has one more WHERE predicate than the first query, so I expect it should have a bigger estimated cardinality than the first query.
If we remove the index i0, the cardinality of the second query is changed to: rows_examined_per_scan: 3, rows_produced_per_join: 1, which is expected.
How to repeat:
docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.2.0
Then execute the above test case.
Description: CREATE TABLE IF NOT EXISTS t0(c0 FLOAT, c1 DOUBLE); CREATE INDEX i0 ON t0(c0); INSERT INTO t0(c0) VALUES(''), (1), (2), (3), (4), (5), (6), (7), (8), (9); INSERT INTO t0(c0) VALUES(NULL); INSERT INTO t0(c0) VALUES(10), (NULL); INSERT INTO t0(c0) VALUES(NULL), (11), (12); INSERT INTO t0(c0) VALUES(13); INSERT INTO t0(c0) VALUES(NULL); ANALYZE TABLE t0 UPDATE HISTOGRAM ON c0; EXPLAIN FORMAT=JSON SELECT t0.c0 FROM t0; -- rows_examined_per_scan: 3, rows_produced_per_join: 3 EXPLAIN FORMAT=JSON SELECT t0.c0 FROM t0 WHERE (t0.c0) IS NULL; -- rows_examined_per_scan: 4, rows_produced_per_join: 4 The second query has one more WHERE predicate than the first query, so I expect it should have a bigger estimated cardinality than the first query. If we remove the index i0, the cardinality of the second query is changed to: rows_examined_per_scan: 3, rows_produced_per_join: 1, which is expected. How to repeat: docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.2.0 Then execute the above test case.