[MDEV-2898] LP:860553 - Crash in create_ref_for_key with semijoin + materialization Created: 2011-09-27  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: Major
Reporter: Philip Stoev (Inactive) Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug860553.xml    

 Description   

#5 0x0832c786 in create_ref_for_key (join=0xae63add8, j=0xae645994, org_keyuse=0xae633ff8, used_tables=4611686018427387907) at sql_select.cc:7307
#6 0x0832c029 in get_best_combination (join=0xae63add8) at sql_select.cc:7122
#7 0x0832448c in make_join_statistics (join=0xae63add8, tables_list=..., conds=0xae6334e8, keyuse_array=0xae63ff18) at sql_select.cc:3548
#8 0x0831c038 in JOIN::optimize (this=0xae63add8) at sql_select.cc:1112
#9 0x081b67f2 in st_select_lex::optimize_unflattened_subqueries (this=0xb336120) at sql_lex.cc:3126
#10 0x083e4a35 in JOIN::optimize_unflattened_subqueries (this=0xae635b50) at opt_subselect.cc:4334
#11 0x0831dc0a in JOIN::optimize (this=0xae635b50) at sql_select.cc:1621
#12 0x08321e11 in mysql_select (thd=0xb3346e0, rref_pointer_array=0xb33625c, tables=0xae6130c0, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0,
group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae632020, unit=0xb335e40, select_lex=0xb336120) at sql_select.cc:2886
#13 0x08319c67 in handle_select (thd=0xb3346e0, lex=0xb335de4, result=0xae632020, setup_tables_done_option=0) at sql_select.cc:283
#14 0x082b4434 in execute_sqlcom_select (thd=0xb3346e0, all_tables=0xae6130c0) at sql_parse.cc:5110
#15 0x082ab1f1 in mysql_execute_command (thd=0xb3346e0) at sql_parse.cc:2250
#16 0x082b6a63 in mysql_parse (thd=0xb3346e0,
rawbuf=0xae612ec0 "SELECT *\nFROM t1\nJOIN t2\nON ( t2.f5 ) IN (\nSELECT t3.f4 \nFROM t3\nWHERE ( 1 ) IN (\nSELECT t4.f3 \nFROM t4 , t5\n)\n)",
length=112, found_semicolon=0xae9e1228) at sql_parse.cc:6110
#17 0x082a8e40 in dispatch_command (command=COM_QUERY, thd=0xb3346e0,
packet=0xb38d321 "SELECT *\nFROM t1\nJOIN t2\nON ( t2.f5 ) IN (\nSELECT t3.f4 \nFROM t3\nWHERE ( 1 ) IN (\nSELECT t4.f3 \nFROM t4 , t5\n)\n)",
packet_length=112) at sql_parse.cc:1221
#18 0x082a829b in do_command (thd=0xb3346e0) at sql_parse.cc:916
#19 0x082a527f in handle_one_connection (arg=0xb3346e0) at sql_connect.cc:1191
#20 0x00821919 in start_thread () from /lib/libpthread.so.0
#21 0x0076acce in clone () from /lib/libc.so.6

minimal switch: semijoin=on,materialization=on

full switch: 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=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAIN crashes

test case:

CREATE TABLE t1 ( f1 int ) ;

CREATE TABLE t2 ( f5 varchar(52) NOT NULL ) ;

CREATE TABLE t3 ( f1 varchar(3), f4 varchar(52) , KEY (f4), PRIMARY KEY (f1)) ;

CREATE TABLE t4 ( f3 int, KEY (f3));
INSERT INTO t4 VALUES (17),(20);

CREATE TABLE t5 ( f2 int);
INSERT INTO t5 VALUES (0),(0);

SET SESSION optimizer_switch='semijoin=ON,materialization=ON';

SELECT *
FROM t1
JOIN t2
ON ( t2.f5 ) IN (
SELECT t3.f4
FROM t3
WHERE ( 1 ) IN (
SELECT t4.f3
FROM t4 , t5
)
);

revision-id: <email address hidden>
date: 2011-09-26 23:54:00 +0300
build-date: 2011-09-27 16:15:50 +0300
revno: 3196
branch-nick: maria-5.3

 Comments   
Comment by Sergei Petrunia [ 2011-09-29 ]

Re: Crash in create_ref_for_key with semijoin + materialization
The crash happens because create_ref_for_key() is invoked with a keyuse that does not match the table:

Breakpoint 17, create_ref_for_key (join=0xb29d690, j=0xb2a8db4, org_keyuse=0xb2abf70, used_tables=4611686018427387907) at sql_select.cc:7264
(gdb) p j->table->alias->Ptr
$170 = 0xb291230 "t4"
(gdb) p org_keyuse->table->alias->Ptr
$171 = 0xb28ce58 "t3"

Comment by Sergei Petrunia [ 2011-09-30 ]

Re: Crash in create_ref_for_key with semijoin + materialization
The mismatch between JOIN_TAB and its KEYUSE is created in fix_semijoin_strategies_for_picked_join_order(), here:

if (pos->sj_strategy == SJ_OPT_MATERIALIZE)
{
SJ_MATERIALIZATION_INFO *sjm= s->emb_sj_nest->sj_mat_info;
sjm->is_used= TRUE;
sjm->is_sj_scan= FALSE;
memcpy(pos - sjm->tables + 1, sjm->positions,
sizeof(POSITION) * sjm->tables);

The mismatch is caused by this scenario:

  • "(1) IN (SELECT t4.f3 FROM t4 , t5)" is converted into a semi-join nest
  • JOIN::optimize is invoked for '(SELECT t3.f4 FROM t3 WHERE ... )')
  • optimize_semijoin_nests() remembers SJ-Materialization plan for "(SELECT t4.f3 FROM t4 , t5)"
  • join->choose_subquery_plan() is invoked
  • it adds another keyuse elements
  • it re-runs choose_plan() which choses a different query plan.
  • fix_semijoin_strategies() is called, it executes the above code that copies over POSITION elements that has "old" keyuse pointers which now refer to the wrong tables.

that fair choice between IN->EXISTS and Materialization modifi

Comment by Sergei Petrunia [ 2011-09-30 ]

Re: Crash in create_ref_for_key with semijoin + materialization
Results of discussion with Timour: Need to save/restore SJM nest query subplans, and re-optimize them in JOIN::reoptimize()

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 860553

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