|
This crash does not occur in MySQL Server 5.5.39.
|
|
Test case:
CREATE TABLE `test1` (`id` bigint(20));
|
|
INSERT INTO `test1` VALUES (1),(2);
|
|
CREATE TABLE `test2` (`id` bigint(20));
|
|
CREATE TABLE `test_temp` (`id` bigint(20), `flag` tinyint(4));
|
|
create view view1 as select id from test1;
|
|
create view view2 as select test2.* from (test2 left join view1 using (id));
|
|
update test_temp left join view2 using (id) set flag=flag+1;
|
|
|
Debug version:
|
Stack trace from revno 4379
|
5.5/sql/sql_select.cc:6626: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `join->best_read < double(1.79769313486231570815e+308L)' failed.
|
141204 0:25:14 [ERROR] mysqld got signal 6 ;
|
|
#6 0x00007f2a604186f1 in *__GI___assert_fail (assertion=0xd6a560 "join->best_read < double(1.79769313486231570815e+308L)", file=<optimized out>, line=6626, function=0xd6d3e0 "bool greedy_search(JOIN*, table_map, uint, uint)") at assert.c:81
|
#7 0x00000000006753b9 in greedy_search (join=0x7f2a5ad43400, remaining_tables=6, search_depth=62, prune_level=1) at 5.5/sql/sql_select.cc:6626
|
#8 0x00000000006749f7 in choose_plan (join=0x7f2a5ad43400, join_tables=6) at 5.5/sql/sql_select.cc:6206
|
#9 0x000000000066e510 in make_join_statistics (join=0x7f2a5ad43400, tables_list=..., conds=0x0, keyuse_array=0x7f2a5ad43708) at 5.5/sql/sql_select.cc:3800
|
#10 0x00000000006655b6 in JOIN::optimize (this=0x7f2a5ad43400) at 5.5/sql/sql_select.cc:1229
|
#11 0x000000000066be1f in mysql_select (thd=0x7f2a5bd50060, rref_pointer_array=0x7f2a5bd53cc8, tables=0x7f2a5ae871b0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x7f2a5ad43340, unit=0x7f2a5bd53380, select_lex=0x7f2a5bd53a58) at 5.5/sql/sql_select.cc:3080
|
#12 0x00000000006ea51a in mysql_multi_update (thd=0x7f2a5bd50060, table_list=0x7f2a5ae871b0, fields=0x7f2a5bd53b70, values=0x7f2a5bd53ff8, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x7f2a5bd53380, select_lex=0x7f2a5bd53a58, result=0x7f2a5b7b5288) at 5.5/sql/sql_update.cc:1438
|
#13 0x0000000000636af0 in mysql_execute_command (thd=0x7f2a5bd50060) at 5.5/sql/sql_parse.cc:2915
|
#14 0x000000000063e6d4 in mysql_parse (thd=0x7f2a5bd50060, rawbuf=0x7f2a5ae87078 "update test_temp left join view2 using (id) set flag=flag+1", length=59, parser_state=0x7f2a5b7b5630) at 5.5/sql/sql_parse.cc:5800
|
#15 0x0000000000632701 in dispatch_command (command=COM_QUERY, thd=0x7f2a5bd50060, packet=0x7f2a5be07061 "update test_temp left join view2 using (id) set flag=flag+1", packet_length=59) at 5.5/sql/sql_parse.cc:1079
|
#16 0x000000000063188d in do_command (thd=0x7f2a5bd50060) at 5.5/sql/sql_parse.cc:793
|
#17 0x00000000007339da in do_handle_one_connection (thd_arg=0x7f2a5bd50060) at 5.5/sql/sql_connect.cc:1266
|
#18 0x0000000000733499 in handle_one_connection (arg=0x7f2a5bd50060) at 5.5/sql/sql_connect.cc:1181
|
#19 0x0000000000b6b855 in pfs_spawn_thread (arg=0x7f2a5bd71ca0) at 5.5/storage/perfschema/pfs.cc:1015
|
#20 0x00007f2a62212b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
|
#21 0x00007f2a604c920d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
|
|
|
Ok, UPDATE crashes for me on current 10.0 tree. The corresponding SELECT does not:
MariaDB [j11]> select * from test_temp left join view2 using (id);
|
Empty set (0.00 sec)
|
|
|
The queries are:
update
|
test_temp left join view2 using (id) set flag=flag+1;
|
|
create view view2 as
|
select test2.* from test2 left join view1 using (id);
|
|
create view view1 as select id from test1;
|
So, we have this left join structure:
test_temp
|
left join
|
(
|
test2
|
left join
|
(test1)
|
using (id)
|
)
|
using (id)
|
The failure scenario is:
We build a join prefix:
idx=0 test_tmp
idx=1 test2
The only remaining table for idx=2 is 'test1'.
However, when we try to put it into the join order, check_interleaving_with_nj() forbids it.
Because of that, we never build a complete join order and crash with assertion.
|
|
when we enter check_interleaving_with_nj, we have
(gdb) p join->cur_embedding_map
|
$54 = 1
|
(gdb) p next_tab->embedding_map
|
$55 = 0
|
(gdb) p next_tab->table->alias.Ptr
|
$56 = 0x7fffa4047ec0 "test1"
|
which means this code will return TRUE:
if (join->cur_embedding_map & ~next_tab->embedding_map)
|
{
|
/*
|
next_tab is outside of the "pair of brackets" we're currently in.
|
Cannot add it.
|
*/
|
return TRUE;
|
|
|
So,
table 'test1' has join_tab->embedding_map=0, which is incorrect.
embedding_map is built by make_join_statistics(). it is made from TABLE_LIST->embedding==NULL, which is also incorrect.
btw, in SELECT query, TABLE_LIST(test1)->embedding != NULL.
|
|
Looking where the SELECT query sets table_list->embedding for "test1":
(gdb) wher
|
#0 TABLE_LIST::wrap_into_nested_join (this=0x7fff9c041ae0, join_list=...) at /home/psergey/dev2/10.0-cp2/sql/table.cc:6969
|
#1 0x0000000000658aa2 in st_select_lex::merge_subquery (this=0x7fff9c0408c0, thd=0x31eade0, derived=0x7fff9c041ae0, subq_select=0x7fff9c059060, table_no=2, map=4) at /home/psergey/dev2/10.0-cp2/sql/sql_lex.cc:3738
|
#2 0x000000000063bce3 in mysql_derived_merge (thd=0x31eade0, lex=0x31eea28, derived=0x7fff9c041ae0) at /home/psergey/dev2/10.0-cp2/sql/sql_derived.cc:421
|
#3 0x000000000063b93e in mysql_handle_single_derived (lex=0x31eea28, derived=0x7fff9c041ae0, phases=8) at /home/psergey/dev2/10.0-cp2/sql/sql_derived.cc:192
|
#4 0x000000000074f199 in TABLE_LIST::handle_derived (this=0x7fff9c041ae0, lex=0x31eea28, phases=8) at /home/psergey/dev2/10.0-cp2/sql/table.cc:6910
|
#5 0x00000000006586ca in st_select_lex::handle_derived (this=0x7fff9c0408c0, lex=0x31eea28, phases=8) at /home/psergey/dev2/10.0-cp2/sql/sql_lex.cc:3550
|
#6 0x000000000074f13a in TABLE_LIST::handle_derived (this=0x7fff9c009188, lex=0x31eea28, phases=8) at /home/psergey/dev2/10.0-cp2/sql/table.cc:6908
|
#7 0x00000000006586ca in st_select_lex::handle_derived (this=0x31ef1e0, lex=0x31eea28, phases=8) at /home/psergey/dev2/10.0-cp2/sql/sql_lex.cc:3550
|
#8 0x000000000069a491 in JOIN::optimize_inner (this=0x7fff9c05a200) at /home/psergey/dev2/10.0-cp2/sql/sql_select.cc:1089
|
#9 0x000000000069a1c0 in JOIN::optimize (this=0x7fff9c05a200) at /home/psergey/dev2/10.0-cp2/sql/sql_select.cc:1024
|
#10 0x00000000006a2055 in mysql_select (thd=0x31eade0, rref_pointer_array=0x31ef458, tables=0x7fff9c008b58, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fff9c04f518, unit=0x31eeaf0, select_lex=0x31ef1e0) at /home/psergey/dev2/10.0-cp2/sql/sql_select.cc:3296
|
#11 0x00000000006d4da4 in mysql_explain_union (thd=0x31eade0, unit=0x31eeaf0, result=0x7fff9c04f518) at /home/psergey/dev2/10.0-cp2/sql/sql_select.cc:23989
|
#12 0x000000000066b5ca in execute_sqlcom_select (thd=0x31eade0, all_tables=0x7fff9c008b58) at /home/psergey/dev2/10.0-cp2/sql/sql_parse.cc:5221
|
#13 0x00000000006637b6 in mysql_execute_command (thd=0x31eade0) at /home/psergey/dev2/10.0-cp2/sql/sql_parse.cc:2546
|
#14 0x000000000066e4e1 in mysql_parse (thd=0x31eade0, rawbuf=0x7fff9c008918 "explain select * from test_temp left join view2 using (id)", length=58, parser_state=0x7fffe00674e0) at /home/psergey/dev2/10.0-cp2/sql/sql_parse.cc:6513
|
The most interesting is frame #2, mysql_derived_merge. In that function, we
have:
if (!derived->merged_for_insert ||
|
(derived->is_multitable() &&
|
(thd->lex->sql_command == SQLCOM_UPDATE_MULTI ||
|
thd->lex->sql_command == SQLCOM_DELETE_MULTI)))
|
{
|
....
|
/* Merge derived table's subquery in the parent select. */
|
if (parent_lex->merge_subquery(thd, derived, dt_select, tablenr, map))
|
|
Execution enters the merge_subquery() call.
|
|
Now, debugging the UPDATE query.
First, there is a special kind of merging:
(gdb) wher
|
#0 mysql_derived_merge_for_insert (thd=0xe6aa620, lex=0xe6ae268, derived=0x7ffe9c041c80) at /home/psergey/dev2/10.0-cp/sql/sql_derived.cc:505
|
#1 0x000000000063b6c3 in mysql_handle_derived (lex=0xe6ae268, phases=16) at /home/psergey/dev2/10.0-cp/sql/sql_derived.cc:118
|
#2 0x00000000007339cd in mysql_multi_update_prepare (thd=0xe6aa620) at /home/psergey/dev2/10.0-cp/sql/sql_update.cc:1367
|
#3 0x000000000066569b in mysql_execute_command (thd=0xe6aa620) at /home/psergey/dev2/10.0-cp/sql/sql_parse.cc:3315
|
this is not merging, but rather substituion. It is designed to handle DML over single-table VIEWs.
- test1 is "merged into" view1 this way
- contents of view2 are not merged this way, because view2 includes multiple tables.
Then, UPDATE code calls tries to merge in the regular way.
(gdb) wher
|
#0 mysql_derived_merge (thd=0xe6aa620, lex=0xe6ae268, derived=0x7ffe9c041c80) at /home/psergey/dev2/10.0-cp/sql/sql_derived.cc:381
|
#1 0x000000000063b93e in mysql_handle_single_derived (lex=0xe6ae268, derived=0x7ffe9c041c80, phases=8) at /home/psergey/dev2/10.0-cp/sql/sql_derived.cc:192
|
#2 0x000000000074f199 in TABLE_LIST::handle_derived (this=0x7ffe9c041c80, lex=0xe6ae268, phases=8) at /home/psergey/dev2/10.0-cp/sql/table.cc:6910
|
#3 0x00000000006586ca in st_select_lex::handle_derived (this=0x7ffe9c040420, lex=0xe6ae268, phases=8) at /home/psergey/dev2/10.0-cp/sql/sql_lex.cc:3550
|
#4 0x000000000074f13a in TABLE_LIST::handle_derived (this=0x7ffe9c006d70, lex=0xe6ae268, phases=8) at /home/psergey/dev2/10.0-cp/sql/table.cc:6908
|
#5 0x00000000006586ca in st_select_lex::handle_derived (this=0xe6aea20, lex=0xe6ae268, phases=8) at /home/psergey/dev2/10.0-cp/sql/sql_lex.cc:3550
|
#6 0x0000000000733ac6 in mysql_multi_update_prepare (thd=0xe6aa620) at /home/psergey/dev2/10.0-cp/sql/sql_update.cc:1379
|
#7 0x000000000066569b in mysql_execute_command (thd=0xe6aa620) at /home/psergey/dev2/10.0-cp/sql/sql_parse.cc:3315
|
#8 0x000000000066e4e1 in mysql_parse (thd=0xe6aa620, rawbuf=0x7ffe9c006608 "update test_temp left join view2 using (id) set flag=flag+1", length=59, parser_state=0x7ffff7f82530) at /home/psergey/dev2/10.0-cp/sql/sql_parse.cc:6513
|
mysql_derived_merge( ... , view1) doesn`t do much, because
derived->merged_for_insert= true ...
mysql_derived_merge( ... , view2) enters the if-branch of interest
we enter st_select_lex::merge_subquery, TABLE_LIST::wrap_into_nested_join, etc.
the problem ssems to be with the first merge, for which
mysql_derived_merge_for_insert does some pseudo-merge, and then
mysql_handle_single_derived short-circuits.
|
|
This is interesting: if table "test1" was "merged for insert" into "view1", why does make_join_statistics() sees a TABLE_LIST object with table_list->alias="test1" when it is building JOIN_TABs?
|
|
2 ideas:
1) Use "merge for insert" only with tables which will be inserted/updated deleted (easy with delete, but dificult (if possible for else)
2) make everything working with merging VIEWs as they done now...
|
|
Just setting correctly embedding do not allow to get small patch...
|
|
Fixed by making everything working with merged normally view (in SELECT-like commands)
|
|
sanja:
- it would be nice if find_table_for_update() had a comment.
- If I undo a part of your patch like shown below, the testcase still passes and ##mtr --suite=main## passes, too. Is that change necessary? If yes, there needs to be a comment.
diff -urp 5.5-cp/sql/sql_delete.cc 5.5/sql/sql_delete.cc
|
--- 5.5-cp/sql/sql_delete.cc 2015-02-10 21:58:41.668350308 +0300
|
+++ 5.5/sql/sql_delete.cc 2015-02-10 21:39:51.896391975 +0300
|
@@ -657,10 +657,11 @@ multi_delete::initialize_tables(JOIN *jo
|
delete_while_scanning= 1;
|
for (walk= delete_tables; walk; walk= walk->next_local)
|
{
|
- TABLE_LIST *tbl= walk->correspondent_table->find_table_for_update();
|
- tables_to_delete_from|= tbl->table->map;
|
+ tables_to_delete_from|= walk->table->map;
|
+ /// TABLE_LIST *tbl= walk->correspondent_table->find_table_for_update();
|
+ /// tables_to_delete_from|= tbl->table->map;
|
if (delete_while_scanning &&
|
- unique_table(thd, tbl, join->tables_list, false))
|
+ unique_table(thd, walk, join->tables_list, false))
|
{
|
/*
|
If the table we are going to delete from appears
|
|
|
Try it with --ps-protocol it will fail.
|