[SOLVED] MySQL column have null value but "is null" is not working

Issue

I have mySQL table and have column which have null and not null data.

While running query and visibly i can see that BLOCKER column have null values.

mysql> select count(1), BLOCKER from mysql.PRSSTATE group by BLOCKER;
+----------+----------------+
| count(1) | BLOCKER        |
+----------+----------------+
|   193403 |                | 
|      350 | Beta           | 
|       24 | Build          | 

If i issue query as shown below i am getting count(1) as zero.

mysql> select count(1) from mysql.PRSSTATE where BLOCKER  is NULL;
+----------+
| count(1) |
+----------+
|        0 | 
+----------+
1 row in set (0.13 sec)

My doubt is that it might have special character as i have migrated the data from some other system into this table. Wondering how to resolve this. It should be showing by “is null” statement.

Solution

BLOCKER may be has zero length:

select count(1) from mysql.PRSSTATE where (BLOCKER  is NULL or BLOCKER = "");

Answered By – Sanj

Answer Checked By – Terry (BugsFixing Volunteer)