[MDEV-5387] Assertion `0' fails in Item_equal::get_first with materialization+semijoin, EXPLAIN, IN subquery Created: 2013-12-04  Updated: 2022-09-08

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12, 5.5.34, 10.0.6
Fix Version/s: 5.5

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates

 Description   

I've set it to 'Minor' because there is a rather senseless condition in WHERE, so it can be considered a corner case, and because it's a debug assertion only

mysqld: item_cmpfunc.cc:6138: Item* Item_equal::get_first(JOIN_TAB*, Item*): Assertion `0' failed.
 
131205  1:37:51 [ERROR] mysqld got signal 6 ;
 

#6  0x00007f181a616621 in *__GI___assert_fail (assertion=0xc6b59c "0", file=<optimized out>, line=6138, function=0xc6c360 "Item* Item_equal::get_first(JOIN_TAB*, Item*)") at assert.c:81
 
#7  0x00000000005ffd70 in Item_equal::get_first (this=0x23214b0, context=0x2324eb8, field_item=0x229bcd8) at item_cmpfunc.cc:6138
 
#8  0x00000000005acbaf in Item_field::replace_equal_field (this=0x229bcd8, arg=0x7f1811b32520 "\260\024\062\002") at item.cc:5155
 
#9  0x00000000005a0f03 in Item::transform (this=0x229bcd8, transformer=&virtual Item::replace_equal_field(unsigned char*), arg=0x7f1811b32520 "\260\024\062\002") at item.cc:725
 
#10 0x0000000000746398 in substitute_for_best_equal_field (context_tab=0x2324eb8, cond=0x229bcd8, cond_equal=0x2321218, table_join_idx=0x2322a50) at sql_select.cc:12254
 
#11 0x0000000000729405 in JOIN::optimize (this=0x22cf8c8) at sql_select.cc:1299
 
#12 0x000000000072f58b in mysql_select (thd=0x2216bc8, rref_pointer_array=0x22198b8, tables=0x229aba8, wild_num=1, fields=..., conds=0x22ceb38, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0x22cf208, unit=0x2219158, select_lex=0x2219660) at sql_select.cc:2995
 
#13 0x0000000000760c84 in mysql_explain_union (thd=0x2216bc8, unit=0x2219158, result=0x22cf208) at sql_select.cc:22377
 
#14 0x00000000006b4bdf in execute_sqlcom_select (thd=0x2216bc8, all_tables=0x229aba8) at sql_parse.cc:5151
 
#15 0x00000000006abf18 in mysql_execute_command (thd=0x2216bc8) at sql_parse.cc:2305
 
#16 0x00000000006b76e3 in mysql_parse (thd=0x2216bc8, rawbuf=0x229a900 "EXPLAIN \nSELECT * FROM t1, t2, t3\nWHERE c = a AND ( e, c )  IN ( SELECT e, e FROM t3 )\nAND b IS NULL AND b = 'Midland'", length=118, found_semicolon=0x7f1811b33cb8) at sql_parse.cc:6173
 
#17 0x00000000006a96f8 in dispatch_command (command=COM_QUERY, thd=0x2216bc8, packet=0x2291499 "", packet_length=118) at sql_parse.cc:1243
 
#18 0x00000000006a89e4 in do_command (thd=0x2216bc8) at sql_parse.cc:923
 
#19 0x00000000006a586d in handle_one_connection (arg=0x2216bc8) at sql_connect.cc:1231
 
#20 0x00007f181b322b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#21 0x00007f181a6c5a7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
 

revision-id: bar@mnogosearch.org-20131203110843-1bsdu0hcrpzhie8w
 
revno: 3731
 
branch-nick: 5.3
 
BUILD/compile-pentium-debug-max-no-ndb
 

SET optimizer_switch='materialization=on,semijoin=on';
 
 
 
CREATE TABLE t1 (a VARCHAR(52));
 
INSERT INTO t1 VALUES ('USA'),('CAN');
 
 
 
CREATE TABLE t2 (b VARCHAR(52), c VARCHAR(52), INDEX(b));
 
INSERT INTO t2 VALUES ('Mesa','USA'),('Chesapeake','USA');
 
 
 
CREATE TABLE t3 (d INT, e VARCHAR(3), f VARCHAR(35), INDEX(e,f));
 
INSERT INTO t3 VALUES 
(1,'USA','Cedar Rapids'),
 
(2,'USA','Chandler'),
 
(3,'USA','Charleston'),
 
(4,'USA','Charlotte'),
 
(5,'USA','Chattanooga'),
 
(6,'USA','Chesapeake'),
 
(7,'USA','Chicago'),
 
(8,'USA','Chula Vista'),
 
(9,'USA','Cincinnati'),
 
(10,'USA','Mesa'),
 
(11,'USA','Mesquite'),
 
(12,'USA','Metairie'),
 
(13,'USA','Miami'),
 
(14,'USA','Miami Beach'),
 
(15,'USA','Midland');
 
 
 
EXPLAIN 
SELECT * FROM t1, t2, t3
 
WHERE c = a AND ( e, c )  IN ( SELECT e, e FROM t3 )
 
AND b IS NULL AND b = 'Midland';



 Comments   
Comment by Sergei Petrunia [ 2014-02-17 ]

Modified the query to make table names unique:

create table t3a like t3;
insert into t3a select * from t3;
EXPLAIN SELECT * FROM t1, t2, t3 WHERE c = a AND ( e, c ) IN ( SELECT e, e FROM t3a ) AND b IS NULL AND b = 'Midland';

The crash happens when attempting to substitute_for_best_equal() for ref access on table t3a. The ref access is for "t3.e= t2.c"

We're looking for top-level multi-equalities (cond_equal->current_level). The first one we find is
$79 = 0x153b600 "multiple equal(`j9`.`t2`.`c`, `j9`.`t1`.`a`)"

The substitution happens in the context of JOIN_TAB(t3a). In Item_equal::get_first(..) we have:

(gdb) p emb_nest
$81 = (TABLE_LIST *) 0x7fffccc4d388
(gdb) p emb_nest->sj_mat_info
$82 = (SJ_MATERIALIZATION_INFO *) 0x7fffcccd5be0
(gdb) p emb_nest->sj_mat_info->is_used
$83 = true

Item_equal has only "t2.c" and "t1.a". Basically, we're inside an SJ-Materialization nest, and we're looking for an appropriate substitute for item that's outside the SJM-nest. We fail to find it, and assertion is fired.

Comment by Sergei Petrunia [ 2014-02-17 ]

Prettified the query:

EXPLAIN SELECT *
FROM
t1, t2, t3
WHERE
t2.c = t1.a AND
(t3.e, t2.c) IN (SELECT t3a.e, t3a.e FROM t3a ) AND
t2.b IS NULL AND
t2.b = 'Midland';

t2.b is NULLable, there is an index on t2.b (which means that the optimizer should infer that the top select has "Impossible WHERE").

Comment by Sergei Petrunia [ 2014-02-17 ]

Debugging in fix_semijoin_strategies_for_picked_join_order(), I can see that
this join order was constructed:

t2
t1
SJ_MATERIALIZE_SCAN(t3a)
t3

fix_semijoin_strategies_for_picked_join_order calls best_access_path() for
t3a. It picks ref access on t3.e=t2.c. This is incorrect, ref access cannot be used.

Comment by Sergei Petrunia [ 2014-02-17 ]

... I actually don't understand why fix_semijoin_...() re-runs best_access_path() for tables inside the SJM nest. Query plan for performing SJ-materialization has been produced and stashed away by optimize_semijoin_tests(). Why not re-use it?

We might want to re-run best_access_path() for some tables outside the SJM nest, because SJ-Materialization changes their fanout. But this doesn't apply to tables inside the SJM nest.

Comment by Sergei Petrunia [ 2014-02-17 ]

Another puzzling thing is, why is join optimizer invoked at all, when we know that the WHERE has an impossible condition? If I work around the crash in debugger, the EXPLAIN will show "Impossible WHERE noticed after reading const tables".

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