摘要:
记录stonedb的不同子查询的优化器处理
慢子查询：
/stonedb57/install/bin/mysql -D tpch -e "select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority ;"
调用堆栈:
(gdb) bt
#0 JOIN::optimize (this=0x7f571c007138, part=0 '\000') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:375
#1 0x00000000024baf1d in st_select_lex::optimize (this=0x7f571c006240, thd=0x7f571c000b70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1020
#2 0x00000000024b96c3 in handle_query (thd=0x7f571c000b70, lex=0x7f571c002e98, result=0x7f571c007098, added_options=0, removed_options=0, optimize_after_bh=0, free_join_from_bh=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:175
#3 0x000000000246f964 in execute_sqlcom_select (thd=0x7f571c000b70, all_tables=0x0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5184
#4 0x0000000002468cae in mysql_execute_command (thd=0x7f571c000b70, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:2831
#5 0x00000000024708f3 in mysql_parse (thd=0x7f571c000b70, parser_state=0x7f59ba84eeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5621
#6 0x0000000002465b8b in dispatch_command (thd=0x7f571c000b70, com_data=0x7f59ba84f650, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1495
#7 0x0000000002464ab7 in do_command (thd=0x7f571c000b70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1034
#8 0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_:313
#9 0x0000000002c7b304 in pfs_spawn_thread (arg=0x92ec380) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/:2197
#10 0x00007f59c3ccdea5 in start_thread () from /lib64/libpthread.so.0
#11 0x00007f59c2104b0d in clone () from /lib64/libc.so.6
(gdb) bt
#0 JOIN::optimize (this=0x7f56fc012098, part=1 '\001') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:297
#1 0x0000000002d43ec5 in stonedb::core::optimize_select (thd=0x7f56fc000b70, select_options=2147748608, result=0x7f56fc011a60, select_lex=0x7f56fc005f90,
optimize_after_sdb=@0x7f59b9ecdd0c: 1, free_join=@0x7f59b9ecdd10: 1) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/core/engine_execute.cpp:352
#2 0x0000000002d43778 in stonedb::core::Engine::HandleSelect (this=0x7ed99e0, thd=0x7f56fc000b70, lex=0x7f56fc002e98, result=@0x7f59b9ecdd18: 0x7f56fc011a60, setup_tables_done_option=0,
res=@0x7f59b9ecdd14: 0, optimize_after_sdb=@0x7f59b9ecdd0c: 1, sdb_free_join=@0x7f59b9ecdd10: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/core/engine_execute.cpp:225
#3 0x0000000002e2bfaf in stonedb::dbhandler::SDB_HandleSelect (thd=0x7f56fc000b70, lex=0x7f56fc002e98, result=@0x7f59b9ecdd18: 0x7f56fc011a60, setup_tables_done_option=0,
res=@0x7f59b9ecdd14: 0, optimize_after_sdb=@0x7f59b9ecdd0c: 1, sdb_free_join=@0x7f59b9ecdd10: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/handler/ha_rcengine.cpp:82
#4 0x000000000246f92a in execute_sqlcom_select (thd=0x7f56fc000b70, all_tables=0x7f56fc00f1b0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5182
#5 0x0000000002468cae in mysql_execute_command (thd=0x7f56fc000b70, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:2831
#6 0x00000000024708f3 in mysql_parse (thd=0x7f56fc000b70, parser_state=0x7f59b9eceeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5621
#7 0x0000000002465b8b in dispatch_command (thd=0x7f56fc000b70, com_data=0x7f59b9ecf650, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1495
#8 0x0000000002464ab7 in do_command (thd=0x7f56fc000b70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1034
#9 0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_:313
#10 0x0000000002c7b304 in pfs_spawn_thread (arg=0x7ef82c0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/:2197
#11 0x00007f59c3ccdea5 in start_thread () from /lib64/libpthread.so.0
#12 0x00007f59c2104b0d in clone () from /lib64/libc.so.6
核心处理:
if (!tables_list)
{
DBUG_PRINT("info",("No tables"));
best_rowcount= 1;
error= 0;
if (make_tmp_tables_info())
DBUG_RETURN(1);
count_field_types(select_lex, &tmp_table_param, all_fields, false, false);
// Make plan visible for EXPLAIN
set_plan_state(NO_TABLES);
DBUG_RETURN(0);
}
快速子查询:
/stonedb57/install/bin/mysql -D tpch -e "select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_shipdate < date '1993-07-01'
)
group by
o_orderpriority
order by
o_orderpriority ;"
调用堆栈:
(gdb) bt
#0 JOIN::optimize (this=0x7f571c002078, part=0 '\000') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:388
#1 0x00000000024baf1d in st_select_lex::optimize (this=0x7f571c01bf40, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1020
#2 0x000000000251b766 in st_select_lex_unit::optimize (this=0x7f571c01c228, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:710
#3 0x0000000002307c31 in Item_subselect::exec (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_sub:625
#4 0x000000000230a904 in Item_exists_subselect::val_int (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_sub:1539
#5 0x0000000001e86305 in eval_const_cond (thd=0x7f571c012770, cond=0x7f571c0012f8, value=0x7f59ba84d342) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_:82
#6 0x000000000245ba02 in internal_remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0012f8, retcond=0x7f59ba84d428, cond_value=0x7f59ba84d424, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10303
#7 0x000000000245b446 in internal_remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0108a0, retcond=0x7f571c001f78, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10157
#8 0x000000000245be68 in remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0108a0, retcond=0x7f571c001f78, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10403
#9 0x000000000245b0e4 in optimize_cond (thd=0x7f571c012770, cond=0x7f571c001f78, cond_equal=0x7f571c001f98, join_list=0x7f571c00f5f0, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10114
#10 0x0000000002442b39 in JOIN::optimize (this=0x7f571c001c78, part=1 '\001') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:263
#11 0x0000000002d43ec5 in stonedb::core::optimize_select (thd=0x7f571c012770, select_options=2147748608, result=0x7f571c001730, select_lex=0x7f571c00f450,
optimize_after_sdb=@0x7f59ba84dd0c: 1, free_join=@0x7f59ba84dd10: 1) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/core/engine_execute.cpp:352
#12 0x0000000002d43778 in stonedb::core::Engine::HandleSelect (this=0x7ed99e0, thd=0x7f571c012770, lex=0x7f571c014a98, result=@0x7f59ba84dd18: 0x7f571c001730, setup_tables_done_option=0,
res=@0x7f59ba84dd14: 0, optimize_after_sdb=@0x7f59ba84dd0c: 1, sdb_free_join=@0x7f59ba84dd10: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/core/engine_execute.cpp:225
#13 0x0000000002e2bfaf in stonedb::dbhandler::SDB_HandleSelect (thd=0x7f571c012770, lex=0x7f571c014a98, result=@0x7f59ba84dd18: 0x7f571c001730, setup_tables_done_option=0,
res=@0x7f59ba84dd14: 0, optimize_after_sdb=@0x7f59ba84dd0c: 1, sdb_free_join=@0x7f59ba84dd10: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/handler/ha_rcengine.cpp:82
#14 0x000000000246f92a in execute_sqlcom_select (thd=0x7f571c012770, all_tables=0x7f571c01b1b8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5182
#15 0x0000000002468cae in mysql_execute_command (thd=0x7f571c012770, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:2831
#16 0x00000000024708f3 in mysql_parse (thd=0x7f571c012770, parser_state=0x7f59ba84eeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5621
#17 0x0000000002465b8b in dispatch_command (thd=0x7f571c012770, com_data=0x7f59ba84f650, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1495
#18 0x0000000002464ab7 in do_command (thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1034
#19 0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_:313
#20 0x0000000002c7b304 in pfs_spawn_thread (arg=0x92ec380) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/:2197
#21 0x00007f59c3ccdea5 in start_thread () from /lib64/libpthread.so.0
#22 0x00007f59c2104b0d in clone () from /lib64/libc.so.6
(gdb) bt
#0 JOIN::optimize (this=0x7f571c002078, part=0 '\000') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:469
#1 0x00000000024baf1d in st_select_lex::optimize (this=0x7f571c01bf40, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1020
#2 0x000000000251b766 in st_select_lex_unit::optimize (this=0x7f571c01c228, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:710
#3 0x0000000002307c31 in Item_subselect::exec (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_sub:625
#4 0x000000000230a904 in Item_exists_subselect::val_int (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_sub:1539
#5 0x0000000001e86305 in eval_const_cond (thd=0x7f571c012770, cond=0x7f571c0012f8, value=0x7f59ba84d342) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_:82
#6 0x000000000245ba02 in internal_remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0012f8, retcond=0x7f59ba84d428, cond_value=0x7f59ba84d424, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10303
#7 0x000000000245b446 in internal_remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0108a0, retcond=0x7f571c001f78, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10157
#8 0x000000000245be68 in remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0108a0, retcond=0x7f571c001f78, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10403
#9 0x000000000245b0e4 in optimize_cond (thd=0x7f571c012770, cond=0x7f571c001f78, cond_equal=0x7f571c001f98, join_list=0x7f571c00f5f0, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10114
#10 0x0000000002442b39 in JOIN::optimize (this=0x7f571c001c78, part=1 '\001') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:263
#11 0x0000000002d43ec5 in stonedb::core::optimize_select (thd=0x7f571c012770, select_options=2147748608, result=0x7f571c001730, select_lex=0x7f571c00f450,
optimize_after_sdb=@0x7f59ba84dd0c: 1, free_join=@0x7f59ba84dd10: 1) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/core/engine_execute.cpp:352
#12 0x0000000002d43778 in stonedb::core::Engine::HandleSelect (this=0x7ed99e0, thd=0x7f571c012770, lex=0x7f571c014a98, result=@0x7f59ba84dd18: 0x7f571c001730, setup_tables_done_option=0,
res=@0x7f59ba84dd14: 0, optimize_after_sdb=@0x7f59ba84dd0c: 1, sdb_free_join=@0x7f59ba84dd10: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/core/engine_execute.cpp:225
#13 0x0000000002e2bfaf in stonedb::dbhandler::SDB_HandleSelect (thd=0x7f571c012770, lex=0x7f571c014a98, result=@0x7f59ba84dd18: 0x7f571c001730, setup_tables_done_option=0,
res=@0x7f59ba84dd14: 0, optimize_after_sdb=@0x7f59ba84dd0c: 1, sdb_free_join=@0x7f59ba84dd10: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/handler/ha_rcengine.cpp:82
#14 0x000000000246f92a in execute_sqlcom_select (thd=0x7f571c012770, all_tables=0x7f571c01b1b8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5182
#15 0x0000000002468cae in mysql_execute_command (thd=0x7f571c012770, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:2831
#16 0x00000000024708f3 in mysql_parse (thd=0x7f571c012770, parser_state=0x7f59ba84eeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5621
#17 0x0000000002465b8b in dispatch_command (thd=0x7f571c012770, com_data=0x7f59ba84f650, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1495
#18 0x0000000002464ab7 in do_command (thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1034
#19 0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_:313
#20 0x0000000002c7b304 in pfs_spawn_thread (arg=0x92ec380) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/:2197
#21 0x00007f59c3ccdea5 in start_thread () from /lib64/libpthread.so.0
#22 0x00007f59c2104b0d in clone () from /lib64/libc.so.6
(gdb) bt
#0 JOIN::optimize (this=0x7f571c002078, part=0 '\000') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:587
#1 0x00000000024baf1d in st_select_lex::optimize (this=0x7f571c01bf40, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1020
#2 0x000000000251b766 in st_select_lex_unit::optimize (this=0x7f571c01c228, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:710
#3 0x0000000002307c31 in Item_subselect::exec (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_sub:625
#4 0x000000000230a904 in Item_exists_subselect::val_int (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_sub:1539
#5 0x0000000001e86305 in eval_const_cond (thd=0x7f571c012770, cond=0x7f571c0012f8, value=0x7f59ba84d342) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_:82
#6 0x000000000245ba02 in internal_remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0012f8, retcond=0x7f59ba84d428, cond_value=0x7f59ba84d424, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10303
#7 0x000000000245b446 in internal_remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0108a0, retcond=0x7f571c001f78, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10157
#8 0x000000000245be68 in remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0108a0, retcond=0x7f571c001f78, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10403
#9 0x000000000245b0e4 in optimize_cond (thd=0x7f571c012770, cond=0x7f571c001f78, cond_equal=0x7f571c001f98, join_list=0x7f571c00f5f0, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10114
#10 0x0000000002442b39 in JOIN::optimize (this=0x7f571c001c78, part=1 '\001') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:263
#11 0x0000000002d43ec5 in stonedb::core::optimize_select (thd=0x7f571c012770, select_options=2147748608, result=0x7f571c001730, select_lex=0x7f571c00f450,
optimize_after_sdb=@0x7f59ba84dd0c: 1, free_join=@0x7f59ba84dd10: 1) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/core/engine_execute.cpp:352
#12 0x0000000002d43778 in stonedb::core::Engine::HandleSelect (this=0x7ed99e0, thd=0x7f571c012770, lex=0x7f571c014a98, result=@0x7f59ba84dd18: 0x7f571c001730, setup_tables_done_option=0,
res=@0x7f59ba84dd14: 0, optimize_after_sdb=@0x7f59ba84dd0c: 1, sdb_free_join=@0x7f59ba84dd10: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/core/engine_execute.cpp:225
#13 0x0000000002e2bfaf in stonedb::dbhandler::SDB_HandleSelect (thd=0x7f571c012770, lex=0x7f571c014a98, result=@0x7f59ba84dd18: 0x7f571c001730, setup_tables_done_option=0,
res=@0x7f59ba84dd14: 0, optimize_after_sdb=@0x7f59ba84dd0c: 1, sdb_free_join=@0x7f59ba84dd10: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/handler/ha_rcengine.cpp:82
#14 0x000000000246f92a in execute_sqlcom_select (thd=0x7f571c012770, all_tables=0x7f571c01b1b8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5182
#15 0x0000000002468cae in mysql_execute_command (thd=0x7f571c012770, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:2831
#16 0x00000000024708f3 in mysql_parse (thd=0x7f571c012770, parser_state=0x7f59ba84eeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5621
#17 0x0000000002465b8b in dispatch_command (thd=0x7f571c012770, com_data=0x7f59ba84f650, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1495
#18 0x0000000002464ab7 in do_command (thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1034
#19 0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_:313
#20 0x0000000002c7b304 in pfs_spawn_thread (arg=0x92ec380) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/:2197
#21 0x00007f59c3ccdea5 in start_thread () from /lib64/libpthread.so.0
#22 0x00007f59c2104b0d in clone () from /lib64/libc.so.6
replace_index_subquery
(gdb) bt
#0 JOIN::replace_index_subquery (this=0x7f571c002078) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1050
#1 0x0000000002443e8e in JOIN::optimize (this=0x7f571c002078, part=0 '\000') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:587
#2 0x00000000024baf1d in st_select_lex::optimize (this=0x7f571c01bf40, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1020
#3 0x000000000251b766 in st_select_lex_unit::optimize (this=0x7f571c01c228, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:710
#4 0x0000000002307c31 in Item_subselect::exec (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_sub:625
#5 0x000000000230a904 in Item_exists_subselect::val_int (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_sub:1539
#6 0x0000000001e86305 in eval_const_cond (thd=0x7f571c012770, cond=0x7f571c0012f8, value=0x7f59ba84d342) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_:82
#7 0x000000000245ba02 in internal_remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0012f8, retcond=0x7f59ba84d428, cond_value=0x7f59ba84d424, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10303
#8 0x000000000245b446 in internal_remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0108a0, retcond=0x7f571c001f78, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10157
#9 0x000000000245be68 in remove_eq_conds (thd=0x7f571c012770, cond=0x7f571c0108a0, retcond=0x7f571c001f78, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10403
#10 0x000000000245b0e4 in optimize_cond (thd=0x7f571c012770, cond=0x7f571c001f78, cond_equal=0x7f571c001f98, join_list=0x7f571c00f5f0, cond_value=0x7f571c00f538, part=1 '\001')
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:10114
#11 0x0000000002442b39 in JOIN::optimize (this=0x7f571c001c78, part=1 '\001') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:263
#12 0x0000000002d43ec5 in stonedb::core::optimize_select (thd=0x7f571c012770, select_options=2147748608, result=0x7f571c001730, select_lex=0x7f571c00f450,
optimize_after_sdb=@0x7f59ba84dd0c: 1, free_join=@0x7f59ba84dd10: 1) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/core/engine_execute.cpp:352
#13 0x0000000002d43778 in stonedb::core::Engine::HandleSelect (this=0x7ed99e0, thd=0x7f571c012770, lex=0x7f571c014a98, result=@0x7f59ba84dd18: 0x7f571c001730, setup_tables_done_option=0,
res=@0x7f59ba84dd14: 0, optimize_after_sdb=@0x7f59ba84dd0c: 1, sdb_free_join=@0x7f59ba84dd10: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/core/engine_execute.cpp:225
#14 0x0000000002e2bfaf in stonedb::dbhandler::SDB_HandleSelect (thd=0x7f571c012770, lex=0x7f571c014a98, result=@0x7f59ba84dd18: 0x7f571c001730, setup_tables_done_option=0,
res=@0x7f59ba84dd14: 0, optimize_after_sdb=@0x7f59ba84dd0c: 1, sdb_free_join=@0x7f59ba84dd10: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/stonedb/handler/ha_rcengine.cpp:82
#15 0x000000000246f92a in execute_sqlcom_select (thd=0x7f571c012770, all_tables=0x7f571c01b1b8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5182
#16 0x0000000002468cae in mysql_execute_command (thd=0x7f571c012770, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:2831
#17 0x00000000024708f3 in mysql_parse (thd=0x7f571c012770, parser_state=0x7f59ba84eeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:5621
#18 0x0000000002465b8b in dispatch_command (thd=0x7f571c012770, com_data=0x7f59ba84f650, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1495
#19 0x0000000002464ab7 in do_command (thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_:1034
#20 0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_:313
#21 0x0000000002c7b304 in pfs_spawn_thread (arg=0x92ec380) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/:2197
#22 0x00007f59c3ccdea5 in start_thread () from /lib64/libpthread.so.0
#23 0x00007f59c2104b0d in clone () from /lib64/libc.so.6
(gdb) p *join
$6 = {
<Sql_alloc> = {<No data fields>},
members of JOIN:
select_lex = 0x7f571c01bf40,
unit = 0x7f571c01c228,
thd = 0x7f571c012770,
join_list = 0x0,
select_options = 0,
join_tab = 0x0,
qep_tab = 0x7f571c003df8,
best_ref = 0x0,
map2table = 0x7f571c0025f8,
sort_by_table = 0x0,
tables = 1,
primary_tables = 1,
const_tables = 0,
tmp_tables = 0,
send_group_parts = 0,
sort_and_group = false,
first_record = false,
grouped = false,
do_send_rows = true,
all_table_map = 1,
const_table_map = 0,
found_const_table_map = 0,
send_records = 0,
found_records = 0,
examined_rows = 0,
row_limit = 1,
m_select_limit = 1,
fetch_limit = 18446744073709551615,
best_positions = 0x0,
positions = 0x0,
first_select = 0x24233cb <sub_select(JOIN*, QEP_TAB*, bool)>,
best_read = 5000009.9989999998,
best_rowcount = 1,
sort_cost = 0,
fields = 0x7f571c01c078,
group_fields = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
last = 0x7f571c002160,
elements = 0
}, <No data fields>},
group_fields_cache = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
---Type <return> to continue, or q <return> to quit---
last = 0x7f571c002178,
elements = 0
}, <No data fields>},
sum_funcs = 0x7f571c002458,
sum_funcs_end = 0x7f571c002460,
sum_funcs2 = 0x0,
sum_funcs_end2 = 0x0,
tmp_table_param = {
<Sql_alloc> = {<No data fields>},
members of Temp_table_param:
copy_funcs = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
last = 0x7f571c0021b8,
elements = 0
}, <No data fields>},
copy_field = 0x0,
copy_field_end = 0x0,
group_buff = 0x0,
items_to_copy = 0x0,
recinfo = 0x0,
start_recinfo = 0x0,
keyinfo = 0x0,
end_write_records = 18446744073709551615,
field_count = 0,
func_count = 1,
sum_func_count = 0,
hidden_field_count = 0,
group_parts = 0,
group_length = 0,
group_null_parts = 0,
quick_group = 1,
outer_sum_func_count = 0,
using_outer_summary_function = false,
table_charset = 0x0,
schema_table = false,
precomputed_group_by = false,
force_copy_fields = false,
skip_create_table = false,
bit_fields_as_long = false,
can_use_pk_for_unique = true
},
lock = 0x7f571c94b260,
rollup = {
state = st_rollup::STATE_NONE,
null_items = {
m_array = 0x0,
m_size = 0
},
---Type <return> to continue, or q <return> to quit---
ref_pointer_arrays = 0x0,
fields = 0x0
},
implicit_grouping = false,
select_distinct = false,
group_optimized_away = false,
simple_order = true,
simple_group = true,
ordered_index_usage = JOIN::ordered_index_void,
no_order = false,
skip_sort_order = false,
need_tmp = false,
keyuse_array = {
<Mem_root_array_YY<Key_use, true>> = {
m_root = 0x7f571c0154b0,
m_array = 0x0,
m_size = 0,
m_capacity = 0
}, <No data fields>},
all_fields = @0x7f571c01c0a0,
tmp_all_fields1 = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
last = 0x7f571c0022b0,
elements = 0
}, <No data fields>},
tmp_all_fields2 = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
last = 0x7f571c0022c8,
elements = 0
}, <No data fields>},
tmp_all_fields3 = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
last = 0x7f571c0022e0,
elements = 0
}, <No data fields>},
tmp_fields_list1 = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
last = 0x7f571c0022f8,
elements = 0
---Type <return> to continue, or q <return> to quit---
}, <No data fields>},
tmp_fields_list2 = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
last = 0x7f571c002310,
elements = 0
}, <No data fields>},
tmp_fields_list3 = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
last = 0x7f571c002328,
elements = 0
}, <No data fields>},
fields_list = @0x7f571c01c078,
error = 0,
order = {
order = 0x0,
src = ESC_none,
flags = 0
},
group_list = {
order = 0x0,
src = ESC_GROUP_BY,
flags = 0
},
explain_flags = {
sorts = "\000\000\000\000"
},
where_cond = 0x7f571c001158,
having_cond = 0x0,
having_for_explain = 0x0,
tables_list = 0x7f571c000980,
cond_equal = 0x0,
return_tab = 0 '\000',
ref_ptrs = {
m_array = 0x7f571c001b30,
m_size = 4
},
items0 = {
m_array = 0x0,
m_size = 0
},
items1 = {
m_array = 0x0,
m_size = 0
},
items2 = {
---Type <return> to continue, or q <return> to quit---
m_array = 0x0,
m_size = 0
},
items3 = {
m_array = 0x0,
m_size = 0
},
current_ref_ptrs = {
m_array = 0x0,
m_size = 0
},
zero_result_cause = 0x0,
child_subquery_can_materialize = true,
allow_outer_refs = true,
sj_tmp_tables = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
last = 0x7f571c002418,
elements = 0
}, <No data fields>},
sjm_exec_list = {
<base_list> = {
<Sql_alloc> = {<No data fields>},
members of base_list:
first = 0x489f510 <end_of_list>,
last = 0x7f571c002430,
elements = 0
}, <No data fields>},
set_group_rpa = false,
group_sent = false,
calc_found_rows = false,
with_json_agg = false,
optimized = true,
executed = false,
plan_state = JOIN::PLAN_READY
}
int JOIN::replace_index_subquery()
{
DBUG_ENTER("replace_index_subquery");
ASSERT_BEST_REF_IN_JOIN_ORDER(this);
if (group_list ||
!(unit->item && unit->item->substype() == Item_subselect::IN_SUBS) ||
primary_tables != 1 || !where_cond ||
unit->is_union())
DBUG_RETURN(0);
// Guaranteed by remove_redundant_subquery_clauses():
assert(order == NULL && !select_distinct);
subselect_engine *engine= NULL;
Item_in_subselect * const in_subs=
static_cast<Item_in_subselect *>(unit->item);
enum join_type type= JT_UNKNOWN;
JOIN_TAB *const first_join_tab= best_ref[0];
if (in_subs->exec_method == Item_exists_subselect::EXEC_MATERIALIZATION)
{
// We cannot have two engines at the same time
}
else if (having_cond == NULL)
{
if (first_join_tab->type() == JT_EQ_REF &&
first_join_tab->ref().items[0]->item_name.ptr() == in_left_expr_name)
{
type= JT_UNIQUE_SUBQUERY;
/*
This uses test_if_ref(), which needs access to JOIN_TAB::join_cond() so
it must be done before we get rid of JOIN_TAB.
*/
remove_subq_pushed_predicates();
}
else if (first_join_tab->type() == JT_REF &&
first_join_tab->ref().items[0]->item_name.ptr() == in_left_expr_name)
{
type= JT_INDEX_SUBQUERY;
remove_subq_pushed_predicates();
}
}
else if (first_join_tab->type() == JT_REF_OR_NULL &&
first_join_tab->ref().items[0]->item_name.ptr() == in_left_expr_name &&
having_cond->item_name.ptr() == in_having_cond)
{
type= JT_INDEX_SUBQUERY;
where_cond= remove_additional_cond(where_cond);
}
if (type == JT_UNKNOWN)
DBUG_RETURN(0);
if (alloc_qep(tables))
DBUG_RETURN(-1); /* purecov: inspected */
unplug_join_tabs();
error= 0;
QEP_TAB *const first_qep_tab= &qep_tab[0];
if (first_qep_tab->table()->covering_keys.is_set(first_qep_tab->ref().key))
{
assert(!first_qep_tab->table()->no_keyread);
first_qep_tab->table()->set_keyread(true);
}
// execution uses where_cond:
first_qep_tab->set_condition(where_cond);
engine=
new subselect_indexsubquery_engine(thd, first_qep_tab, unit->item,
where_cond,
having_cond,
// check_null
first_qep_tab->type() == JT_REF_OR_NULL,
// unique
type == JT_UNIQUE_SUBQUERY);
/**
@todo If having_cond!=NULL we pass unique=false. But for this query:
(oe1, oe2) IN (SELECT primary_key, non_key_maybe_null_field FROM tbl)
we could use "unique=true" for the first index component and let
Item_is_not_null_test(non_key_maybe_null_field) handle the second.
*/
first_qep_tab->set_type(type);
if (!unit->item->change_engine(engine))
DBUG_RETURN(1);
else // error:
DBUG_RETURN(-1); /* purecov: inspected */
}
if (group_list ||
!(unit->item && unit->item->substype() == Item_subselect::IN_SUBS) ||
primary_tables != 1 || !where_cond ||
unit->is_union())
DBUG_RETURN(0);
