Saturday, March 04, 2006

NULL Issue In MySQL

In the last few days, I was working on some mysterious issue regarding some SQL statements, it was something like this.
SELECT count(*) from some_table WHERE (condition_a);
the result was:
+----------+
| count(*) |
+----------+
|    11223 |
+----------+

alright Now:
SELECT count(*) from some_table WHERE (condition_a) AND (condition_b);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
aha Ok then:
SELECT count(*) from some_table WHERE condition_a AND NOT (condition_b);
The result was also
+----------+
| count(*) |
+----------+
|        0 |
+----------+

HA !!!!
Ok for those 11223 records the condition_a was true, from the second statement I could tell that condition_b was false, so NOT condition_b should be true, but it was also false from the third statement.
So for Logic to play around condition_b, and NOT condition_b both are false (How can that be) all theories of logic are broken (what is going on?)
After breaking up the two conditions, I found that I was comparing one of the values in condition_b with NULL, so in BOTH cases the result would be FALSE, I've always assumed that NULL would be casted to 0, but the fact that in MySQL it's not.
So this was just a thing that you might want to take care of when you write your SQL statements.
Hope it was Useful

Technorati Tags: , , ,

1 comment:

Hamzeh N. said...

Hi Ala'a. I was still after reading your post, because I still couldn't figure out how a "condition" and its logical complement (ie. "not condition") can both evaluate to FALSE because of some "comparison" inside. It still didn't add up. So I had to read more since I don't know anything about SQL.

I found this. It says that the reason your query yielded no results in both cases is because the condition in the WHERE clause evaluated to "UNKNOWN", not "FALSE".

So, SQL can refer to a condition's boolean value as UNKNOWN. And of course, that means that the value of the complement will be UNKNOWN too. And because the condition evaluates to UNKNOWN, the query returns no results. So that's why this was happening.

This would be a nice topic for an interview question.

Nice blog.