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

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.5.33
    • Component/s: None
    • Labels:

      Description

      Due to the fix for MDEV-4817 Closed , 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 Closed 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

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: