[MDEV-3654] LP:922254 - Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*) with semijoin+materialization on EXPLAIN Created: 2012-01-26  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
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug922254.xml    

 Description   

#8 0xb747c014 in __assert_fail () from /lib/libc.so.6
#9 0x0824b839 in Item_equal::get_first (this=0x946ef38, context=0x94bf138, field_item=0x945e2c0) at item_cmpfunc.cc:5899
#10 0x08205fe4 in Item_field::replace_equal_field (this=0x945e2c0, arg=0xae8f3fc0 "8\357F\t8\361K\t\274\355F\tP\360F\t")
at item.cc:5038
#11 0x081fbd55 in Item::transform (this=0x945e2c0, transformer=&virtual Item::replace_equal_field(uchar*),
arg=0xae8f3fc0 "8\357F\t8\361K\t\274\355F\tP\360F\t") at item.cc:647
#12 0x0836c6de in substitute_for_best_equal_field (context_tab=0x94bf138, cond=0x945e2c0, cond_equal=0x946edb4,
table_join_idx=0x94be150) at sql_select.cc:11702
#13 0x08353862 in JOIN::optimize (this=0x9476628) at sql_select.cc:1271
#14 0x08358dc0 in mysql_select (thd=0x93edad0, rref_pointer_array=0x93ef65c, tables=0x945dba8, wild_num=1, fields=...,
conds=0x946d760, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0x946d888,
unit=0x93ef240, select_lex=0x93ef520) at sql_select.cc:2917
#15 0x083838e1 in mysql_explain_union (thd=0x93edad0, unit=0x93ef240, result=0x946d888) at sql_select.cc:21381
#16 0x082ebf1a in execute_sqlcom_select (thd=0x93edad0, all_tables=0x945dba8) at sql_parse.cc:5127
#17 0x082e3474 in mysql_execute_command (thd=0x93edad0) at sql_parse.cc:2281
#18 0x082ee6d3 in mysql_parse (thd=0x93edad0,
rawbuf=0x945cc20 "EXPLAIN \nSELECT * FROM \n( SELECT * FROM t1 ) AS alias1, \nt2 AS alias2 \nWHERE b = a AND a IN ( \nSELECT alias3.c \nFROM t2 AS alias3, t2 AS alias4 \nWHERE alias4.c = alias3.b \n)", length=175, found_semicolon=0xae8f5234)
at sql_parse.cc:6149
#19 0x082e10d6 in dispatch_command (command=COM_QUERY, thd=0x93edad0, packet=0x9447361 "", packet_length=175)
at sql_parse.cc:1227
#20 0x082e0580 in do_command (thd=0x93edad0) at sql_parse.cc:922
#21 0x082dd545 in handle_one_connection (arg=0x93edad0) at sql_connect.cc:1193
#22 0xb770ab25 in start_thread () from /lib/libpthread.so.0

bzr version-info
revision-id: <email address hidden>
date: 2012-01-25 22:05:20 +0400
build-date: 2012-01-26 23:04:21 +0400
revno: 3395
branch-nick: maria-5.3

Also reproducible on revno 3390, but isn't reproducible on 3389.

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=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

Test case:

SET optimizer_switch = 'semijoin=on,materialization=on';

CREATE TABLE t1 ( a VARCHAR(3) );
CREATE TABLE t2 ( b VARCHAR(3), c VARCHAR(8), KEY(c) );
INSERT INTO t2 VALUES ('USA','Abilene'),('USA','Akron');

EXPLAIN
SELECT * FROM
( SELECT * FROM t1 ) AS alias1,
t2 AS alias2
WHERE b = a AND a IN (
SELECT alias3.c
FROM t2 AS alias3, t2 AS alias4
WHERE alias4.c = alias3.b
);

 Comments   
Comment by Elena Stepanova [ 2012-01-26 ]

Re: Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*) with semijoin+materialization on EXPLAIN
There were 5 assertion failures (non-simplified variations of the test case) during the test run.

Comment by Sergei Petrunia [ 2012-01-27 ]

Re: Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*) with semijoin+materialization on EXPLAIN

The system constructs a join order of:

t1, i.e. alias1 (the const table)
<sj-materialize>
alias3
alias4
alias2

access to table alias3 is made via

alias3.c = alias1.a

This is correct: alias1.a is a constant, so this is a ref(const) access.

The system then tries to perform equality substitution for "alias1.a" within
the context of table alias3.

Eventually, it will call this function:
Item *Item_field::replace_equal_field(uchar *arg)

and reach this code:

Item *const_item= item_equal->get_const();

and here we will have:

(gdb) p item_equal->with_const
$101 = false

... which is surprising, considering that the item_equal consists of (in order):
alias1.a
alias2.b

Eventually, we will reach

Item* Item_equal::get_first(JOIN_TAB *context, Item *field_item)

with context =

{ alias3 }

and field_item=

{t1.a}

and there we will fail an assert because we were unable to find a member of
Item_equal that could be evaluated within the semi-join nest.

Comment by Sergei Petrunia [ 2012-01-27 ]

Re: Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*) with semijoin+materialization on EXPLAIN
Committed a fix.

Comment by Rasmus Johansson (Inactive) [ 2012-02-20 ]

Launchpad bug id: 922254

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