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)
-- Rene Churchill Specializing in Internet Databases, Astute Computing, LLC. Custom Programming and Phone: 802-244-5151 Webmaster support Fax: 802-244-5512 http://www.astutecomputing.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]