Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.4, 5.5.32
-
None
-
None
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.
Attachments
Issue Links
- relates to
-
MDEV-14911 zero_date is considered as NULL, depending on optimizer_switch
- Closed