[MDEV-3582] LP:978847 - Server crashes in Item_ref::real_item on INSERT .. SELECT with FROM subquery and derived_merge=ON Created: 2012-04-11  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

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

Attachments: XML File LPexportBug978847.xml    

 Description   

#4 <signal handler called>
#5 0x08214ee2 in Item_ref::real_item (this=0x9486a20) at item.h:2659
#6 0x0837eae0 in count_field_types (select_lex=0x93ee1b4, param=0x94867b0, fields=...,
reset_with_sum_func=false) at sql_select.cc:19599
#7 0x083525ae in JOIN::prepare_stage2 (this=0x9486678) at sql_select.cc:864
#8 0x0835249b in JOIN::prepare (this=0x9486678, rref_pointer_array=0x93ee2f0,
tables_init=0x9485d58, wild_num=1, conds_init=0x0, og_num=0, order_init=0x0,
group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x93ee1b4,
unit_arg=0x93edec0) at sql_select.cc:838
#9 0x0835920d in mysql_select (thd=0x93ec740, rref_pointer_array=0x93ee2f0,
tables=0x9485d58, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0,
having=0x0, proc_param=0x0, select_options=3489942016, result=0x9486600,
unit=0x93edec0, select_lex=0x93ee1b4) at sql_select.cc:2932
#10 0x08350f2f in handle_select (thd=0x93ec740, lex=0x93ede64, result=0x9486600,
setup_tables_done_option=1073741824) at sql_select.cc:285
#11 0x082e6057 in mysql_execute_command (thd=0x93ec740) at sql_parse.cc:3304
#12 0x082ee4d8 in mysql_parse (thd=0x93ec740,
rawbuf=0x9484bc8 "INSERT INTO t1 SELECT * FROM \n( SELECT * FROM t1 ) AS alias",
length=59, found_semicolon=0xae9c9234) at sql_parse.cc:6152
#13 0x082e0eb9 in dispatch_command (command=COM_QUERY, thd=0x93ec740,
packet=0x9444311 "INSERT INTO t1 SELECT * FROM \n( SELECT * FROM t1 ) AS alias",
packet_length=59) at sql_parse.cc:1228
#14 0x082e0363 in do_command (thd=0x93ec740) at sql_parse.cc:923
#15 0x082dd259 in handle_one_connection (arg=0x93ec740) at sql_connect.cc:1218

bzr version-info
revision-id: <email address hidden>
date: 2012-04-07 17:27:00 -0700
build-date: 2012-04-11 15:49:16 +0400
revno: 3500

Notes:
maria/5.5-release revno 3367 and the latest build on 5.5-release from buildbot also crash, but maria-5.5 revno 3364 does not. 5.5-release looks newer, though.
No crash on MySQL trunk revno 3827 or MySQL 5.5 revno 3737.
Could not replace the FROM subquery with a view, no crash.
SELECT without INSERT does not cause the crash.

Minimal optimizer_switch: derived_merge=on
Full optimizer_switch (default): index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAIN for the SELECT with the minimal switch:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
INSERT INTO t1 SELECT * FROM
( SELECT * FROM t1 ) AS alias;

Test case:

SET optimizer_switch = 'derived_merge=on';

CREATE TABLE t1 ( a INT, b INT );
INSERT INTO t1 VALUES (2,1),(3,2);

INSERT INTO t1 SELECT * FROM
( SELECT * FROM t1 ) AS alias;

  1. End of test case


 Comments   
Comment by Igor Babaev [ 2012-04-15 ]

Re: Server crashes in Item_ref::real_item on INSERT .. SELECT with FROM subquery and derived_merge=ON
I've checked that this bug was introduced by the patch for bug #968720.
The bug is not reproducible on rev, 3493, but is reproducible on rev. 3494.

Comment by Oleksandr Byelkin [ 2012-04-18 ]

Re: Server crashes in Item_ref::real_item on INSERT .. SELECT with FROM subquery and derived_merge=ON
The crash is fixed, but the test suite shows yet another bug:
SET optimizer_switch = 'derived_merge=on';
CREATE TABLE t1 ( a INT, b INT );
INSERT INTO t1 VALUES (2,1),(3,2);
select * from t1;
a b
2 1
3 2
INSERT INTO t1 SELECT * FROM
( SELECT * FROM t1 ) AS alias;
select * from t1;
a b
2 1
3 2
3 2
3 2

i.e. it inserts two times (3,2) and no (2,1).

Comment by Oleksandr Byelkin [ 2012-04-18 ]

Re: Server crashes in Item_ref::real_item on INSERT .. SELECT with FROM subquery and derived_merge=ON
Problem is that select_insert::send_data uses Item_direct_view_ref which points on field of table t1 (not temporary (derived) table).

Comment by Oleksandr Byelkin [ 2012-04-18 ]

Re: Server crashes in Item_ref::real_item on INSERT .. SELECT with FROM subquery and derived_merge=ON
TABLE_LIST::change_refs_to_fields called only once.

Comment by Oleksandr Byelkin [ 2012-04-18 ]

Re: Server crashes in Item_ref::real_item on INSERT .. SELECT with FROM subquery and derived_merge=ON
and list for update is empty.

Comment by Rasmus Johansson (Inactive) [ 2012-05-03 ]

Launchpad bug id: 978847

Generated at Thu Feb 08 06:49:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.