Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.8.5, 11.8
-
None
-
None
-
docker
Description
the IS NULL check within a WHERE clause incorrectly evaluates to TRUE for this zero date, causing the row to be selected as if the value were NULL. However, when IS NULL is used as an expression in the SELECT list for the exact same row, it correctly evaluates to FALSE (0).
MariaDB [test]> CREATE TABLE t4 (c1 DATE, c2 DECIMAL, PRIMARY KEY (c2, c1));
|
Query OK, 0 rows affected (0.010 sec)
|
MariaDB [test]> INSERT IGNORE t4 SET c2 = 9490505532;
|
Query OK, 1 row affected, 1 warning (0.002 sec)
|
MariaDB [test]> SELECT * FROM t4;
|
+------------+------------+
|
| c1 | c2 |
|
+------------+------------+
|
| 0000-00-00 | 9490505532 |
|
+------------+------------+
|
1 row in set (0.000 sec)
|
MariaDB [test]> SELECT * FROM t4 WHERE (((t4.c1) IS NULL));
|
+------------+------------+
|
| c1 | c2 |
|
+------------+------------+
|
| 0000-00-00 | 9490505532 |
|
+------------+------------+
|
1 row in set (0.001 sec)
|
MariaDB [test]> SELECT (t4.c1) IS NULL FROM t4;
|
+-----------------+
|
| (t4.c1) IS NULL |
|
+-----------------+
|
| 0 |
|
+-----------------+
|
1 row in set (0.000 sec)
|