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

A part of a ROW comparison is erroneously optimized away

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0, 10.1
    • 10.1.6
    • Optimizer
    • None
    • 10.1.6-1

    Description

      This script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
      INSERT INTO t1 VALUES ('1e1'),('1é1');
      SELECT * FROM t1 WHERE a=10;
      SELECT * FROM t1 WHERE a='1e1';
      SELECT * FROM t1 WHERE a=10 AND a='1e1';

      returns:

      MariaDB [test]> SELECT * FROM t1 WHERE a=10;
      +------+
      | a    |
      +------+
      | 1e1  |
      +------+
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t1 WHERE a='1e1';
      +------+
      | a    |
      +------+
      | 1e1  |
      | 1é1  |
      +------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t1 WHERE a=10 AND a='1e1';
      +------+
      | a    |
      +------+
      | 1e1  |
      +------+
      1 row in set, 1 warning (0.00 sec)

      Looks good so far.

      Now if I rewrite the last query using ROW syntax:

      SELECT * FROM t1 WHERE (a,a)=(10,'1e1');

      It erroneously starts to return both rows:

      +------+
      | a    |
      +------+
      | 1e1  |
      | 1é1  |
      +------+

      This EXPLAIN EXTENDED:

      MariaDB [test]> EXPLAIN SELECT * FROM t1 WHERE (a,a)=(10,'1e1');
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      Empty set (0.00 sec)
       
      MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a,a)=(10,'1e1');
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      +-------+------+--------------------------------------------------------------------------------+
      | Level | Code | Message                                                                        |
      +-------+------+--------------------------------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = '1e1') |
      +-------+------+--------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       

      tells that the a=10 part was moved away from the condition.
      This is wrong. It should be preserved.

      Attachments

        Activity

          People

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.