Bug #8711 <=> fails in WHERE clause, works in HAVING
Submitted: 22 Feb 2005 21:19 Modified: 11 Mar 2005 0:10
Reporter: Rene Churchill Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.0.21 OS:MacOS (Mac OSX 10.3.6)
Assigned to: Sergey Petrunya CPU Architecture:Any

[22 Feb 2005 21:19] Rene Churchill
Description:
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.

How to repeat:
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);

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) 

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)
[5 Mar 2005 9:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22687
[10 Mar 2005 23:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22919
[10 Mar 2005 23:17] Sergey Petrunya
Pushed into 4.0.25
[10 Mar 2005 23:29] Sergey Petrunya
Short description of the bug:
Queries with OUTER JOIN and '<=>' operator in the WHERE clause return fewer rows.
(as if some of query outer joins were inner joins)
[11 Mar 2005 0:10] Paul DuBois
Noted in 4.0.25 changelog.