Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
Description
from 5.5.44
| gpd | CREATE TABLE `gpd` (
|
`recID` int(11) NOT NULL AUTO_INCREMENT,
|
`sessionID` int(11) DEFAULT NULL,
|
`time` decimal(12,0) DEFAULT NULL,
|
`position` int(11) DEFAULT NULL,
|
`angle` int(11) DEFAULT NULL,
|
`velocity` decimal(12,6) DEFAULT NULL,
|
`acceleration` decimal(12,6) DEFAULT NULL,
|
`forcePlate` decimal(12,6) DEFAULT '0.000000',
|
PRIMARY KEY (`recID`),
|
KEY `sessionID` (`sessionID`)
|
) ENGINE=InnoDB AUTO_INCREMENT=464380557 DEFAULT CHARSET=latin1 |
|
Queries like this should end up as 'Impossible Where' the same as 'WHERE 0' is used. Same with > or < as an operator.
explain SELECT 1 FROM `gpd` WHERE `gpd`.`sessionID` != `gpd`.`sessionID`;
|
+------+-------------+---------------+-------+---------------+-----------+---------+------+-----------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------------+-------+---------------+-----------+---------+------+-----------+--------------------------+
|
| 1 | SIMPLE | gpd | index | NULL | sessionID | 5 | NULL | 461252463 | Using where; Using index |
|
I came across this and was potentially generated from the depths of an ORM (sqlalchemy)
Attachments
Activity
Daniel,
For the above examples the current 10.4 returns:
MariaDB [test]> CREATE TABLE t1 (a TINYINT NOT NULL, KEY(a));
|
Query OK, 0 rows affected (0.025 sec)
|
|
|
MariaDB [test]> INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
|
Query OK, 5 rows affected (0.011 sec)
|
Records: 5 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> EXPLAIN SELECT * FROM t1 WHERE a<a;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
There is no wonder, because the MySQL code that processes a<a is practically the same as in MariaDB
Quite right MySQL and MariaDB are behaving identical. I was getting confused on table definitions too.
Remove the `NOT NULL` from the column definition is where the 'Impossible WHERE' is no longer detected. Adding an index just changes plan to 'Using index'. Also the `WHERE not (a!=a)` is a condition that doesn't result in 'Select tables optimized away'. The updated PR corrects both of these to use the more optimal query plan.
Summary of existing MariaDB behaviour (and can switch to MySQL-8.0.13 quickly):
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=f4b57e5eb6b34bd47d2d71a8f9bde3cf
Fixed in MySQL-8.0
----------- MYSQLTEST OUTPUT START -----------CREATE TABLE t1 (a TINYINT NOT NULL, KEY(a));INSERT INTO t1 VALUES (1),(2),(3),(4),(5);SELECT VERSION();VERSION()8.0.13EXPLAIN SELECT * FROM t1 WHERE a!=a;id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHEREWarnings:Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0EXPLAIN SELECT * FROM t1 WHERE a>a;id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHEREWarnings:Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0EXPLAIN SELECT * FROM t1 WHERE a<a;id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHEREWarnings:Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0EXPLAIN SELECT * FROM t1 WHERE NOT a=a;id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE