| 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: | |
| 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 |
[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.
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)