Assertion `join->best_read < ((double)1.79..5e+308L)' failed in greedy_search with LEFT JOINs and unique keys

[Elena Stepanova]

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

[Sergey Petrunia (sergefp)]

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

[Sergey Petrunia (sergefp)]

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.

[Sergey Petrunia (sergefp)]

With 99% certainity, this is the same problem as with
https://bugs.launchpad.net/maria/+bug/806524

[Sergey Petrunia (sergefp)]

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(n);

Should I back-port it?

[Sergey Petrunia (sergefp)]

Backported and pushed into 5.1, 5.2