Description:
Followup to Igor's comments on bug #47123:
"
Alexey,
After your patch, for your test case, we have:
mysql> EXPLAIN SELECT * FROM t1 WHERE a <> NULL;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (10.68 sec)
Yet, if we drop the index we get:
mysql > drop index a on t1;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
(I've added two extra records to t1 with values '2' and '3').
mysql> EXPLAIN SELECT * FROM t1 WHERE a <> NULL;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4 | Using
where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
This seems to me to be counter-intuitive. I would expect to get "Impossible where" in
this case too.
"
How to repeat:
CREATE TABLE t1(a INT, KEY(a));
INSERT INTO t1 VALUES (1), (NULL);
EXPLAIN SELECT * FROM t1 WHERE a <> NULL;
DROP INDEX a ON t1;
EXPLAIN SELECT * FROM t1 WHERE a <> NULL;
Suggested fix:
In addition to catching null-rejecting predicates returning UNKNOWN in get_mm_leaf(), do the same in optimize_cond() so that we have "imp. where" for both indexed and non-indexed columns.
