Bug #120283 DATE Column Printed as NULL Still Satisfies `IS NOT NULL`,Allowing a Contradictory Predicate to Return Rows

• Submitted: 17 Apr 13:00
• Modified: 20 Apr 7:22
• Reporter: Y F
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.45
• OS: Ubuntu (Ubuntu 20.04.4 LTS)
• CPU Architecture: x86 (Intel(R) Xeon(R) Platinum 8358P @ 2.60GHz (64 Cores, 96MB L3 Cache))

[17 Apr 13:00] Y F

Description:
This issue can be described using a single table and a logically impossible
predicate.

After creating the table and inserting several rows via `INSERT IGNORE`, the
stored `DATE NOT NULL` column `c5` is displayed as `NULL` in the result set:

------------------------------------
mysql> SELECT c1, c4, c5,
    ->        c5 IS NULL     AS is_null,
    ->        c5 IS NOT NULL AS is_not_null
    -> FROM t
    -> ORDER BY c1;
+----+------+------+---------+-------------+
| c1 | c4   | c5   | is_null | is_not_null |
+----+------+------+---------+-------------+
| 1  | 573  | NULL | 0       | 1           |
| 2  | -2   | NULL | 0       | 1           |
| 3  | -6   | NULL | 0       | 1           |
| 4  | -552 | NULL | 0       | 1           |
+----+------+------+---------+-------------+
------------------------------------

So the displayed value is `NULL`, but MySQL still evaluates `c5 IS NULL` as
false and `c5 IS NOT NULL` as true.

Then the following query is executed:

------------------------------------
mysql> SELECT c1, c4, c5
    -> FROM t
    -> WHERE c6 >= 'not-a-date'
    ->   AND c5 IS NOT NULL
    ->   AND c5 IS NULL
    -> ORDER BY c4 DESC;
+----+------+------+
| c1 | c4   | c5   |
+----+------+------+
| 1  | 573  | NULL |
| 2  | -2   | NULL |
| 3  | -6   | NULL |
| 4  | -552 | NULL |
+----+------+------+
------------------------------------

This predicate is a contradiction and should be equivalent to `FALSE`, so the
query result must be empty. Instead, MySQL returns all four rows.

The problem is also visible in the execution plan. Rather than recognizing the
predicate as impossible, MySQL executes a full table scan:

------------------------------------
mysql> EXPLAIN FORMAT=TRADITIONAL
    -> SELECT c1, c4, c5
    -> FROM t
    -> WHERE c6 >= 'not-a-date'
    ->   AND c5 IS NOT NULL
    ->   AND c5 IS NULL
    -> ORDER BY c4 DESC;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1  | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4    | 25.0     | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
------------------------------------

This is a wrong-result bug. A contradiction of the form

------------------------------------
c5 IS NOT NULL AND c5 IS NULL
------------------------------------

must never return rows.

How to repeat:
SET SESSION sql_mode =
  'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

DROP DATABASE IF EXISTS repro_null_contradiction;
CREATE DATABASE repro_null_contradiction;
USE repro_null_contradiction;

CREATE TABLE t (
    c1 INT NOT NULL PRIMARY KEY,
    c4 INT NULL,
    c5 DATE NOT NULL,
    c6 VARCHAR(10) NOT NULL
);

-- INSERT IGNORE is important here.
INSERT IGNORE INTO t (c1, c4, c5, c6) VALUES
(1,  573, '0000-00-00', 'sd d7j5nr6'),
(2,   -2, 'not-a-date', 'zzzzzzzzzz'),
(3,   -6, '0000-00-00', 'zzzzzzzzzz'),
(4, -552, 'not-a-date', 'zzzzzzzzzz');

ANALYZE TABLE t;

-- Control: inspect how MySQL treats c5 on these rows.
SELECT c1, c4, c5,
       c5 IS NULL     AS is_null,
       c5 IS NOT NULL AS is_not_null
FROM t
ORDER BY c1;

-- Bug: this predicate is logically impossible, so the result must be empty.
SELECT c1, c4, c5
FROM t
WHERE c6 >= 'not-a-date'
  AND c5 IS NOT NULL
  AND c5 IS NULL
ORDER BY c4 DESC;

-- Optional: MySQL should normally reduce this to Impossible WHERE.
EXPLAIN FORMAT=TRADITIONAL
SELECT c1, c4, c5
FROM t
WHERE c6 >= 'not-a-date'
  AND c5 IS NOT NULL
  AND c5 IS NULL
ORDER BY c4 DESC;

[20 Apr 7:22] Roy Lyseng

Thank you for the bug report.
However, this is not a bug.

Documentation says:

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.

IS NOT NULL is not specified to have similar limitations, thus we may see some apparently contradictory results from this rule.

Workaround: Do not use the special 0000-00-00 dates, as they are not according to standard SQL specifications.