2022-11-04 mysql列存储引擎-自定义函数-派生表-问题排查
2023-09-27 14:25:42 时间
摘要:
mysql列存储引擎-自定义函数-条件下推问题-排查
DDL:
表结构:
DROP TABLE IF EXISTS employees;
CREATE TABLE `employees` (
`employee_id` int(11) NOT NULL ,
`employee_name` varchar(50) NOT NULL,
`employee_sex` varchar(10) DEFAULT 'men',
`hire_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`employee_mgr` int(11) DEFAULT NULL,
`employee_salary` float DEFAULT '3000',
`department_id` int(11) DEFAULT NULL
) ENGINE=TIANMU DEFAULT CHARSET=utf8mb4;
自定义函数:
DROP FUNCTION IF EXISTS get_desc;
DROP FUNCTION IF EXISTS get_int;
CREATE FUNCTION get_desc(id INT) RETURNS VARCHAR(300)
RETURN (SELECT CONCAT('employee name:',employee_name,'---','salary: ',employee_salary) FROM employees WHERE employee_id=id);
CREATE FUNCTION get_int(id INT) RETURNS VARCHAR(300)
RETURN (SELECT employee_salary FROM employees WHERE employee_id=id);
插入数据:
INSERT INTO employees(employee_id,employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (1,'David Tian','man',10,7500,1);
INSERT INTO employees(employee_id,employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (2,'Black Xie','man',10,6600,1);
INSERT INTO employees(employee_id,employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (3,'Moses Wang','man',10,4300,1);
INSERT INTO employees(employee_id,employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (4,'Rena Ruan','woman',10,5300,1);
INSERT INTO employees(employee_id,employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (5,'Sunshine Ma','woman',10,6500,2);
SQL:
select
employee_id
from
employees,
(
select
get_int(1) ) as ta
where
employees.employee_name = 'David Tian';
出现的问题:
一. 条件下推执行出错
出问题的SQL:
select
employee_id
from
employees,
(
select
get_int(1) ) as ta
where
employees.employee_name = 'David Tian';
出问题的堆栈:
(gdb)
#0 Tianmu::handler::ha_tianmu::cond_push (this=0x7f6f88981c30, a_cond=0x7f6f8898c2f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/handler/tianmu_handler.cpp:1438
#1 0x00000000023879dd in make_join_select (join=0x7f6f8898b948, cond=0x7f6f8898c2f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_optimizer.cc:8777
#2 0x0000000002372626 in JOIN::optimize (this=0x7f6f8898b948, part=0 '\000') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_optimizer.cc:485
#3 0x00000000023ea1e5 in st_select_lex::optimize (this=0x7f6f88908fe0, thd=0x7f6f88002c00) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_select.cc:985
#4 0x000000000244aa18 in st_select_lex_unit::optimize (this=0x7f6f889092c8, thd=0x7f6f88002c00) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_union.cc:656
#5 0x000000000223669f in Item_subselect::exec (this=0x7f6f8890a388) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item_subselect.cc:597
#6 0x00000000022383b2 in Item_singlerow_subselect::val_real (this=0x7f6f8890a388) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item_subselect.cc:1172
#7 0x0000000001d549fc in Item::save_in_field_inner (this=0x7f6f8890a388, field=0x7f6f8801a090, no_conversions=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item.cc:6265
#8 0x0000000001d54328 in Item::save_in_field (this=0x7f6f8890a388, field=0x7f6f8801a090, no_conversions=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item.cc:6168
#9 0x00000000022dda30 in sp_eval_expr (thd=0x7f6f88002c00, result_field=0x7f6f8801a090, expr_item_ptr=0x7f6f8890a5e8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sp.cc:2413
#10 0x00000000022f1113 in sp_rcontext::set_return_value (this=0x7f6f88987860, thd=0x7f6f88002c00, return_value_item=0x7f6f8890a5e8)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sp_rcontext.cc:140
#11 0x00000000022e9cfb in sp_instr_freturn::exec_core (this=0x7f6f8890a520, thd=0x7f6f88002c00, nextp=0x7f92be435be8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sp_instr.cc:1347
#12 0x00000000022e782d in sp_lex_instr::reset_lex_and_exec_core (this=0x7f6f8890a520, thd=0x7f6f88002c00, nextp=0x7f92be435be8, open_tables=true)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sp_instr.cc:394
#13 0x00000000022e8528 in sp_lex_instr::validate_lex_and_execute_core (this=0x7f6f8890a520, thd=0x7f6f88002c00, nextp=0x7f92be435be8, open_tables=true)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sp_instr.cc:721
#14 0x00000000022eb3ca in sp_lex_instr::execute (this=0x7f6f8890a520, thd=0x7f6f88002c00, nextp=0x7f92be435be8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sp_instr.h:293
#15 0x00000000022e122e in sp_head::execute (this=0x7f6f889028b0, thd=0x7f6f88002c00, merge_da_on_success=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sp_head.cc:735
#16 0x00000000022e25fa in sp_head::execute_function (this=0x7f6f889028b0, thd=0x7f6f88002c00, argp=0x7f6f88011600, argcount=1, return_value_fld=0x7f6f8801a090)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sp_head.cc:1195
#17 0x0000000001dcfff3 in Item_func_sp::execute_impl (this=0x7f6f88011558, thd=0x7f6f88002c00) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item_func.cc:8129
#18 0x0000000001dcfd74 in Item_func_sp::execute (this=0x7f6f88011558) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item_func.cc:8064
#19 0x0000000001dd3f65 in Item_func_sp::val_str (this=0x7f6f88011558, str=0x7f6f88011568) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item_func.h:2779
#20 0x0000000001d54874 in Item::save_in_field_inner (this=0x7f6f88011558, field=0x7f6f889836d8, no_conversions=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item.cc:6247
#21 0x0000000001db6b64 in Item_func::save_possibly_as_json (this=0x7f6f88011558, field=0x7f6f889836d8, no_conversions=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item_func.cc:597
#22 0x0000000001dcfcfc in Item_func_sp::save_in_field_inner (this=0x7f6f88011558, field=0x7f6f889836d8, no_conversions=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item_func.cc:8025
#23 0x0000000001d54328 in Item::save_in_field (this=0x7f6f88011558, field=0x7f6f889836d8, no_conversions=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/item.cc:6168
#24 0x0000000002320a8d in fill_record (thd=0x7f6f88002c00, table=0x7f6f88985740, ptr=0x7f6f88986498, values=..., bitmap=0x0, insert_into_fields_bitmap=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_base.cc:9084
#25 0x0000000002448f41 in Query_result_union::send_data (this=0x7f6f88019ed8, values=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_union.cc:61
#26 0x000000000234f769 in JOIN::exec (this=0x7f6f88901f20) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_executor.cc:152
#27 0x000000000234ade2 in TABLE_LIST::materialize_derived (this=0x7f6f88019030, thd=0x7f6f88002c00) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_derived.cc:313
#28 0x000000000234a91d in TABLE_LIST::optimize_derived (this=0x7f6f88019030, thd=0x7f6f88002c00) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_derived.cc:206
#29 0x0000000002371412 in JOIN::optimize (this=0x7f6f88901b40, part=1 '\001') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_optimizer.cc:213
#30 0x0000000002c76017 in Tianmu::core::optimize_select (thd=0x7f6f88002c00, select_options=2147748608, result=0x7f6f88019ea0, select_lex=0x7f6f88007ed0, optimize_after_tianmu=@0x7f92be437ccc: 1,
free_join=@0x7f92be437cd0: 1) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:348
#31 0x0000000002c758ca in Tianmu::core::Engine::HandleSelect (this=0x4c83710, thd=0x7f6f88002c00, lex=0x7f6f88004f28, result=@0x7f92be437cd8: 0x7f6f88019ea0, setup_tables_done_option=0,
res=@0x7f92be437cd4: 0, optimize_after_tianmu=@0x7f92be437ccc: 1, tianmu_free_join=@0x7f92be437cd0: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:231
#32 0x0000000002d5edd1 in Tianmu::handler::ha_my_tianmu_query (thd=0x7f6f88002c00, lex=0x7f6f88004f28, result=@0x7f92be437cd8: 0x7f6f88019ea0, setup_tables_done_option=0, res=@0x7f92be437cd4: 0,
optimize_after_tianmu=@0x7f92be437ccc: 1, tianmu_free_join=@0x7f92be437cd0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/handler/ha_rcengine.cpp:83
---Type <return> to continue, or q <return> to quit---
#33 0x000000000239ebf2 in execute_sqlcom_select (thd=0x7f6f88002c00, all_tables=0x7f6f88010010) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:4848
#34 0x0000000002397f76 in mysql_execute_command (thd=0x7f6f88002c00, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:2655
#35 0x000000000239fbbb in mysql_parse (thd=0x7f6f88002c00, parser_state=0x7f92be438e70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5252
#36 0x0000000002394e53 in dispatch_command (thd=0x7f6f88002c00, com_data=0x7f92be439610, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1399
#37 0x0000000002393d7f in do_command (thd=0x7f6f88002c00) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:976
#38 0x00000000024c697f in handle_connection (arg=0x171983b0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/conn_handler/connection_handler_per_thread.cc:313
#39 0x0000000002bac02a in pfs_spawn_thread (arg=0x171214a0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/perfschema/pfs.cc:2197
#40 0x00007f930ea4cea5 in start_thread () from /lib64/libpthread.so.0
#41 0x00007f930bcc9b0d in clone () from /lib64/libc.so.6
问题解决:
自定义函数不参与条件下推
const Item *ha_tianmu::cond_push(const Item *a_cond) {
Item const *ret = a_cond;
Item *cond = const_cast<Item *>(a_cond);
if (cond && (cond->type() == Item::FUNC_ITEM)) {
Item_func *item_func = down_cast<Item_func *>(cond);
if (uint arg_count = item_func->argument_count()) {
Item **args = item_func->arguments();
for (uint i = 0; i < arg_count; ++i) {
if ((args[i]->is_splocal()) || (args[i]->type() == Item::CACHE_ITEM)) {
return a_cond;
}
}
}
}
if (cond && (cond->type() == Item::FUNC_ITEM)) {
Item_func *item_func = down_cast<Item_func *>(cond);
if (uint arg_count = item_func->argument_count()) {
Item **args = item_func->arguments();
for (uint i = 0; i < arg_count; ++i) {
if ((args[i]->is_splocal()) || (args[i]->type() == Item::CACHE_ITEM)) {
return a_cond;
}
}
}
}
二. 派生表执行列存储引擎complie出错
出错的堆栈:
(gdb) bt
#0 0x00000000031a5d80 in __cxa_throw ()
#1 0x0000000002cc986b in Tianmu::core::Query::Compile (this=0x7f3e9a902790, compiled_query=0x7f3e9a9026c0, selects_list=0x7f1b24010d98, last_distinct=0x0, res_tab=0x7f3e9a902224,
ignore_limit=false, left_expr_for_subselect=0x0, oper_for_subselect=0x0, ignore_minmax=false, for_subq_in_where=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/query_compile.cpp:1066
#2 0x0000000002cc57e5 in Tianmu::core::Query::AddJoins (this=0x7f3e9a902790, join=..., tmp_table=..., left_tables=std::vector of length 1, capacity 1 = {...},
right_tables=std::vector of length 0, capacity 0, in_subquery=false, first_table=@0x7f3e9a90247f: false, for_subq_in_where=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/query_compile.cpp:351
#3 0x0000000002cc983a in Tianmu::core::Query::Compile (this=0x7f3e9a902790, compiled_query=0x7f3e9a9026c0, selects_list=0x7f1b24007570, last_distinct=0x0, res_tab=0x0, ignore_limit=false,
left_expr_for_subselect=0x0, oper_for_subselect=0x0, ignore_minmax=false, for_subq_in_where=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/query_compile.cpp:1064
#4 0x0000000002c7640a in Tianmu::core::Engine::Execute (this=0x5423710, thd=0x7f1b24002c00, lex=0x7f1b24004f28, result_output=0x7f1b2401e228, unit_for_union=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:401
#5 0x0000000002c75904 in Tianmu::core::Engine::HandleSelect (this=0x5423710, thd=0x7f1b24002c00, lex=0x7f1b24004f28, result=@0x7f3e9a902cd8: 0x7f1b2401e228, setup_tables_done_option=0,
res=@0x7f3e9a902cd4: 0, optimize_after_tianmu=@0x7f3e9a902ccc: 1, tianmu_free_join=@0x7f3e9a902cd0: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:238
#6 0x0000000002d5edc9 in Tianmu::handler::ha_my_tianmu_query (thd=0x7f1b24002c00, lex=0x7f1b24004f28, result=@0x7f3e9a902cd8: 0x7f1b2401e228, setup_tables_done_option=0, res=@0x7f3e9a902cd4: 0,
optimize_after_tianmu=@0x7f3e9a902ccc: 1, tianmu_free_join=@0x7f3e9a902cd0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/handler/ha_rcengine.cpp:83
#7 0x000000000239ebf2 in execute_sqlcom_select (thd=0x7f1b24002c00, all_tables=0x7f1b24008b78) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:4848
#8 0x0000000002397f76 in mysql_execute_command (thd=0x7f1b24002c00, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:2655
#9 0x000000000239fbbb in mysql_parse (thd=0x7f1b24002c00, parser_state=0x7f3e9a903e70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5252
#10 0x0000000002394e53 in dispatch_command (thd=0x7f1b24002c00, com_data=0x7f3e9a904610, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1399
#11 0x0000000002393d7f in do_command (thd=0x7f1b24002c00) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:976
#12 0x00000000024c697f in handle_connection (arg=0x1790abf0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/conn_handler/connection_handler_per_thread.cc:313
#13 0x0000000002bac02a in pfs_spawn_thread (arg=0x1790e050) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/perfschema/pfs.cc:2197
#14 0x00007f3ea55b3ea5 in start_thread () from /lib64/libpthread.so.0
#15 0x00007f3ea2830b0d in clone () from /lib64/libc.so.6
函数上下文:
TabID tab(0);
if (join_ptr->is_view_or_derived()) {
if (!Compile(cq, join_ptr->derived_unit()->first_select(), join_ptr->derived_unit()->union_distinct, &tab))
return RETURN_QUERY_TO_MYSQL_ROUTE;
table_alias = join_ptr->alias;
} else {
相关文章
- mysql数据库中的存储引擎是什么意思呢
- 深入浅出MySQL++数据库开发、优化与管理维护+第2版+唐汉明 -- 存储引擎 - 数据类型 - 字符集和校验规则 -
- [小白技巧]如何在Linux上检查MySQL数据表的存储引擎类型
- mysql 数据表的引擎 MyISAM 和 InnoDB
- MySQL InnoDB 引擎的持久性与性能
- 基于Java（ Spring+SpringMVC+JDBC）+MySQL实现（Web）家电售后服务系统【100010064】
- Mysql 存储引擎中InnoDB与Myisam的主要区别
- MySQL各存储引擎
- Mysql 存储引擎中InnoDB与Myisam的主要区别
- MySQL · 引擎特性 · InnoDB崩溃恢复
- 【3.3】mysql链接服务器，Federated存储引擎实现远程表，相当于sql server的连接服务器
- （2.6）Mysql之SQL基础——存储引擎的查看与修改
- Mysql数据库存储引擎--转