Details
Critical 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.
Attachments
Issue Links
- relates to
-
MDEV-15454 Nested SELECT IN returns wrong results
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Alice Sherepa [ alice ] |
| Link |
This issue relates to |
| Assignee | Alice Sherepa [ alice ] | Sergei Petrunia [ psergey ] |
| Assignee | Sergei Petrunia [ psergey ] | Varun Gupta [ varun ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
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; |
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 |
}
|
}
|
}
|
|
"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.
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
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".
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
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.2.10 [ 22615 ] | |
| Affects Version/s | 10.2.12 [ 22810 ] | |
| Affects Version/s | 10.2.13 [ 22910 ] | |
| Affects Version/s | 10.2.14 [ 22911 ] | |
| Affects Version/s | 10.3.6 [ 23003 ] |
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)
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.
Patch with the copy_back approach
http://lists.askmonty.org/pipermail/commits/2018-May/012489.html
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
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.
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?)
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).
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())) |
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.
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)
|
{
|
^^ This is the correct patch. The patch that was submitted for review was going in the wrong direction.
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 5.5.61 [ 22914 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Fix Version/s | 10.0.36 [ 22916 ] | |
| Fix Version/s | 10.1.35 [ 23116 ] | |
| Fix Version/s | 10.2.17 [ 23111 ] | |
| Fix Version/s | 10.3.9 [ 23114 ] |
| Workflow | MariaDB v3 [ 86722 ] | MariaDB v4 [ 154224 ] |
| Zendesk Related Tickets | 143273 |
Reproducible on MariaDB 5.5-10.3, maybe the same problem as
MDEV-15454.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`))