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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 10.1.6
    • Optimizer
    • 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}
       

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.