2022-07-13 mysql/stonedb子查询-优化器处理记录

目录

​​摘要:​​

​​慢子查询：​​

​​调用堆栈:​​

​​核心处理:​​

​​快速子查询:​​

​​调用堆栈:​​

​​replace_index_subquery​​

​​优化处理后的join数据:​​

记录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_optimizer.cc:375
#1  0x00000000024baf1d in st_select_lex::optimize (this=0x7f571c006240, thd=0x7f571c000b70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_select.cc: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_select.cc:175
#3  0x000000000246f964 in execute_sqlcom_select (thd=0x7f571c000b70, all_tables=0x0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:5184
#4  0x0000000002468cae in mysql_execute_command (thd=0x7f571c000b70, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:2831
#5  0x00000000024708f3 in mysql_parse (thd=0x7f571c000b70, parser_state=0x7f59ba84eeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc: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_parse.cc:1495
#7  0x0000000002464ab7 in do_command (thd=0x7f571c000b70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:1034
#8  0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_thread.cc:313
#9  0x0000000002c7b304 in pfs_spawn_thread (arg=0x92ec380) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/pfs.cc: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_optimizer.cc: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_parse.cc:5182
#5  0x0000000002468cae in mysql_execute_command (thd=0x7f56fc000b70, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:2831
#6  0x00000000024708f3 in mysql_parse (thd=0x7f56fc000b70, parser_state=0x7f59b9eceeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc: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_parse.cc:1495
#8  0x0000000002464ab7 in do_command (thd=0x7f56fc000b70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:1034
#9  0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_thread.cc:313
#10 0x0000000002c7b304 in pfs_spawn_thread (arg=0x7ef82c0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/pfs.cc: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_optimizer.cc:388
#1  0x00000000024baf1d in st_select_lex::optimize (this=0x7f571c01bf40, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_select.cc: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_union.cc:710
#3  0x0000000002307c31 in Item_subselect::exec (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_subselect.cc:625
#4  0x000000000230a904 in Item_exists_subselect::val_int (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_subselect.cc: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_func.cc: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_optimizer.cc: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_optimizer.cc: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_optimizer.cc: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_optimizer.cc:10114
#10 0x0000000002442b39 in JOIN::optimize (this=0x7f571c001c78, part=1 '\001') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_optimizer.cc: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_parse.cc:5182
#15 0x0000000002468cae in mysql_execute_command (thd=0x7f571c012770, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:2831
#16 0x00000000024708f3 in mysql_parse (thd=0x7f571c012770, parser_state=0x7f59ba84eeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc: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_parse.cc:1495
#18 0x0000000002464ab7 in do_command (thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:1034
#19 0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_thread.cc:313
#20 0x0000000002c7b304 in pfs_spawn_thread (arg=0x92ec380) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/pfs.cc: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_optimizer.cc:469
#1  0x00000000024baf1d in st_select_lex::optimize (this=0x7f571c01bf40, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_select.cc: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_union.cc:710
#3  0x0000000002307c31 in Item_subselect::exec (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_subselect.cc:625
#4  0x000000000230a904 in Item_exists_subselect::val_int (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_subselect.cc: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_func.cc: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_optimizer.cc: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_optimizer.cc: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_optimizer.cc: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_optimizer.cc:10114
#10 0x0000000002442b39 in JOIN::optimize (this=0x7f571c001c78, part=1 '\001') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_optimizer.cc: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_parse.cc:5182
#15 0x0000000002468cae in mysql_execute_command (thd=0x7f571c012770, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:2831
#16 0x00000000024708f3 in mysql_parse (thd=0x7f571c012770, parser_state=0x7f59ba84eeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc: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_parse.cc:1495
#18 0x0000000002464ab7 in do_command (thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:1034
#19 0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_thread.cc:313
#20 0x0000000002c7b304 in pfs_spawn_thread (arg=0x92ec380) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/pfs.cc: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_optimizer.cc:587
#1  0x00000000024baf1d in st_select_lex::optimize (this=0x7f571c01bf40, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_select.cc: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_union.cc:710
#3  0x0000000002307c31 in Item_subselect::exec (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_subselect.cc:625
#4  0x000000000230a904 in Item_exists_subselect::val_int (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_subselect.cc: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_func.cc: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_optimizer.cc: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_optimizer.cc: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_optimizer.cc: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_optimizer.cc:10114
#10 0x0000000002442b39 in JOIN::optimize (this=0x7f571c001c78, part=1 '\001') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_optimizer.cc: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_parse.cc:5182
#15 0x0000000002468cae in mysql_execute_command (thd=0x7f571c012770, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:2831
#16 0x00000000024708f3 in mysql_parse (thd=0x7f571c012770, parser_state=0x7f59ba84eeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc: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_parse.cc:1495
#18 0x0000000002464ab7 in do_command (thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:1034
#19 0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_thread.cc:313
#20 0x0000000002c7b304 in pfs_spawn_thread (arg=0x92ec380) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/pfs.cc:2197
#21 0x00007f59c3ccdea5 in start_thread () from /lib64/libpthread.so.0
#22 0x00007f59c2104b0d in clone () from /lib64/libc.so.6

(gdb) bt
#0  JOIN::replace_index_subquery (this=0x7f571c002078) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_optimizer.cc:1050
#1  0x0000000002443e8e in JOIN::optimize (this=0x7f571c002078, part=0 '\000') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_optimizer.cc:587
#2  0x00000000024baf1d in st_select_lex::optimize (this=0x7f571c01bf40, thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_select.cc: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_union.cc:710
#4  0x0000000002307c31 in Item_subselect::exec (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_subselect.cc:625
#5  0x000000000230a904 in Item_exists_subselect::val_int (this=0x7f571c0012f8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/item_subselect.cc: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_func.cc: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_optimizer.cc: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_optimizer.cc: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_optimizer.cc: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_optimizer.cc:10114
#11 0x0000000002442b39 in JOIN::optimize (this=0x7f571c001c78, part=1 '\001') at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_optimizer.cc: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_parse.cc:5182
#16 0x0000000002468cae in mysql_execute_command (thd=0x7f571c012770, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:2831
#17 0x00000000024708f3 in mysql_parse (thd=0x7f571c012770, parser_state=0x7f59ba84eeb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc: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_parse.cc:1495
#19 0x0000000002464ab7 in do_command (thd=0x7f571c012770) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/sql_parse.cc:1034
#20 0x00000000025976d3 in handle_connection (arg=0x92ebe50) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/sql/conn_handler/connection_handler_per_thread.cc:313
#21 0x0000000002c7b304 in pfs_spawn_thread (arg=0x92ec380) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-vm/storage/perfschema/pfs.cc: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);