Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.33
-
Fix Version/s: 5.5.33
-
Component/s: None
-
Labels:None
Description
In 5.5, revision 3589, we've had:
CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL); CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; i1 i2 a b # CHECK: SELECT * FROM t1 JOIN t2 ON i1 = i2 WHERE a < b; i1 i2 a b DROP VIEW v2; DROP TABLE t1,t2;
in revision 3860, we get
CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL); CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; i1 i2 a b NULL NULL 2 3 NULL NULL 1 2 # CHECK: SELECT * FROM t1 JOIN t2 ON i1 = i2 WHERE a < b; i1 i2 a b DROP VIEW v2; DROP TABLE t1,t2;
Note that the query that used to return zero records now returns two records. This is incorrect.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
- EXPLAIN for the empty-set (correct) result:
MariaDB [j12]> explain SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
-------------------------------------------------------------------------------------------------------------+id select_type table type possible_keys key key_len ref rows Extra -----
--------------------------------------------------------------------------------------------------------+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DERIVED t2 ALL NULL NULL NULL NULL 2 -----
--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
- EXPLAIN for the incorrect result:
-----------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------+
| 1 | PRIMARY | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | const | 0 | Using where |
| 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 2 |
-----------------------------------------------------------------------------+
The problem started to occur after this push:
------------------------------------------------------------
revno: 3860 [merge]
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.5-trunk
timestamp: Fri 2013-08-23 08:34:35 -0700
message:
Merge
------------------------------------------------------------
revno: 3857.1.1
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.5
timestamp: Fri 2013-08-23 07:25:45 -0700
message:
Fixed bug mdev-4420.
The code of JOIN::optimize that performed substitutions for the best equal
field in all ref items did not take into account that a multiple equality
could contain the result of the single-value subquery if the subquery is
inexpensive. This code was corrected.
Also made necessary corresponding corrections in the code of make_join_select().
The difference in make_join_select/add_not_null_conds. The optimizer used to add equality, but doesn't do it anymore.