I have a database that I imported from Access, and there are several columns showing unusual behavior. Specific columns are of type datetime, with valid NULL values. In the original dataset there were several rows with dates, and some with a null value, and I try to select all rows without dates (IS NULL) and always return with "Empty set". Sorry for the horrible column names, the long story, and check out the abbreviated table with three PK fields and follow the column in question:
+-----------------------------------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-----------------------------------------+-------------+------+-----+---------+
| HID | varchar(50) | NO | PRI | NULL |
| yr | varchar(50) | NO | PRI | NULL |
| mo | varchar(50) | NO | PRI | NULL |
| wanem deit yu yu risivim ol meresin | datetime | YES | | NULL |
Running a simple query against it pulls out the following:
mysql> SELECT hid, yr, mo, `wanem deit yu yu risivim ol meresin` FROM AP WHERE "wanem deit yu yu risivim ol meresin" IS NOT NULL LIMIT 5;
+-----+------+----+-------------------------------------+
| hid | yr | mo | wanem deit yu yu risivim ol meresin |
+-----+------+----+-------------------------------------+
| 109 | 2010 | 01 | NULL |
| 109 | 2011 | 03 | NULL |
| 109 | 2012 | 01 | 2011-11-11 00:00:00 |
| 109 | 2012 | 02 | 2011-11-11 00:00:00 |
| 109 | 2012 | 03 | NULL |
+-----+------+----+-------------------------------------+
5 rows in set (0.00 sec)
mysql> SELECT hid, yr, mo, `wanem deit yu yu risivim ol meresin` FROM AP WHERE "wanem deit yu yu risivim ol meresin" IS NULL LIMIT 5;
Empty set (0.00 sec)
, , , , - - , , , , NULL .
mysql> SELECT hid, yr, mo, `wanem deit yu yu risivim ol meresin` FROM AP WHERE "wanem deit yu yu risivim ol meresin" = "NULL" LIMIT 5;
Empty set (0.00 sec)
mysql> SELECT hid, yr, mo, `wanem deit yu yu risivim ol meresin` FROM AP WHERE "wanem deit yu yu risivim ol meresin" = NULL LIMIT 5;
Empty set (0.00 sec)
mysql> SELECT hid, yr, mo, `wanem deit yu yu risivim ol meresin` FROM AP WHERE "wanem deit yu yu risivim ol meresin" = 0 LIMIT 5;
+-----+------+----+-------------------------------------+
| hid | yr | mo | wanem deit yu yu risivim ol meresin |
+-----+------+----+-------------------------------------+
| 109 | 2010 | 01 | NULL |
| 109 | 2011 | 03 | NULL |
| 109 | 2012 | 01 | 2011-11-11 00:00:00 |
| 109 | 2012 | 02 | 2011-11-11 00:00:00 |
| 109 | 2012 | 03 | NULL |
+-----+------+----+-------------------------------------+
5 rows in set, 1 warning (0.00 sec)
mysql> SELECT hid, yr, mo, `wanem deit yu yu risivim ol meresin` FROM AP WHERE "wanem deit yu yu risivim ol meresin" = "" LIMIT 5;
Empty set (0.00 sec)
mysql> SELECT hid, yr, mo, `wanem deit yu yu risivim ol meresin` FROM AP WHERE "wanem deit yu yu risivim ol meresin" = '' LIMIT 5;
Empty set (0.00 sec)
, . stackoverflow " ", , "IS NOT NULL", NULL ( IS NULL ).