[MDEV-5107] Left Join Yields All Nulls Instead of Appropriate Matches Created: 2013-10-07  Updated: 2013-10-15  Resolved: 2013-10-15

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 5.3.12, 5.5.33a
Fix Version/s: 5.5.34, 10.0.6, 5.3.13

Type: Bug Priority: Major
Reporter: Jamie Jackson Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: optimizer
Environment:

All (Win 7 64-bit; Ubuntu 12.10 64-bit)

Attachments: Zip Archive sampledump1.zip    

 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.

 Comments   
Comment by Oleksandr Byelkin [ 2013-10-08 ]

It looks like MDEV-5034 duplicate

Comment by Oleksandr Byelkin [ 2013-10-09 ]

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;

Comment by Oleksandr Byelkin [ 2013-10-09 ]

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.

Comment by Oleksandr Byelkin [ 2013-10-09 ]

Above maybe wrong because mark_as_null_row was really called twice for both tables.

Generated at Thu Feb 08 07:01:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.