Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1
-
None
-
10.1.6-1
Description
This script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (10),(20),(30),(40),(50);
|
EXPLAIN SELECT * FROM t1 WHERE a<>10;
|
EXPLAIN SELECT * FROM t1 WHERE 10<>a;
|
returns:
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | a | a | 5 | NULL | 5 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
...
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | NULL | a | 5 | NULL | 5 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
Notice, "possible_keys" is different in the two EXPLAINs.
"Not equal" operations is symmetric, so the expected results should the the same for the two EXPLAINs.
Another SQL script demonstrating the same problem:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT, b INT, KEY(a)) ENGINE=MyISAM;
|
INSERT INTO t1 (a) VALUES (10),(10),(10),(10),(10),(10),(10),(10),(10),(10),(70);
|
EXPLAIN SELECT * FROM t1 WHERE a<>10;
|
EXPLAIN SELECT * FROM t1 WHERE 10<>a;
|
The first explain returns:
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 3 | Using index condition |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|
The second returns:
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
{code}
|
 |