Assertion `join->best_read < 1.7976931348623157e+308 with table_elimination=on and derived_merge=on

[Philip Stoev]

Not repeatable without table_elimination=on,derived_merge=on . Not repeatable before WL#106. EXPLAIN also asserts.

mysqld: sql_select.cc:5950: bool greedy_search(JOIN*, table_map, uint, uint): Assertion `join->best_read < 1.7976931348623157e+308' failed.

#6 0x00000033b58296e6 in __assert_fail () from /lib64/libc.so.6
#7 0x000000000073c9d2 in greedy_search (join=0x1b2283f0, remaining_tables=5, search_depth=62, prune_level=1) at sql_select.cc:5950
#8 0x000000000073cef1 in choose_plan (join=0x1b2283f0, join_tables=5) at sql_select.cc:5604
#9 0x00000000007483a6 in make_join_statistics (join=0x1b2283f0, tables_list=..., conds=0x0, keyuse_array=0x1b22e3a0) at sql_select.cc:3497
#10 0x000000000074ad00 in JOIN::optimize (this=0x1b2283f0) at sql_select.cc:1101
#11 0x000000000074e839 in mysql_select (thd=0x1b117068, rref_pointer_array=0x1b119c18, tables=0x1b19e608, wild_num=1, fields=..., conds=0x0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x1b1c9ef8, unit=0x1b119510, select_lex=0x1b1199f8)
    at sql_select.cc:2875
#12 0x0000000000754c80 in handle_select (thd=0x1b117068, lex=0x1b119470, result=0x1b1c9ef8, setup_tables_done_option=0) at sql_select.cc:283
#13 0x00000000006a1a5c in execute_sqlcom_select (thd=0x1b117068, all_tables=0x1b19e608) at sql_parse.cc:5087
#14 0x00000000006a373a in mysql_execute_command (thd=0x1b117068) at sql_parse.cc:2231
#15 0x00000000006ac4d3 in mysql_parse (thd=0x1b117068,
    rawbuf=0x1b19ccb0 "SELECT *\nFROM ( SELECT * FROM t1 ) AS alias1\nRIGHT JOIN (\nt2 AS alias2\nLEFT JOIN (\nSELECT t4.*\nFROM ( SELECT * FROM t3 ) AS SQ1_alias1\nRIGHT JOIN t4\nON t4.f2 = SQ1_alias1.f1\n) AS alias3 \nON alias3.f4 "..., length=225, found_semicolon=0x4245df08) at sql_parse.cc:6088
#16 0x00000000006ad36b in dispatch_command (command=COM_QUERY, thd=0x1b117068,
    packet=0x1b193939 "SELECT *\nFROM ( SELECT * FROM t1 ) AS alias1\nRIGHT JOIN (\nt2 AS alias2\nLEFT JOIN (\nSELECT t4.*\nFROM ( SELECT * FROM t3 ) AS SQ1_alias1\nRIGHT JOIN t4\nON t4.f2 = SQ1_alias1.f1\n) AS alias3 \nON alias3.f4 "..., packet_length=225) at sql_parse.cc:1208
#17 0x00000000006ae979 in do_command (thd=0x1b117068) at sql_parse.cc:906
#18 0x00000000006993ef in handle_one_connection (arg=0x1b117068) at sql_connect.cc:1178
#19 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
#20 0x00000033b58d40cd in clone () from /lib64/libc.so.6

bzr version-info
revision-id: <email address hidden>
date: 2011-07-06 10:30:51 +0400
build-date: 2011-07-06 18:09:42 +0300
revno: 3085
branch-nick: maria-5.3

test case:

CREATE TABLE t1 ( f4 int) ;

CREATE TABLE t2 ( f4 int) ;

CREATE TABLE t3 ( f1 int NOT NULL , PRIMARY KEY (f1)) ;

CREATE TABLE t4 ( f2 int, f4 int) ;

SELECT *
FROM ( SELECT * FROM t1 ) AS alias1
RIGHT JOIN (
        t2 AS alias2
        LEFT JOIN (
                SELECT t4.*
                FROM ( SELECT * FROM t3 ) AS SQ1_alias1
                RIGHT JOIN t4
                ON t4.f2 = SQ1_alias1.f1
        ) AS alias3
        ON alias3.f4 != 0
) ON alias3.f4 != 0;

[Timour Katchaounov (timour)]

I confirm the crash. Based on a quick analysis with GDB, it is hard to decide
who will handle this bug best. If the value of join->best_read is set to some
number < DBL_MAX just before the ASSERT, then we get a crash inside
  fix_semijoin_strategies_for_picked_join_order()
in the following line:
    if ((handled_tabs & s->table->map) || pos->sj_strategy == SJ_OPT_NONE)
where 's' is NULL.

Since this code is in the area of Sergey, assigning the bug to him.