Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.4, 5.3.12, 5.5.33a
-
None
-
All (Win 7 64-bit; Ubuntu 12.10 64-bit)
Description
Querying the attached data with:
|
left_join.sql |
SELECT v.filename, t6.id
|
FROM gt_tbl_contact t6
|
LEFT JOIN (
|
SELECT d.attributeValue as grantee_id
|
, c.filename as filename,
|
d.baseId
|
FROM tclassextend e
|
JOIN tclassextendsets es on es.subTypeID = e.subTypeID
|
JOIN tclassextendattributes a on a.extendSetID = es.extendSetID
|
JOIN tclassextenddata d on d.attributeID = a.attributeID
|
JOIN tcontent c on c.contentHistID = d.baseID
|
WHERE
|
c.active = 1
|
AND c.approved = 1
|
AND c.display = 1
|
AND e.subType = 'Grantee') v ON v.grantee_id = t6.contact_orgid;
|
Yields:
For comparison, results from MySQL 5.1.69:
|
mysql5_1_69_results.txt |
result:
|
|
+-----------+----+
|
| filename | id |
|
+-----------+----+
|
| NULL | 1 |
|
| NULL | 2 |
|
| NULL | 3 |
|
| NULL | 4 |
|
| NULL | 12 |
|
| NULL | 14 |
|
| NULL | 16 |
|
| 17944.pdf | 5 |
|
| 17942.pdf | 9 |
|
| 17941.pdf | 7 |
|
| 17940.pdf | 6 |
|
| 17965.pdf | 8 |
|
| 17965.pdf | 10 |
|
| 17965.pdf | 11 |
|
| 17965.pdf | 13 |
|
| 17965.pdf | 15 |
|
+-----------+----+
|
|
extended explain:
|
|
+----+-------------+------------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+------------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
|
| 1 | PRIMARY | t6 | index | NULL | fk_gt_tbl_contact_8_idx | 5 | NULL | 16 | 100.00 | Using index |
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 22 | 100.00 | |
|
| 2 | DERIVED | c | ref | IX_TContent_1,IX_tcontent_approved,IX_tcontent_active,IX_tcontent_display | IX_tcontent_approved | 2 | | 11 | 100.00 | Using where |
|
| 2 | DERIVED | d | ref | Index_2,Index_3 | Index_2 | 105 | thingtest2.c.ContentHistID | 3 | 100.00 | |
|
| 2 | DERIVED | a | eq_ref | PRIMARY,Index_2 | PRIMARY | 4 | thingtest2.d.attributeID | 1 | 100.00 | |
|
| 2 | DERIVED | es | eq_ref | PRIMARY,Index_2 | PRIMARY | 105 | thingtest2.a.extendSetID | 1 | 100.00 | |
|
| 2 | DERIVED | e | eq_ref | PRIMARY | PRIMARY | 105 | thingtest2.es.subTypeID | 1 | 100.00 | Using where |
|
+----+-------------+------------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
|
Results from MariaDB 5.5.32:
|
mariadb5_5_32_results.txt |
result:
|
|
+----------+----+
|
| filename | id |
|
+----------+----+
|
| NULL | 1 |
|
| NULL | 2 |
|
| NULL | 3 |
|
| NULL | 4 |
|
| NULL | 12 |
|
| NULL | 14 |
|
| NULL | 16 |
|
| NULL | 5 |
|
| NULL | 9 |
|
| NULL | 7 |
|
| NULL | 6 |
|
| NULL | 8 |
|
| NULL | 10 |
|
| NULL | 11 |
|
| NULL | 13 |
|
| NULL | 15 |
|
+----------+----+
|
|
extended explain:
|
|
+------+-------------+-------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
|
| 1 | SIMPLE | t6 | index | NULL | fk_gt_tbl_contact_8_idx | 5 | NULL | 16 | 100.00 | Using index |
|
| 1 | SIMPLE | c | ref | IX_TContent_1,IX_tcontent_approved,IX_tcontent_active,IX_tcontent_display | IX_tcontent_approved | 2 | const | 2 | 100.00 | Using where |
|
| 1 | SIMPLE | d | ref | Index_2,Index_3 | Index_2 | 105 | thingtest2.c.ContentHistID | 6 | 100.00 | Using where |
|
| 1 | SIMPLE | a | eq_ref | PRIMARY,Index_2 | PRIMARY | 4 | thingtest2.d.attributeID | 1 | 100.00 | Using where |
|
| 1 | SIMPLE | es | eq_ref | PRIMARY,Index_2 | PRIMARY | 105 | thingtest2.a.extendSetID | 1 | 100.00 | Using where |
|
| 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 105 | thingtest2.es.subTypeID | 1 | 100.00 | Using where |
|
+------+-------------+-------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
|
Note: Switching
set optimizer_switch='derived_merge=off';
|
yields correct results.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.0.6 [ 13202 ] | |
| Fix Version/s | 5.5.34 [ 13700 ] | |
| Fix Version/s | 5.3.13 [ 12602 ] | |
| Affects Version/s | 5.3.12 [ 12000 ] | |
| Affects Version/s | 5.5.33a [ 13500 ] | |
| Affects Version/s | 10.0.4 [ 13101 ] | |
| Assignee | Oleksandr Byelkin [ sanja ] | |
| Labels | MariaDB_5.5 optimizer | optimizer |
| Comment |
[ Simple test suite: create table t1 (a int, b int); insert into t1 values (0,0),(1,1),(2,2),(3,3),(5,5); create table t2 (c int, d int); insert into t2 values (1,1),(3,3),(4,4); create table t3 (e int, f int); insert into t3 values (1,1),(2,2),(4,4); set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=on'; select * from t1 left join (select * from t2, t3 where t2.c=t3.e) v on v.d = t1.a; set optimizer_switch='derived_merge=off'; select * from t1 left join (select * from t2, t3 where t2.c=t3.e) v on v.d = t1.a; set optimizer_switch=@save_optimizer_switch; drop tables t1,t2,t3; ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | defaullt [ 29232 ] | MariaDB v2 [ 44606 ] |
| Workflow | MariaDB v2 [ 44606 ] | MariaDB v3 [ 63957 ] |
| Workflow | MariaDB v3 [ 63957 ] | MariaDB v4 [ 147101 ] |