| Bug #93642 | Contribution: Impossible WHERE for a!=a, a<a, a>a | ||
|---|---|---|---|
| Submitted: | 17 Dec 2018 16:22 | Modified: | 12 Dec 2019 1:55 |
| Reporter: | OCA Admin (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[17 Dec 2018 16:22]
OCA Admin
[17 Dec 2018 16:22]
OCA Admin
Contribution submitted via Github - Impossible WHERE for a!=a, a<a, a>a (*) Contribution by Daniel Black (Github grooverdan, mysql-server/pull/234#issuecomment-447770563): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: git_patch_239069979.txt (text/plain), 8.58 KiB.
[17 Dec 2018 18:26]
MySQL Verification Team
Thank you for the contribution.
[18 Dec 2018 1:02]
Daniel Black
The existing optimizer will choose 'Impossible WHERE' when the column is 'NOT NULL', however a NULLable column will use an index. Likewise `not (a!=a)` gets an optimization. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f4b57e5eb6b34bd47d2d71a8f9bde3cf mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.11 | +-----------+ 1 row in set (0.00 sec) mysql> create database test; Query OK, 1 row affected (0.02 sec) mysql> use test; Database changed mysql> CREATE TABLE t1 (a TINYINT NOT NULL, KEY(a)); Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE t2 (a TINYINT, KEY(a)); Query OK, 0 rows affected (0.13 sec) mysql> INSERT INTO t1 VALUES (1),(2),(3),(4),(5); Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t2 VALUES (1),(2),(3),(4),(5); Query OK, 5 rows affected (0.09 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM t1 WHERE a!=a; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM t2 WHERE a!=a; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t2 | NULL | index | NULL | a | 2 | NULL | 5 | 80.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM t1 WHERE a<a; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM t2 WHERE a<a; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t2 | NULL | index | NULL | a | 2 | NULL | 5 | 33.33 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM t1 WHERE a>a; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.01 sec) mysql> EXPLAIN SELECT * FROM t2 WHERE a>a; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t2 | NULL | index | NULL | a | 2 | NULL | 5 | 33.33 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE not (a!=a); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT COUNT(*) FROM t2 WHERE not (a!=a); +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t2 | NULL | index | NULL | a | 2 | NULL | 5 | 20.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
[12 Dec 2019 1:55]
Paul DuBois
Posted by developer: Fixed in 8.0.20. For a non-NULL column c, the optimizer now recognizes when the conditions c < c, c > c, and c <> c are always false and need not be evaluated for every row. Thanks to Daniel Black for the contribution.
[12 Dec 2019 2:14]
Daniel Black
Thanks Paul, Only non-NULL? What did I miss?
[12 Dec 2019 11:09]
Paul DuBois
Posted by developer: Correction. Changelog entry should begin: For a nullable column c, ...
