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

Wrong result on <not null date column> IS NULL (old documented hack stopped working)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 5.5.33
    • None

    Description

      Due to the fix for MDEV-4817, the ancient bugfeature related to DATE/DATETIME and IS NULL stopped working.

      Here is the story: http://bugs.mysql.com/bug.php?id=940
      Here is the doc: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html

      For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:

      SELECT * FROM tbl_name WHERE date_column IS NULL
      This is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value.

      Here is how it looks:

      CREATE TABLE t1 (id INT, d DATE NOT NULL);
      		 
      INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00');
      		 
       
      		 
      SELECT * FROM t1 WHERE d IS NULL;
      		 
      # +------+------------+
      		 
      # | id   | d          |
      		 
      # +------+------------+
      		 
      # |    1 | 0000-00-00 |
      		 
      # |    2 | 0000-00-00 |
      		 
      # +------+------------+
      		 
      # 2 rows in set (0.01 sec)

      But it doesn't work any longer for scenarios affected by MDEV-4817 fix:

      CREATE TABLE t1 (id INT, d DATE NOT NULL);
      		 
      INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00');
      		 
      CREATE TABLE t2 (i INT);
      		 
      SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL;
      		 
      # Empty set (0.01 sec)
      		 

      EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL;
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      		 
      |    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 |                                                 |
      		 
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (flat, BNL join) |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      		 
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                                                                                                                |
      		 
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`id` AS `id`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`i` AS `i` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`id` = `test`.`t2`.`i`) and (`test`.`t1`.`d` = 0)) |
      		 
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Attachments

        Issue Links

          is part of

          Bug - A problem which impairs or prevents the functions of the product. MDEV-4817 Optimizer fails to optimize expression of the form 'FOO' IS NULL

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-4817 Optimizer fails to optimize expression of the form 'FOO' IS NULL

          • Major - Major loss of function.
          • Closed
          links to

          Web Link Bug #940 - zero date is both null and not null in where clause

          Web Link Documented behavior

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              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.