JOIN::choose_subquery_plan(table_map): Assertion `outer_lookup_keys <= outer_record_count' failed with materialization in maria-5.3-mwl89

[Philip Stoev]

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

backtrace:

JOIN::choose_subquery_plan(unsigned long long)+0x35e) [0x83c358c]
) [0x830dcd2]
JOIN::optimize()+0x999) [0x830620b]
st_select_lex::optimize_unflattened_subqueries()+0x104) [0x81aec14]
JOIN::optimize_unflattened_subqueries()+0x17) [0x83c322b]
JOIN::optimize()+0x2212) [0x8307a84]
mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x33d) [0x830ba4c]
mysql_derived_filling(THD*, st_lex*, TABLE_LIST*)+0x1e3) [0x844985c]
mysql_handle_derived(st_lex*, bool (*)(THD*, st_lex*, TABLE_LIST*))+0x73) [0x8449197]
open_and_lock_tables_derived(THD*, TABLE_LIST*, bool)+0x1c4) [0x82ecf7e]
open_and_lock_tables(THD*, TABLE_LIST*)+0x20) [0x82a8a07]
) [0x82a1dd8]
mysql_execute_command(THD*)+0x719) [0x8298ffb]
mysql_parse(THD*, char*, unsigned int, char const**)+0x26f) [0x82a4584]
dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x8bc) [0x8296c93]
do_command(THD*)+0x271) [0x8296140]
handle_one_connection+0x152) [0x829321e]

test case:

SET SESSION optimizer_switch = 'materialization=on';
CREATE TABLE t1 ( pk int(11), PRIMARY KEY (pk)) ;
CREATE TABLE t2 ( f2 int(11)) ;
CREATE TABLE t3 ( f1 int(11), f3 varchar(1), KEY (f1)) ;
INSERT INTO t3 VALUES (7,'f');

SELECT * FROM (
        SELECT t1.* FROM t3
        RIGHT JOIN t1 ON t1.pk = t3.f1
        WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 )
) AS a2;

revision-id: <email address hidden>
date: 2011-02-03 17:00:28 +0200
build-date: 2011-02-07 22:07:32 +0200
revno: 2883
branch-nick: maria-5.3-mwl89

not repeatable with maria-5.3

[Timour Katchaounov (timour)]

The problem query was reduced to:

SELECT t1.*
FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1
WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 )

Both the RIGHT JOIN and the OR are needed because
otherwise the optimizer detects earlier that the subquery
result is empty.

[Timour Katchaounov (timour)]

Analysis:

The function best_access_path() computes the number or records as
follows:
----------------------------------------
...
      if (rec < MATCHING_ROWS_IN_OTHER_TABLE)
        rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables
...
              if (table->quick_keys.is_set(key))
                records= (double) table->quick_rows[key];
              else
              {
                /* quick_range couldn't use key! */
                records= (double) s->records/rec;
              }
----------------------------------------
Above MATCHING_ROWS_IN_OTHER_TABLE == 10, and s->records == 1,
thus we get an estimated 0.1 records. As a result JOIN::get_partial_join_cost()
for the outer query computes outer_record_count == 0.1 records, which is
meaningless in this context.

Solution:
Round estimates that are < 1 to 1.