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

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 5.5.33, 5.3.12
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: File mdev5137.test     Zip Archive sampledump2.zip    

 Description   

Querying the attached data with:

left_join.sql

 
SELECT 
    p.filename, r.recipient_grantee_id
 
FROM
 
    gt_asc_grantee_recipient r
 
        LEFT JOIN gt_vw_grantee_page p
 
            ON r.recipient_grantee_id = p.grantee_id
 
WHERE
 
    r.grantee_id = 220;

For comparison, results from MySQL 5.6.14:

mysql_5_6_14.results.txt

 
+-----------+----------------------+
 
| filename  | recipient_grantee_id |
 
+-----------+----------------------+
 
| NULL      |                 NULL |
 
| 16059_foo |                 2431 |
 
| NULL      |                 NULL |
 
| NULL      |                 NULL |
 
| 16079_foo |                 3257 |
 
| 16080_foo |                 3357 |
 
| 16590_foo |                  469 |
 
| 16093_foo |                 4165 |
 
| 15943_foo |                 1368 |
 
| 16751_foo |                  639 |
 
| NULL      |                 NULL |
 
| 15950_foo |                 1381 |
 
| NULL      |                 NULL |
 
| 16113_foo |                 4828 |
 
| 16122_foo |                 5069 |
 
| NULL      |                 NULL |
 
| NULL      |                 NULL |
 
| NULL      |                 NULL |
 
| NULL      |                 NULL |
 
| 15679_foo |                 1073 |
 
| NULL      |                 NULL |
 
| 16146_foo |                 6097 |
 
| NULL      |                 NULL |
 
| 16196_foo |                13431 |
 
| 15838_foo |                 1238 |
 
+-----------+----------------------+
 
25 rows in set (0.01 sec)

MariaDB 5.5.32 results (incorrect):

mariadb_5_5_32.results.txt

 
+----------+----------------------+
 
| filename | recipient_grantee_id |
 
+----------+----------------------+
 
| NULL     |                 NULL |
 
| NULL     |                 2431 |
 
| NULL     |                 NULL |
 
| NULL     |                 NULL |
 
| NULL     |                 3257 |
 
| NULL     |                 3357 |
 
| NULL     |                  469 |
 
| NULL     |                 4165 |
 
| NULL     |                 1368 |
 
| NULL     |                  639 |
 
| NULL     |                 NULL |
 
| NULL     |                 1381 |
 
| NULL     |                 NULL |
 
| NULL     |                 4828 |
 
| NULL     |                 5069 |
 
| NULL     |                 NULL |
 
| NULL     |                 NULL |
 
| NULL     |                 NULL |
 
| NULL     |                 NULL |
 
| NULL     |                 1073 |
 
| NULL     |                 NULL |
 
| NULL     |                 6097 |
 
| NULL     |                 NULL |
 
| NULL     |                13431 |
 
| NULL     |                 1238 |
 
+----------+----------------------+
 
25 rows in set (0.01 sec)

Note: Unlike MDEV-5107, switching 

set optimizer_switch='derived_merge=off';

does not have any affect.

 Comments   
Comment by Elena Stepanova [ 2013-10-15 ]

Switching off derived_merge doesn't help here because, unlike in MDEV-5107, here the query is using a view instead of a SELECT subquery. The workaround here is to create the view `gt_vw_grantee_page` explicitly with the TEMPTABLE algorithm (CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW `gt_vw_grantee_page` AS ...).

I think there is a good chance it's the same problem as in MDEV-5107, but I will pass it to Sanja to make sure (to check whether his fix for MDEV-5107 fixes this one as well).

I've attached the MTR-ish test case. It is the very same data and query that Jamie provided, just put together in a single file with stripped mysqldump comments and other technicalities so that it can be executed by MTR (or by a client, all the same).

Comment by Oleksandr Byelkin [ 2013-10-15 ]

I've checked in the current 5.3 it is fixed there by fix of 5107

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