[MDEV-2958] LP:997747 - Assertion `join->best_read < ((double)1.79..5e+308L)' failed in greedy_search with LEFT JOINs and unique keys Created: 2012-05-10  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 Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug997747.xml    

 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 ;

  1. End of test case

 Comments   
Comment by Sergei Petrunia [ 2012-05-11 ]

Re: Assertion `join->best_read < ((double)1.79..5e+308L)' failed in greedy_search with LEFT JOINs and unique keys
Tables D and C are eliminated.
Remaining tables are:

{ A B E}

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

Comment by Sergei Petrunia [ 2012-05-11 ]

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

{table B}

->nested_join has n_tables==1 (correct value after table elimination). 5.2 has n_tables==2, which is the cause of the bug.

Comment by Sergei Petrunia [ 2012-05-11 ]

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

Comment by Sergei Petrunia [ 2012-05-11 ]

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?

Comment by Sergei Petrunia [ 2012-05-12 ]

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

Comment by Rasmus Johansson (Inactive) [ 2012-05-12 ]

Launchpad bug id: 997747

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