Details
Major
Description
CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (i2 INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (10),(20); CREATE TABLE t3 (i3 INT, d3 DATETIME NOT NULL) ENGINE=MyISAM; INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12'); SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL; EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL;
Expected result:
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL; i1 i2 i3 d3 1 NULL NULL NULL 2 NULL NULL NULL
Actual result:
SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL; i1 i2 i3 d3
On 5.3, the problem appeared with the revision below; but on 5.5 it had apparently existed long before that, and still does.
revno: 3654 committer: Igor Babaev <igor@askmonty.org> branch nick: maria-5.3-bugs timestamp: Fri 2013-05-03 18:45:20 -0700 message: Made consistent handling of the predicates of the form <non-nullable datatime field> IS NULL in outer joins with that in inner joins. Previously such condition was transformed into the condition <non-nullable datatime field> = 0 unless the field belonged to an inner table of an outer join. In this case the predicate was interpreted as for any other field. Now if the field in the predicate <non-nullable datatime field> IS NULL belongs to an inner table of an outer join the predicate is transformed into the disjunction <non-nullable datatime field> = 0 OR <non-nullable datatime field> IS NULL. This is fully compatible with the semantics of such predicates in 5.5.
MariaDB 5.2, MySQL 5.1, MySQL 5.6 return 2 rows.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
You are right, typo. I updated the description to remove MySQL 5.5 from the list of the good ones. It was meant to be MySQL 5.1, MySQL 5.6, MySQL 5.7.
The fix was pushed into the 5.3 tree and merged into the 5.5 tree.
Elena,
I see the correct result in mysql-5.6, but don't see it in mysql-5.5 (I built both versions from the launchpad trees).