Details
Major
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I've checked in the current 5.3 it is fixed there by fix of 5107
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 forMDEV-5107fixes 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).