Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.33
-
None
-
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.
Attachments
Activity
- 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.