I recently ran into a very odd MySQL behavior, which is either a glaring bug or an example of our old friend "undefined behavior"—I can't find or invent any reason it should not be defined in a more reasonable way, however, so ... well, somebody here will know the answer, I'm sure, so I just hope he or she feels like telling me. :-)

The behavior can be summarized by the following set of results:

mysql> select count(1) from foo where remote in (select remote from ba
+r);
+----------+
| count(1) |
+----------+
|     5750 |
+----------+
1 row in set (0.30 sec)

mysql> select count(1) from foo ;
+----------+
| count(1) |
+----------+
|    94587 |
+----------+
1 row in set (0.01 sec)

mysql> select count(1) from foo where remote not in (select remote fro
+m bar);
+----------+
| count(1) |
+----------+
|      56  |
+----------+
1 row in set (0.45 sec)

mysql> select count(1) from votes where remote is null;
+----------+
| count(1) |
+----------+
|      112 |
+----------+
1 row in set (0.02 sec)
[download]

As you can see, the query which should be returning 88837 rows (roughly) is returning 56, which is, probably not coincidentally, one half the number of rows in the table for which the "remote" column is null. (Never mind why it's null—it was a historical artifact.) When the NULL values are set to an empty string, the problem goes away.

Clearly, returning the first (#nulls)/2 rows of the table (I *believe* that was what was getting returned) is not the desired behavior for this type of query—my best interpretation is that the NULL rows should not have been included in either of the two sets—so the question is whether the query should ever have been allowed in the first place on a column that can contain null values. I believe that it should, and hence that this behavior (in mysql Ver 14.7 Distrib 4.1.10, for portbld-freebsd4.10 (i386)) is a bug. Anybody want to explain why I'm horribly wrong?