Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
mysqld: sql_select.cc:5216: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `join->best_read < ((double)1.79769313486231570815e+308L)' failed.
#8 0xb7530014 in __assert_fail () from /lib/libc.so.6
#9 0x0831264d in greedy_search (join=0x93a0ac8, remaining_tables=19, search_depth=62, prune_level=1)
at sql_select.cc:5216
#10 0x08312106 in choose_plan (join=0x93a0ac8, join_tables=19) at sql_select.cc:4923
#11 0x0830dde9 in make_join_statistics (join=0x93a0ac8, tables_arg=0x9389130, conds=0x0,
keyuse_array=0x93a1bd4) at sql_select.cc:3101
#12 0x08306da8 in JOIN::optimize (this=0x93a0ac8) at sql_select.cc:1034
#13 0x0830beff in mysql_select (thd=0x9315728, rref_pointer_array=0x9316d90, tables=0x9389130,
wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2147764736, result=0x938adf0, unit=0x9316a28, select_lex=0x9316c98)
at sql_select.cc:2551
#14 0x08304e3a in handle_select (thd=0x9315728, lex=0x93169cc, result=0x938adf0,
setup_tables_done_option=0) at sql_select.cc:280
#15 0x082ae487 in execute_sqlcom_select (thd=0x9315728, all_tables=0x9389130) at sql_parse.cc:5241
#16 0x082a58fa in mysql_execute_command (thd=0x9315728) at sql_parse.cc:2380
#17 0x082b089b in mysql_parse (thd=0x9315728,
rawbuf=0x9388f38 "SELECT a1 FROM A LEFT JOIN B LEFT JOIN C LEFT JOIN D \nON c1 = d1 ON d1 = b1 ON a1 = b1\nLEFT JOIN E ON a1 = e1", length=109, found_semicolon=0xae98b220) at sql_parse.cc:6216
#18 0x082a354c in dispatch_command (command=COM_QUERY, thd=0x9315728,
packet=0x9373651 "SELECT a1 FROM A LEFT JOIN B LEFT JOIN C LEFT JOIN D \nON c1 = d1 ON d1 = b1 ON a1 = b1\nLEFT JOIN E ON a1 = e1 ", packet_length=110) at sql_parse.cc:1294
#19 0x082a27b7 in do_command (thd=0x9315728) at sql_parse.cc:906
#20 0x0829f7b3 in handle_one_connection (arg=0x9315728) at sql_connect.cc:1208
#21 0xb767bb25 in start_thread () from /lib/libpthread.so.0
maria-5.1:
bzr version-info
revision-id: <email address hidden>
date: 2012-04-24 17:29:03 +0200
revno: 3147
Also reproducible on maria-5.2 (revno 3149).
Could not reproduce on maria-5.3, maria-5.5, although possibly it requires custom optimizer switch.
Could not reproduce on mysql-trunk (revno 3827)/
EXPLAIN also fails.
minimal optimizer_switch: table_elimination=on
full optimizer_switch (default): index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,table_elimination=on
Test case:
SET optimizer_switch = 'table_elimination=on';
CREATE TABLE A (a1 INT);
CREATE TABLE B (b1 INT);
CREATE TABLE C (c1 INT, UNIQUE KEY(c1));
CREATE TABLE D (d1 INT, UNIQUE KEY(d1));
CREATE TABLE E (e1 INT);
INSERT INTO A VALUES (1),(2);
INSERT INTO B VALUES (2),(3);
INSERT INTO C VALUES (3),(4);
INSERT INTO D VALUES (4),(5);
INSERT INTO E VALUES (5),(6);
SELECT a1 FROM A LEFT JOIN B LEFT JOIN C LEFT JOIN D
ON c1 = d1 ON d1 = b1 ON a1 = b1
LEFT JOIN E ON a1 = e1 ;
- End of test case
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Assertion `join->best_read < ((double)1.79..5e+308L)' failed in greedy_search with LEFT JOINs and unique keys
Ok, the bug doesn't repeat in 5.3 because the NESTED_JOIN structure for the
->nested_join has n_tables==1 (correct value after table elimination). 5.2 has n_tables==2, which is the cause of the bug.
Re: Assertion `join->best_read < ((double)1.79..5e+308L)' failed in greedy_search with LEFT JOINs and unique keys
With 99% certainity, this is the same problem as with
https://bugs.launchpad.net/maria/+bug/806524
Re: Assertion `join->best_read < ((double)1.79..5e+308L)' failed in greedy_search with LEFT JOINs and unique keys
Fix for that bug is small:
=== modified file 'sql/sql_select.cc'
— sql/sql_select.cc 2011-07-18 06:12:31 +0000
+++ sql/sql_select.cc 2011-07-19 21:31:40 +0000
@@ -11999,8 +11999,8 @@
if (!nested_join->n_tables)
is_eliminated_nest= TRUE;
}
- if ((!table->table && !is_eliminated_nest) ||
- (table->table && (table->table->map & ~join->eliminated_tables)))
+ if ((table->nested_join && !is_eliminated_nest) ||
+ (!table->nested_join && (table->table->map & ~join->eliminated_tables)))
n++;
}
DBUG_RETURN
;
Should I back-port it?
Re: Assertion `join->best_read < ((double)1.79..5e+308L)' failed in greedy_search with LEFT JOINs and unique keys
Backported and pushed into 5.1, 5.2
Launchpad bug id: 997747
Re: Assertion `join->best_read < ((double)1.79..5e+308L)' failed in greedy_search with LEFT JOINs and unique keys
{ A B E}Tables D and C are eliminated.
Remaining tables are:
Optimization considers a join prefix of
{A, B}best_access_path(<E>) is never called,
because check_interleaving_with_nj() doesn't allow to put it into the join
order,
because join->cur_embedding_map!=0.
This is so, because we've "entered" a join nest for table B and have never left it.
Will need to check why it works in 5.3