Bug #120712 Inconsistent EXPLAIN row estimation

• Submitted: 17 Jun 6:44
• Reporter: Thomas Morgan
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.7.0
• OS: Any
• CPU Architecture: Any

[17 Jun 6:44] Thomas Morgan

Description:
The result of an EXPLAIN command includes an estimation of the number of rows a query will return. Consider the following two queries:

EXPLAIN SELECT ALL c FROM t;

EXPLAIN SELECT ALL c FROM t WHERE c IS UNKNOWN;

As query 2 is a restriction of query 1 (it appends a WHERE clause), it is impossible for query 2 to return more rows than query 1. However, in the following case, the EXPLAIN command for query 2 estimates more rows than query 1. This suggests an optimiser bug.

The behaviour is observed when an index is created between two NULL inserts.

How to repeat:
MySQL version: 9.7.0.

Driver version: mysql-connector-j 9.7.0.

Execute the following statements:

DROP DATABASE IF EXISTS db;
CREATE DATABASE db;
USE db;
CREATE TABLE t(c INT);
INSERT INTO t VALUES(NULL);
CREATE INDEX a ON t(c);
INSERT INTO t VALUES(NULL);
ANALYZE TABLE t UPDATE HISTOGRAM ON c;
-- Query 1, estimates 1 row
EXPLAIN SELECT ALL c FROM t;
-- Query 2, estimates 2 rows
EXPLAIN SELECT ALL c FROM t WHERE c IS UNKNOWN;