超级会员免费看
本文探讨了MySQL如何尝试将IN子查询转换为EXISTS，以优化查询性能。通过分析具体例子（TPCH-Q16查询），详细展示了原始SQL与转换后的SQL，并提供了TRACE输出以辅助理解这一过程。关键处理集中在`Item_in_subselect::single_value_in_to_exists_transformer`函数上，该函数在查询优化过程中起着重要作用。
摘要:
mysql中对于in的子查询, 会尝试将in转换为exists, 本文分析改策略
mysql版本:
https://github.com/adofsauron/mysql-5.7.41-dev
https://download.youkuaiyun.com/download/adofsauron/87546347?spm=1001.2014.3001.5501
[root@localhost ~]# mysqld --version
mysqld Ver 5.7.41-debug for Linux on x86_64 (Source distribution)
DML:
TPCH-Q16
原SQL:
select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,
part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#34'
and p_type not like 'LARGE BRUSHED%'
and p_size in (48, 19, 12, 4, 41, 7, 21, 39)
and ps_suppkey not in (
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
having supplier_cnt = 3
order by
supplier_cnt desc,
p_brand,
p_type,
p_size ;被转换后的SQL:
/* select#1 */
select
`part`.`p_brand` AS `p_brand`,
`part`.`p_type` AS `p_type`,
`part`.`p_size` AS `p_size`,
count(distinct `partsupp`.`ps_suppkey`) AS `supplier_cnt`
from
`partsupp`
join `part`
where
((`part`.`p_partkey` = `partsupp`.`ps_partkey`)
and (`part`.`p_brand` <> 'Brand#34')
and (not((`part`.`p_type` like 'LARGE BRUSHED%')))
and (`part`.`p_size` in (48, 19, 12, 4, 41, 7, 21, 39))
and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,
<exists>(/* select#2 */
select
`supplier`.`s_suppkey`
from
`supplier`
where
((`supplier`.`s_comment` like '%Customer%Complaints%')
and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`)))))))
group by
`part`.`p_brand`,
`part`.`p_type`,
`part`.`p_size`
having
(`supplier_cnt` = 3)
order by
`supplier_cnt` desc,
`part`.`p_brand`,
`part`.`p_type`,
`part`.`p_size`;TRACE输出
2022-10-13 mysql-打开trace输出_禅定悟世的博客-优快云博客
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"IN_uses_bisection": true
},
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where (`supplier`.`s_comment` like '%Customer%Complaints%')"
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": false
}
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "EXISTS (CORRELATED SELECT)",
"chosen": true,
"evaluating_constant_where_conditions": [
]
}
}
]
}
},
{
"expanded_query": "/* select#1 */ select `part`.`p_brand` AS `p_brand`,`part`.`p_type` AS `p_type`,`part`.`p_size` AS `p_size`,count(distinct `partsupp`.`ps_suppkey`) AS `supplier_cnt` from `partsupp` join `part` where ((`part`.`p_partkey` = `partsupp`.`ps_partkey`) and (`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)) and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`))))))) group by `part`.`p_brand`,`part`.`p_type`,`part`.`p_size` having (`supplier_cnt` = 3) order by `supplier_cnt` desc,`part`.`p_brand`,`part`.`p_type`,`part`.`p_size`"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`part`.`p_partkey` = `partsupp`.`ps_partkey`) and (`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)) and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`)))))))",
"steps": [
{
"transformation": "equality_propagation",
"subselect_evaluation": [
],
"resulting_condition": "((`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)) and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`)))))) and multiple equal(`part`.`p_partkey`, `partsupp`.`ps_partkey`))"
},
{
"transformation": "constant_propagation",
"subselect_evaluation": [
],
"resulting_condition": "((`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)) and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`)))))) and multiple equal(`part`.`p_partkey`, `partsupp`.`ps_partkey`))"
},
{
"transformation": "trivial_condition_removal",
"subselect_evaluation": [
],
"resulting_condition": "((`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)) and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`)))))) and multiple equal(`part`.`p_partkey`, `partsupp`.`ps_partkey`))"
}
]
}
},
{
"condition_processing": {
"condition": "HAVING",
"original_condition": "(`supplier_cnt` = 3)",
"steps": [
{
"transformation": "constant_propagation",
"resulting_condition": "(`supplier_cnt` = 3)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`supplier_cnt` = 3)"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`partsupp`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`part`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`partsupp`",
"field": "ps_partkey",
"equals": "`part`.`p_partkey`",
"null_rejecting": false
},
{
"table": "`part`",
"field": "p_partkey",
"equals": "`partsupp`.`ps_partkey`",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`partsupp`",
"table_scan": {
"rows": 736223,
"cost": 12078
}
},
{
"table": "`part`",
"table_scan": {
"rows": 198348,
"cost": 2020
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`part`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 198348,
"access_type": "scan",
"resulting_rows": 79340,
"cost": 41690,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 79340,
"cost_for_plan": 41690,
"rest_of_plan": [
{
"plan_prefix": [
"`part`"
],
"table": "`partsupp`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 3.2103,
"cost": 130623,
"chosen": true
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 254704,
"cost_for_plan": 172312,
"chosen": true
}
]
},
{
"plan_prefix": [
],
"table": "`partsupp`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 736223,
"access_type": "scan",
"resulting_rows": 736223,
"cost": 159323,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 736223,
"cost_for_plan": 159323,
"rest_of_plan": [
{
"plan_prefix": [
"`partsupp`"
],
"table": "`part`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 883468,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"rows_to_scan": 198348,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 23,
"resulting_rows": 79340,
"cost": 1.2e10,
"chosen": false
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 736223,
"cost_for_plan": 1.04e6,
"pruned_by_cost": true
}
]
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`partsupp`.`ps_partkey` = `part`.`p_partkey`) and (`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)) and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`)))))))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`part`",
"attached": "((`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)))"
},
{
"table": "`partsupp`",
"attached": "(not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`))))))"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`supplier_cnt` desc,`part`.`p_brand`,`part`.`p_type`,`part`.`p_size`",
"items": [
{
"item": "count(distinct `partsupp`.`ps_suppkey`)"
},
{
"item": "`part`.`p_brand`"
},
{
"item": "`part`.`p_type`"
},
{
"item": "`part`.`p_size`"
}
],
"resulting_clause_is_simple": false,
"resulting_clause": "`supplier_cnt` desc,`part`.`p_brand`,`part`.`p_type`,`part`.`p_size`"
}
},
{
"clause_processing": {
"clause": "GROUP BY",
"original_clause": "`part`.`p_brand`,`part`.`p_type`,`part`.`p_size`",
"items": [
{
"item": "`part`.`p_brand`"
},
{
"item": "`part`.`p_type`"
},
{
"item": "`part`.`p_size`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`part`.`p_brand`,`part`.`p_type`,`part`.`p_size`"
}
},
{
"refine_plan": [
{
"table": "`part`"
},
{
"table": "`partsupp`"
}
]
},
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table",
"row_length": 5,
"key_length": 4,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 3355443
}
}
},
{
"sort_using_internal_table": {
"condition_for_sort": "(`supplier_cnt` = 3)",
"having_after_sort": null
}
}
]
}
},
{
"join_optimization": {
"select#": 2,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`supplier`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 17282567
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
关键处理:
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "EXISTS (CORRELATED SELECT)",
"chosen": true,
"evaluating_constant_where_conditions": [
]
}
} "condition_processing": {
"condition": "WHERE",
"original_condition": "((`part`.`p_partkey` = `partsupp`.`ps_partkey`) and (`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)) and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`)))))))",
"steps": [
{
"transformation": "equality_propagation",
"subselect_evaluation": [
],
"resulting_condition": "((`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)) and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`)))))) and multiple equal(`part`.`p_partkey`, `partsupp`.`ps_partkey`))"
},
{
"transformation": "constant_propagation",
"subselect_evaluation": [
],
"resulting_condition": "((`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)) and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`)))))) and multiple equal(`part`.`p_partkey`, `partsupp`.`ps_partkey`))"
},
{
"transformation": "trivial_condition_removal",
"subselect_evaluation": [
],
"resulting_condition": "((`part`.`p_brand` <> 'Brand#34') and (not((`part`.`p_type` like 'LARGE BRUSHED%'))) and (`part`.`p_size` in (48,19,12,4,41,7,21,39)) and (not(<in_optimizer>(`partsupp`.`ps_suppkey`,<exists>(/* select#2 */ select `supplier`.`s_suppkey` from `supplier` where ((`supplier`.`s_comment` like '%Customer%Complaints%') and (<cache>(`partsupp`.`ps_suppkey`) = `supplier`.`s_suppkey`)))))) and multiple equal(`part`.`p_partkey`, `partsupp`.`ps_partkey`))"
}
]
}in转exists分析
说明:
调用堆栈:
#0 Item_in_subselect::single_value_in_to_exists_transformer (this=0x7fa0a09f2dd0, select=0x7fa0a09f5728, func=0x2cbd6f8 <eq_creator>)
at /root/work/trunk/mysql-5.7.41/sql/item_subselect.cc:1878
#1 0x00000000013c847d in Item_in_subselect::single_value_transformer (this=0x7fa0a09f2dd0, select=0x7fa0a09f5728, func=0x2cbd6f8 <eq_creator>)
at /root/work/trunk/mysql-5.7.41/sql/item_subselect.cc:1832
#2 0x00000000013caee2 in Item_in_subselect::select_in_like_transformer (this=0x7fa0a09f2dd0, select=0x7fa0a09f5728, func=0x2cbd6f8 <eq_creator>)
at /root/work/trunk/mysql-5.7.41/sql/item_subselect.cc:2442
#3 0x00000000013cab9e in Item_in_subselect::select_transformer (this=0x7fa0a09f2dd0, select=0x7fa0a09f5728) at /root/work/trunk/mysql-5.7.41/sql/item_subselect.cc:2351
#4 0x000000000156ce9c in st_select_lex::resolve_subquery (this=0x7fa0a09f5728, thd=0x7fa0a0000b70) at /root/work/trunk/mysql-5.7.41/sql/sql_resolver.cc:1019
#5 0x000000000156af7b in st_select_lex::prepare (this=0x7fa0a09f5728, thd=0x7fa0a0000b70) at /root/work/trunk/mysql-5.7.41/sql/sql_resolver.cc:299
#6 0x00000000013cbbb7 in subselect_single_select_engine::prepare (this=0x7fa0a09f6988) at /root/work/trunk/mysql-5.7.41/sql/item_subselect.cc:2748
#7 0x00000000013c3e64 in Item_subselect::fix_fields (this=0x7fa0a09f2dd0, thd=0x7fa0a0000b70, ref=0x7fa0a09f6a60) at /root/work/trunk/mysql-5.7.41/sql/item_subselect.cc:416
#8 0x00000000013cb1d3 in Item_in_subselect::fix_fields (this=0x7fa0a09f2dd0, thd_arg=0x7fa0a0000b70, ref=0x7fa0a09f6a60) at /root/work/trunk/mysql-5.7.41/sql/item_subselect.cc:2485
#9 0x0000000000fa31e6 in Item_func::fix_func_arg (this=0x7fa0a09f69b8, thd=0x7fa0a0000b70, arg=0x7fa0a09f6a60) at /root/work/trunk/mysql-5.7.41/sql/item_func.cc:247
#10 0x0000000000fa3106 in Item_func::fix_fields (this=0x7fa0a09f69b8, thd=0x7fa0a0000b70, ref=0x7fa0a09f2fc8) at /root/work/trunk/mysql-5.7.41/sql/item_func.cc:234
#11 0x0000000000f674ef in Item_cond::fix_fields (this=0x7fa0a0007550, thd=0x7fa0a0000b70, ref=0x7fa0a0005f08) at /root/work/trunk/mysql-5.7.41/sql/item_cmpfunc.cc:5323
#12 0x000000000156d413 in st_select_lex::setup_conds (this=0x7fa0a0005e30, thd=0x7fa0a0000b70) at /root/work/trunk/mysql-5.7.41/sql/sql_resolver.cc:1120
#13 0x000000000156aa80 in st_select_lex::prepare (this=0x7fa0a0005e30, thd=0x7fa0a0000b70) at /root/work/trunk/mysql-5.7.41/sql/sql_resolver.cc:201
#14 0x0000000001576927 in handle_query (thd=0x7fa0a0000b70, lex=0x7fa0a0002e98, result=0x7fa0a09f75c0, added_options=0, removed_options=0)
at /root/work/trunk/mysql-5.7.41/sql/sql_select.cc:135
#15 0x000000000152c353 in execute_sqlcom_select (thd=0x7fa0a0000b70, all_tables=0x7fa0a09f4110) at /root/work/trunk/mysql-5.7.41/sql/sql_parse.cc:4822
#16 0x0000000001525d89 in mysql_execute_command (thd=0x7fa0a0000b70, first_level=true) at /root/work/trunk/mysql-5.7.41/sql/sql_parse.cc:2653
#17 0x000000000152d2f1 in mysql_parse (thd=0x7fa0a0000b70, parser_state=0x7fa0f5914550) at /root/work/trunk/mysql-5.7.41/sql/sql_parse.cc:5218
#18 0x0000000001522ca4 in dispatch_command (thd=0x7fa0a0000b70, com_data=0x7fa0f5914cb0, command=COM_QUERY) at /root/work/trunk/mysql-5.7.41/sql/sql_parse.cc:1396
#19 0x0000000001521bd6 in do_command (thd=0x7fa0a0000b70) at /root/work/trunk/mysql-5.7.41/sql/sql_parse.cc:973
#20 0x000000000165547c in handle_connection (arg=0x4b91070) at /root/work/trunk/mysql-5.7.41/sql/conn_handler/connection_handler_per_thread.cc:313
#21 0x000000000188cc10 in pfs_spawn_thread (arg=0x4b8cdb0) at /root/work/trunk/mysql-5.7.41/storage/perfschema/pfs.cc:2197
#22 0x00007fa102194ea5 in start_thread () from /lib64/libpthread.so.0
#23 0x00007fa100783b0d in clone () from /lib64/libc.so.6
核心函数:
Item_in_subselect::single_value_in_to_exists_transformer
/**
Transofrm an IN predicate into EXISTS via predicate injection.
@details The transformation injects additional predicates into the subquery
(and makes the subquery correlated) as follows.
- If the subquery has aggregates, GROUP BY, or HAVING, convert to
SELECT ie FROM ... HAVING subq_having AND
trigcond(oe $cmp$ ref_or_null_helper<ie>)
the addition is wrapped into trigger only when we want to distinguish
between NULL and FALSE results.
- Otherwise (no aggregates/GROUP BY/HAVING) convert it to one of the
following:
= If we don't need to distinguish between NULL and FALSE subquery:
SELECT 1 FROM ... WHERE (oe $cmp$ ie) AND subq_where
= If we need to distinguish between those:
SELECT 1 FROM ...
WHERE subq_where AND trigcond((oe $cmp$ ie) OR (ie IS NULL))
HAVING trigcond(<is_not_null_test>(ie))
At JOIN::optimize() we will compare costs of materialization and EXISTS; if
the former is cheaper we will switch to it.
@param select Query block of the subquery
@param func Subquery comparison creator
@retval RES_OK Either subquery was transformed, or appopriate
predicates where injected into it.
@retval RES_REDUCE The subquery was reduced to non-subquery
@retval RES_ERROR Error
*/
Item_subselect::trans_res Item_in_subselect::single_value_in_to_exists_transformer(SELECT_LEX *select,
Comp_creator *func)
{
THD *const thd = unit->thd;
DBUG_ENTER("Item_in_subselect::single_value_in_to_exists_transformer");
SELECT_LEX *outer = select->outer_select();
OPT_TRACE_TRANSFORM(&thd->opt_trace, oto0, oto1, select->select_number, "IN (SELECT)", "EXISTS (CORRELATED SELECT)");
oto1.add("chosen", true);
// Transformation will make the subquery a dependent one.
if (!left_expr->const_item())
select->uncacheable |= UNCACHEABLE_DEPENDENT;
in2exists_info->added_to_where = false;
if (select->having_cond() || select->with_sum_func || select->group_list.elements)
{
bool tmp;
Item_bool_func *item = func->create(
m_injected_left_expr,
new Item_ref_null_helper(&select->context, this, &select->ref_ptrs[0], (char *)"<ref>", this->full_name()));
item->set_created_by_in2exists();
if (!abort_on_null && left_expr->maybe_null)
{
/*
We can encounter "NULL IN (SELECT ...)". Wrap the added condition
within a trig_cond.
*/
item = new Item_func_trig_cond(item, get_cond_guard(0), NULL, NO_PLAN_IDX,
Item_func_trig_cond::OUTER_FIELD_IS_NOT_NULL);
item->set_created_by_in2exists();
}
/*
AND and comparison functions can't be changed during fix_fields()
we can assign select_lex->having_cond here, and pass NULL as last
argument (reference) to fix_fields()
*/
select->set_having_cond(and_items(select->having_cond(), item));
if (select->having_cond() == item)
item->item_name.set(in_having_cond);
select->having_cond()->top_level_item();
select->having_fix_field = true;
/*
we do not check having_cond()->fixed, because Item_and (from and_items)
or comparison function (from func->create) can't be fixed after creation
*/
Opt_trace_array having_trace(&thd->opt_trace, "evaluating_constant_having_conditions");
tmp = select->having_cond()->fix_fields(thd, NULL);
select->having_fix_field = false;
if (tmp)
DBUG_RETURN(RES_ERROR);
}
else
{
/*
Grep for "WL#6570" to see the relevant comment about real_item.
*/
Item *orig_item = select->item_list.head()->real_item();
if (select->table_list.elements || select->where_cond())
{
bool tmp;
Item_bool_func *item = func->create(m_injected_left_expr, orig_item);
/*
We may soon add a 'OR inner IS NULL' to 'item', but that may later be
removed if 'inner' is not nullable, so the in2exists mark must be on
'item' too. Not only on the OR node.
*/
item->set_created_by_in2exists();
if (!abort_on_null && orig_item->maybe_null)
{
Item_bool_func *having = new Item_is_not_null_test(this, orig_item);
having->set_created_by_in2exists();
if (left_expr->maybe_null)
{
if (!(having = new Item_func_trig_cond(having, get_cond_guard(0), NULL, NO_PLAN_IDX,
Item_func_trig_cond::OUTER_FIELD_IS_NOT_NULL)))
DBUG_RETURN(RES_ERROR);
having->set_created_by_in2exists();
}
/*
Item_is_not_null_test can't be changed during fix_fields()
we can assign select_lex->having_cond() here, and pass NULL as last
argument (reference) to fix_fields()
*/
having->item_name.set(in_having_cond);
select->set_having_cond(having);
select->having_fix_field = true;
/*
No need to check select_lex->having_cond()->fixed, because Item_and
(from and_items) or comparison function (from func->create)
can't be fixed after creation.
*/
Opt_trace_array having_trace(&thd->opt_trace, "evaluating_constant_having_conditions");
tmp = select->having_cond()->fix_fields(thd, NULL);
select->having_fix_field = false;
if (tmp)
DBUG_RETURN(RES_ERROR);
item = new Item_cond_or(item, new Item_func_isnull(orig_item));
item->set_created_by_in2exists();
}
/*
If we may encounter NULL IN (SELECT ...) and care whether subquery
result is NULL or FALSE, wrap condition in a trig_cond.
*/
if (!abort_on_null && left_expr->maybe_null)
{
if (!(item = new Item_func_trig_cond(item, get_cond_guard(0), NULL, NO_PLAN_IDX,
Item_func_trig_cond::OUTER_FIELD_IS_NOT_NULL)))
DBUG_RETURN(RES_ERROR);
item->set_created_by_in2exists();
}
/*
The following is intentionally not done in row_value_transformer(),
see comment of JOIN::remove_subq_pushed_predicates().
*/
item->item_name.set(in_additional_cond);
/*
AND can't be changed during fix_fields()
we can assign select_lex->having_cond() here, and pass NULL as last
argument (reference) to fix_fields()
Note that if select_lex is the fake one of UNION, it does not make
much sense to give it a WHERE clause below... we already give one to
each member of the UNION.
*/
select->set_where_cond(and_items(select->where_cond(), item));
select->where_cond()->top_level_item();
in2exists_info->added_to_where = true;
/*
No need to check select_lex->where_cond()->fixed, because Item_and
can't be fixed after creation.
*/
Opt_trace_array where_trace(&thd->opt_trace, "evaluating_constant_where_conditions");
if (select->where_cond()->fix_fields(thd, NULL))
DBUG_RETURN(RES_ERROR);
}
else
{
bool tmp;
if (unit->is_union())
{
/*
comparison functions can't be changed during fix_fields()
we can assign select_lex->having_cond() here, and pass NULL as last
argument (reference) to fix_fields()
*/
Item_bool_func *new_having =
func->create(m_injected_left_expr, new Item_ref_null_helper(&select->context, this, &select->ref_ptrs[0],
(char *)"<no matter>", (char *)"<result>"));
new_having->set_created_by_in2exists();
if (!abort_on_null && left_expr->maybe_null)
{
if (!(new_having = new Item_func_trig_cond(new_having, get_cond_guard(0), NULL, NO_PLAN_IDX,
Item_func_trig_cond::OUTER_FIELD_IS_NOT_NULL)))
DBUG_RETURN(RES_ERROR);
new_having->set_created_by_in2exists();
}
new_having->item_name.set(in_having_cond);
select->set_having_cond(new_having);
select->having_fix_field = true;
/*
No need to check select_lex->having_cond()->fixed, because comparison
function (from func->create) can't be fixed after creation.
*/
Opt_trace_array having_trace(&thd->opt_trace, "evaluating_constant_having_conditions");
tmp = select->having_cond()->fix_fields(thd, NULL);
select->having_fix_field = false;
if (tmp)
DBUG_RETURN(RES_ERROR);
}
else
{
/*
Single query block, without tables, without WHERE, HAVING, LIMIT:
its content has one row and is equal to the item in the SELECT list,
so we can replace the IN(subquery) with an equality.
The expression is moved to the immediately outer query block, so it
may no longer contain outer references.
*/
outer->merge_contexts(select);
orig_item->fix_after_pullout(outer, select);
/*
fix_field of substitution item will be done in time of
substituting.
Note that real_item() should be used for all the runtime
created Ref items instead of original left expression
because these items would be deleted at the end
of the statement. Thus one of 'substitution' arguments
can be broken in case of PS.
*/
substitution = func->create(left_expr->substitutional_item(), orig_item);
have_to_be_excluded = 1;
if (thd->lex->describe)
{
char warn_buff[MYSQL_ERRMSG_SIZE];
sprintf(warn_buff, ER(ER_SELECT_REDUCED), select->select_number);
push_warning(thd, Sql_condition::SL_NOTE, ER_SELECT_REDUCED, warn_buff);
}
DBUG_RETURN(RES_REDUCE);
}
}
}
DBUG_RETURN(RES_OK);
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
