CODEWITHSUNDEEP
mysqlsql
Timothy Harding
Timothy Harding

Reputation: 377

When NULL is not a NULL in MySQL

I've got a database I've imported from Access and there are a few columns displaying some unusual behavior. The particular columns are of type datetime, with NULL values allowed. The original dataset had some rows with dates, and some with null, and I'm trying to select all the rows with no dates (IS NULL) and always coming back with "Empty set". Please excuse the horrendous columns names, long story, and check out the abbreviated table with the three field PK and followed by 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)

Very strange behavior, so I am thinking that maybe it is somehow a string, thanks to a possibly faulty import, so I broke every rule in the book trying the following, and nothing would let me get the rows that were NULL separated from those with values.

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)

So I am a bit stumped. I've found numerous other stackoverflow articles on "How do I select for nulls" but none where "IS NOT NULL" so strangely returns rows with NULL in them (and IS NULL returns an Empty set every time).

Upvotes: 4

Views: 192

Answers (1)

Sadikhasan
Sadikhasan

Reputation: 18601

Try with back ticks instead of double quotes in where clause because it's field name

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;

Actually below query will not check where condition for NOT NULL that's why it return all records change following query to back ticks instead of double quote like

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;

Upvotes: 5

Related Questions