[MDEV-7034] Assertion `table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2' failed in handler::ha_rnd_next on EXPLAIN INSERT .. SELECT Created: 2014-11-06  Updated: 2020-07-14  Resolved: 2014-11-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.14, 10.1.1
Fix Version/s: 10.0.15, 10.1.2

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-18163 Assertion `table_share->tmp_table != ... Closed
relates to MDEV-23159 Assertion `table_share->tmp_table != ... Closed

 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



 Comments   
Comment by Sergei Petrunia [ 2014-11-13 ]

We fail this assert:

  DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE ||
 
              m_lock_type != F_UNLCK);

because the table has been unlocked:

(gdb) p table_share->tmp_table != NO_TMP_TABLE
 
  $104 = false
 
(gdb) p m_lock_type != F_UNLCK
 
  $106 = false
 
(gdb) p table->alias.Ptr
 
  $110 = 0x7fff84012a70 "t1"

Comment by Sergei Petrunia [ 2014-11-13 ]

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

Comment by Sergei Petrunia [ 2014-11-13 ]

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?

Comment by Sergei Petrunia [ 2014-11-15 ]

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.

Comment by Sergei Petrunia [ 2014-11-15 ]

sanja, what's your opinion on this?

Comment by Sergei Petrunia [ 2014-11-18 ]

The patch is ok to push

Comment by Oleksandr Byelkin [ 2014-11-19 ]

review was done by skype

Generated at Thu Feb 08 07:16:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.