Details
Minor
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';
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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").
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.
... 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.
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".
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.