[MDEV-6219] Server crashes in Bitmap<64u>::merge (this=0x180, map2=...) on 2nd execution of PS with INSERT .. SELECT, derived_merge Created: 2014-05-07  Updated: 2015-08-31  Resolved: 2015-08-31

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.0.10
Fix Version/s: 10.0.22, 10.1.7

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


 Description    

SET optimizer_switch = 'in_to_exists=on,derived_merge=on';
 
 
 
CREATE TABLE t1 (a VARCHAR(8)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES ('foo'),('bar');
 
 
 
PREPARE stmt FROM "
 
  INSERT INTO t1 SELECT * FROM ( 
    SELECT * FROM t1
 
  ) AS sq
 
  WHERE sq.a IN ( SELECT 'baz' FROM DUAL ) 
";
 
 
 
EXECUTE stmt;
 
EXECUTE stmt;
 

#3  <signal handler called>
 
#4  0x0000000000623162 in Bitmap<64u>::merge (this=0x180, map2=...) at 10.0/sql/sql_bitmap.h:158
 
#5  0x00000000006b45e8 in add_key_field (join=0x7fd46a4dc2d8, key_fields=0x7fd48079c8a0, and_level=0, cond=0x7fd46a4ddd38, field=0x7fd46a428eb8, eq_func=true, value=0x7fd48079c790, num_values=1, usable_tables=18446744073709551615, sargables=0x7fd48079c9f8) at 10.0/sql/sql_select.cc:4370
 
#6  0x00000000006b57c7 in add_key_fields (join=0x7fd46a4dc2d8, key_fields=0x7fd48079c8a0, and_level=0x7fd48079c8ac, cond=0x7fd46a4ddd38, usable_tables=18446744073709551615, sargables=0x7fd48079c9f8) at 10.0/sql/sql_select.cc:4760
 
#7  0x00000000006b69d3 in update_ref_and_keys (thd=0x7fd4747f5070, keyuse=0x7fd46a4dc5e0, join_tab=0x7fd46a7fb088, tables=1, cond=0x7fd46a4ddd38, normal_tables=18446744073709551615, select_lex=0x7fd46a610840, sargables=0x7fd48079c9f8) at 10.0/sql/sql_select.cc:5184
 
#8  0x00000000006b1efd in make_join_statistics (join=0x7fd46a4dc2d8, tables_list=..., conds=0x7fd46a4ddd38, keyuse_array=0x7fd46a4dc5e0) at 10.0/sql/sql_select.cc:3575
 
#9  0x00000000006aa54e in JOIN::optimize_inner (this=0x7fd46a4dc2d8) at 10.0/sql/sql_select.cc:1327
 
#10 0x00000000006a950c in JOIN::optimize (this=0x7fd46a4dc2d8) at 10.0/sql/sql_select.cc:1013
 
#11 0x00000000006b108b in mysql_select (thd=0x7fd4747f5070, rref_pointer_array=0x7fd46a610ab8, tables=0x7fd46a7bc088, wild_num=0, fields=..., conds=0x7fd46a7bdd60, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489925888, result=0x7fd46a4dc240, unit=0x7fd46a610158, select_lex=0x7fd46a610840) at 10.0/sql/sql_select.cc:3279
 
#12 0x00000000006a7833 in handle_select (thd=0x7fd4747f5070, lex=0x7fd46a610090, result=0x7fd46a4dc240, setup_tables_done_option=1073741824) at 10.0/sql/sql_select.cc:372
 
#13 0x0000000000677011 in mysql_execute_command (thd=0x7fd4747f5070) at 10.0/sql/sql_parse.cc:3522
 
#14 0x0000000000697298 in Prepared_statement::execute (this=0x7fd46a599470, expanded_query=0x7fd48079db00, open_cursor=false) at 10.0/sql/sql_prepare.cc:3971
 
#15 0x0000000000696373 in Prepared_statement::execute_loop (this=0x7fd46a599470, expanded_query=0x7fd48079db00, open_cursor=false, packet=0x0, packet_end=0x0) at 10.0/sql/sql_prepare.cc:3626
 
#16 0x0000000000694675 in mysql_sql_stmt_execute (thd=0x7fd4747f5070) at 10.0/sql/sql_prepare.cc:2777
 
#17 0x00000000006749e1 in mysql_execute_command (thd=0x7fd4747f5070) at 10.0/sql/sql_parse.cc:2564
 
#18 0x000000000067ed75 in mysql_parse (thd=0x7fd4747f5070, rawbuf=0x7fd46a4dc088 "EXECUTE stmt", length=12, parser_state=0x7fd48079e610) at 10.0/sql/sql_parse.cc:6410
 
#19 0x0000000000671c54 in dispatch_command (command=COM_QUERY, thd=0x7fd4747f5070, packet=0x7fd474a23071 "EXECUTE stmt", packet_length=12) at 10.0/sql/sql_parse.cc:1309
 
#20 0x0000000000670ff6 in do_command (thd=0x7fd4747f5070) at 10.0/sql/sql_parse.cc:1006
 
#21 0x000000000078c03a in do_handle_one_connection (thd_arg=0x7fd4747f5070) at 10.0/sql/sql_connect.cc:1379
 
#22 0x000000000078bd8d in handle_one_connection (arg=0x7fd4747f5070) at 10.0/sql/sql_connect.cc:1293
 
#23 0x0000000000cb5ef4 in pfs_spawn_thread (arg=0x7fd474871bd0) at 10.0/storage/perfschema/pfs.cc:1853
 
#24 0x00007fd48041bb50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#25 0x00007fd47e913a7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Stack trace from:

revision-id: sergii@pisem.net-20140506115756-1q98x2ce75opl05y
 
revno: 4188
 
branch-nick: 10.0

Also reproducible on earlier versions of 10.0.
Could not reproduce on 5.5.
Could not reproduce with a merge view instead of SQ.
Could not reproduce with MySQL.

 Comments   
Comment by Oleksandr Byelkin [ 2014-05-13 ]

A field (key field) refers to table which is not mentioned in current join.

Comment by Oleksandr Byelkin [ 2014-06-11 ]

It is temporary table (created). So probably it moved somehow from derived table... (wrong merge?)

Comment by Oleksandr Byelkin [ 2014-06-12 ]

It looks like the derived table prepared to be merged then materialized without converting references.

Comment by Oleksandr Byelkin [ 2014-06-13 ]

The derived table turned to materialized because of updating the same table. References converted. the field refers via reference array => everythoing should work. Where it finds reference on old table it is not clear...

Comment by Oleksandr Byelkin [ 2015-02-18 ]

The same problem exists for second execution of SP.

SET optimizer_switch = 'in_to_exists=on,derived_merge=on';
 
 
 
CREATE TABLE t1 (a VARCHAR(8)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES ('foo'),('bar');
 
 
 
create procedure p1()
 
  INSERT INTO t1 SELECT * FROM ( 
    SELECT * FROM t1
 
  ) AS sq
 
  WHERE sq.a IN ( SELECT 'baz' FROM DUAL );
 
 
 
call p1();
 
call p1();
 
 
 
 
 
select * from t1;
 
drop table t1;

Comment by Oleksandr Byelkin [ 2015-02-18 ]

Opening tables looks the same => problem probably is in expressions.

Comment by Oleksandr Byelkin [ 2015-02-19 ]

Comparing debug trace of 5.5 and 10.0 did not show some big differences which could explain the problem

Comment by Oleksandr Byelkin [ 2015-02-19 ]

field we are trying to process was allocated during opening tables in previous procedure call

Comment by Oleksandr Byelkin [ 2015-02-19 ]

The Item_field which refer this field was resolved in this execution, so probably it found somewhere bad table reference...

Comment by Oleksandr Byelkin [ 2015-02-19 ]

Above was false trace

Comment by Oleksandr Byelkin [ 2015-02-19 ]

1)first execution uses t1 table in make_join_statistics
2)second execution uses that half-opened temporary table which created for derived table when correct table was used for field resolution.

Comment by Oleksandr Byelkin [ 2015-02-19 ]

the temporary table looks like real materialized derived table

Comment by Oleksandr Byelkin [ 2015-02-19 ]

find_dup_table() decided to materialize the table

Comment by Oleksandr Byelkin [ 2015-02-19 ]

First execution i was also materialized

Comment by Oleksandr Byelkin [ 2015-02-20 ]

There is no mysql_derived_merge() call but second execution have such condition:
@6 : | | | | | | | | | | | | | >setup_conds
T@6 : | | | | | | | | | | | | | | info: thd->mark_used_columns: 1

WHERE:(WHERE in setup_conds) 0x7fffe8034a78 (`test`.`t1`.`a` = 'bar')
T@6 : | | | | | | | | | | | | | | >Item_direct_view_ref::fix_fields
T@6 : | | | | | | | | | | | | | | | enter: Item: 0x7fffe8034770
T@6 : | | | | | | | | | | | | | | | >Item_ref::fix_fields
T@6 : | | | | | | | | | | | | | | | | enter: Item: 0x7fffe8034770
T@6 : | | | | | | | | | | | | | | | <Item_ref::fix_fields
T@6 : | | | | | | | | | | | | | | <Item_direct_view_ref::fix_fields
T@6 : | | | | | | | | | | | | | <setup_conds

Comment by Oleksandr Byelkin [ 2015-02-20 ]

list of leaves tables is not empty on second execution in setup_tables()...

Comment by Oleksandr Byelkin [ 2015-02-20 ]

actually 1 table when on first execution there was 2 tables...

Comment by Oleksandr Byelkin [ 2015-02-20 ]

mysql_insert_select_prepare removes one table the it will be returned, so leaf list should be correct

Comment by Oleksandr Byelkin [ 2015-02-22 ]

It looks like something wrong with leaf_tables_exec, because it is manipulated in mysql_insert_select_prepare() only on the first execution.

Comment by Oleksandr Byelkin [ 2015-02-23 ]

tables / tables list are OK, so problem in Item resolving (where it found wrong table?)

Comment by Oleksandr Byelkin [ 2015-02-23 ]

Item_equal are different but Item_direct_view_res is the same in both cases (resolved differently...)

Comment by Oleksandr Byelkin [ 2015-02-23 ]

ref of the Item_direct_view_res is the same (which should be) but *ref differ.
other item created during bool TABLE_LIST::change_refs_to_fields()

Comment by Oleksandr Byelkin [ 2015-02-24 ]

Item_direct_view_res of the condition in both 5.5 and 10.0 created in fix_fields() of left expression in check_and_do_in_subquery_rewrites on first execution only and reverted to temporary tables also only on first execution. But in 5.5 it manage to work on second execution.

Comment by Oleksandr Byelkin [ 2015-02-24 ]

the problem is in used_items. if save it after creating second Item_direct_view_res in persistent_used_items on first execution. which causes converting the item to temporary table on the second execution everything goes OK.

1) how it happens that 5.5 works without switching to temporary table? (not rolled back conversion?)
2) how to make stored such permanent changes?..

Comment by Oleksandr Byelkin [ 2015-02-24 ]

5.5 has exactly the same problem with non changed for temporary table on second execution Item_direct_view_ref, but execution has different path in join_make_statistic on the second run (first run goes the same path which 10.0 goes both times).

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