Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8189

field<>const and const<>field are not symmetric

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: 10.1.6
        • Component/s: Optimizer
        • Labels:
          None
        • Sprint:
          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}

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved:

                      Agile