Why isn't zero date when compared to null, null?

Because the 2. isn't true:

SELECT CAST('0000-00-00' AS DATE); prints

0000-00-00

It doesn't print

NULL

4th statement show the result 0000-00-00

In mysql Only null is null. Everything else is not, so that it returns 1

Thanks to kiks73, iamsankalp89 and moscas for their answers (kiks73's answer - iamsankalp89's answer - moscas's answer) which helped me find the solution/ explanation for the odd behavior that I've encountered.

The reason for the odd behavior was how JDBC driver which is used by DataGrip would handle a zero date.

It would receive the date as zeros from the database then convert it to null.

That would result in DataGrip reading zero dates as nulls because of the JDBC conversion.

Howevenr, because its done at the JDBC layer (not the database layer) the database doesn't see those values as nulls.

To test for that, i ran the database connection and the query through mysql shell, which would print zeros instead of nulls.

Tested in Ubuntu mysql:

SELECT CAST('0000-00-00' AS DATE) ;

The value of the above query is null,

mysql> SELECT CAST('0000-00-00' AS DATE);
+----------------------------+
| CAST('0000-00-00' AS DATE) |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

SELECT CAST('0000-00-00' AS DATE) IS NULL;

In the above query, you are trying to check the value is null using IS NULL function and the answer is True so only it's displaying the value as 1.

mysql> SELECT CAST('0000-00-00' AS DATE) IS NULL;
+------------------------------------+
| CAST('0000-00-00' AS DATE) IS NULL |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)

While testing using others,

SELECT CAST('0000-00-00' AS DATE) ;

The value of the above query won't display as NULL,

SELECT CAST('0000-00-00' AS DATE) IS NULL;

The result of above query is False, so only it displays as 0