Using <=> in WHERE vs HAVING clause

[Rene Churchill]

Good evening folks, I'm seeing some odd behavior in MySQL 4.0.21
running on Mac OS X 10.3.7

I'm trying to compare two identical tables and find the rows
that are new/modified.  I can't use a timestamp column because
the "new" table is constantly regenerated.  So I'm using a large
WHERE clause and the <=> operator to detect changes.  (BTW, it
would be very nice if there was a NULL safe not equal operator)

I get empty sets returned when I use <=> in the WHERE clause,
which seems wrong to me.  However if I switch to a HAVING clause,
it works as expected.  Is this a bug or am I doing something
funky here?

Note, the testC table is used because in full query, the testA
and testB tables will have 100k+ rows and I need to effeciently
narrow the scope down to the ~250 rows that I'm interested in.

Thanks,
     Rene


Test Script:

create table testA (id int, a int, b int, c int);
create table testB (id int, a int, b int, c int);
create table testC (id int);
insert into testB values(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4);
insert into testC values(2),(3),(4);


WHERE query:

     select testA.a, testB.a, testA.b, testB.b, testA.c, testB.c
      from testC
left join testA on testC.id = testA.id
left join testB on testC.id = testB.id
     where NOT ( testA.a <=> testB.a AND
                 testA.b <=> testB.b AND
                 testA.c <=> testB.c);
Empty set (0.00 sec)


HAVING query:

    select testA.a, testB.a, testA.b, testB.b, testA.c, testB.c
      from testC
left join testA on testC.id = testA.id
left join testB on testC.id = testB.id
    having NOT ( testA.a <=> testB.a AND
                 testA.b <=> testB.b AND
                 testA.c <=> testB.c);

+------+------+------+------+------+------+
| a    | a    | b    | b    | c    | c    |
+------+------+------+------+------+------+
| NULL |    2 | NULL |    2 | NULL |    2 |
| NULL |    3 | NULL |    3 | NULL |    3 |
| NULL |    4 | NULL |    4 | NULL |    4 |
+------+------+------+------+------+------+
3 rows in set (0.00 sec)