2022-09-15 mysql列存储引擎-POC-Q2-语句范围缩小后问题定位

摘要:

缩小POC-Q2的语句的范围, 以在更精确的场景中定位问题

配置参数修改:

[mysqld]

tianmu_ini_allowmysqlquerypath=1
log_bin_trust_function_creators=1

DDL

function:

DELIMITER $$
CREATE FUNCTION get_value(id INT) RETURNS INT
BEGIN
DECLARE FINALVAR INT;
SELECT employee_salary INTO FINALVAR FROM employees WHERE employee_id=id;
RETURN FINALVAR;
END $$
DELIMITER ;

表结构:

CREATE TABLE `employees` (
`employee_id` int(11) NOT NULL AUTO_INCREMENT,
`employee_name` varchar(50) NOT NULL,
`employee_sex` varchar(10) DEFAULT '男',
`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,
PRIMARY KEY (`employee_id`),
KEY `idx_department_id` (`department_id`)
) ENGINE=TIANMU DEFAULT CHARSET=utf8mb4;
insert into employees(employee_name) values('David Tian');

查询语句:

select
a.employee_id,
get_value(b.employee_id)
from
employees a
inner join employees b on
a.employee_id = b.employee_id
and b.employee_name = 'David Tian';

执行查询测试:

left join:

mysql>     select
-> a.employee_id,
-> get_value(b.employee_id)
-> from
-> employees a
-> left join employees b on
-> a.employee_id = b.employee_id
-> and b.employee_name = 'David Tian';
+-------------+--------------------------+
| employee_id | get_value(b.employee_id) |
+-------------+--------------------------+
| 1 | NULL |
+-------------+--------------------------+
1 row in set, 1 warning (0.00 sec)

语法树转换:

T:-1 = TABLE_ALIAS(T:0,"employees")
T:-2 = TMP_TABLE(T:4294967295)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:5))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,"employee_salary","ALL")
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
VC:-2.2 = CREATE_VC(T:-2,EXPR("NULL"))
C:0 = CREATE_CONDS(T:-2,VC:-2.1,=,VC:-2.2,<null>)
T:-2.ADD_CONDS(C:0,WHERE)
T:-2.APPLY_CONDS()
RESULT(T:-2)

调用堆栈:

#0  Tianmu::core::Query::Preexecute (this=0x7f655f4e1620, qu=..., sender=0x7f6390c67420, display_now=true)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/query.cpp:561
#1 0x00000000029d4f6e in Tianmu::core::Engine::Execute (this=0x5a1cfd0, thd=0x7f6390013860, lex=0x7f6390bbd420, result_output=0x7f6390bbecd8, unit_for_union=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/engine_execute.cpp:421
#2 0x00000000029d415e in Tianmu::core::Engine::HandleSelect (this=0x5a1cfd0, thd=0x7f6390013860, lex=0x7f6390bbd420, result=@0x7f655f4e1b38: 0x7f6390bbecd8, setup_tables_done_option=0,
res=@0x7f655f4e1b34: 0, optimize_after_tianmu=@0x7f655f4e1b2c: 1, tianmu_free_join=@0x7f655f4e1b30: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/engine_execute.cpp:232
#3 0x0000000002ab8e2e in Tianmu::dbhandler::TIANMU_HandleSelect (thd=0x7f6390013860, lex=0x7f6390bbd420, result=@0x7f655f4e1b38: 0x7f6390bbecd8, setup_tables_done_option=0,
res=@0x7f655f4e1b34: 0, optimize_after_tianmu=@0x7f655f4e1b2c: 1, tianmu_free_join=@0x7f655f4e1b30: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/handler/ha_rcengine.cpp:82
#4 0x00000000022feff7 in execute_sqlcom_select (thd=0x7f6390013860, all_tables=0x7f6390bbef10) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_parse.cc:5182
#5 0x00000000022f8e1f in mysql_execute_command (thd=0x7f6390013860, first_level=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_parse.cc:2831
#6 0x00000000022643b8 in sp_instr_stmt::exec_core (this=0x7f6390bbf828, thd=0x7f6390013860, nextp=0x7f655f4e3228)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_instr.cc:1027
#7 0x000000000226325b in sp_lex_instr::reset_lex_and_exec_core (this=0x7f6390bbf828, thd=0x7f6390013860, nextp=0x7f655f4e3228, open_tables=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_instr.cc:452
#8 0x0000000002263c42 in sp_lex_instr::validate_lex_and_execute_core (this=0x7f6390bbf828, thd=0x7f6390013860, nextp=0x7f655f4e3228, open_tables=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_instr.cc:753
#9 0x0000000002264110 in sp_instr_stmt::execute (this=0x7f6390bbf828, thd=0x7f6390013860, nextp=0x7f655f4e3228)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_instr.cc:938
#10 0x000000000225cdd5 in sp_head::execute (this=0x7f6390006160, thd=0x7f6390013860, merge_da_on_success=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_head.cc:796
#11 0x000000000225dd97 in sp_head::execute_function (this=0x7f6390006160, thd=0x7f6390013860, argp=0x7f63900c6080, argcount=1, return_value_fld=0x7f63900ca7c0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_head.cc:1287
#12 0x0000000001d75ed9 in Item_func_sp::execute_impl (this=0x7f63900c5fd8, thd=0x7f6390013860) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/item_func.cc:8706
#13 0x0000000001d75c2c in Item_func_sp::execute (this=0x7f63900c5fd8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/item_func.cc:8637
#14 0x0000000001d5c926 in Item_func_sp::val_int (this=0x7f63900c5fd8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/item_func.h:2741
#15 0x0000000001d074ba in Item::send (this=0x7f63900c5fd8, protocol=0x7f63900148c0, buffer=0x7f655f4e4620) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/item.cc:7570
#16 0x00000000022ab95f in THD::send_result_set_row (this=0x7f6390013860, row_items=0x7f63900c4978) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_class.cc:4757
#17 0x00000000022a69ca in Query_result_send::send_data (this=0x7f63900b3c78, items=...) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_class.cc:2742
#18 0x00000000022bf060 in end_send (join=0x7f6390c5d040, qep_tab=0x7f6390c651d8, end_of_records=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_executor.cc:2936
#19 0x00000000022bc6d8 in eval(join=0x7f6390c5d040, qep_tab=0x7f6390c65060) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_executor.cc:1652
#20 0x00000000022bcbd4 in eval(join=0x7f6390c5d040, qep_tab=0x7f6390c65060)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_executor.cc:1792
#21 0x00000000022bbea6 in sub_select (join=0x7f6390c5d040, qep_tab=0x7f6390c65060, end_of_records=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_executor.cc:1311
#22 0x00000000022bc6d8 in eval(join=0x7f6390c5d040, qep_tab=0x7f6390c64ee8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_executor.cc:1652
#23 0x00000000022bbe54 in sub_select (join=0x7f6390c5d040, qep_tab=0x7f6390c64ee8, end_of_records=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_executor.cc:1304
#24 0x00000000022bb8ef in do_select (join=0x7f6390c5d040) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_executor.cc:957
#25 0x00000000022b9baf in JOIN::exec (this=0x7f6390c5d040) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_executor.cc:206
#26 0x000000000233e137 in handle_query (thd=0x7f6390013860, lex=0x7f63900159d8, result=0x7f63900b3c78, added_options=0, removed_options=0, optimize_after_bh=2, free_join_from_bh=1)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_select.cc:195
#27 0x00000000022ff031 in execute_sqlcom_select (thd=0x7f6390013860, all_tables=0x7f63900b2a28) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_parse.cc:5184
#28 0x00000000022f8e1f in mysql_execute_command (thd=0x7f6390013860, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_parse.cc:2831
#29 0x00000000022ffcfe in mysql_parse (thd=0x7f6390013860, parser_state=0x7f655f4e5f00) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_parse.cc:5621
#30 0x00000000022f6148 in dispatch_command (thd=0x7f6390013860, com_data=0x7f655f4e6660, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_parse.cc:1495
---Type <return> to continue, or q <return> to quit---
#31 0x00000000022f52eb in do_command (thd=0x7f6390013860) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_parse.cc:1034
#32 0x00000000023f922b in handle_connection (arg=0x7d410a0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/conn_handler/connection_handler_per_thread.cc:313
#33 0x0000000002913af1 in pfs_spawn_thread (arg=0x7d20e70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/perfschema/pfs.cc:2197
#34 0x00007f65aa4b9ea5 in start_thread () from /lib64/libpthread.so.0
#35 0x00007f65a79ddb0d in clone () from /lib64/libc.so.6

inner join:

mysql>     select
-> a.employee_id,
-> get_value(b.employee_id)
-> from
-> employees a
-> inner join employees b on
-> a.employee_id = b.employee_id
-> and b.employee_name = 'David Tian';
Empty set, 1 warning (0.01 sec)

语法树转换:

T:-1 = TABLE_ALIAS(T:0)
T:-2 = TMP_TABLE(T:4294967295)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,"employee_id","ALL")
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:1))
A:-2 = T:-2.ADD_COLUMN(VC:-2.1,LIST,"employee_name","ALL")
VC:-2.2 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:1))
VC:-2.3 = CREATE_VC(T:-2,EXPR("David Tian"))
C:0 = CREATE_CONDS(T:-2,VC:-2.2,=,VC:-2.3,<null>)
T:-2.ADD_CONDS(C:0,WHERE)
T:-2.APPLY_CONDS()
RESULT(T:-2)

right join:

mysql>     select
-> a.employee_id,
-> get_value(b.employee_id)
-> from
-> employees a
-> right join employees b on
-> a.employee_id = b.employee_id
-> and b.employee_name = 'David Tian';
+-------------+--------------------------+
| employee_id | get_value(b.employee_id) |
+-------------+--------------------------+
| NULL | 3000 |
+-------------+--------------------------+
1 row in set, 1 warning (0.00 sec)

语法树转换:

T:-1 = TABLE_ALIAS(T:0,"employees")
T:-2 = TMP_TABLE(T:4294967295)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:5))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,"employee_salary","ALL")
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
VC:-2.2 = CREATE_VC(T:-2,EXPR("1"))
C:0 = CREATE_CONDS(T:-2,VC:-2.1,=,VC:-2.2,<null>)
T:-2.ADD_CONDS(C:0,WHERE)
T:-2.APPLY_CONDS()
RESULT(T:-2)

结论:

  1. 开启tianmu_ini_allowmysqlquerypath参数,一些无法处理的SQL由mysql/sql层处理
  2. 到达列存储引擎时,结果就已经被mysql/sql层确定了
  3. 问题出在mysql/sql层的处理上