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

Query execution plan depends on strict mode

    Details

      Description

      MariaDB [test]> CREATE TABLE t1 (dt DATETIME NOT NULL);
      Query OK, 0 rows affected (0.29 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES (NOW()),(NOW());
      Query OK, 2 rows affected (0.06 sec)
      Records: 2  Duplicates: 0  Warnings: 0
      

      MariaDB [test]> set sql_mode='';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> explain  SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
      |    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> set sql_mode='STRICT_TRANS_TABLES';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> explain  SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL;
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | 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)
      

      Also reproducible with MySQL 5.7.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: