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

Wrong result: special IS NULL logic for 0000-00-00 does not work with merge views and FROM SQ with derived_merge

        Details

        • Type: Bug
        • Status: Confirmed
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 5.3.12, 10.1, 10.0, 5.5
        • Fix Version/s: 10.1, 10.0
        • Component/s: Optimizer
        • Labels:

          Description

          MySQL has special logic for IS NULL operator with 0000-00-00 dates:
          http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_is-null

          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

          However, it does not work with MERGE views (and derived_merge in 5.7). 

          MariaDB [test]> # This works:
MariaDB [test]> SELECT * FROM t1 WHERE d IS NULL;
+------------+
| d          |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
2 rows in set (0.00 sec)

MariaDB [test]> SELECT * FROM v_temptable WHERE d IS NULL;
+------------+
| d          |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
2 rows in set (0.01 sec)

MariaDB [test]> # This does not work:
MariaDB [test]> SELECT * FROM v_merge WHERE d IS NULL;
Empty set (0.00 sec)

          MariaDB [test]> # This does not work:

MariaDB [test]> SET optimizer_switch = 'derived_merge=on';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM ( SELECT * FROM t1 ) AS sq WHERE d IS NULL;
Empty set (0.00 sec)

MariaDB [test]> # This works:
MariaDB [test]> SET optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM ( SELECT * FROM t1 ) AS sq WHERE d IS NULL;
+------------+
| d          |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
2 rows in set (0.01 sec)
          Test case
          set sql_mode= '';

DROP TABLE IF EXISTS t1, v_merge, v_temptable;

CREATE TABLE t1 (d DATE NOT NULL) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('0000-00-00'), ('0000-00-00');

CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM t1;
CREATE ALGORITHM=TEMPTABLE VIEW v_temptable AS SELECT * FROM t1;

# This works:

SELECT * FROM t1 WHERE d IS NULL;
SELECT * FROM v_temptable WHERE d IS NULL;

# This does not work:

SELECT * FROM v_merge WHERE d IS NULL;


# This does not work:

SET optimizer_switch = 'derived_merge=on';
SELECT * FROM ( SELECT * FROM t1 ) AS sq WHERE d IS NULL;

# This works:

SET optimizer_switch = 'derived_merge=off';
SELECT * FROM ( SELECT * FROM t1 ) AS sq WHERE d IS NULL;

            Gliffy Diagrams

              Attachments

                Issue Links

                links to

                Web Link Bug #78025 - Special IS NULL logic for 0000-00-00 does not work with merge views and SQ

                  Activity

                  There are no comments yet on this issue.

                    People

                    • Assignee:
                      Unassigned
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated: