Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.4, 5.5.33, 5.3.13
-
None
-
None
Description
The problem appeared in the 5.3 tree with the following revision:
revno: 3645 [merge]
|
revision-id: igor@askmonty.org-20130327155816-ocjsyxyqgcsraf0s
|
------------------------------------------------------------
|
revno: 3639.1.1
|
revision-id: igor@askmonty.org-20130323043306-2cteo3ndk22af921
|
message:
|
Fixed bug mdev-4318.
|
In some cases, when using views the optimizer incorrectly determined
|
possible join orders for queries with nested outer and inner joins.
|
This could lead to invalid execution plans for such queries.
|
With the data provided in the test case, the test query produces a result set consisting of 31 rows:
SELECT DISTINCT alias1.id |
FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 |
ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 |
WHERE alias3.i != 3; |
id
|
7
|
9
|
18
|
20
|
24
|
50
|
51
|
61
|
71
|
94
|
97
|
100
|
104
|
114
|
119
|
128
|
129
|
140
|
8
|
43
|
45
|
64
|
74
|
77
|
78
|
93
|
101
|
107
|
132
|
133
|
156
|
However, a similar query with an extended select list (but the same joins and WHERE clause) produces 32 rows:
SELECT DISTINCT alias1.id, alias2.id, alias3.i, alias1.i1, alias2.i1 |
FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 |
ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 |
WHERE alias3.i != 3; |
id id i i1 i1
|
7 7 7 6 6
|
9 7 7 6 6
|
18 7 7 6 6
|
20 7 7 6 6
|
24 7 7 6 6
|
50 7 7 6 6
|
51 7 7 6 6
|
61 7 7 6 6
|
71 7 7 6 6
|
94 7 7 6 6
|
97 7 7 6 6
|
100 7 7 6 6
|
104 7 7 6 6
|
114 7 7 6 6
|
119 7 7 6 6
|
128 7 7 6 6
|
129 7 7 6 6
|
140 7 7 6 6
|
8 8 8 3 3
|
43 8 8 3 3
|
45 8 8 3 3
|
64 8 8 3 3
|
74 8 8 3 3
|
77 8 8 3 3
|
78 8 8 3 3
|
93 8 8 3 3
|
101 8 8 3 3
|
107 8 8 3 3
|
132 8 8 3 3
|
133 8 8 3 3
|
156 7 7 6 6
|
151 8 8 3 3
|
The last row, with alias1.id=151, is missing from the first list. It seems to satisfy the condition, so I presume 32 rows is the correct result and 31 rows is not.
SELECT COUNT and other variations of the query also return 32 rows. With join_cache_level=0, the initial query also returns 32 rows.
Test case:
CREATE TABLE t1 ( |
id INT, |
i1 INT, |
i2 INT DEFAULT 0, |
d1 DATE DEFAULT '2000-01-01', |
d2 DATE DEFAULT '2000-01-01', |
t1 TIME DEFAULT '00:00:00', |
t2 TIME DEFAULT '00:00:00', |
dt1 DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00', |
dt2 DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00', |
c1 VARCHAR(1) NOT NULL, |
c2 VARCHAR(1) NOT NULL |
) ENGINE=MyISAM;
|
|
|
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; |
|
|
INSERT INTO t1 (id,i1,c1,c2) VALUES |
(1,7,'t','t'),(2,4,'k','k'),(3,2,'e','e'),(4,0,'i','i'),(5,1,'t','t'),(6,91,'m','m'),(7,6,'z','z'),(8,3,'c','c'),(9,6,'i','i'),(10,8,'v','v'), |
(11,1,'l','l'),(12,4,'j','j'),(13,5,'w','w'),(14,0,'r','r'),(15,7,'o','o'),(16,9,'o','o'),(17,7,'u','u'),(18,6,'f','f'),(19,0,'l','l'),(20,6,'g','g'), |
(21,1,'e','e'),(22,7,'y','y'),(23,0,'p','p'),(24,6,'v','v'),(25,5,'d','d'),(26,9,'i','i'),(27,5,'z','z'),(28,2,'q','q'),(29,4,'j','j'),(30,9,'m','m'), |
(31,8,'d','d'),(32,5,'r','r'),(33,1,'r','r'),(34,1,'k','k'),(35,4,'p','p'),(36,2,'x','x'),(37,5,'w','w'),(38,0,'k','k'),(39,7,'y','y'),(40,4,'p','p'), |
(41,9,'l','l'),(42,2,'u','u'),(43,3,'r','r'),(44,5,'y','y'),(45,3,'u','u'),(46,9,'t','t'),(47,8,'f','f'),(48,2,'f','f'),(49,2,'q','q'),(50,6,'v','v'), |
(51,6,'u','u'),(52,0,'b','b'),(53,1,'n','n'),(54,2,'p','p'),(55,0,'y','y'),(56,1,'l','l'),(57,1,'c','c'),(58,0,'d','d'),(59,2,'y','y'),(60,7,'l','l'), |
(61,6,'m','m'),(62,9,'q','q'),(63,0,'j','j'),(64,3,'u','u'),(65,4,'w','w'),(66,5,'p','p'),(67,8,'z','z'),(68,5,'u','u'),(69,7,'b','b'),(70,0,'f','f'), |
(71,6,'u','u'),(72,1,'i','i'),(73,9,'s','s'),(74,3,'y','y'),(75,5,'s','s'),(76,8,'x','x'),(77,3,'s','s'),(78,3,'l','l'),(79,8,'b','b'),(80,0,'p','p'), |
(81,9,'m','m'),(82,5,'k','k'),(83,7,'u','u'),(84,0,'y','y'),(85,2,'x','x'),(86,5,'h','h'),(87,5,'j','j'),(88,5,'o','o'),(89,9,'o','o'),(90,1,'c','c'), |
(91,7,'k','k'),(92,9,'t','t'),(93,3,'h','h'),(94,6,'g','g'),(95,9,'r','r'),(96,2,'i','i'),(97,6,'l','l'),(98,4,'a','a'),(99,9,'w','w'),(100,6,'n','n'), |
(101,3,'c','c'),(102,1,'a','a'),(103,9,'z','z'),(104,6,'k','k'),(105,7,'a','a'),(106,7,'g','g'),(107,3,'k','k'),(108,8,'h','h'),(109,7,'y','y'),(110,1,'d','d'), |
(111,0,'p','p'),(112,4,'b','b'),(113,2,'n','n'),(114,6,'l','l'),(115,0,'s','s'),(116,0,'n','n'),(117,8,'o','o'),(118,9,'f','f'),(119,6,'u','u'),(120,1,'j','j'), |
(121,1,'q','q'),(122,8,'y','y'),(123,1,'c','c'),(124,17,'i','i'),(125,1,'v','v'),(126,0,'w','w'),(127,5,'q','q'),(128,6,'n','n'),(129,6,'x','x'),(130,2,'b','b'), |
(131,8,'l','l'),(132,3,'i','i'),(133,3,'d','d'),(134,7,'h','h'),(135,0,'i','i'),(136,1,'n','n'),(137,2,'g','g'),(138,7,'u','u'),(139,0,'d','d'),(140,6,'x','x'), |
(141,1,'a','a'),(142,0,'q','q'),(143,9,'n','n'),(144,8,'r','r'),(145,4,'r','r'),(146,4,'g','g'),(147,0,'u','u'),(148,1,'j','j'),(149,0,'m','m'),(150,4,'u','u'), |
(151,3,'m','m'),(152,9,'p','p'),(153,1,'o','o'),(154,8,'v','v'),(155,9,'f','f'),(156,6,'f','f'),(157,1,'e','e'),(158,5,'l','l'),(159,8,'j','j'); |
|
|
CREATE TABLE t2 (i INT) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (7),(8); |
|
|
SELECT DISTINCT alias1.id |
FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 |
ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 |
WHERE alias3.i != 3; |
|
|
EXPLAIN:
EXPLAIN EXTENDED
|
SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3
|
ON alias2.id = alias3.i ON alias1.i1 = alias2.i1
|
WHERE alias3.i != 3;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY alias1 ALL NULL NULL NULL NULL 159 100.00 Using where; Using temporary
|
1 PRIMARY <derived2> ref key0 key0 5 test.alias1.i1 10 100.00 Using where; Distinct
|
1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 100.00 Using where; Distinct; Using join buffer (flat, BNL join)
|
2 DERIVED t1 ALL NULL NULL NULL NULL 159 100.00
|
Warnings:
|
Note 1003 select distinct `test`.`alias1`.`id` AS `id` from `test`.`t1` `alias1` join `test`.`v1` `alias2` join `test`.`t2` `alias3` where ((`test`.`alias3`.`i` = `alias2`.`id`) and (`alias2`.`i1` = `test`.`alias1`.`i1`) and (`alias2`.`id` <> 3))
|
Attachments
Activity
Both left joins of the query a converted into inner joins:
MariaDB [test]> EXPLAIN EXTENDED SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 WHERE alias3.i != 3;
----------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | alias1 | ALL | idx | NULL | NULL | NULL | 159 | 100.00 | Using where; Using temporary |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.alias1.i1 | 10 | 100.00 | Using where; Distinct |
| 1 | PRIMARY | alias3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Distinct; Using join buffer (flat, BNL join) |
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 159 | 100.00 |
----------------------------------------------------------------------------------------------------------------------------------------
4 rows in set, 1 warning (0.01 sec)
MariaDB [test]> show warnings;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Note | 1003 | select distinct `test`.`alias1`.`id` AS `id` from `test`.`t1` `alias1` join `test`.`v1` `alias2` join `test`.`t2` `alias3` where ((`test`.`alias3`.`i` = `alias2`.`id`) and (`alias2`.`i1` = `test`.`alias1`.`i1`) and (`alias2`.`id` <> 3)) |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
The following query without left joins display the same problems:
SELECT DISTINCT alias1.id FROM t1 AS alias1 STRAIGHT_JOIN (v1 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias2.id = alias3.i) ON alias1.i1 = alias2.i1 WHERE alias3.i != 3;
MariaDB [test]> set join_buffer_size=256;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 WHERE alias3.i != 3;
------
| id |
------
| 7 |
| 9 |
| 18 |
| 20 |
| 24 |
| 43 |
| 45 |
| 50 |
| 61 |
| 64 |
| 71 |
| 74 |
| 77 |
| 93 |
| 94 |
| 97 |
| 100 |
| 101 |
| 104 |
| 107 |
| 114 |
| 119 |
| 128 |
| 129 |
| 132 |
| 140 |
| 151 |
| 156 |
------
28 rows in set (0.03 sec)
MariaDB [test]> set join_buffer_size=default;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT DISTINCT alias1.id FROM t1 AS alias1 STRAIGHT_JOIN (v1 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias2.id = alias3.i) ON alias1.i1 = alias2.i1 WHERE alias3.i != 3;
------
| id |
------
| 7 |
| 9 |
| 18 |
| 20 |
| 24 |
| 50 |
| 51 |
| 61 |
| 71 |
| 94 |
| 97 |
| 100 |
| 104 |
| 114 |
| 119 |
| 128 |
| 129 |
| 140 |
| 8 |
| 43 |
| 45 |
| 64 |
| 74 |
| 77 |
| 78 |
| 93 |
| 101 |
| 107 |
| 132 |
| 133 |
| 156 |
------
31 rows in set (0.03 sec)
We see that the number of rows in the result set depends on the setting for @@join_buffer_size.
The same problem can be observed in mysql-5.6
I reported the bug http://bugs.mysql.com/70491.
Before the fix for mdev-4318 (rev 3639)
the query had the following execution plan:
MariaDB [test]> EXPLAIN EXTENDED SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 WHERE alias3.i != 3;
---
-----------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------