[MDEV-15982] Incorrect results when subquery is materialized. Created: 2018-04-23  Updated: 2020-08-25  Resolved: 2018-07-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 5.5.61, 10.0.36, 10.1.35, 10.2.17, 10.3.9

Type: Bug Priority: Critical
Reporter: Juan Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Attachments: File matbug.sql    
Issue Links:
Relates
relates to MDEV-15454 Nested SELECT IN returns wrong results Closed

 Description   

When set optimizer_switch='materialization=on', the following query returns incorrect results. The problem starts with version 10.2.10 (results are correct when materialization=on on versions 10.2.9 and below) and continues through 10.3.6

set optimizer_switch='materialization=off';
 
 
 
SELECT touter.id
 
FROM touter
 
INNER JOIN tinner1
 
    ON touter.id = tinner1.id
 
WHERE touter.type = 2
 
AND touter.id IN (
 
    SELECT tref.ref_id
 
    FROM tref
 
    INNER JOIN tinner1
 
        ON tref.id = tinner1.id
 
    WHERE tref.type = 'incident'
 
);
 
 
 
+----------------------------------+
 
| id                               |
 
+----------------------------------+
 
| ffffffb61e68ffffff2302003d4f2b49 |
 
| cb9763cda2dd48bbc751130045aa3ea9 |
 
| cb97631ea2dd48bbc7511300d96afaa9 |
 
| ce61030eb96d45a3d71002008d0a4f77 |
 
+----------------------------------+
 
4 rows in set (0.00 sec)
 
 
 
set optimizer_switch='materialization=on';
 
 
 
SELECT touter.id
 
FROM touter
 
INNER JOIN tinner1
 
    ON touter.id = tinner1.id
 
WHERE touter.type = 2
 
AND touter.id IN (
 
    SELECT tref.ref_id
 
    FROM tref
 
    INNER JOIN tinner1
 
        ON tref.id = tinner1.id
 
    WHERE tref.type = 'incident'
 
);
 
 
 
+----------------------------------+
 
| id                               |
 
+----------------------------------+
 
| ce61030eb96d45a3d71002008d0a4f77 |
 
+----------------------------------+
 
1 row in set (0.00 sec)

The attached SQL file will create the database 'matbug', populate it, and run the test query with the materialization switch both on and off.

 Comments   
Comment by Alice Sherepa [ 2018-04-23 ]

Reproducible on MariaDB 5.5-10.3, maybe the same problem as MDEV-15454.

CREATE TABLE `t1` (`id` char(32) NOT NULL DEFAULT '' primary key);
 
INSERT INTO `t1` VALUES ('00dbdc11008d0de4c0a8000cffed9876'),('034c9c1e8c061aebcb380300fff87aa6'),('07c2803a00d36d73c611227affeccbad'),('0efa9126666bdd627f1b3200ffb8e052'),('1206fe231e68286beb3302005c847481'),('1206fe421c13abf1eb231100ffbbcb45'),('1206fe8434d1eeeaeb131200ffadcea3'),('132e7008d7cabde89ff73200ffde283b'),('132e701ad7cabde89ff73200ffde283b'),('1eba8f662d031f05c3121100ffd9a171'),('22d3ae0b467f10c4c3812200ffaa9fdf'),('22d3aeb895ccd024c3111200ffa337bc'),('22d3aedb1f1a10c4c3712200ffed2bd0'),('22d3aefbc4099145c3111200ffad7b83'),('26a8dc1ca96eee5fb3300300ffbcead3'),('26cb0b315d90bba887230300fff66136'),('33efc4c327026d163b100300ffc82d36'),('34efc4a72dc923943b322200fff594a2'),('37673a163aa56c4d0b102200ffa1b742'),('37d929b3dada83ec83311000ffeb39b3'),('382d73be0015f61bc0a80a6effe65262'),('3f07397c32a0854bbf001100ffa0fba8'),('3ffa916b5f58108c7f230200ffe497f3'),('4043975e000934330a0a0b27ffd4d0bd'),('454ba3cb0040adce0a0a020cffc7ea35'),('5206fe01d4360c50eb123100ffe07400'),('549d440f007a8387c0a8000cffee7e4e'),('552e70bec05e19eb9f012200ff9d7aa0'),('557ffb3cd8f81f7693211200ffe94715'),('5685ef096e1e82f467162200ffcadb1f'),('57415aab97eeff5567013200ffe24c97'),('57415af72a3df9d467301200fff286a5'),('5c976304b0ea8b3cc70003005a151f39'),('5c97630db0ea8b3cc7000300e0851f39'),('5c97631cb0ea8b3cc70003007afd6f3d'),('5c976345b0ea8b3cc7000300397dab3d'),('5c9763c1b0ea8b3cc700030063451f39'),('5c9763ccb0ea8b3cc7000300829dab3d'),('5c9763fab0ea8b3cc7000300db951f39'),('5e6103f1de92a5f7d7812200ff9f74be'),('5ed3bf7700528ef50a0a020cffc5523d'),('6e41f1b2fcfbe81d37503100ffcbb784'),('6e41f1b7fcfbe81d37503100ffcbb784'),('6e41f1bcfcfbe81d37503100ffcbb784'),('6e41f1d5fcfbe81d37503100ffbb7784'),('71d47a9c00ea8440c0a80a6effe89cd2'),('7e610387a6018153d7333100fff18c02'),('82d3ae3abde4beaec3002200ff9f44e3'),('82d3aee81e72f2bec3120200ffd23f00'),('83673a022050eddc0b1103006229e665'),('83673a072050eddc0b2003000d2f18cb'),('83673a242050eddc0b2003007ce5e44f'),('83673a422050eddc0b2003007e57ac4f'),('83673a432050eddc0b200300c725244f'),('83673a482050eddc0b20030075373887'),('83673a492050eddc0b20030031a6284f'),('83673a742050eddc0b20030069c6a84f'),('83673a752050eddc0b20030085e7ec4f'),('83673a852050eddc0b200300a6d4604f'),('83673a9b2050eddc0b2003004646e44f'),('83673a9e2050eddc0b1103000d4eeae5'),('83673aa32050eddc0b00030009f5b126'),('83673acc2050eddc0b2003004095644f'),('83673ad32050eddc0b1103006bde2ee5'),('84e3ecfa4ebe19fa87203100ffca6d32'),('867ffbd8ae6e941e93020200ffea5601'),('899fa7f400fe3b4a0a0a020cffd1ae28'),('8e6103e6a77f6d70d7412100ffeb5666'),('942e70343d5c77a09f110200ffce8d6f'),('9585ef5a553fafb467003200ffd31972'),('9bba8fbbc8b83765c3202100ffd96142'),('9bf26373cd7da5f5df110100ffe0fcf0'),('9bf263accd7da5f5df110100fff04a17'),('9d205202b4cffbc3c4111300ffaabd69'),('9d205203b4cffbc3c4511300ff9bf1a5'),('9d205205b4cffbc3c4111300ffbab9a9'),('9d205207b4cffbc3c4511300ff9bf1a5'),('9d205209b4cffbc3c4911300ff9db5ad'),('9d20520ab4cffbc3c4111300ffca39e9'),('9d20520bb4cffbc3c4111300ffaabd69'),('9d20520bb4cffbc3c4111300ffda752d'),('9d20520db4cffbc3c4111300ffaabd69'),('9d20520eb4cffbc3c4511300ff9bf1a5'),('9d205210b4cffbc3c4111300ffbab9a9'),('9d205210b4cffbc3c4911300fffc7129'),('9d205211b4cffbc3c4111300ffca39e9'),('9d205212b4cffbc3c4111300ffda752d'),('9d205212b4cffbc3c4511300ff9bf1a5'),('9d205213b4cffbc3c4111300ffaabd69'),('9d205213b4cffbc3c4511300ffab71e5'),('9d205215b4cffbc3c4111300ffca39e9'),('9d205216b4cffbc3c4111300ffaabd69'),('9d205216b4cffbc3c4111300ffda752d'),('9d205217b4cffbc3c4111300ffbab9a9'),('9d205217b4cffbc3c4511300ffab71e5'),('9d205217b4cffbc3c4911300ff9db5ad'),('9d205219b4cffbc3c4151300ffb0866d'),('9d20521ab4cffbc3c4151300ffb0866d'),('9d20521bb4cffbc3c4111300ffbab9a9'),('9d20521cb4cffbc3c4111300ffca39e9'),('9d20521db4cffbc3c4111300ffda752d'),('9d20521db4cffbc3c4911300fffc7129'),('9d20521eb4cffbc3c4511300ffab71e5'),('9d20521fb4cffbc3c4511300ff9bf1a5'),('9d20521fb4cffbc3c4911300ff9db5ad'),('9d205220b4cffbc3c4111300ffca39e9'),('9d205221b4cffbc3c4111300ffda752d'),('9d205221b4cffbc3c4111300fffa31ad'),('9d205222b4cffbc3c4111300ffbab9a9'),('9d205222b4cffbc3c4511300ffab71e5'),('9d205223b4cffbc3c4111300ffaabd69'),('9d205223b4cffbc3c4511300ff9bf1a5'),('9d205224b4cffbc3c4911300ff9db5ad'),('9d205224b4cffbc3c4911300ffbdb5ed'),('9d205225b4cffbc3c4111300fffa31ad'),('9d205225b4cffbc3c4911300fffc7129'),('9d205226b4cffbc3c4111300ffbab9a9'),('9d205227b4cffbc3c4111300ffaabd69'),('9d205227b4cffbc3c4111300ffca39e9'),('9d205227b4cffbc3c4511300ff9bf1a5'),('9d205229b4cffbc3c4111300ffda752d'),('9d205229b4cffbc3c4511300ffab71e5'),('9d20522bb4cffbc3c4111300ffca39e9'),('9d20522cb4cffbc3c4111300ffea316d'),('9d20522cb4cffbc3c4111300fffa31ad'),('9d20522db4cffbc3c4111300ffbab9a9'),('9d20522db4cffbc3c4511300ffab71e5'),('9d20522eb4cffbc3c4111300ffaabd69'),('9d20522eb4cffbc3c4111300ffda752d'),('9d20522eb4cffbc3c4151300ffb0866d'),('9d20522eb4cffbc3c4511300ff9bf1a5'),('9d20522eb4cffbc3c4911300ff9c3165'),('9d20522eb4cffbc3c4911300ffbdb5ed'),('9d205230b4cffbc3c4111300fffa31ad'),('9d205230b4cffbc3c4911300ff9c3165'),('9d205230b4cffbc3c4d11300fffdb961'),('9d205231b4cffbc3c4111300ffbab9a9'),('9d205232b4cffbc3c4111300ffaabd69'),('9d205232b4cffbc3c4111300ffda752d'),('9d205232b4cffbc3c4511300ff9bf1a5'),('9d205234b4cffbc3c4511300ffab71e5'),('9d205236b4cffbc3c4911300ff9db5ad'),('9d205237b4cffbc3c4911300ff9db5ad'),('9d205238b4cffbc3c4111300ffbab9a9'),('9d205238b4cffbc3c4111300ffea316d'),('9d205238b4cffbc3c4511300ffab71e5'),('9d205239b4cffbc3c4111300ffaabd69'),('9d205239b4cffbc3c4111300ffda752d'),('9d205239b4cffbc3c4511300ff9bf1a5'),('9d20523cb4cffbc3c4111300ffbab9a9'),('9d20523cb4cffbc3c4111300ffea316d'),('9d20523db4cffbc3c4111300ffaabd69'),('9d20523db4cffbc3c4111300ffda752d'),('9d20523db4cffbc3c4151300ffc0c6ad'),('9d20523db4cffbc3c4511300ff9bf1a5'),('9d20523eb4cffbc3c4111300fffa31ad'),('9d20523eb4cffbc3c4151300ffc0c6ad'),('9d20523fb4cffbc3c4511300ffab71e5'),('9d205241b4cffbc3c4911300ffcc39a5'),('9d205242b4cffbc3c4911300ff9c3165'),('9d205243b4cffbc3c4111300ffea316d'),('9d205243b4cffbc3c4911300fffc7129'),('9d205244b4cffbc3c4111300ffaabd69'),('9d205244b4cffbc3c4111300ffca39e9'),('9d205244b4cffbc3c4111300ffda752d'),('9d205244b4cffbc3c4511300ffab71e5'),('9d205245b4cffbc3c4911300ffbdb5ed'),('9d205246b4cffbc3c4111300fffa31ad'),('9d205247b4cffbc3c4111300ffea316d'),('9d205247b4cffbc3c4911300ff9db5ad'),('9d205248b4cffbc3c4111300ffaabd69'),('9d205248b4cffbc3c4111300ffbab9a9'),('9d205248b4cffbc3c4111300ffca39e9'),('9d205248b4cffbc3c4111300ffda752d'),('9d205248b4cffbc3c4911300ff9db5ad'),('9d20524bb4cffbc3c4911300fffc7129'),('9d20524eb4cffbc3c4111300ffea316d'),('9d20524fb4cffbc3c4111300ffaabd69'),('9d20524fb4cffbc3c4111300ffca39e9'),('9d20524fb4cffbc3c4111300ffda752d'),('9d20524fb4cffbc3c4911300ffbdb5ed'),('9d205250b4cffbc3c4511300ffab71e5'),('9d205252b4cffbc3c4111300ffea316d'),('9d205253b4cffbc3c4111300ffaabd69'),('9d205253b4cffbc3c4111300ffca39e9'),('9d205253b4cffbc3c4111300ffda752d'),('9d205254b4cffbc3c4511300ffab71e5'),('9d205254b4cffbc3c4911300ffbdb5ed'),('9d205257b4cffbc3c4911300ff9c3165'),('9d205258b4cffbc3c4111300ffaabd69'),('9d205258b4cffbc3c4911300ff9db5ad'),('9d205259b4cffbc3c4111300ffea316d'),('9d205259b4cffbc3c4111300fffa31ad'),('9d205259b4cffbc3c4911300ff9db5ad'),('9d20525ab4cffbc3c4111300ffca39e9'),('9d20525ab4cffbc3c4111300ffda752d'),('9d20525ab4cffbc3c4d11300fffe7569'),('9d20525bb4cffbc3c4511300ffab71e5'),('9d20525cb4cffbc3c4911300ffbdb5ed'),('9d20525db4cffbc3c4111300ffbab9a9'),('9d20525db4cffbc3c4111300ffea316d'),('9d20525eb4cffbc3c4111300ffca39e9'),('9d20525eb4cffbc3c4111300ffda752d'),('9d20525eb4cffbc3c4911300ffcc39a5'),('9d20525fb4cffbc3c4511300ffab71e5'),('9d205260b4cffbc3c4111300ffaabd69'),('9d205260b4cffbc3c4111300fffa31ad'),('9d205260b4cffbc3c4911300fffc7129'),('9d205261b4cffbc3c4111300ffbab9a9'),('9d205261b4cffbc3c4911300ff9c3165'),('9d205261b4cffbc3c4911300ff9db5ad'),('9d205261b4cffbc3c4911300ffbdb5ed'),('9d205264b4cffbc3c4111300ffaabd69'),('9d205264b4cffbc3c4111300ffea316d'),('9d205264b4cffbc3c4111300fffa31ad'),('9d205265b4cffbc3c4111300ffca39e9'),('9d205266b4cffbc3c4511300ffab71e5'),('9d205267b4cffbc3c4911300ff9c3165'),('9d205268b4cffbc3c4111300ff9a7169'),('9d205268b4cffbc3c4111300ffbab9a9'),('9d205269b4cffbc3c4111300ffca39e9'),('9d205269b4cffbc3c4911300ffbdb5ed'),('9d20526ab4cffbc3c4511300ffab71e5'),('9d20526bb4cffbc3c4111300ffaabd69'),('9d20526bb4cffbc3c4111300fffa31ad'),('9d20526cb4cffbc3c4111300ffbab9a9'),('9d20526cb4cffbc3c4911300ff9c3165'),('9d20526db4cffbc3c4111300ffea316d'),('9d20526eb4cffbc3c4111300ffda752d'),('9d20526eb4cffbc3c4911300ff9db5ad'),('9d20526eb4cffbc3c4911300ffbdb5ed'),('9d20526eb4cffbc3c4911300ffcc39a5'),('9d20526eb4cffbc3c4911300fffc7129'),('9d20526fb4cffbc3c4111300ffaabd69'),('9d20526fb4cffbc3c4111300fffa31ad'),('9d205270b4cffbc3c4111300ffca39e9'),('9d205271b4cffbc3c4511300ffab71e5'),('9d205272b4cffbc3c4111300ffea316d'),('9d205273b4cffbc3c4111300ffbab9a9'),('9d205274b4cffbc3c4111300ff9a7169'),('9d205274b4cffbc3c4911300ff9db5ad'),('9d205275b4cffbc3c4111300ffda752d'),('9d205275b4cffbc3c4511300ffab71e5'),('9d205276b4cffbc3c4111300ffaabd69'),('9d205276b4cffbc3c4111300fffa31ad'),('9d205276b4cffbc3c4911300ffac3965'),('9d205276b4cffbc3c4911300ffbdb5ed'),('9d205277b4cffbc3c4111300ffbab9a9'),('9d205278b4cffbc3c4111300ff9a7169'),('9d205278b4cffbc3c4911300ffcc39a5'),('9d205279b4cffbc3c4111300ffda752d'),('9d205279b4cffbc3c4111300ffea316d'),('9d205279b4cffbc3c4911300ffac3965'),('9d20527ab4cffbc3c4111300ffca39e9'),('9d20527ab4cffbc3c4111300fffa31ad'),('9d20527bb4cffbc3c4911300ffcc39a5'),('9d20527db4cffbc3c4111300ffd93961'),('9d20527db4cffbc3c4111300ffea316d'),('9d20527eb4cffbc3c4111300ffbab9a9'),('9d20527eb4cffbc3c4111300ffca39e9'),('9d20527fb4cffbc3c4111300ff9a7169'),('9d205280b4cffbc3c4111300ffda752d'),('9d205281b4cffbc3c4111300ffd93961'),('9d205281b4cffbc3c4111300fffa31ad'),('9d205281b4cffbc3c4911300ff9db5ad'),('9d205282b4cffbc3c4111300ffbab9a9'),('9d205283b4cffbc3c4111300ff9a7169'),('9d205284b4cffbc3c4111300ffda752d'),('9d205284b4cffbc3c4111300ffea316d'),('9d205285b4cffbc3c4111300ffca39e9'),('9d205285b4cffbc3c4111300fffa31ad'),('9d205285b4cffbc3c4511300ffab71e5'),('9d205288b4cffbc3c4111300ffd93961'),('9d205288b4cffbc3c4111300ffea316d'),('9d205288b4cffbc3c4511300ffab71e5'),('9d205288b4cffbc3c4911300ffac3965'),('9d205289b4cffbc3c4111300ffbab9a9'),('9d205289b4cffbc3c4111300ffca39e9'),('9d205289b4cffbc3c4911300ffcc39a5'),('9d20528ab4cffbc3c4111300ff9a7169'),('9d20528bb4cffbc3c4111300ffda752d'),('9d20528cb4cffbc3c4111300ffd93961'),('9d20528cb4cffbc3c4511300ff9bb1a5'),('9d20528eb4cffbc3c4111300ff9a7169'),('9d20528fb4cffbc3c4111300ffda752d'),('9d20528fb4cffbc3c4111300ffea316d'),('9d205290b4cffbc3c4111300ffca39e9'),('9d205290b4cffbc3c4511300ff9bb1a5'),('9d205291b4cffbc3c4111300fffa31ad'),('9d205293b4cffbc3c4111300ffea316d'),('9d205294b4cffbc3c4111300ffca39e9'),('9d205294b4cffbc3c4911300ffac3965'),('9d205295b4cffbc3c4111300ff9a7169'),('9d205295b4cffbc3c4111300ffbab9a9'),('9d205296b4cffbc3c4111300ffda752d'),('9d205296b4cffbc3c4511300ffab71e5'),('9d205297b4cffbc3c4111300ffd93961'),('9d205297b4cffbc3c4511300ff9bb1a5'),('9d205298b4cffbc3c4111300fffa31ad'),('9d205299b4cffbc3c4111300ff9a7169'),('9d205299b4cffbc3c4111300ffa97121'),('9d205299b4cffbc3c4111300ffbab9a9'),('9d20529ab4cffbc3c4111300ffea316d'),('9d20529bb4cffbc3c4111300ffca39e9'),('9d20529bb4cffbc3c4111300ffd93961'),('9d20529bb4cffbc3c4511300ff9bb1a5'),('9d20529cb4cffbc3c4111300fffa31ad'),('9d20529db4cffbc3c4111300ffa97121'),('9d20529db4cffbc3c4511300ffab71e5'),('9d20529eb4cffbc3c4111300ffea316d'),('9d20529fb4cffbc3c4111300ffca39e9'),('9d2052a0b4cffbc3c4111300ffbab9a9'),('9d2052a1b4cffbc3c4111300ffa97121'),('9d2052a1b4cffbc3c4511300ffab71e5'),('9d2052a3b4cffbc3c4111300ffd93961'),('9d2052a3b4cffbc3c4111300ffda752d'),('9d2052a3b4cffbc3c4111300fffa31ad'),('9d2052a3b4cffbc3c4911300ffac3965'),('9d2052a4b4cffbc3c4111300ffbab9a9'),('9d2052a5b4cffbc3c4111300ffa97121'),('9d2052a5b4cffbc3c4111300ffea316d'),('9d2052a5b4cffbc3c4d11300ffbeb9e5'),('9d2052a6b4cffbc3c4111300ffca39e9'),('9d2052a6b4cffbc3c4d11300ffbeb9e5'),('9d2052a7b4cffbc3c4111300fffa31ad'),('9d2052a7b4cffbc3c4151300ffb0466d'),('9d2052a7b4cffbc3c4511300ff9bb1a5'),('9d2052a8b4cffbc3c4151300ffe0c6ed'),('9d2052a8b4cffbc3c4191300ffe34e6d'),('9d2052a8b4cffbc3c4511300ffab71e5'),('9d2052a9b4cffbc3c4111300ff9a7169'),('9d2052a9b4cffbc3c4191300ffe34e6d'),('9d2052a9b4cffbc3c4911300ffcc39a5'),('9d2052aab4cffbc3c4111300ffa97121'),('9d2052aab4cffbc3c4111300ffca39e9'),('9d2052aab4cffbc3c4111300ffda752d'),('9d2052aab4cffbc3c4151300ffe0c6ed'),('9d2052abb4cffbc3c4111300ffbab9a9'),('9d2052abb4cffbc3c4911300ffac3965'),('9d2052acb4cffbc3c4511300ff9bb1a5'),('9d2052acb4cffbc3c4511300ffab71e5'),('9d2052adb4cffbc3c4191300ffe34e6d'),('9d2052aeb4cffbc3c4111300ffda752d'),('9d2052aeb4cffbc3c4111300fffa31ad'),('9d2052afb4cffbc3c4111300ffbab9a9'),('9d2052b0b4cffbc3c4511300ff9bb1a5'),('9d2052b1b4cffbc3c4111300ffd93961'),('9d2052b1b4cffbc3c4911300ffcc39a5'),('9d2052b2b4cffbc3c4111300fffa31ad'),('9d2052b3b4cffbc3c4511300ffab71e5'),('9d2052b4b4cffbc3c4111300ffa97121'),('9d2052b6b4cffbc3c4111300ffca39e9'),('9d2052b6b4cffbc3c4111300ffda752d'),('9d2052b6b4cffbc3c4911300ffcc39a5'),('9d2052b7b4cffbc3c4511300ff9bb1a5'),('9d2052b9b4cffbc3c4111300fffa31ad'),('9d2052b9b4cffbc3c4151300ffb0466d'),('9d2052bab4cffbc3c4151300ffb0466d'),('9d2052bbb4cffbc3c4111300ffa97121'),('9d2052bbb4cffbc3c4511300ff9bb1a5'),('9d2052bcb4cffbc3c4111300ff9a7169'),('9d2052bdb4cffbc3c4111300fffa31ad'),('9d2052beb4cffbc3c4111300ffaa7d69'),('9d2052beb4cffbc3c4111300ffd93961'),('9d2052bfb4cffbc3c4111300ffa97121'),('9d2052c0b4cffbc3c4111300ff9a7169'),('9d2052c1b4cffbc3c4d11300ff9e3da5'),('9d2052c2b4cffbc3c4511300ff9bb1a5'),('9d2052c6b4cffbc3c4511300ff9bb1a5'),('9d2052c7b4cffbc3c4111300ff9a7169'),('9d2052c7b4cffbc3c4111300ffd93961'),('9d2052c7b4cffbc3c4111300fffa31ad'),('9d2052c8b4cffbc3c4111300ffa97121'),('9d2052cbb4cffbc3c4111300ff9a7169'),('9d2052cbb4cffbc3c4111300ffa97121'),('9d2052cbb4cffbc3c4111300ffd93961'),('9d2052ccb4cffbc3c4111300ffa97121'),('9d2052cdb4cffbc3c4511300ff9bb1a5'),('9d2052cdb4cffbc3c4d11300ff9e3da5'),('9d2052d0b4cffbc3c4111300ffa97121'),('9d2052d1b4cffbc3c4511300ff9bb1a5'),('9d2052d2b4cffbc3c4111300ff9a7169'),('9d2052d2b4cffbc3c4111300ffaa7d69'),('9d2052d6b4cffbc3c4111300ff9a7169'),('9d2052d6b4cffbc3c4111300ffaa7d69'),('9d2052d8b4cffbc3c4111300ffd93961'),('9d2052d8b4cffbc3c4511300ff9bb1a5'),('9d2052dcb4cffbc3c4111300ffd93961'),('9d2052dcb4cffbc3c4511300ff9bb1a5'),('9d2052ddb4cffbc3c4111300ff9a7169'),('9d2052ddb4cffbc3c4111300ffaa7d69'),('9d2052dfb4cffbc3c4d11300ff9e3da5'),('9d2052e1b4cffbc3c4111300ff9a7169'),('9d2052e1b4cffbc3c4111300ffa97121'),('9d2052e1b4cffbc3c4111300ffaa7d69'),('9d2052e2b4cffbc3c4d11300ff9e3da5'),('9d2052e3b4cffbc3c4911300ffadfdad'),('9d2052e5b4cffbc3c4111300ffd93961'),('9d2052e5b4cffbc3c4511300ff9bb1a5'),('9d2052e6b4cffbc3c4511300ff9bb1a5'),('9d2052e8b4cffbc3c4111300ffaa7d69'),('9d2052e9b4cffbc3c4111300ffd93961'),('9d2052ecb4cffbc3c4111300ffaa7d69'),('9d2052edb4cffbc3c4511300ff9bb1a5'),('9d2052efb4cffbc3c4111300ffba79a9'),('9d2052efb4cffbc3c4911300ffadfdad'),('9d2052f1b4cffbc3c4511300ff9bb1a5'),('9d2052f3b4cffbc3c4111300ffa97121'),('9d2052f3b4cffbc3c4111300ffaa7d69'),('9d2052f3b4cffbc3c4111300ffba79a9'),('9d2052f5b4cffbc3c4191300ffe34e6d'),('9d2052f6b4cffbc3c4191300ffe34e6d'),('9d2052f7b4cffbc3c4111300ffaa7d69'),('9d2052f8b4cffbc3c4511300ff9bb1a5'),('9d2052f8b4cffbc3c4911300ff9d75ad'),('9d2052fab4cffbc3c4111300ffa97121'),('9d2052fab4cffbc3c4111300ffba79a9'),('9d2052fcb4cffbc3c4511300ff9bb1a5'),('9d2052fdb4cffbc3c4111300ffa97121'),('9d2052fdb4cffbc3c4d11300ff9e3da5'),('9d2052feb4cffbc3c4111300ffaa7d69'),('9d2052feb4cffbc3c4111300ffba79a9'),('9d2052feb4cffbc3c4151300ffb0466d'),('9d2052ffb4cffbc3c4911300ff9d75ad'),('9dbe5d353e7d40ed37a33100ffe1d6cb'),('9e61035b158ba685d7211100ffb678c4'),('a14f7b020069413e4a362312ffc7a8c2'),('a1d3ae5dadce9624c3503200fffbae53'),('a206fee8871aac6aeb232100fff05660'),('a2d3ae033a657bfac3130200ffe56b22'),('a57fcf5db18a7fee9fb31200ffef825f'),('a9075a4d00b307a10a258103ffbeefcf'),('aa9763b6bde4435ec7201300ffa8e6d1'),('adfa918532dcb0d87f110300ffdbeec6'),('b1d3ae00782db348c3322200ffb83a56'),('b1d3ae530295b348c3301200ffe1a583'),('bb71bb8700cf7c280a0a020cffd30133'),('bcbe5d90deeabfc837601000ffd6ba23'),('bcef3393945a21b23a2440c0ffac1fb2'),('be071361000091411b102000ffc2b2f4'),('c18c9585e8f802d32fc00300ffd7f4e9'),('c3d3aea976173b0ac3322200ffaf5469'),('c67fcf1bcf4696fc9f333100ffb9860d'),('c67fcf34ef4afa7d9f1022001b71e4d0'),('c67fcf43ef4afa7d9f612200e9607062'),('c67fcf72ef4afa7d9f811200c1e60a86'),('c67fcf7def4afa7d9fa42200a7b73722'),('c67fcfa9ef4afa7d9f0022009648c7a7'),('c6a8dc0cf220eee1b3611300ffa1ce99'),('c6a8dc0ef220eee1b3611300ffb00e11'),('c6a8dc0ff220eee1b3611300ffb1021d'),('c6a8dc12f220eee1b3611300ffc0c251'),('c6a8dc2bf220eee1b3211300ffdff15d'),('c6a8dc2bf220eee1b3211300ffeff55d'),('c6a8dc36f220eee1b3211300e5e7eedd'),('c6a8dc38f220eee1b3611300ffd04e51'),('c6a8dc41f220eee1b3611300ffb1021d'),('c6a8dc42f220eee1b3211300ffdff15d'),('c6a8dc4bf220eee1b3611300ffc0c251'),('c6a8dc54f220eee1b3211300ffceb955'),('c6a8dc56f220eee1b3211300ffceb955'),('c6a8dc58f220eee1b3211300ffceb955'),('c6a8dc5af220eee1b3211300ffceb955'),('c6a8dc5cf220eee1b3211300ffceb955'),('c6a8dc62f220eee1b3211300ffcffd1d'),('c6a8dc68f220eee1b3611300ffd04e51'),('c6a8dc6ef220eee1b3211300ffdff15d'),('c6a8dc7df220eee1b3611300ffc0c251'),('c6a8dc82f220eee1b3211300ff9e7515'),('c6a8dc85f220eee1b3611300ffe00691'),('c6a8dc89f220eee1b3211300ff9e7515'),('c6a8dc91f220eee1b3211300ff9e7515'),('c6a8dc93f220eee1b3611300fff0ce91'),('c6a8dc94f220eee1b3211300ffbff91d'),('c6a8dc94f220eee1b3611300ffa18e99'),('c6a8dc98f220eee1b3211300ff9e7515'),('c6a8dc9af220eee1b3211300ffbe3555'),('c6a8dc9df220eee1b3611300ffa14e99'),('c6a8dca0f220eee1b3211300ff9e7515'),('c6a8dca7f220eee1b3211300ff9e7515'),('c6a8dcaff220eee1b3211300ff9e7515'),('c6a8dcb0f220eee1b3211300fffff95d'),('c6a8dcb3f220eee1b3611300ffd04e51'),('c6a8dcb4f220eee1b3611300ffa18e99'),('c6a8dcb6f220eee1b3211300ff9e7515'),('c6a8dcbcf220eee1b3211300ff9e7515'),('c6a8dcc6f220eee1b3211300ffaff51d'),('c6a8dcc7f220eee1b3211300aec72edd'),('c6a8dcc9f220eee1b3211300ffbe3555'),('c6a8dccdf220eee1b3611300ffb1ced9'),('c6a8dcd6f220eee1b3611300ffb1ced9'),('c6a8dce7f220eee1b3211300ffceb955'),('c6a8dce9f220eee1b3211300ffceb955'),('c6a8dceff220eee1b3611300fff08e91'),('c6a8dcf5f220eee1b3611300ffb0ca11'),('c6a8dcfff220eee1b3211300ff9f351d'),('cb97631ea2dd48bbc7511300d96afaa9'),('cb976321a2dd48bbc7511300edcabea9'),('cb9763cda2dd48bbc751130045aa3ea9'),('cb9763f8a2dd48bbc75113004d8afaa9'),('cb9a393100c813cd0a0a020cffd66960'),('cb9a712e7a977eae47110300ffbb141c'),('cdba8f86e72ce219c3211000ffd4f02b'),('cdba8f999d5ae219c3000200ffdb6866'),('ce61030eb96d45a3d71002008d0a4f77'),('ce610313b96d45a3d7100200b5d57bfb'),('ce61031bb96d45a3d7100200ffcd4551'),('ce61031eb96d45a3d7200200dc0ec9e5'),('ce61034db96d45a3d71002005c3a4f77'),('ce610359b96d45a3d720020062005de5'),('ce61037534d145bcd7003200ffa0feb3'),('ce61039ffa6c0c12d7222100ff9bec01'),('ce6103c4b96d45a3d7200200508d49e5'),('ce9619772ccf52f4dbbbf6048bb35566'),('ce9619db2ccf52f4dbbbf604c346d9e6'),('ce9619f02ccf52f4dbbbf60459939166'),('d206fead3623666ceb923000ffad8ea2'),('d206fee43623666ceb923000ffad8ea2'),('d2f6a9e3c4726188e7010300ffc8dc84'),('d928b692008732050a0a020cffc7923c'),('e0b8f58d4d27b31bdb110300ffe274c4'),('ee4bcc768f88ed939f102000ffe973f6'),('f0b8f50402533691db423200ffd83d68'),('f0b8f54cefb03bc8db421200fff953c8'),('f106fe6678e44d3deb102200ffb50c0c'),('f106fef66cc28c1ceb110200fffe9313'),('f27f9d31dada1c57ff201000ffbdeaa9'),('f27f9d7bdada1c57ffa01000ffbfe42f'),('f67fcf001e021a1c9f122100ffb53172'),('ff7b125197a2ddee53b20300ffd88cdf'),('fff6a74a1038234f73630300ffe9343b'),('ffffff331e68ffffff230200ffb59561'),('ffffff4eb18affffff830200fff27e70'),('ffffffb61e68ffffff2302003d4f2b49'),('ffffffb7b18affffff220200fff43d1b');
 
 
 
CREATE TABLE `t2` (`type` int , `id` char(32) NOT NULL DEFAULT '' primary key);
 
INSERT INTO `t2` VALUES (2,'5c976304b0ea8b3cc70003005a151f39'),(2,'5c97630db0ea8b3cc7000300e0851f39'),(1,'5c97631cb0ea8b3cc70003007afd6f3d'),(2,'5c976345b0ea8b3cc7000300397dab3d'),(1,'83673a022050eddc0b1103006229e665'),(1,'83673a072050eddc0b2003000d2f18cb'),(5,'83673a422050eddc0b2003007e57ac4f'),(1,'83673a432050eddc0b200300c725244f'),(1,'83673a482050eddc0b20030075373887'),(1,'83673a742050eddc0b20030069c6a84f'),(1,'83673a752050eddc0b20030085e7ec4f'),(1,'83673a852050eddc0b200300a6d4604f'),(2,'83673a9b2050eddc0b2003004646e44f'),(1,'83673a9e2050eddc0b1103000d4eeae5'),(2,'83673acc2050eddc0b2003004095644f'),(1,'83673ad32050eddc0b1103006bde2ee5'),(1,'c67fcf72ef4afa7d9f811200c1e60a86'),(1,'c67fcf7def4afa7d9fa42200a7b73722'),(2,'c67fcfa9ef4afa7d9f0022009648c7a7'),(2,'cb97631ea2dd48bbc7511300d96afaa9'),(2,'cb9763cda2dd48bbc751130045aa3ea9'),(2,'ce61030eb96d45a3d71002008d0a4f77'),(1,'ce610313b96d45a3d7100200b5d57bfb'),(1,'ce61031eb96d45a3d7200200dc0ec9e5'),(3,'ce610359b96d45a3d720020062005de5'),(1,'ce6103c4b96d45a3d7200200508d49e5'),(2,'ce9619772ccf52f4dbbbf6048bb35566'),(1,'ce9619db2ccf52f4dbbbf604c346d9e6'),(5,'ce9619f02ccf52f4dbbbf60459939166'),(2,'ffffffb61e68ffffff2302003d4f2b49');
 
 
 
CREATE TABLE `t3` (`ref_id` varchar(32) ,`type` varchar(80),`id` char(32) NOT NULL DEFAULT '');
 
INSERT INTO `t3` VALUES ('ffffffb61e68ffffff2302003d4f2b49','incident','1206fe231e68286beb3302005c847481'),('5c976304b0ea8b3cc70003005a151f39','faux pas','5c9763c1b0ea8b3cc700030063451f39'),('5c976345b0ea8b3cc7000300397dab3d','oopsies','5c9763ccb0ea8b3cc7000300829dab3d'),('5c97630db0ea8b3cc7000300e0851f39','deniable','5c9763fab0ea8b3cc7000300db951f39'),('83673acc2050eddc0b2003004095644f','wasntme','83673a242050eddc0b2003007ce5e44f'),('83673a9b2050eddc0b2003004646e44f','wasntme','83673a492050eddc0b20030031a6284f'),('ce9619772ccf52f4dbbbf6048bb35566','faux pas','83673aa32050eddc0b00030009f5b126'),('c67fcfa9ef4afa7d9f0022009648c7a7','unlikely','c67fcf34ef4afa7d9f1022001b71e4d0'),('c67fcfa9ef4afa7d9f0022009648c7a7','improbable','c67fcf43ef4afa7d9f612200e9607062'),('cb9763cda2dd48bbc751130045aa3ea9','incident','c6a8dc36f220eee1b3211300e5e7eedd'),('cb97631ea2dd48bbc7511300d96afaa9','problem','c6a8dcc7f220eee1b3211300aec72edd'),('cb9763cda2dd48bbc751130045aa3ea9','problem','cb976321a2dd48bbc7511300edcabea9'),('cb97631ea2dd48bbc7511300d96afaa9','incident','cb9763f8a2dd48bbc75113004d8afaa9'),('ce61030eb96d45a3d71002008d0a4f77','incident','ce61034db96d45a3d71002005c3a4f77');
 
 
 
set optimizer_switch='materialization=on';
 
 
 
SELECT t2.id FROM t2 JOIN t1 ON t2.id = t1.id
 
  WHERE  t2.id IN (SELECT t3.ref_id FROM t3 JOIN t1 ON t3.id = t1.id);
 
 
 
set optimizer_switch='materialization=off';
 
 
 
SELECT t2.id FROM t2 JOIN t1 ON t2.id = t1.id
 
  WHERE  t2.id IN (SELECT t3.ref_id FROM t3 JOIN t1 ON t3.id = t1.id);
 
 
 
DROP TABLE t1,t2,t3;

With materialization=on -empty set as a result of the query in 5.5, 1 row is returned in 10.0 and higher

Empty set (0.00 sec)
 
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+--------------------------+
 
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref            | rows | filtered | Extra                    |
 
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+--------------------------+
 
|    1 | PRIMARY      | t2          | index  | PRIMARY       | PRIMARY      | 32      | NULL           |   30 |   100.00 | Using index              |
 
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 35      | func           |    1 |   100.00 | Using where              |
 
|    1 | PRIMARY      | t1          | eq_ref | PRIMARY       | PRIMARY      | 32      | test.t3.ref_id |    1 |   100.00 | Using where; Using index |
 
|    2 | MATERIALIZED | t3          | ALL    | NULL          | NULL         | NULL    | NULL           |   14 |   100.00 | Using where              |
 
|    2 | MATERIALIZED | t1          | eq_ref | PRIMARY       | PRIMARY      | 32      | test.t3.id     |    1 |   100.00 | Using index              |
 
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+--------------------------+
 
5 rows in set, 1 warning (0.00 sec)
 
 
 
Note (Code 1003): select `test`.`t2`.`id` AS `id` from `test`.`t2` semi join (`test`.`t3` join `test`.`t1`) join `test`.`t1` where ((`test`.`t1`.`id` = `test`.`t2`.`id`) and (`test`.`t1`.`id` = `test`.`t3`.`id`) and (`test`.`t2`.`id` = `test`.`t3`.`ref_id`))

Comment by Varun Gupta (Inactive) [ 2018-04-24 ]

Reduced rows from t1

CREATE TABLE `t1` (`id` char(32) NOT NULL DEFAULT '' primary key);
 
INSERT INTO `t1` VALUES ('00dbdc11008d0de4c0a8000cffed9876'),('034c9c1e8c061aebcb380300fff87aa6'),('07c2803a00d36d73c611227affeccbad'),('0efa9126666bdd627f1b3200ffb8e052'),('1206fe231e68286beb3302005c847481'),('1206fe421c13abf1eb231100ffbbcb45'),('1206fe8434d1eeeaeb131200ffadcea3'),('132e7008d7cabde89ff73200ffde283b'),('132e701ad7cabde89ff73200ffde283b'),('1eba8f662d031f05c3121100ffd9a171'),('22d3ae0b467f10c4c3812200ffaa9fdf'),('22d3aeb895ccd024c3111200ffa337bc'),('22d3aedb1f1a10c4c3712200ffed2bd0'),('22d3aefbc4099145c3111200ffad7b83'),('26a8dc1ca96eee5fb3300300ffbcead3'),('26cb0b315d90bba887230300fff66136'),('33efc4c327026d163b100300ffc82d36'),('34efc4a72dc923943b322200fff594a2'),('37673a163aa56c4d0b102200ffa1b742'),('37d929b3dada83ec83311000ffeb39b3'),('382d73be0015f61bc0a80a6effe65262'),('3f07397c32a0854bbf001100ffa0fba8'),('3ffa916b5f58108c7f230200ffe497f3'),('4043975e000934330a0a0b27ffd4d0bd'),('454ba3cb0040adce0a0a020cffc7ea35'),('5206fe01d4360c50eb123100ffe07400'),('549d440f007a8387c0a8000cffee7e4e'),('552e70bec05e19eb9f012200ff9d7aa0'),('557ffb3cd8f81f7693211200ffe94715'),('5685ef096e1e82f467162200ffcadb1f'),('57415aab97eeff5567013200ffe24c97'),('57415af72a3df9d467301200fff286a5'),('5c976304b0ea8b3cc70003005a151f39'),('5c97630db0ea8b3cc7000300e0851f39'),('5c97631cb0ea8b3cc70003007afd6f3d'),('5c976345b0ea8b3cc7000300397dab3d'),('5c9763c1b0ea8b3cc700030063451f39'),('5c9763ccb0ea8b3cc7000300829dab3d'),('5c9763fab0ea8b3cc7000300db951f39'),('5e6103f1de92a5f7d7812200ff9f74be'),('5ed3bf7700528ef50a0a020cffc5523d'),('6e41f1b2fcfbe81d37503100ffcbb784'),('6e41f1b7fcfbe81d37503100ffcbb784'),('6e41f1bcfcfbe81d37503100ffcbb784'),('6e41f1d5fcfbe81d37503100ffbb7784'),('71d47a9c00ea8440c0a80a6effe89cd2'),('7e610387a6018153d7333100fff18c02'),('82d3ae3abde4beaec3002200ff9f44e3'),('82d3aee81e72f2bec3120200ffd23f00'),('83673a022050eddc0b1103006229e665'),('83673a072050eddc0b2003000d2f18cb'),('83673a242050eddc0b2003007ce5e44f'),('83673a422050eddc0b2003007e57ac4f'),('83673a432050eddc0b200300c725244f'),('83673a482050eddc0b20030075373887'),('83673a492050eddc0b20030031a6284f'),('83673a742050eddc0b20030069c6a84f'),('83673a752050eddc0b20030085e7ec4f'),('83673a852050eddc0b200300a6d4604f'),('83673a9b2050eddc0b2003004646e44f'),('83673a9e2050eddc0b1103000d4eeae5'),('83673aa32050eddc0b00030009f5b126'),('83673acc2050eddc0b2003004095644f'),('83673ad32050eddc0b1103006bde2ee5'),('84e3ecfa4ebe19fa87203100ffca6d32'),('867ffbd8ae6e941e93020200ffea5601'),('899fa7f400fe3b4a0a0a020cffd1ae28'),('8e6103e6a77f6d70d7412100ffeb5666'),('942e70343d5c77a09f110200ffce8d6f'),('9585ef5a553fafb467003200ffd31972'),('9bba8fbbc8b83765c3202100ffd96142'),('9bf26373cd7da5f5df110100ffe0fcf0'),('9bf263accd7da5f5df110100fff04a17'),('9d205202b4cffbc3c4111300ffaabd69'),('9d205203b4cffbc3c4511300ff9bf1a5'),('9d205205b4cffbc3c4111300ffbab9a9'),('9d205207b4cffbc3c4511300ff9bf1a5'),('9d205209b4cffbc3c4911300ff9db5ad'),('9d20520ab4cffbc3c4111300ffca39e9'),('9d20520bb4cffbc3c4111300ffaabd69'),('9d20520bb4cffbc3c4111300ffda752d'),('9d20520db4cffbc3c4111300ffaabd69'),('9d20520eb4cffbc3c4511300ff9bf1a5'),('9d205210b4cffbc3c4111300ffbab9a9'),('9d205210b4cffbc3c4911300fffc7129'),('9d205211b4cffbc3c4111300ffca39e9'),('9d205212b4cffbc3c4111300ffda752d'),('9d205212b4cffbc3c4511300ff9bf1a5'),('9d205213b4cffbc3c4111300ffaabd69'),('9d205213b4cffbc3c4511300ffab71e5'),('9d205215b4cffbc3c4111300ffca39e9'),('9d205216b4cffbc3c4111300ffaabd69'),('9d205216b4cffbc3c4111300ffda752d'),('9d205217b4cffbc3c4111300ffbab9a9'),('9d205217b4cffbc3c4511300ffab71e5'),('9d205217b4cffbc3c4911300ff9db5ad'),('9d205219b4cffbc3c4151300ffb0866d'),('9d20521ab4cffbc3c4151300ffb0866d'),('9d20521bb4cffbc3c4111300ffbab9a9'),('9d20521cb4cffbc3c4111300ffca39e9'),('9d20521db4cffbc3c4111300ffda752d'),('9d20521db4cffbc3c4911300fffc7129'),('9d20521eb4cffbc3c4511300ffab71e5'),('9d20521fb4cffbc3c4511300ff9bf1a5'),('9d20521fb4cffbc3c4911300ff9db5ad'),('9d205220b4cffbc3c4111300ffca39e9'),('9d205221b4cffbc3c4111300ffda752d'),('9d205221b4cffbc3c4111300fffa31ad'),('9d205222b4cffbc3c4111300ffbab9a9'),('9d205222b4cffbc3c4511300ffab71e5'),('9d205223b4cffbc3c4111300ffaabd69'),('9d205223b4cffbc3c4511300ff9bf1a5'),('9d205224b4cffbc3c4911300ff9db5ad'),('9d205224b4cffbc3c4911300ffbdb5ed'),('9d205225b4cffbc3c4111300fffa31ad'),('9d205225b4cffbc3c4911300fffc7129'),('9d205226b4cffbc3c4111300ffbab9a9'),('9d205227b4cffbc3c4111300ffaabd69'),('9d205227b4cffbc3c4111300ffca39e9'),('9d205227b4cffbc3c4511300ff9bf1a5'),('9d205229b4cffbc3c4111300ffda752d'),('9d205229b4cffbc3c4511300ffab71e5'),('9d20522bb4cffbc3c4111300ffca39e9'),('9d20522cb4cffbc3c4111300ffea316d'),('9d20522cb4cffbc3c4111300fffa31ad'),('9d20522db4cffbc3c4111300ffbab9a9'),('9d20522db4cffbc3c4511300ffab71e5'),('9d20522eb4cffbc3c4111300ffaabd69'),('9d20522eb4cffbc3c4111300ffda752d'),('9d20522eb4cffbc3c4151300ffb0866d'),('9d20522eb4cffbc3c4511300ff9bf1a5'),('9d20522eb4cffbc3c4911300ff9c3165'),('9d20522eb4cffbc3c4911300ffbdb5ed'),('9d205230b4cffbc3c4111300fffa31ad'),('9d205230b4cffbc3c4911300ff9c3165'),('9d205230b4cffbc3c4d11300fffdb961'),('9d205231b4cffbc3c4111300ffbab9a9'),('9d205232b4cffbc3c4111300ffaabd69'),('9d205232b4cffbc3c4111300ffda752d'),('9d205232b4cffbc3c4511300ff9bf1a5'),('9d205234b4cffbc3c4511300ffab71e5'),('9d205236b4cffbc3c4911300ff9db5ad'),('9d205237b4cffbc3c4911300ff9db5ad'),('9d205238b4cffbc3c4111300ffbab9a9'),('9d205238b4cffbc3c4111300ffea316d'),('9d205238b4cffbc3c4511300ffab71e5'),('9d205239b4cffbc3c4111300ffaabd69'),('9d205239b4cffbc3c4111300ffda752d'),('9d205239b4cffbc3c4511300ff9bf1a5'),('9d20523cb4cffbc3c4111300ffbab9a9'),('9d20523cb4cffbc3c4111300ffea316d'),('9d20523db4cffbc3c4111300ffaabd69'),('9d20523db4cffbc3c4111300ffda752d'),('9d20523db4cffbc3c4151300ffc0c6ad'),('9d20523db4cffbc3c4511300ff9bf1a5'),('9d20523eb4cffbc3c4111300fffa31ad'),('9d20523eb4cffbc3c4151300ffc0c6ad'),('9d20523fb4cffbc3c4511300ffab71e5'),('9d205241b4cffbc3c4911300ffcc39a5'),('9d205242b4cffbc3c4911300ff9c3165'),('9d205243b4cffbc3c4111300ffea316d'),('9d205243b4cffbc3c4911300fffc7129'),('9d205244b4cffbc3c4111300ffaabd69');
 
 
CREATE TABLE `t2` (`type` int , `id` char(32) NOT NULL DEFAULT '' primary key);
 
INSERT INTO `t2` VALUES 
(2,'5c976304b0ea8b3cc70003005a151f39'),(2,'5c97630db0ea8b3cc7000300e0851f39'),
 
(1,'5c97631cb0ea8b3cc70003007afd6f3d'),(2,'5c976345b0ea8b3cc7000300397dab3d'),
 
(1,'83673a022050eddc0b1103006229e665'),(1,'83673a072050eddc0b2003000d2f18cb'),
 
(5,'83673a422050eddc0b2003007e57ac4f'),(1,'83673a432050eddc0b200300c725244f'),
 
(1,'83673a482050eddc0b20030075373887'),(1,'83673a742050eddc0b20030069c6a84f'),
 
(1,'83673a752050eddc0b20030085e7ec4f'),(1,'83673a852050eddc0b200300a6d4604f'),
 
(2,'83673a9b2050eddc0b2003004646e44f'),(1,'83673a9e2050eddc0b1103000d4eeae5'),
 
(2,'83673acc2050eddc0b2003004095644f'),(1,'83673ad32050eddc0b1103006bde2ee5'),
 
(1,'c67fcf72ef4afa7d9f811200c1e60a86'),(1,'c67fcf7def4afa7d9fa42200a7b73722'),
 
(2,'c67fcfa9ef4afa7d9f0022009648c7a7'),(2,'cb97631ea2dd48bbc7511300d96afaa9'),
 
(2,'cb9763cda2dd48bbc751130045aa3ea9'),(2,'ce61030eb96d45a3d71002008d0a4f77'),
 
(1,'ce610313b96d45a3d7100200b5d57bfb'),(1,'ce61031eb96d45a3d7200200dc0ec9e5'),
 
(3,'ce610359b96d45a3d720020062005de5'),(1,'ce6103c4b96d45a3d7200200508d49e5'),
 
(2,'ce9619772ccf52f4dbbbf6048bb35566'),(1,'ce9619db2ccf52f4dbbbf604c346d9e6'),
 
(5,'ce9619f02ccf52f4dbbbf60459939166'),(2,'ffffffb61e68ffffff2302003d4f2b49');
 
 
CREATE TABLE `t3` (`ref_id` varchar(32) ,`type` varchar(80),`id` char(32) NOT NULL DEFAULT '');
 
INSERT INTO `t3` VALUES 
('ffffffb61e68ffffff2302003d4f2b49','incident','1206fe231e68286beb3302005c847481'),
 
('5c976304b0ea8b3cc70003005a151f39','faux pas','5c9763c1b0ea8b3cc700030063451f39'),
 
('5c976345b0ea8b3cc7000300397dab3d','oopsies','5c9763ccb0ea8b3cc7000300829dab3d'),
 
('5c97630db0ea8b3cc7000300e0851f39','deniable','5c9763fab0ea8b3cc7000300db951f39'),
 
('83673acc2050eddc0b2003004095644f','wasntme','83673a242050eddc0b2003007ce5e44f'),
 
('83673a9b2050eddc0b2003004646e44f','wasntme','83673a492050eddc0b20030031a6284f'),
 
('ce9619772ccf52f4dbbbf6048bb35566','faux pas','83673aa32050eddc0b00030009f5b126'),
 
('c67fcfa9ef4afa7d9f0022009648c7a7','unlikely','c67fcf34ef4afa7d9f1022001b71e4d0'),
 
('c67fcfa9ef4afa7d9f0022009648c7a7','improbable','c67fcf43ef4afa7d9f612200e9607062'),
 
('cb9763cda2dd48bbc751130045aa3ea9','incident','c6a8dc36f220eee1b3211300e5e7eedd'),
 
('cb97631ea2dd48bbc7511300d96afaa9','problem','c6a8dcc7f220eee1b3211300aec72edd'),
 
('cb9763cda2dd48bbc751130045aa3ea9','problem','cb976321a2dd48bbc7511300edcabea9'),
 
('cb97631ea2dd48bbc7511300d96afaa9','incident','cb9763f8a2dd48bbc75113004d8afaa9'),
 
('ce61030eb96d45a3d71002008d0a4f77','incident','ce61034db96d45a3d71002005c3a4f77');
 
 
set optimizer_switch='materialization=on';
 
 
 
SELECT t2.id FROM t2,t1 
  WHERE  t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id;
 
 
 set optimizer_switch='materialization=off';
 
 
 
SELECT t2.id FROM t2,t1 
  WHERE  t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id;
 
 
 
DROP TABLE t1,t2,t3;

Comment by Varun Gupta (Inactive) [ 2018-04-24 ]

With materialization ON

analyze format=json
 
SELECT t2.id FROM t2,t1 
WHERE  t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id;
 
ANALYZE
 
{
 
  "query_block": {
 
    "select_id": 1,
 
    "r_loops": 1,
 
    "r_total_time_ms": 7527.8,
 
    "table": {
 
      "table_name": "t2",
 
      "access_type": "index",
 
      "possible_keys": ["PRIMARY"],
 
      "key": "PRIMARY",
 
      "key_length": "32",
 
      "used_key_parts": ["id"],
 
      "r_loops": 1,
 
      "rows": 30,
 
      "r_rows": 30,
 
      "r_total_time_ms": 0.4188,
 
      "filtered": 100,
 
      "r_filtered": 100,
 
      "using_index": true
 
    },
 
    "table": {
 
      "table_name": "<subquery2>",
 
      "access_type": "eq_ref",
 
      "possible_keys": ["distinct_key"],
 
      "key": "distinct_key",
 
      "key_length": "35",
 
      "used_key_parts": ["ref_id"],
 
      "ref": ["func"],
 
      "r_loops": 30,
 
      "rows": 1,
 
      "r_rows": 0.2333,
 
      "r_total_time_ms": 0.2576,
 
      "filtered": 100,
 
      "r_filtered": 100,
 
      "attached_condition": "t2.`id` = t3.ref_id",
 
      "materialized": {
 
        "unique": 1,
 
        "query_block": {
 
          "select_id": 2,
 
          "table": {
 
            "table_name": "t3",
 
            "access_type": "ALL",
 
            "r_loops": 1,
 
            "rows": 14,
 
            "r_rows": 14,
 
            "r_total_time_ms": 0.0929,
 
            "filtered": 100,
 
            "r_filtered": 100,
 
            "attached_condition": "t3.ref_id is not null"
 
          },
 
          "table": {
 
            "table_name": "t1",
 
            "access_type": "eq_ref",
 
            "possible_keys": ["PRIMARY"],
 
            "key": "PRIMARY",
 
            "key_length": "32",
 
            "used_key_parts": ["id"],
 
            "ref": ["test.t3.id"],
 
            "r_loops": 14,
 
            "rows": 1,
 
            "r_rows": 0.5,
 
            "r_total_time_ms": 0.4384,
 
            "filtered": 100,
 
            "r_filtered": 100,
 
            "using_index": true
 
          }
 
        }
 
      }
 
    },
 
    "table": {
 
      "table_name": "t1",
 
      "access_type": "eq_ref",
 
      "possible_keys": ["PRIMARY"],
 
      "key": "PRIMARY",
 
      "key_length": "32",
 
      "used_key_parts": ["id"],
 
      "ref": ["test.t3.ref_id"],
 
      "r_loops": 7,
 
      "rows": 1,
 
      "r_rows": 0,
 
      "r_total_time_ms": 0.0257,
 
      "filtered": 100,
 
      "r_filtered": 100,
 
      "attached_condition": "t1.`id` = t2.`id`",
 
      "using_index": true
 
    }
 
  }
 
}

Comment by Varun Gupta (Inactive) [ 2018-04-24 ] 

"table": {
 
      "table_name": "t1",
 
      "access_type": "eq_ref",
 
      "possible_keys": ["PRIMARY"],
 
      "key": "PRIMARY",
 
      "key_length": "32",
 
      "used_key_parts": ["id"],
 
      "ref": ["test.t3.ref_id"],
 
      "r_loops": 7,
 
      "rows": 1,
 
      "r_rows": 0,
 
      "r_total_time_ms": 0.0257,
 
      "filtered": 100,
 
      "r_filtered": 100,
 
      "attached_condition": "t1.`id` = t2.`id`",
 
      "using_index": true
 
}

After investigation i find out that for the table t1, when we do the join with the materialised table, we have ref: [test.t3.ref_id] . So in the debugger if I see

(lldb) p (char*)dbug_print_item(table_ref->items[0])
 
(char *) $15 = 0x00000001018d72e0 "t3.ref_id"
 
(lldb) p ((Item_field*)table_ref->items[0])->field->table->alias.ptr()
 
(const char *) $17 = 0x0000000102e10330 "t3"

So the field is mapped to table t3 instead of the materialised table <subquery3>. We don't have access to table t3 in the parent select. After materialisation we can only refer to fields of the materialised table in the parent select.

Comment by Varun Gupta (Inactive) [ 2018-04-24 ]

After discussing with psergey, he confirmed that table t1 in the parent select should not be referring to test.t3.ref_id , instead it should be referring to "test.t2.id".

Comment by Varun Gupta (Inactive) [ 2018-04-25 ]

We are referring to an incorrect field (t3.ref_id instead pf t2.id) because we don't form a multiple equality between the left_expr and the right_expr fields of the IN subquery due to difference between there types( one is VARCHAR(32) and the other is CHAR(32)).
If I change the types we get the same result with the materialization switch ON or OFF.

Ideas:

  • Disable materialization for such cases
  • Do what SJ-Materialization-Scan does: when making a lookup into the SJ-Materialization temp. table, unpack its columns into the original fields
  • when making a reference from outside SJM nest to inside, refer to the column of SJ-Materialization table
Comment by Sergei Petrunia [ 2018-05-04 ]

Also need to check: does non-merged semi-join materialization (JTBM) work in such cases? (such cases being the same kind of type mismatch between the parts of the IN-equality)

Comment by Varun Gupta (Inactive) [ 2018-05-04 ]

Running the query so that we get a non-merged semi join materialisation

explain extended
 
SELECT t2.id FROM t2,t1 
WHERE  t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id order by t1.id) and t2.id = t1.id;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	t2	index	PRIMARY	PRIMARY	32	NULL	8	100.00	Using index
 
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	35	test.t2.id	1	100.00	Using where
 
1	PRIMARY	t1	ref	id	id	32	<subquery2>.ref_id	1	100.00	Using where; Using index
 
2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	14	100.00	
2	MATERIALIZED	t1	ref	id	id	32	test.t3.id	1	100.00	Using index

We see that the table t1 actually refers to the field ref_id of the materialised table instead of the inner tables involved in the materialization.

Comment by Varun Gupta (Inactive) [ 2018-05-07 ]

Patch with the copy_back approach

http://lists.askmonty.org/pipermail/commits/2018-May/012489.html

Comment by Sergei Petrunia [ 2018-07-09 ]

Taking the original example and modifying the tables in the subquery to have names t1i, t2i.
The debug trace shows

WHERE:(after remove) 0x7fffb006f5e8 
(
 
  (t2.id = t3i.ref_id) and          // the IN-equality.
 
  multiple equal(t2.id, t1.id) and  // top level SELECT's WHERE
 
  multiple equal(t3i.id, t1i.id)    // subquery's WHERE
 
)

Then, ref optimizer generates:

KEYUSE array (5 elements)
 
 
 
KEYUSE: t2.id=t3i.ref_id  // IN-equality
 
KEYUSE: t2.id=t1.id       // parent select WHERE multi-equality
 
 
 
KEYUSE: t1.id=t3i.ref_id  // (!!)
 
KEYUSE: t1.id=t2.id       // parent select WHERE multi-equality
 
 
 
KEYUSE: t1i.id=t3i.id     // subquery's WHERE multi-equality

Look at (!!). This is an equality between inner field and outer field, but it
is not the IN-equality.
Another notable property is that outer columns participate in the
multi-equality, and inner columns participate in the multi-equality, however
the IN-equality itself does not participate in a multi-equality.

Comment by Sergei Petrunia [ 2018-07-10 ]

Once again

explain 
SELECT t2.id 
FROM 
  t2 JOIN t1 ON t2.id = t1.id
 
WHERE  
  t2.id IN (SELECT t3i.ref_id 
            FROM 
              t3i JOIN t1i ON t3i.id = t1i.id);


+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+--------------------------+
 
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref            | rows | Extra                    |
 
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+--------------------------+
 
|    1 | PRIMARY      | t2          | index  | PRIMARY       | PRIMARY      | 4       | NULL           |   30 | Using index              |
 
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | func           |    1 | Using where              |
 
|    1 | PRIMARY      | t1          | eq_ref | PRIMARY       | PRIMARY      | 4       | j10.t3i.ref_id |    1 | Using where; Using index |
 
|    2 | MATERIALIZED | t3i         | ALL    | NULL          | NULL         | NULL    | NULL           |   14 | Using where              |
 
|    2 | MATERIALIZED | t1i         | eq_ref | PRIMARY       | PRIMARY      | 4       | j10.t3i.id     |    1 | Using index              |
 
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+--------------------------+

So, here access to table t1 is done on t1.id= t3.ref_id. Why is it not done using the t2.id = t1.id (which is explicitly present in the parent select's ON expression?)

Comment by Sergei Petrunia [ 2018-07-10 ]

MDEV-12838 was a similar (but not the same) problem.
It was fixed by filtering inappropriate KEYUSE objects: https://github.com/MariaDB/server/commit/c258ca2463947fcc3d69bb50a8b5cf6906778508

JOIN_TAB::keyuse_is_valid_for_access_in_chosen_plan
 
create_ref_for_key 
get_best_combination

keyuse_is_valid_for_access_in_chosen_plan does essentially this:

If ref access to some table outside of SJ-Materialization nest attempts to use a KEYUSE object that refers to columns inside the materialization nest, the right side of the KEYUSE (the value) must be from the subselect's SELECT list. (This is required because "Unpacking" will unpack the data into the columns that were in the subselect's SELECT list).

Comment by Sergei Petrunia [ 2018-07-10 ]

There is also another check next to it (See are_tables_local) which prevents SJ-inner tables from using KEYUSEs that refer to SJ-outer tables: 

      if (!(~used_tables & keyuse->used_tables) &&
 
	  j->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse))
 
      {
 
        if  (are_tables_local(j, keyuse->val->used_tables()))

Comment by Sergei Petrunia [ 2018-07-10 ]

table_map bits for tables in the query:

t2, map=1
 
t3i, map=4
 
t1i, map=8
 
t1, map=2

This is ok. But the value of JOIN::sjm_lookup_tables seems to be wrong:

(gdb) p join->sjm_lookup_tables
 
  $253 = 8

The above is observed when the execution is in get_best_combination.

Comment by Sergei Petrunia [ 2018-07-10 ]

an alternative patch

diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
 
index ec7b10f..a138a6e 100644
 
--- a/sql/opt_subselect.cc
 
+++ b/sql/opt_subselect.cc
 
@@ -3532,7 +3532,9 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
 
       first= tablenr - sjm->tables + 1;
 
       join->best_positions[first].n_sj_tables= sjm->tables;
 
       join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE;
 
-      join->sjm_lookup_tables|= s->table->map;
 
+      // psergey-fix-it
 
+      for (uint i= first; i < first+ sjm->tables; i++)
 
+        join->sjm_lookup_tables |= join->best_positions[i].table->table->map;
 
     }
 
     else if (pos->sj_strategy == SJ_OPT_MATERIALIZE_SCAN)
 
     {

Comment by Sergei Petrunia [ 2018-07-11 ]

^^ This is the correct patch. The patch that was submitted for review was going in the wrong direction.

Comment by Sergei Petrunia [ 2018-07-11 ]

Fix pushed into 5.5 tree

Generated at Thu Feb 08 08:25:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.