Description:
When a subquery with IN predicate is used in the ON clause of a LEFT JOIN,
and the optimizer chooses to materialize the subquery, it silently drops
some WHERE conditions from the subquery, resulting in INCORRECT query results.
How to repeat:
Conditions: IN subquery in the ON clause of a LEFT JOIN
Trigger: Optimizer chooses materialization when the optimal index is missing
Symptom: Loss of conditions during materialization
Impact: Incorrect results (not just slow performance)
Workaround: Appropriate index or materialization=off
Query:
select c.sys_id_apps_users, c.countAccessi, max(b.sys_id) as exist
from
( select
a.sys_id_apps_users as sys_id_apps_users,
count(*) as countAccessi
from (
select
sys_id_apps_users,
date(dtaccess),
count(*)
from
apps_user_accesses2
where
sys_id_apps=53
and date(dtaccess) >= '2023-02-27'
and date(dtaccess) <= '2025-10-20'
AND sys_id_apps_users = 19458842
group by
sys_id_apps_users,
date(dtaccess)
) a
group by
a.sys_id_apps_users
) c left join apps_prize_users b on c.sys_id_apps_users=b.sys_id_apps_users
AND b.sys_id_apps_users = 19458842
and b.prize_code in
(select prize_code from apps_prizes where `special_prize` = '1' and sys_id_apps = 53
and prize_code not like 'LD%'
)
and date(b.dtassigned) >= '2025-01-01'
group by c.sys_id_apps_users, c.countAccessi
;
Explain Plan with SET optimizer_switch='materialization=on';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "676673.64"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "c",
"access_type": "ALL",
"rows_examined_per_scan": 269300,
"rows_produced_per_join": 269300,
"filtered": "100.00",
"cost_info": {
"read_cost": "3368.75",
"eval_cost": "26930.00",
"prefix_cost": "30298.75",
"data_read_per_join": "6M"
},
"used_columns": [
"sys_id_apps_users",
"countAccessi"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "30298.75"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"table": {
"table_name": "a",
"access_type": "ALL",
"rows_examined_per_scan": 269300,
"rows_produced_per_join": 269300,
"filtered": "100.00",
"cost_info": {
"read_cost": "3368.75",
"eval_cost": "26930.00",
"prefix_cost": "30298.75",
"data_read_per_join": "6M"
},
"used_columns": [
"sys_id_apps_users",
"date(dtaccess)",
"count(*)"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "2878962.58"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"table": {
"table_name": "apps_user_accesses2",
"access_type": "ALL",
"rows_examined_per_scan": 26930009,
"rows_produced_per_join": 269300,
"filtered": "1.00",
"cost_info": {
"read_cost": "2852032.57",
"eval_cost": "26930.01",
"prefix_cost": "2878962.58",
"data_read_per_join": "410M"
},
"used_columns": [
"sys_id",
"sys_id_apps",
"sys_id_apps_users",
"dtaccess"
],
"attached_condition": "((`care`.`apps_user_accesses2`.`sys_id_apps_users` = 19458842) and (`care`.`apps_user_accesses2`.`sys_id_apps` = 53) and (cast(`care`.`apps_user_accesses2`.`dtaccess` as date) >= '2023-02-27') and (cast(`care`.`apps_user_accesses2`.`dtaccess` as date) <= '2025-10-20'))"
}
}
}
}
}
}
}
}
}
},
{
"table": {
"table_name": "b",
"access_type": "ref",
"possible_keys": [
"index_1",
"index_3",
"Index_9",
"Index_8"
],
"key": "index_3",
"used_key_parts": [
"sys_id_apps_users"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 2,
"rows_produced_per_join": 538600,
"filtered": "100.00",
"index_condition": "<if>(is_not_null_compl(b..<subquery4>), ((`c`.`sys_id_apps_users` = 19458842) and (cast(`care`.`b`.`dtassigned` as date) >= '2025-01-01')), true)",
"cost_info": {
"read_cost": "538590.55",
"eval_cost": "53860.00",
"prefix_cost": "622749.30",
"data_read_per_join": "164M"
},
"used_columns": [
"sys_id",
"prize_code",
"sys_id_apps_users",
"dtassigned"
]
}
},
{
"table": {
"table_name": "<subquery4>",
"access_type": "eq_ref",
"key": "<auto_distinct_key>",
"key_length": "103",
"ref": [
"care.b.prize_code"
],
"rows_examined_per_scan": 1,
"materialized_from_subquery": {
"using_temporary_table": true,
"query_block": {
"table": {
"table_name": "apps_prizes",
"access_type": "ref",
"possible_keys": [
"index_2",
"Index_3"
],
"key": "Index_3",
"used_key_parts": [
"sys_id_apps"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 310,
"rows_produced_per_join": 310,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1.34",
"eval_cost": "31.00",
"prefix_cost": "32.34",
"data_read_per_join": "498K"
},
"used_columns": [
"sys_id",
"sys_id_apps",
"special_prize",
"prize_code"
]
}
}
}
}
}
]
}
}
}
Resultset WRONG:
19458842 1 225321903
Explain Plan with SET optimizer_switch='materialization=off';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "811241.09"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "c",
"access_type": "ALL",
"rows_examined_per_scan": 269295,
"rows_produced_per_join": 269295,
"filtered": "100.00",
"cost_info": {
"read_cost": "3368.69",
"eval_cost": "26929.50",
"prefix_cost": "30298.19",
"data_read_per_join": "6M"
},
"used_columns": [
"sys_id_apps_users",
"countAccessi"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "30298.19"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"table": {
"table_name": "a",
"access_type": "ALL",
"rows_examined_per_scan": 269295,
"rows_produced_per_join": 269295,
"filtered": "100.00",
"cost_info": {
"read_cost": "3368.69",
"eval_cost": "26929.50",
"prefix_cost": "30298.19",
"data_read_per_join": "6M"
},
"used_columns": [
"sys_id_apps_users",
"date(dtaccess)",
"count(*)"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "2899113.57"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"table": {
"table_name": "apps_user_accesses2",
"access_type": "ALL",
"rows_examined_per_scan": 26929524,
"rows_produced_per_join": 269295,
"filtered": "1.00",
"cost_info": {
"read_cost": "2872184.05",
"eval_cost": "26929.52",
"prefix_cost": "2899113.57",
"data_read_per_join": "410M"
},
"used_columns": [
"sys_id",
"sys_id_apps",
"sys_id_apps_users",
"dtaccess"
],
"attached_condition": "((`care`.`apps_user_accesses2`.`sys_id_apps_users` = 19458842) and (`care`.`apps_user_accesses2`.`sys_id_apps` = 53) and (cast(`care`.`apps_user_accesses2`.`dtaccess` as date) >= '2023-02-27') and (cast(`care`.`apps_user_accesses2`.`dtaccess` as date) <= '2025-10-20'))"
}
}
}
}
}
}
}
}
}
},
{
"table": {
"table_name": "b",
"access_type": "ref",
"possible_keys": [
"index_1",
"index_3",
"Index_9",
"Index_8"
],
"key": "index_3",
"used_key_parts": [
"sys_id_apps_users"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 2,
"rows_produced_per_join": 538590,
"filtered": "100.00",
"index_condition": "<if>(is_not_null_compl(b..apps_prizes), ((`c`.`sys_id_apps_users` = 19458842) and (cast(`care`.`b`.`dtassigned` as date) >= '2025-01-01')), true)",
"cost_info": {
"read_cost": "538577.40",
"eval_cost": "53859.00",
"prefix_cost": "622734.59",
"data_read_per_join": "164M"
},
"used_columns": [
"sys_id",
"prize_code",
"sys_id_apps_users",
"dtassigned"
]
}
},
{
"table": {
"table_name": "apps_prizes",
"access_type": "ref",
"possible_keys": [
"index_2",
"Index_3"
],
"key": "Index_3",
"used_key_parts": [
"sys_id_apps",
"prize_code",
"special_prize"
],
"key_length": "113",
"ref": [
"const",
"care.b.prize_code",
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 538590,
"filtered": "100.00",
"using_index": true,
"first_match": "b",
"cost_info": {
"read_cost": "134647.50",
"eval_cost": "53859.00",
"prefix_cost": "811241.09",
"data_read_per_join": "846M"
},
"used_columns": [
"sys_id",
"sys_id_apps",
"special_prize",
"prize_code"
],
"attached_condition": "<if>(is_not_null_compl(b..apps_prizes), (not((`care`.`apps_prizes`.`prize_code` like 'LD%'))), true)"
}
}
]
}
}
}
Resultset correct:
19458842 1 NULL
Description: When a subquery with IN predicate is used in the ON clause of a LEFT JOIN, and the optimizer chooses to materialize the subquery, it silently drops some WHERE conditions from the subquery, resulting in INCORRECT query results. How to repeat: Conditions: IN subquery in the ON clause of a LEFT JOIN Trigger: Optimizer chooses materialization when the optimal index is missing Symptom: Loss of conditions during materialization Impact: Incorrect results (not just slow performance) Workaround: Appropriate index or materialization=off Query: select c.sys_id_apps_users, c.countAccessi, max(b.sys_id) as exist from ( select a.sys_id_apps_users as sys_id_apps_users, count(*) as countAccessi from ( select sys_id_apps_users, date(dtaccess), count(*) from apps_user_accesses2 where sys_id_apps=53 and date(dtaccess) >= '2023-02-27' and date(dtaccess) <= '2025-10-20' AND sys_id_apps_users = 19458842 group by sys_id_apps_users, date(dtaccess) ) a group by a.sys_id_apps_users ) c left join apps_prize_users b on c.sys_id_apps_users=b.sys_id_apps_users AND b.sys_id_apps_users = 19458842 and b.prize_code in (select prize_code from apps_prizes where `special_prize` = '1' and sys_id_apps = 53 and prize_code not like 'LD%' ) and date(b.dtassigned) >= '2025-01-01' group by c.sys_id_apps_users, c.countAccessi ; Explain Plan with SET optimizer_switch='materialization=on'; { "query_block": { "select_id": 1, "cost_info": { "query_cost": "676673.64" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": false, "nested_loop": [ { "table": { "table_name": "c", "access_type": "ALL", "rows_examined_per_scan": 269300, "rows_produced_per_join": 269300, "filtered": "100.00", "cost_info": { "read_cost": "3368.75", "eval_cost": "26930.00", "prefix_cost": "30298.75", "data_read_per_join": "6M" }, "used_columns": [ "sys_id_apps_users", "countAccessi" ], "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 2, "cost_info": { "query_cost": "30298.75" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": false, "table": { "table_name": "a", "access_type": "ALL", "rows_examined_per_scan": 269300, "rows_produced_per_join": 269300, "filtered": "100.00", "cost_info": { "read_cost": "3368.75", "eval_cost": "26930.00", "prefix_cost": "30298.75", "data_read_per_join": "6M" }, "used_columns": [ "sys_id_apps_users", "date(dtaccess)", "count(*)" ], "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 3, "cost_info": { "query_cost": "2878962.58" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": false, "table": { "table_name": "apps_user_accesses2", "access_type": "ALL", "rows_examined_per_scan": 26930009, "rows_produced_per_join": 269300, "filtered": "1.00", "cost_info": { "read_cost": "2852032.57", "eval_cost": "26930.01", "prefix_cost": "2878962.58", "data_read_per_join": "410M" }, "used_columns": [ "sys_id", "sys_id_apps", "sys_id_apps_users", "dtaccess" ], "attached_condition": "((`care`.`apps_user_accesses2`.`sys_id_apps_users` = 19458842) and (`care`.`apps_user_accesses2`.`sys_id_apps` = 53) and (cast(`care`.`apps_user_accesses2`.`dtaccess` as date) >= '2023-02-27') and (cast(`care`.`apps_user_accesses2`.`dtaccess` as date) <= '2025-10-20'))" } } } } } } } } } }, { "table": { "table_name": "b", "access_type": "ref", "possible_keys": [ "index_1", "index_3", "Index_9", "Index_8" ], "key": "index_3", "used_key_parts": [ "sys_id_apps_users" ], "key_length": "5", "ref": [ "const" ], "rows_examined_per_scan": 2, "rows_produced_per_join": 538600, "filtered": "100.00", "index_condition": "<if>(is_not_null_compl(b..<subquery4>), ((`c`.`sys_id_apps_users` = 19458842) and (cast(`care`.`b`.`dtassigned` as date) >= '2025-01-01')), true)", "cost_info": { "read_cost": "538590.55", "eval_cost": "53860.00", "prefix_cost": "622749.30", "data_read_per_join": "164M" }, "used_columns": [ "sys_id", "prize_code", "sys_id_apps_users", "dtassigned" ] } }, { "table": { "table_name": "<subquery4>", "access_type": "eq_ref", "key": "<auto_distinct_key>", "key_length": "103", "ref": [ "care.b.prize_code" ], "rows_examined_per_scan": 1, "materialized_from_subquery": { "using_temporary_table": true, "query_block": { "table": { "table_name": "apps_prizes", "access_type": "ref", "possible_keys": [ "index_2", "Index_3" ], "key": "Index_3", "used_key_parts": [ "sys_id_apps" ], "key_length": "5", "ref": [ "const" ], "rows_examined_per_scan": 310, "rows_produced_per_join": 310, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "1.34", "eval_cost": "31.00", "prefix_cost": "32.34", "data_read_per_join": "498K" }, "used_columns": [ "sys_id", "sys_id_apps", "special_prize", "prize_code" ] } } } } } ] } } } Resultset WRONG: 19458842 1 225321903 Explain Plan with SET optimizer_switch='materialization=off'; { "query_block": { "select_id": 1, "cost_info": { "query_cost": "811241.09" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": false, "nested_loop": [ { "table": { "table_name": "c", "access_type": "ALL", "rows_examined_per_scan": 269295, "rows_produced_per_join": 269295, "filtered": "100.00", "cost_info": { "read_cost": "3368.69", "eval_cost": "26929.50", "prefix_cost": "30298.19", "data_read_per_join": "6M" }, "used_columns": [ "sys_id_apps_users", "countAccessi" ], "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 2, "cost_info": { "query_cost": "30298.19" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": false, "table": { "table_name": "a", "access_type": "ALL", "rows_examined_per_scan": 269295, "rows_produced_per_join": 269295, "filtered": "100.00", "cost_info": { "read_cost": "3368.69", "eval_cost": "26929.50", "prefix_cost": "30298.19", "data_read_per_join": "6M" }, "used_columns": [ "sys_id_apps_users", "date(dtaccess)", "count(*)" ], "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 3, "cost_info": { "query_cost": "2899113.57" }, "grouping_operation": { "using_temporary_table": true, "using_filesort": false, "table": { "table_name": "apps_user_accesses2", "access_type": "ALL", "rows_examined_per_scan": 26929524, "rows_produced_per_join": 269295, "filtered": "1.00", "cost_info": { "read_cost": "2872184.05", "eval_cost": "26929.52", "prefix_cost": "2899113.57", "data_read_per_join": "410M" }, "used_columns": [ "sys_id", "sys_id_apps", "sys_id_apps_users", "dtaccess" ], "attached_condition": "((`care`.`apps_user_accesses2`.`sys_id_apps_users` = 19458842) and (`care`.`apps_user_accesses2`.`sys_id_apps` = 53) and (cast(`care`.`apps_user_accesses2`.`dtaccess` as date) >= '2023-02-27') and (cast(`care`.`apps_user_accesses2`.`dtaccess` as date) <= '2025-10-20'))" } } } } } } } } } }, { "table": { "table_name": "b", "access_type": "ref", "possible_keys": [ "index_1", "index_3", "Index_9", "Index_8" ], "key": "index_3", "used_key_parts": [ "sys_id_apps_users" ], "key_length": "5", "ref": [ "const" ], "rows_examined_per_scan": 2, "rows_produced_per_join": 538590, "filtered": "100.00", "index_condition": "<if>(is_not_null_compl(b..apps_prizes), ((`c`.`sys_id_apps_users` = 19458842) and (cast(`care`.`b`.`dtassigned` as date) >= '2025-01-01')), true)", "cost_info": { "read_cost": "538577.40", "eval_cost": "53859.00", "prefix_cost": "622734.59", "data_read_per_join": "164M" }, "used_columns": [ "sys_id", "prize_code", "sys_id_apps_users", "dtassigned" ] } }, { "table": { "table_name": "apps_prizes", "access_type": "ref", "possible_keys": [ "index_2", "Index_3" ], "key": "Index_3", "used_key_parts": [ "sys_id_apps", "prize_code", "special_prize" ], "key_length": "113", "ref": [ "const", "care.b.prize_code", "const" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 538590, "filtered": "100.00", "using_index": true, "first_match": "b", "cost_info": { "read_cost": "134647.50", "eval_cost": "53859.00", "prefix_cost": "811241.09", "data_read_per_join": "846M" }, "used_columns": [ "sys_id", "sys_id_apps", "special_prize", "prize_code" ], "attached_condition": "<if>(is_not_null_compl(b..apps_prizes), (not((`care`.`apps_prizes`.`prize_code` like 'LD%'))), true)" } } ] } } } Resultset correct: 19458842 1 NULL