[MDEV-3438] LP:885162 - Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8 - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
- Launchpad

Description

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' ) ;

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

LPexportBug885162.xml
17 kB
2012-10-03 11:32

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Timour Katchaounov added a comment - 2011-11-03 14:48

Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8
The problem is in calling add_key_field for the whole UNION
in JOIN::reoptimize.

Show

Timour Katchaounov added a comment - 2011-11-03 14:48 Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8 The problem is in calling add_key_field for the whole UNION in JOIN::reoptimize.

Hide

Permalink

Igor Babaev added a comment - 2011-11-03 15:14

Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8
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

Show

Igor Babaev added a comment - 2011-11-03 15:14 Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8 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

Hide

Permalink

Igor Babaev added a comment - 2011-11-03 15:20

Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8
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.

Show

Igor Babaev added a comment - 2011-11-03 15:20 Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8 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.

Hide

Permalink

Philip Stoev added a comment - 2011-11-08 08:16

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

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

Show

Philip Stoev added a comment - 2011-11-08 08:16 Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8 see also: https://bugs.launchpad.net/maria/+bug/887458

Hide

Permalink

Timour Katchaounov added a comment - 2011-11-29 21:01

Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8
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.

Show

Timour Katchaounov added a comment - 2011-11-29 21:01 Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8 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.

Hide

Permalink

Oleksandr Byelkin added a comment - 2011-11-29 21:05

Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8
Fixed by the patch which fixed LP BUG#859375 and LP BUG#887458

Show

Oleksandr Byelkin added a comment - 2011-11-29 21:05 Re: Got error 124 from storage engine with UNION inside subquery and join_cache_level=3..8 Fixed by the patch which fixed LP BUG#859375 and LP BUG#887458

Hide

Permalink

Rasmus Johansson added a comment - 2011-11-29 21:09

Launchpad bug id: 885162

Show

Rasmus Johansson added a comment - 2011-11-29 21:09 Launchpad bug id: 885162

People

Assignee:

Oleksandr Byelkin

Reporter:

Philip Stoev

Votes:

0 Vote for this issue

Watchers:

0 Start watching this issue

Dates

Created:

2011-11-02 11:23

Updated:

2015-02-02 21:55

Resolved:

2012-10-04 12:52

Agile

View on Board