Assertion `!created && key_to_save < (int)s->keys' failed in st_table::use_index with derived_with_keys=ON, join_cache_level=8

[Philip Stoev]

mysqld: table.cc:5376: void st_table::use_index(int): Assertion `!created && key_to_save < (int)s->keys' failed.

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0831853e in st_table::use_index (this=0xae726378, key_to_save=0) at table.cc:5376
#10 0x083305f5 in JOIN::drop_unused_derived_keys (this=0xae735b50) at sql_select.cc:8543
#11 0x0831c9ce in JOIN::optimize (this=0xae735b50) at sql_select.cc:1120
#12 0x081b6a72 in st_select_lex::optimize_unflattened_subqueries (this=0xa136120) at sql_lex.cc:3126
#13 0x083e5623 in JOIN::optimize_unflattened_subqueries (this=0xae72dfe0) at opt_subselect.cc:4382
#14 0x0831e502 in JOIN::optimize (this=0xae72dfe0) at sql_select.cc:1621
#15 0x08322709 in mysql_select (thd=0xa1346e0, rref_pointer_array=0xa13625c, tables=0xae7127c8, wild_num=1, fields=..., conds=0xae713b90, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae7141e0, unit=0xa135e40, select_lex=0xa136120)
    at sql_select.cc:2886
#16 0x0831a55f in handle_select (thd=0xa1346e0, lex=0xa135de4, result=0xae7141e0, setup_tables_done_option=0) at sql_select.cc:283
#17 0x082b4d18 in execute_sqlcom_select (thd=0xa1346e0, all_tables=0xae7127c8) at sql_parse.cc:5112
#18 0x082abad5 in mysql_execute_command (thd=0xa1346e0) at sql_parse.cc:2250
#19 0x082b7347 in mysql_parse (thd=0xa1346e0,
    rawbuf=0xae7125f0 "SELECT * \nFROM t1\nWHERE t1.b <> ANY (\nSELECT t2.b\nFROM t2\nLEFT JOIN v3 ON ( v3.a = t2.a )\n)", length=92,
    found_semicolon=0xae8ff228) at sql_parse.cc:6112
#20 0x082a9724 in dispatch_command (command=COM_QUERY, thd=0xa1346e0,
    packet=0xa18d321 "SELECT * \nFROM t1\nWHERE t1.b <> ANY (\nSELECT t2.b\nFROM t2\nLEFT JOIN v3 ON ( v3.a = t2.a )\n)", packet_length=92)
    at sql_parse.cc:1221
#21 0x082a8b7f in do_command (thd=0xa1346e0) at sql_parse.cc:916
#22 0x082a5b63 in handle_one_connection (arg=0xa1346e0) at sql_connect.cc:1191
#23 0x00821919 in start_thread () from /lib/libpthread.so.0
#24 0x0076acce in clone () from /lib/libc.so.6

EXPLAIN crashes.

minimal switch: derived_with_keys=ON, join_cache_level=8
full switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

 bzr version-info
revision-id: <email address hidden>
date: 2011-10-23 05:46:03 -0700
build-date: 2011-10-24 12:04:51 +0300
revno: 3248
branch-nick: maria-5.3

test case:

CREATE TABLE t1 (a int, b varchar(1)) ;

CREATE TABLE t2 (a int, b varchar(1)) ;
INSERT INTO t2 VALUES (9,NULL),(6,'r');

CREATE TABLE t3 (a int(11));
INSERT INTO t3 VALUES (1);

CREATE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3;

SET SESSION optimizer_switch = 'derived_with_keys=on';
SET SESSION join_cache_level = 8;

SELECT *
FROM t1
WHERE t1.b <> ANY (
        SELECT t2.b
        FROM t2
        LEFT JOIN v3 ON ( v3.a = t2.a )
);

[Igor Babaev (igorb-seattle)]

The following query returns an unexpected result:

MariaDB [test]> SELECT t2.b FROM t2 LEFT JOIN v3 ON v3.a = t2.a;
ERROR 1030 (HY000): Got error 124 from storage engine

We have the same problems with SET SESSION join_cache_level = 4;