Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8

[Philip Stoev]

When executing the following query:

SELECT * FROM t1 WHERE t1.f1 IN ( SELECT 'k' UNION SELECT 'e' ) ;

mysqld returned:

ERROR 1030 (HY000): Got error 124 from storage engine

backtrace:

#0 my_error (nr=1030, MyFlags=0) at my_error.c:81
#1 0x083f34ef in handler::print_error (this=0xa7550990, error=124, errflag=0) at handler.cc:2981
#2 0x0833eb0f in report_error (table=0xa754fa18, error=124) at sql_select.cc:15468
#3 0x0833f549 in join_read_key2 (thd=0xaf82a20, tab=0xa7571840, table=0xa754fa18, table_ref=0xa757199c) at sql_select.cc:15731
#4 0x0833f3da in join_read_key (tab=0xa7571840) at sql_select.cc:15692
#5 0x0833e023 in sub_select (join=0xa7576490, join_tab=0xa7571840, end_of_records=false) at sql_select.cc:15129
#6 0x0833d8d0 in do_select (join=0xa7576490, fields=0xa754c028, table=0x0, procedure=0x0) at sql_select.cc:14795
#7 0x08322156 in JOIN::exec (this=0xa7576490) at sql_select.cc:2679
#8 0x08322982 in mysql_select (thd=0xaf82a20, rref_pointer_array=0xa754c414, tables=0xa754bdcc, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=268435456, result=0xa754c628, unit=0xa754bd98, select_lex=0xa754c2d8) at sql_select.cc:2900
#9 0x0846fb8c in st_select_lex_unit::exec (this=0xa754bd98) at sql_union.cc:724
#10 0x0824d00d in subselect_union_engine::exec (this=0xa754c640) at item_subselect.cc:3009
#11 0x08246bc9 in Item_subselect::exec (this=0xa754c538) at item_subselect.cc:587
#12 0x08247092 in Item_in_subselect::exec (this=0xa754c538) at item_subselect.cc:742
#13 0x08248be6 in Item_in_subselect::val_bool (this=0xa754c538) at item_subselect.cc:1455
#14 0x081e2714 in Item::val_bool_result (this=0xa754c538) at item.h:843
#15 0x0820ec74 in Item_in_optimizer::val_int (this=0xa754c8c0) at item_cmpfunc.cc:1715
#16 0x08320189 in JOIN::exec (this=0xa75619e0) at sql_select.cc:2120
#17 0x08322982 in mysql_select (thd=0xaf82a20, rref_pointer_array=0xaf8459c, tables=0xa754b8b8, wild_num=1, fields=..., conds=0xa754c538, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xa754c6d0, unit=0xaf84180, select_lex=0xaf84460)
    at sql_select.cc:2900
#18 0x0831a87f in handle_select (thd=0xaf82a20, lex=0xaf84124, result=0xa754c6d0, setup_tables_done_option=0) at sql_select.cc:283
#19 0x082b4fec in execute_sqlcom_select (thd=0xaf82a20, all_tables=0xa754b8b8) at sql_parse.cc:5112
#20 0x082abda9 in mysql_execute_command (thd=0xaf82a20) at sql_parse.cc:2250
#21 0x082b762d in mysql_parse (thd=0xaf82a20, rawbuf=0xa754b718 "SELECT * FROM t1 WHERE t1.f1 IN ( SELECT 'k' UNION SELECT 'e' )", length=64,
    found_semicolon=0x91616228) at sql_parse.cc:6113
#22 0x082a99f8 in dispatch_command (command=COM_QUERY, thd=0xaf82a20, packet=0xafa18e1 "SELECT * FROM t1 WHERE t1.f1 IN ( SELECT 'k' UNION SELECT 'e' )",
    packet_length=64) at sql_parse.cc:1221
#23 0x082a8e53 in do_command (thd=0xaf82a20) at sql_parse.cc:916
#24 0x082a5e37 in handle_one_connection (arg=0xaf82a20) at sql_connect.cc:1191
#25 0x00821919 in start_thread () from /lib/libpthread.so.0
#26 0x0076acce in clone () from /lib/libc.so.6

explain:

1 PRIMARY t1 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL

minimal switch: join_cache_level=3
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=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,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-11-02 13:51:47 +0400
build-date: 2011-11-02 13:22:20 +0200
revno: 3264
branch-nick: maria-5.3

test case:

CREATE TABLE t1 (
  f1 varchar(1) DEFAULT NULL
);
INSERT INTO t1 VALUES ('c');
SET SESSION join_cache_level=8;
SELECT * FROM t1 WHERE t1.f1 IN ( SELECT 'k' UNION SELECT 'e' ) ;

[Timour Katchaounov (timour)]

The problem is in calling add_key_field for the whole UNION
in JOIN::reoptimize.

[Igor Babaev (igorb-seattle)]

This bug can be reproduced with the following test case that does not use constant tables:

--echo #
--echo # Bug #802860: UNION with IN subquery and hash join enabled
--echo #

CREATE TABLE t1 (a varchar(1));
INSERT INTO t1 VALUES ('c'), ('e');

CREATE TABLE t2 (a varchar(1));
INSERT INTO t2 VALUES ('k'), ('e'), ('h'), ('g');

SET SESSION join_cache_level=1;
SELECT * FROM t1
  WHERE t1.a IN (SELECT a FROM t2 WHERE a>='k' UNION
                 SELECT a FROM t2 WHERE a<='e');

SET SESSION join_cache_level=3;
SELECT * FROM t1
  WHERE t1.a IN (SELECT a FROM t2 WHERE a>='k' UNION
                 SELECT a FROM t2 WHERE a<='e');

SET SESSION join_cache_level = DEFAULT;

DROP TABLE t1,t2;

We get here:

MariaDB [test]> SET SESSION join_cache_level=3;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT * FROM t1
    -> WHERE t1.a IN (SELECT a FROM t2 WHERE a>='k' UNION
    -> SELECT a FROM t2 WHERE a<='e');
ERROR 1030 (HY000): Got error 124 from storage engine

[Igor Babaev (igorb-seattle)]

This problem appears because the current code tries to employ a KEYUSE element to access the temporary table
created for the union. The code should not do it as IN into EXISTS transformation has already pushed the used equality into
each select of the union.

[Philip Stoev (pstoev-askmonty)]

see also:

https://bugs.launchpad.net/maria/+bug/887458

[Timour Katchaounov (timour)]

The bug is no longer reproducible after the fixes for
LP BUG#859375 and LP BUG#887458. It is in fact a
manifestation of the same problem.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Fixed by the patch which fixed LP BUG#859375 and LP BUG#887458