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
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.
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:
j->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse)){