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 ] |
It is simplified test suite:
CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL,
`grantee_id` int(10) unsigned DEFAULT NULL,
`contact_orgid` int(10) unsigned DEFAULT NULL
);
INSERT INTO `t1` VALUES (11,NULL,13319),(12,NULL,NULL),(13,NULL,13319),(14,NULL,NULL),(15,NULL,13319),(16,NULL,NULL);
CREATE TABLE `t2` (
`attributeID` int(11) NOT NULL,
`extendSetID` char(35) DEFAULT NULL
);
INSERT INTO `t2` VALUES (78,'807702B6-C614-36DA-9EABA66ABE212B22'),(15,'C759EEC9-9A0F-D253-2DD3BEF19E43E960'),(16,'C759EEC9-9A0F-D253-2DD3BEF19E43E960'),(77,'E7441CB5-9035-D6C3-BBC9ABE6A3770B25');
CREATE TABLE `t3` (
`baseID` char(35) NOT NULL,
`attributeID` int(11) NOT NULL,
`attributeValue` longtext
);
INSERT INTO `t3` VALUES ('9907E324-020E-5865-A0DAB0E3AE4969C6',78,'13319'),('9907E324-020E-5865-A0DAB0E3AE4969C6',15,'No'),('9907E324-020E-5865-A0DAB0E3AE4969C6',16,'No');
CREATE TABLE `t4` (
`TContent_ID` int(10) unsigned NOT NULL,
`ContentHistID` char(35) DEFAULT NULL,
`Filename` varchar(255) DEFAULT NULL
);
INSERT INTO `t4` VALUES (18552,'9907E324-020E-5865-A0DAB0E3AE4969C6','!!!'),(18551,'95EB705A-C6DA-3C90-E87200E74B96434A','18551.pdf');
set @save_optimizer_switch=@@optimizer_switch;
set optimizer_switch='derived_merge=on';
SELECT v.filename, t1.id FROM t1
LEFT JOIN
(SELECT d.attributeValue as grantee_id,
c.filename as filename, d.baseId
FROM t2 a JOIN
t3 d on d.attributeID = a.attributeID JOIN
t4 c on c.contentHistID = d.baseID) v
ON v.grantee_id = t1.contact_orgid;
set optimizer_switch='derived_merge=off';
SELECT v.filename, t1.id FROM t1
LEFT JOIN
(SELECT d.attributeValue as grantee_id,
c.filename as filename, d.baseId
FROM t2 a JOIN
t3 d on d.attributeID = a.attributeID JOIN
t4 c on c.contentHistID = d.baseID) v
ON v.grantee_id = t1.contact_orgid;
set optimizer_switch=@save_optimizer_switch;
drop tables t1, t2, t3, t4;
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
To find table on which we should check NULL Item_direct_view_ref::check_null_ref gets the left most real table of the VIEW or derived table (get_real_join_table()) and get unused table which probably do net update status correctly (if change order of the tables in the view or in the derived table bug will disappear).
The problem now how to find correct table in the all leaf tables of the view or of the derived table.
Above maybe wrong because mark_as_null_row was really called twice for both tables.
| 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 ] |
It looks like
MDEV-5034duplicate