Gerry Lawrence <gwlp...@gmail.com> writes:
> (re:) Mysql comparison of DATE of 0000-00-00 to NULL
>
>
> It's not equal to null.
> It's not not equal to null.
> It's not greater than null.
> It's not less than null.
> It's not rlike null.
> It's not <> null......
>
> It's..... something altogether different.....
That goes for any value; all comparisons against NULL except IS (NOT)
NULL (and <=> in MySQL) is NULL.
The hate mentioned upthread is this:
mysql> CREATE TABLE date (date DATE NOT NULL);
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO date VALUES ('0000-00-00');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM date WHERE date IS NOT NULL;
+------------+
| date |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM date WHERE date IS NULL;
+------------+
| date |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT date IS NULL, date IS NOT NULL, date <=> NULL FROM DATE;
+--------------+------------------+---------------+
| date IS NULL | date IS NOT NULL | date <=> NULL |
+--------------+------------------+---------------+
| 0 | 1 | 0 |
+--------------+------------------+---------------+
1 row in set (0.00 sec)
Note in particular the difference between 'SELECT date IS NULL' and
'SELECT … WHERE date IS NULL';
--
ilmari
"A disappointingly low fraction of the human race is,
at any given time, on fire." - Stig Sandbeck Mathisen
