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

zero_date is considered as NULL, depending on optimizer_switch

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2
    • Fix Version/s: 5.5.59
    • Component/s: Optimizer
    • Labels:
      None

      Description

       
      DROP TABLE IF EXISTS  t1;
       
      SELECT  CAST('0000-00-00 00:00:00' as datetime) is null;
      CREATE TABLE t1 (d1 datetime NOT NULL);
      INSERT INTO t1 VALUES ('0000-00-00 00:00:00'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');
       
      SET SESSION optimizer_switch='derived_merge=off';
      SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;  ## result 2, expected result 2
      SET SESSION optimizer_switch='derived_merge=on';
      SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;  ## result 0, expected result 2
      DROP TABLE t1;
      
      

      MariaDB [test]> SELECT  CAST('0000-00-00 00:00:00' as datetime) is null;
      +-------------------------------------------------+
      | CAST('0000-00-00 00:00:00' as datetime) is null |
      +-------------------------------------------------+
      |                                               0 |
      +-------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> CREATE TABLE t1 (d1 datetime NOT NULL);
      Query OK, 0 rows affected (0.13 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES ('0000-00-00 00:00:00'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');
      Query OK, 3 rows affected (0.03 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> SET SESSION optimizer_switch='derived_merge=off';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;
      +----------+
      | count(*) |
      +----------+
      |        2 |
      +----------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SET SESSION optimizer_switch='derived_merge=on';  
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL ;
      +----------+
      | count(*) |
      +----------+
      |        0 |
      +----------+
      1 row in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                alice Alice Sherepa
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: