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)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
