Another instance of Assertion `outer_lookup_keys <= outer_record_count' failed

[Philip Stoev]

Even after the fix for bug 714808 , the assertion still appears on certain queries.

mysqld: opt_subselect.cc:3747: bool JOIN::choose_subquery_plan(table_map): Assertion `outer_lookup_keys <= outer_record_count' failed.

backtrace:

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x083c36f6 in JOIN::choose_subquery_plan (this=0xa748e550, join_tables=1) at opt_subselect.cc:3747
#10 0x0830dd92 in make_join_statistics (join=0xa748e550, tables_arg=0xa7506108, conds=0xa75065b0, keyuse_array=0xa7493294) at sql_select.cc:3232
#11 0x083062cb in JOIN::optimize (this=0xa748e550) at sql_select.cc:952
#12 0x081aecc0 in st_select_lex::optimize_unflattened_subqueries (this=0xa32bad0) at sql_lex.cc:3140
#13 0x083c336b in JOIN::optimize_unflattened_subqueries (this=0xa74896b8) at opt_subselect.cc:3617
#14 0x083075e4 in JOIN::optimize (this=0xa74896b8) at sql_select.cc:1302
#15 0x0830bb0c in mysql_select (thd=0xa32a138, rref_pointer_array=0xa32bbd4, tables=0xa7504980, wild_num=0, fields=..., conds=0xa744b278, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xa7486158, unit=0xa32b834, select_lex=0xa32bad0)
    at sql_select.cc:2653
#16 0x083045ed in handle_select (thd=0xa32a138, lex=0xa32b7d8, result=0xa7486158, setup_tables_done_option=0) at sql_select.cc:283
#17 0x082a20ac in execute_sqlcom_select (thd=0xa32a138, all_tables=0xa7504980) at sql_parse.cc:5070
#18 0x082990bb in mysql_execute_command (thd=0xa32a138) at sql_parse.cc:2234
#19 0x082a4644 in mysql_parse (thd=0xa32a138,
    rawbuf=0xa7502700 "SELECT alias1.col_int_key FROM ( SELECT SQ1_alias2.* FROM t1 RIGHT JOIN t3 AS SQ1_alias2 RIGHT JOIN t1 AS SQ1_alias3 ON SQ1_alias3.col_int_nokey = SQ1_alias2.col_int_key ON SQ1_alias3.col_int_key = SQ"..., length=552, found_semicolon=0xa47bb228) at sql_parse.cc:6077
#20 0x08296d53 in dispatch_command (command=COM_QUERY, thd=0xa32a138,
    packet=0xa34a1d9 "SELECT alias1.col_int_key FROM ( SELECT SQ1_alias2.* FROM t1 RIGHT JOIN t3 AS SQ1_alias2 RIGHT JOIN t1 AS SQ1_alias3 ON SQ1_alias3.col_int_nokey = SQ1_alias2.col_int_key ON SQ1_alias3.col_int_key = SQ"..., packet_length=552) at sql_parse.cc:1210
#21 0x08296200 in do_command (thd=0xa32a138) at sql_parse.cc:903
#22 0x082932de in handle_one_connection (arg=0xa32a138) at sql_connect.cc:1154
#23 0x00821919 in start_thread () from /lib/libpthread.so.0
#24 0x0076acce in clone () from /lib/libc.so.6

bzr version-info:

revision-id: <email address hidden>
date: 2011-02-14 08:26:36 +0200
build-date: 2011-02-15 13:41:24 +0200
revno: 2890
branch-nick: maria-5.3-mwl89

[Philip Stoev (pstoev-askmonty)]

Unfortunately, this bug is not repeatable with a standalone MTR test case and only partial simplification of the crashing query was possible. The reason may be that repeated crashes have caused a particular table configuration that is not repeatable when starting with a fresh server.

To reproduce, mount the attached vardir using MTR:

MTR_VERSION=1 perl mysql-test-run.pl --start-and-exit --start-dirty --vardir= ... 1st

and then use the mysql client to run the following query:

 SELECT alias1.col_int_key FROM ( SELECT SQ1_alias2.* FROM t1 RIGHT JOIN t3 AS SQ1_alias2 RIGHT JOIN t1 AS SQ1_alias3 ON SQ1_alias3.col_int_nokey = SQ1_alias2.col_int_key ON SQ1_alias3.col_int_key = SQ1_alias2.col_int_nokey ) AS alias1 JOIN ( t3 AS alias2 STRAIGHT_JOIN t1 AS alias3 ON alias3.col_int_key = alias2.pk ) ON alias3.col_varchar_key WHERE ( alias1.col_varchar_key , alias3.col_varchar_key ) IN ( SELECT SQ2_alias1.col_varchar_nokey , SQ2_alias2.col_varchar_nokey FROM t1 AS SQ2_alias1 JOIN t2 AS SQ2_alias2 ON SQ2_alias2.col_varchar_key );

[Philip Stoev (pstoev-askmonty)]

MTR test case that leads to the same assertion. Unfortunately quite unsimplified and involving views inside subqueries.

[Timour Katchaounov (timour)]

Correct query:

SELECT alias1.col_varchar_key
FROM
t1 AS alias1 JOIN
(t2 AS alias2 JOIN (SELECT * FROM view_21264_inline_0) AS alias3 ON alias3.pk)
 ON alias3.col_int_key = alias2.col_int_key
WHERE alias1.col_int_key IN (SELECT * FROM view_21264_inline_1);