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.