We're updating the issue view to help you get more done. 

Assertion `example' failed in Item_cache::is_expensive_processor with a 2-level IN subquery

Description

1 2 mysqld: item.h:3759: virtual bool Item_cache::is_expensive_processor(uchar*): Assertion `example' failed. [ERROR] mysqld got signal 6 ;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 #6 0x00007f896f21cd4d in __GI___assert_fail (assertion=0xcb12d3 "example", file=<optimized out>, line=3759, function=<optimized out>) at assert.c:81 #7 0x00000000005d0622 in Item_cache::is_expensive_processor (this=0x1c9d618, arg=0x0) at item.h:3759 #8 0x00000000005cbd97 in Item::walk (this=0x1c9d618, processor=&virtual Item::is_expensive_processor(unsigned char*), walk_subquery=false, arg=0x0) at item.h:985 #9 0x00000000005e3087 in Item_func::walk (this=0x1c96bd8, processor=&virtual table offset 632, walk_subquery=false, argument=0x0) at item_func.cc:276 #10 0x00000000005cc261 in Item::is_expensive (this=0x1c96bd8) at item.h:1277 #11 0x000000000075f5c5 in remove_eq_conds (thd=0x1bf4b10, cond=0x1c96bd8, cond_value=0x7f896651f0a8) at sql_select.cc:13182 #12 0x000000000075f00b in remove_eq_conds (thd=0x1bf4b10, cond=0x1c96d78, cond_value=0x1c98dd0) at sql_select.cc:13065 #13 0x000000000075eed4 in optimize_cond (join=0x1c98ab8, conds=0x1c96d78, join_list=0x1c95938, cond_value=0x1c98dd0, cond_equal=0x1c98f30) at sql_select.cc:13032 #14 0x000000000073f9c6 in JOIN::optimize (this=0x1c98ab8) at sql_select.cc:1016 #15 0x0000000000598191 in st_select_lex::optimize_unflattened_subqueries (this=0x1c94a58) at sql_lex.cc:3161 #16 0x00000000008351e5 in JOIN::optimize_unflattened_subqueries (this=0x1c98328) at opt_subselect.cc:4880 #17 0x0000000000741ac2 in JOIN::optimize (this=0x1c98328) at sql_select.cc:1554 #18 0x0000000000598191 in st_select_lex::optimize_unflattened_subqueries (this=0x1bf75d0) at sql_lex.cc:3161 #19 0x00000000008351e5 in JOIN::optimize_unflattened_subqueries (this=0x1c97778) at opt_subselect.cc:4880 #20 0x0000000000741ac2 in JOIN::optimize (this=0x1c97778) at sql_select.cc:1554 #21 0x0000000000746cf5 in mysql_select (thd=0x1bf4b10, rref_pointer_array=0x1bf7878, tables=0x1c94628, wild_num=1, fields=..., conds=0x1c97358, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x1c97728, unit=0x1bf70a8, select_lex=0x1bf75d0) at sql_select.cc:2969 #22 0x000000000073d7a3 in handle_select (thd=0x1bf4b10, lex=0x1bf7008, result=0x1c97728, setup_tables_done_option=0) at sql_select.cc:286 #23 0x00000000006c8c3b in execute_sqlcom_select (thd=0x1bf4b10, all_tables=0x1c94628) at sql_parse.cc:5157 #24 0x00000000006bf9fa in mysql_execute_command (thd=0x1bf4b10) at sql_parse.cc:2290 #25 0x00000000006cb693 in mysql_parse (thd=0x1bf4b10, rawbuf=0x1c7fab8 "SELECT * FROM t1 WHERE a1 IN ( \nSELECT a2 FROM t2 WHERE a2 IN ( \nSELECT a3 FROM t3 WHERE b2 = b1 AND b2 <= b1 ORDER BY b3 \n)\n)", length=126, found_semicolon=0x7f89665207e0) at sql_parse.cc:6158 #26 0x00000000006bd1a0 in dispatch_command (command=COM_QUERY, thd=0x1bf4b10, packet=0x1c62871 "SELECT * FROM t1 WHERE a1 IN ( \nSELECT a2 FROM t2 WHERE a2 IN ( \nSELECT a3 FROM t3 WHERE b2 = b1 AND b2 <= b1 ORDER BY b3 \n)\n)", packet_length=126) at sql_parse.cc:1228 #27 0x00000000006bc4b4 in do_command (thd=0x1bf4b10) at sql_parse.cc:923 #28 0x00000000006b8f0d in handle_one_connection (arg=0x1bf4b10) at sql_connect.cc:1218
1 2 3 4 5 Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x1c7fab8): SELECT * FROM t1 WHERE a1 IN ( SELECT a2 FROM t2 WHERE a2 IN ( SELECT a3 FROM t3 WHERE b2 = b1 AND b2 <= b1 ORDER BY b3 ) ) Connection ID (thread ID): 1 Status: NOT_KILLED
1 2 3 4 branch maria/5.3 revision-id: sanja@montyprogram.com-20121205190600-fzhla1o8ecu3hf20 date: 2012-12-05 21:06:00 +0200 revno: 3606

Reproducible on maria/5.3, maria/5.5 and maria/10.0. Not reproducible on maria/5.2.
Minimal optimizer_switch: in_to_exists=on
Also reproducible with the default optimizer_switch:

1 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,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=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAIN also crashes.

Test case:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SET optimizer_switch = 'in_to_exists=on'; CREATE TABLE t1 (a1 INT, b1 TIME) ENGINE=MyISAM; INSERT INTO t1 VALUES (4,'21:22:34'),(6,'10:50:38'); CREATE TABLE t2 (a2 INT, b2 TIME) ENGINE=MyISAM; INSERT INTO t2 VALUES (8, '06:17:39'); CREATE TABLE t3 (a3 INT, b3 TIME) ENGINE=MyISAM; INSERT INTO t3 VALUES (1,'00:00:01'),(7,'00:00:02'); SELECT * FROM t1 WHERE a1 IN ( SELECT a2 FROM t2 WHERE a2 IN ( SELECT a3 FROM t3 WHERE b2 = b1 AND b2 <= b1 ORDER BY b3 ) );

It seems to be a debug-only assertion, no visible problems on a release build, and the scenario is an edge test case, so I'm setting it to Minor

Environment

None

Status

Assignee

Timour Katchaounov

Reporter

Elena Stepanova

Labels

External issue ID

None

External issue ID

None

Fix versions

Affects versions

5.5.28a
10.0.0
5.3.11

Priority

Minor