Details
Major
Description
Test case
CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); INSERT INTO t2 VALUES (1),(2); CREATE VIEW v1 (a) AS SELECT a FROM t1; CREATE VIEW v2 AS SELECT * FROM v1; EXPLAIN INSERT INTO v2 SELECT * FROM t2;
10.0/sql/handler.cc:2551: int handler::ha_rnd_next(uchar*): Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed. 141106 19:18:04 [ERROR] mysqld got signal 6 ;
#6 0x00007f75e63936f1 in *__GI___assert_fail (assertion=0xfed398 "table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2", file=<optimized out>, line=2551, function=0xfefd80 "int handler::ha_rnd_next(uchar*)") at assert.c:81 #7 0x000000000086d439 in handler::ha_rnd_next (this=0x7f75de47f088, buf=0x7f75de428488 "\377") at 10.0/sql/handler.cc:2550 #8 0x000000000086e451 in handler::read_first_row (this=0x7f75de47f088, buf=0x7f75de428488 "\377", primary_key=64) at 10.0/sql/handler.cc:2749 #9 0x00000000006efcd5 in handler::ha_read_first_row (this=0x7f75de47f088, buf=0x7f75de428488 "\377", primary_key=64) at 10.0/sql/sql_class.h:4912 #10 0x00000000006db8a0 in join_read_system (tab=0x7f75de5f3cf8) at 10.0/sql/sql_select.cc:18215 #11 0x00000000006db47f in join_read_const_table (tab=0x7f75de5f3cf8, pos=0x7f75de5f4230) at 10.0/sql/sql_select.cc:18111 #12 0x00000000006b8de7 in make_join_statistics (join=0x7f75de5f37c0, tables_list=..., conds=0x0, keyuse_array=0x7f75de5f3ac8) at 10.0/sql/sql_select.cc:3626 #13 0x00000000006b124f in JOIN::optimize_inner (this=0x7f75de5f37c0) at 10.0/sql/sql_select.cc:1339 #14 0x00000000006b01e6 in JOIN::optimize (this=0x7f75de5f37c0) at 10.0/sql/sql_select.cc:1024 #15 0x00000000006b7d6b in mysql_select (thd=0x7f75e0f2f070, rref_pointer_array=0x7f75de45cab0, tables=0x7f75de679470, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7f75de679f80, unit=0x7f75de45c150, select_lex=0x7f75de45c838) at 10.0/sql/sql_select.cc:3294 #16 0x00000000006ea0a5 in mysql_explain_union (thd=0x7f75e0f2f070, unit=0x7f75de45c150, result=0x7f75de679f80) at 10.0/sql/sql_select.cc:23898 #17 0x00000000006e9e04 in select_describe (join=0x7f75de5f10a8, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at 10.0/sql/sql_select.cc:23855 #18 0x00000000006b5748 in JOIN::exec_inner (this=0x7f75de5f10a8) at 10.0/sql/sql_select.cc:2568 #19 0x00000000006b4a90 in JOIN::exec (this=0x7f75de5f10a8) at 10.0/sql/sql_select.cc:2370 #20 0x00000000006b7dfb in mysql_select (thd=0x7f75e0f2f070, rref_pointer_array=0x7f75de678ab0, tables=0x7f75de415150, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7f75de679f80, unit=0x7f75de678150, select_lex=0x7f75de678838) at 10.0/sql/sql_select.cc:3308 #21 0x00000000006ea0a5 in mysql_explain_union (thd=0x7f75e0f2f070, unit=0x7f75de678150, result=0x7f75de679f80) at 10.0/sql/sql_select.cc:23898 #22 0x00000000006e9e04 in select_describe (join=0x7f75de45d470, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at 10.0/sql/sql_select.cc:23855 #23 0x00000000006b5748 in JOIN::exec_inner (this=0x7f75de45d470) at 10.0/sql/sql_select.cc:2568 #24 0x00000000006b4a90 in JOIN::exec (this=0x7f75de45d470) at 10.0/sql/sql_select.cc:2370 #25 0x00000000006b7dfb in mysql_select (thd=0x7f75e0f2f070, rref_pointer_array=0x7f75e0f336e0, tables=0x7f75de4148a8, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489925892, result=0x7f75de679f80, unit=0x7f75e0f32d80, select_lex=0x7f75e0f33468) at 10.0/sql/sql_select.cc:3308 #26 0x00000000006ae3f5 in handle_select (thd=0x7f75e0f2f070, lex=0x7f75e0f32cb8, result=0x7f75de679f80, setup_tables_done_option=1073741824) at 10.0/sql/sql_select.cc:373 #27 0x000000000067dae9 in mysql_execute_command (thd=0x7f75e0f2f070) at 10.0/sql/sql_parse.cc:3519 #28 0x000000000068586f in mysql_parse (thd=0x7f75e0f2f070, rawbuf=0x7f75de414088 "EXPLAIN INSERT INTO v2 SELECT * FROM t2", length=39, parser_state=0x7f75e8360630) at 10.0/sql/sql_parse.cc:6407 #29 0x0000000000678652 in dispatch_command (command=COM_QUERY, thd=0x7f75e0f2f070, packet=0x7f75e1ff6071 "EXPLAIN INSERT INTO v2 SELECT * FROM t2", packet_length=39) at 10.0/sql/sql_parse.cc:1299 #30 0x00000000006779f7 in do_command (thd=0x7f75e0f2f070) at 10.0/sql/sql_parse.cc:996 #31 0x00000000007944aa in do_handle_one_connection (thd_arg=0x7f75e0f2f070) at 10.0/sql/sql_connect.cc:1379 #32 0x00000000007941fd in handle_one_connection (arg=0x7f75e0f2f070) at 10.0/sql/sql_connect.cc:1293 #33 0x0000000000ccb4a6 in pfs_spawn_thread (arg=0x7f75e0367bf0) at 10.0/storage/perfschema/pfs.cc:1860 #34 0x00007f75e7f4cb50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #35 0x00007f75e644420d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Stack trace from
revision-id: sergii@pisem.net-20141103164737-457hfby1eg82zol9 date: 2014-11-03 17:47:37 +0100 build-date: 2014-11-06 19:22:17 +0400 revno: 4471 branch-nick: 10.0
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The used VIEWs are meregable, however, walking up the stack I find:
#11 0x0000000000691bf4 in JOIN::optimize (this=0x7fff84044070) at /home/psergey/dev2/10.0/sql/sql_select.cc:1024 (gdb) p select_lex->select_number $119 = 3
#15 0x0000000000697184 in JOIN::exec_inner (this=0x7fff84042ab8) at /home/psergey/dev2/10.0/sql/sql_select.cc:2568 (gdb) p select_lex->select_number $125 = 2
The table gets unlocked here:
#0 ha_myisam::external_lock (this=0x7fff84014298, thd=0x422afb0, lock_type=2) at /home/psergey/dev2/10.0/storage/myisam/ha_myisam.cc:1957 #1 0x0000000000855867 in handler::ha_external_lock (this=0x7fff84014298, thd=0x422afb0, lock_type=2) at /home/psergey/dev2/10.0/sql/handler.cc:5890 #2 0x000000000092b225 in unlock_external (thd=0x422afb0, table=0x7fff84047ff8, count=1) at /home/psergey/dev2/10.0/sql/lock.cc:673 #3 0x000000000092a799 in mysql_unlock_tables (thd=0x422afb0, sql_lock=0x7fff84047fd0, free_lock=true) at /home/psergey/dev2/10.0/sql/lock.cc:384 #4 0x000000000092a83a in mysql_unlock_some_tables (thd=0x422afb0, table=0x7fff840436f0, count=1) at /home/psergey/dev2/10.0/sql/lock.cc:402 #5 0x000000000069303e in JOIN::optimize_inner (this=0x7fff84042ab8) at /home/psergey/dev2/10.0/sql/sql_select.cc:1388 #6 0x0000000000691bf4 in JOIN::optimize (this=0x7fff84042ab8) at /home/psergey/dev2/10.0/sql/sql_select.cc:1024 #7 0x00000000006997a5 in mysql_select (thd=0x422afb0, rref_pointer_array=0x7fff8400dc30, tables=0x7fff840076c8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fff840104e8, unit=0x7fff8400d2d0, select_lex=0x7fff8400d9b8) at /home/psergey/dev2/10.0/sql/sql_select.cc:3294 #8 0x00000000006cb88f in mysql_explain_union (thd=0x422afb0, unit=0x7fff8400d2d0, result=0x7fff840104e8) at /home/psergey/dev2/10.0/sql/sql_select.cc:23898 #9 0x00000000006cb5ee in select_describe (join=0x7fff84010580, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at /home/psergey/dev2/10.0/sql/sql_select.cc:23855 #10 0x0000000000697184 in JOIN::exec_inner (this=0x7fff84010580) at /home/psergey/dev2/10.0/sql/sql_select.cc:2568 #11 0x00000000006964c6 in JOIN::exec (this=0x7fff84010580) at /home/psergey/dev2/10.0/sql/sql_select.cc:2370 #12 0x0000000000699835 in mysql_select (thd=0x422afb0, rref_pointer_array=0x422f620, tables=0x7fff84006e28, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489925892, result=0x7fff840104e8, unit=0x422ecc0, select_lex=0x422f3a8) at /home/psergey/dev2/10.0/sql/sql_select.cc:3308 #13 0x000000000068fdff in handle_select (thd=0x422afb0, lex=0x422ebf8, result=0x7fff840104e8, setup_tables_done_option=1073741824) at /home/psergey/dev2/10.0/sql/sql_select.cc:373 #14 0x000000000065f67f in mysql_execute_command (thd=0x422afb0) at /home/psergey/dev2/10.0/sql/sql_parse.cc:3519 #15 0x000000000066745d in mysql_parse (thd=0x422afb0, rawbuf=0x7fff84006608 "EXPLAIN INSERT INTO v2 SELECT * FROM t2", length=39, parser_state=0x7fffc86db650) at /home/psergey/dev2/10.0/sql/sql_parse.cc:6407
it seems that
- we have merged the VIEW
- however, EXPLAIN printing code attempts to run optimization for it.
I recall, "UPDATE mergeable_view" statements had a problem - the view is not removed from SELECT_LEX hierarchy, so [SHOW] EXPLAIN code had to explicitly ignore the first child select when it was a VIEW... perhaps, here it's the same problem?
Yes, this is that problem. v2's SELECT is seen as child select of the SELECT part of the query. This code in select_describe():
for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
unit;
unit= unit->next_unit())
{
walks into the merged select and tries to optimize it, etc. And fails.
I was wondering how MySQL 5.6 solves problem. They have similar code in Explain::send():
for (SELECT_LEX_UNIT *unit= select_lex()->first_inner_unit();
unit;
unit= unit->next_unit())
propagate_explain_option(thd, unit);
however this loop doesn't walk into v2. And the reason for that is open_table behavior. mysql_make_view in mysql-5.6:
#0 mysql_make_view (thd=0xceec180, share=0x7ffea8017580, table=0x7ffea8005248, open_view_no_parse=false) at /home/psergey/dev2/mysql-5.6/sql/sql_view.cc:1760 #1 0x000000000075a7e4 in open_table (thd=0xceec180, table_list=0x7ffea8005248, ot_ctx=0x7ffed793a9b0) at /home/psergey/dev2/mysql-5.6/sql/sql_base.cc:3071 #2 0x000000000075d041 in open_and_process_table (thd=0xceec180, lex=0xceee2f0, tables=0x7ffea8005248, counter=0xceee3a0, flags=0, prelocking_strategy=0x7ffed793aae0, has_prelocking_list=false, ot_ctx=0x7ffed793a9b0) at /home/psergey/dev2/mysql-5.6/sql/sql_base.cc:4628 #3 0x000000000075dd67 in open_tables (thd=0xceec180, start=0x7ffed793aaa0, counter=0xceee3a0, flags=0, prelocking_strategy=0x7ffed793aae0) at /home/psergey/dev2/mysql-5.6/sql/sql_base.cc:5068 #4 0x000000000075ee0d in open_normal_and_derived_tables (thd=0xceec180, tables=0x7ffea8005248, flags=0) at /home/psergey/dev2/mysql-5.6/sql/sql_base.cc:5756 #5 0x00000000007c58f5 in mysql_execute_command (thd=0xceec180) at /home/psergey/dev2/mysql-5.6/sql/sql_parse.cc:3500 #6 0x00000000007cd053 in mysql_parse (thd=0xceec180, rawbuf=0x7ffea8004fd0 "EXPLAIN INSERT INTO v2 SELECT * FROM t2", length=39, parser_state=0x7ffed793c120) at /home/psergey/dev2/mysql-5.6/sql/sql_parse.cc:6245
Does not call
lex->unit.include_down(table->select_lex);
This call is made only for views with algorithm=temptable.
On the other hand, 10.0 has this code in mysql_make_view():
ok: /* SELECT tree link */ lex->unit.include_down(table->select_lex); lex->unit.slave= view_select; // fix include_down initialisation /* global SELECT list linking */
it does call lex->unit.include_down.
Oleksandr Byelkin, what's your opinion on this?
The patch is ok to push
review was done by skype
We fail this assert:
DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK);because the table has been unlocked: