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

Query execution plan depends on strict mode

    XMLWordPrintable

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

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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