| Bug #9938 | VIEW, OUTER JOIN, EXPLICIT COALESCE, wrong result | ||
|---|---|---|---|
| Submitted: | 15 Apr 2005 17:44 | Modified: | 23 May 2005 2:01 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[15 Apr 2005 17:45]
Matthias Leich
test case with variations of the problem + comments
Attachment: ml96.test (application/test, text), 3.82 KiB.
[21 May 2005 15:45]
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/25159
[21 May 2005 15:49]
Igor Babaev
The not_null_tables method for class Item_ref was missing.
ChangeSet
1.1897 05/05/21 08:44:50 igor@rurik.mysql.com +3 -0
join_outer.test, join_outer.result:
Added test cases for bug #9938.
item.h:
Fixed bug #9938: missing method not_null_tables for Item_ref.
Fix will appear in release 5.0.7.
[23 May 2005 2:01]
Paul DuBois
Noted in 5.0.7 changelog.
Description: A have a test case derived from the NIST tests where I got correct results till yesterday (2005-April-14). Since today I get wrong results. CREATE TABLE t1 (EMPNUM INT, GRP INT); INSERT INTO t1 VALUES (0, 10); INSERT INTO t1 VALUES (2, 30); CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5)); INSERT INTO t2 VALUES (0, 'KERI'); INSERT INTO t2 VALUES (9, 'BARRY'); CREATE VIEW v1 AS SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP FROM t2 LEFT OUTER JOIN t1 USING (EMPNUM); # Note: COALESCE was used to bypass other open bugs. SELECT * FROM v1 WHERE EMPNUM = 9 ; EMPNUM NAME GRP <--- This empty result is wrong. SELECT * FROM v1 WHERE NAME = 'BARRY'; EMPNUM NAME GRP 9 BARRY NULL It looks like the bug appears if - a column value is coalesced with a "special" NULL column value. "special" NULL means, that this NULL is result of the outer join row completion. - there is a qualification for this coalesced column (EMPNUM) - a view seems to be needed My environment: - Intel PC with Linux(SuSE 9.1) - MySQL compiled from source Version 5.0 ChangeSet@1.1836, 2005-04-14 How to repeat: Please use the attached test case ml96.test , copy it to mysql-test/t touch r/ml96.result # Produce a dummy file with expected # results. ./mysql-test-run ml96 inspect r/ml96.reject # The protocol of the execution.