[MDEV-22375] Assertion `(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference' failed in Item_in_subselect::val_bool Created: 2020-04-27  Updated: 2023-12-12

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: affects-tests

Attachments: File stack.log    
Issue Links:
Duplicate
duplicates MDEV-29232 Assertion `(engine->uncacheable() & ~... Closed
Relates
relates to MDEV-32866 Debug assertion failure in Item_subse... Open

 Description    

CREATE TABLE t1 (a INT, b INT, c INT, KEY (c)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (1,0,1),(2,0,2);
 
 
 
CREATE TABLE t2 (d INT) ENGINE=MyISAM;
 
INSERT INTO t2 VALUES (10),(20);
 
 
 
CREATE TABLE t3 (e INT, f INT, KEY (e)) ENGINE=MyISAM;
 
 
 
CREATE TABLE t4 (g INT) ENGINE=MyISAM;
 
INSERT INTO t4 VALUES (4);
 
 
 
SELECT DISTINCT a FROM t1 LEFT JOIN (
 
    SELECT * FROM t3 WHERE t3.f = t3.e AND t3.f >= 0
 
  ) AS sq ON (sq.e = t1.b)
 
  WHERE t1.b IN (
 
    SELECT g FROM t4
 
    WHERE t1.c IN ( g >= ALL ( SELECT g FROM t4 HAVING g = 5 ) )
 
  );
 
 
 
# Cleanup
 
DROP TABLE t1,t2,t3,t4;

10.4 81511b47

 
mysqld: /data/src/10.4/sql/item_subselect.cc:1802: virtual bool Item_in_subselect::val_bool(): Assertion `(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference' failed.
 
200427 14:49:15 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f1e95360102 in __GI___assert_fail (assertion=0x55645db11a20 "(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference", file=0x55645db106a0 "/data/src/10.4/sql/item_subselect.cc", line=1802, function=0x55645db15a00 <Item_in_subselect::val_bool()::__PRETTY_FUNCTION__> "virtual bool Item_in_subselect::val_bool()") at assert.c:101
 
#8  0x000055645c35b9a4 in Item_in_subselect::val_bool (this=0x62900023fe78) at /data/src/10.4/sql/item_subselect.cc:1801
 
#9  0x000055645b6e25f0 in Item::val_bool_result (this=0x62900023fe78) at /data/src/10.4/sql/item.h:1548
 
#10 0x000055645c206be8 in Item_in_optimizer::val_int (this=0x629000244d80) at /data/src/10.4/sql/item_cmpfunc.cc:1643
 
#11 0x000055645beaf036 in Type_handler_int_result::Item_val_bool (this=0x55645e79a420 <type_handler_bool>, item=0x629000244d80) at /data/src/10.4/sql/sql_type.cc:4416
 
#12 0x000055645b6e2150 in Item::val_bool (this=0x629000244d80) at /data/src/10.4/sql/item.h:1460
 
#13 0x000055645c1f5bb0 in Item_func_not_all::val_int (this=0x629000240100) at /data/src/10.4/sql/item_cmpfunc.cc:219
 
#14 0x000055645c20070f in Arg_comparator::compare_e_int (this=0x629000251120) at /data/src/10.4/sql/item_cmpfunc.cc:1033
 
#15 0x000055645c23ee22 in Arg_comparator::compare (this=0x629000251120) at /data/src/10.4/sql/item_cmpfunc.h:104
 
#16 0x000055645c208ced in Item_func_equal::val_int (this=0x629000251058) at /data/src/10.4/sql/item_cmpfunc.cc:1786
 
#17 0x000055645beaf036 in Type_handler_int_result::Item_val_bool (this=0x55645e79a420 <type_handler_bool>, item=0x629000251058) at /data/src/10.4/sql/sql_type.cc:4416
 
#18 0x000055645b6e2150 in Item::val_bool (this=0x629000251058) at /data/src/10.4/sql/item.h:1460
 
#19 0x000055645c22a1d8 in Item_cond_and::val_int (this=0x629000250e20) at /data/src/10.4/sql/item_cmpfunc.cc:5297
 
#20 0x000055645babe584 in evaluate_join_record (join=0x6290002418e8, join_tab=0x62900024c9d8, error=0) at /data/src/10.4/sql/sql_select.cc:20452
 
#21 0x000055645babd8f7 in sub_select (join=0x6290002418e8, join_tab=0x62900024c9d8, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20357
 
#22 0x000055645babba08 in do_select (join=0x6290002418e8, procedure=0x0) at /data/src/10.4/sql/sql_select.cc:19895
 
#23 0x000055645ba4c809 in JOIN::exec_inner (this=0x6290002418e8) at /data/src/10.4/sql/sql_select.cc:4459
 
#24 0x000055645ba49e7c in JOIN::exec (this=0x6290002418e8) at /data/src/10.4/sql/sql_select.cc:4241
 
#25 0x000055645ba4dd62 in mysql_select (thd=0x62b00005b270, tables=0x62b000062aa8, wild_num=0, fields=..., conds=0x629000240c38, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748609, result=0x6290002418b8, unit=0x62b00005f198, select_lex=0x62b0000624a0) at /data/src/10.4/sql/sql_select.cc:4673
 
#26 0x000055645ba1ff9a in handle_select (thd=0x62b00005b270, lex=0x62b00005f0d8, result=0x6290002418b8, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:410
 
#27 0x000055645b994e32 in execute_sqlcom_select (thd=0x62b00005b270, all_tables=0x62b000062aa8) at /data/src/10.4/sql/sql_parse.cc:6359
 
#28 0x000055645b982845 in mysql_execute_command (thd=0x62b00005b270) at /data/src/10.4/sql/sql_parse.cc:3898
 
#29 0x000055645b99defd in mysql_parse (thd=0x62b00005b270, rawbuf=0x62b000062290 "SELECT DISTINCT a FROM t1 LEFT JOIN (\nSELECT * FROM t3 WHERE t3.f = t3.e AND t3.f >= 0\n) AS sq ON (sq.e = t1.b)\nWHERE t1.b IN (\nSELECT g FROM t4\nWHERE t1.c IN ( g >= ALL ( SELECT g FROM t4 HAVING g = "..., length=207, parser_state=0x7f1e8c3b6a70, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7900
 
#30 0x000055645b9755b0 in dispatch_command (command=COM_QUERY, thd=0x62b00005b270, packet=0x629000226271 "", packet_length=207, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1841
 
#31 0x000055645b97202f in do_command (thd=0x62b00005b270) at /data/src/10.4/sql/sql_parse.cc:1359
 
#32 0x000055645bd46ef3 in do_handle_one_connection (connect=0x6110000083f0) at /data/src/10.4/sql/sql_connect.cc:1412
 
#33 0x000055645bd46795 in handle_one_connection (arg=0x6110000083f0) at /data/src/10.4/sql/sql_connect.cc:1316
 
#34 0x000055645d367331 in pfs_spawn_thread (arg=0x61600000c9f0) at /data/src/10.4/storage/perfschema/pfs.cc:1869
 
#35 0x00007f1e95bdafa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
 
#36 0x00007f1e954294cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Execution plan:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	t4	system	NULL	NULL	NULL	NULL	1	100.00	Using temporary
 
1	PRIMARY	t3	const	e	NULL	NULL	NULL	1	100.00	Impossible ON condition
 
1	PRIMARY	t1	ref	c	c	5	const	0	0.00	Using where
 
4	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING noticed after reading const tables
 
Warnings:
 
Note	1276	Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #1
 
Note	1003	/* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (dual) left join (`test`.`t3`) on(NULL >= 0 and multiple equal(4, `test`.`t1`.`b`, NULL, NULL)) where `test`.`t1`.`b` = 4 and `test`.`t1`.`c` = <cache>(<not>(<in_optimizer>(4,<exists>(/* select#4 */ select 4 from dual having 0))))

Switching off semijoin prevents the assertion failure.

Reproducible on 10.4, 10.5.
No obvious problem on a non-debug build.

The failure started happening on 10.4 branch after this commit:

commit 658128af43b4d7c6db445164f8ed25ed4d1e3109
 
Author: Igor Babaev
 
Date:   Sun Feb 3 14:56:12 2019 -0800
 
 
 
    MDEV-16188 Use in-memory PK filters built from range index scans



 Comments   
Comment by Ramesh Sivaraman [ 2022-06-09 ]

Another reduced test case to reproduce crash

CREATE TABLE t (a INT KEY,b CHAR(1)) ENGINE=MyISAM ;
 
INSERT INTO t VALUES (0,'1');
 
SELECT a FROM t WHERE '' IN (SELECT 'x' LIKE a having a LIKE a);

Leads to

10.4.26 96f4b4a55b449a29af7e4b70bebf0ff238ae7f80 (Debug)

 
mysqld: /test/10.4_dbg/sql/item_subselect.cc:1908: virtual bool Item_in_subselect::val_bool(): Assertion `(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference' failed.

10.4.26 96f4b4a55b449a29af7e4b70bebf0ff238ae7f80 (Debug)

 
Core was generated by `/test/GAL_MD070622-mariadb-10.4.26-linux-x86_64-dbg/bin/mysqld --no-defaults --'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=6)
 
    at ../sysdeps/unix/sysv/linux/pthread_kill.c:56
 
[Current thread is 1 (Thread 0x148e2c522700 (LWP 1983987))]
 
(gdb) bt
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:56
 
#1  0x000056021b806517 in my_write_core (sig=sig@entry=6) at /test/10.4_dbg/mysys/stacktrace.c:386
 
#2  0x000056021aff5995 in handle_fatal_signal (sig=6) at /test/10.4_dbg/sql/signal_handler.cc:356
 
#3  <signal handler called>
 
#4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#5  0x0000148e4701c859 in __GI_abort () at abort.c:79
 
#6  0x0000148e4701c729 in __assert_fail_base (fmt=0x148e471b2588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x56021bb0a680 "(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference", file=0x56021bb0a208 "/test/10.4_dbg/sql/item_subselect.cc", line=1908, function=<optimized out>) at assert.c:92
 
#7  0x0000148e4702df36 in __GI___assert_fail (assertion=assertion@entry=0x56021bb0a680 "(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference", file=file@entry=0x56021bb0a208 "/test/10.4_dbg/sql/item_subselect.cc", line=line@entry=1908, function=function@entry=0x56021bb0a5e8 "virtual bool Item_in_subselect::val_bool()") at assert.c:101
 
#8  0x000056021b0f4faa in Item_in_subselect::val_bool (this=0x148de8015338) at /test/10.4_dbg/sql/item_subselect.cc:1908
 
#9  0x000056021ac945c5 in Item::val_bool_result (this=<optimized out>) at /test/10.4_dbg/sql/item.h:1553
 
#10 0x000056021b04f9bc in Item_in_optimizer::val_int (this=0x148de8016fc0) at /test/10.4_dbg/sql/item_cmpfunc.cc:1647
 
#11 0x000056021adee664 in JOIN::exec_inner (this=this@entry=0x148de8015ec0) at /test/10.4_dbg/sql/sql_select.cc:4429
 
#12 0x000056021adef455 in JOIN::exec (this=this@entry=0x148de8015ec0) at /test/10.4_dbg/sql/sql_select.cc:4327
 
#13 0x000056021aded909 in mysql_select (thd=thd@entry=0x148de8000d90, tables=0x148de8013768, wild_num=0, fields=@0x148de80132c8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x148de8013720, last = 0x148de8013720, elements = 1}, <No data fields>}, conds=0x148de8015338, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x148de8015e98, unit=0x148de8004b28, select_lex=0x148de8013180) at /test/10.4_dbg/sql/sql_select.cc:4766
 
#14 0x000056021adedbba in handle_select (thd=thd@entry=0x148de8000d90, lex=lex@entry=0x148de8004a68, result=result@entry=0x148de8015e98, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.4_dbg/sql/sql_select.cc:436
 
#15 0x000056021ad67d5f in execute_sqlcom_select (thd=thd@entry=0x148de8000d90, all_tables=0x148de8013768) at /test/10.4_dbg/sql/sql_parse.cc:6449
 
#16 0x000056021ad72273 in mysql_execute_command (thd=thd@entry=0x148de8000d90) at /test/10.4_dbg/sql/sql_parse.cc:3963
 
#17 0x000056021ad7c67c in mysql_parse (thd=thd@entry=0x148de8000d90, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x148e2c521520, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.4_dbg/sql/sql_parse.cc:7995
 
#18 0x000056021ad7f200 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x148de8000d90, packet=packet@entry=0x148de801a1d1 "SELECT a FROM t WHERE '' IN (SELECT 'x' LIKE a having a LIKE a)", packet_length=packet_length@entry=63, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.4_dbg/sql/sql_class.h:1201
 
#19 0x000056021ad81cdc in do_command (thd=0x148de8000d90) at /test/10.4_dbg/sql/sql_parse.cc:1378
 
#20 0x000056021aea3e2c in do_handle_one_connection (connect=<optimized out>) at /test/10.4_dbg/sql/sql_connect.cc:1420
 
#21 0x000056021aea3eec in handle_one_connection (arg=<optimized out>) at /test/10.4_dbg/sql/sql_connect.cc:1316
 
#22 0x0000148e4752b609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#23 0x0000148e47119293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Comment by Ramesh Sivaraman [ 2022-08-10 ]

Found another test case with a slightly different stack that only crashes on the debug build.

CREATE TABLE t (c INT KEY );
 
INSERT INTO t VALUES(1),(3);
 
SELECT c FROM t WHERE c ORDER BY (1 IN ((SELECT c FROM t WHERE c IN (SELECT c FROM t WHERE c IN (SELECT (SELECT 3 FROM t)))))) IN (SELECT c FROM t WHERE c IN (1));

Leads to

10.9.2 bfdc4ff22ecf626eb46479e1a0dc1049d61a8d78 (Debug)

 
mysqld: /test/10.9_dbg/sql/item_subselect.cc:1986: virtual bool Item_in_subselect::val_bool(): Assertion `(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference' failed.

10.9.2 bfdc4ff22ecf626eb46479e1a0dc1049d61a8d78 (Debug)

 
Core was generated by `/test/mtest/MD090822-mariadb-10.9.2-linux-x86_64-dbg/bin/mysqld --no-defaults -'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x14fa4ce6c700 (LWP 1838214))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x000014fa65815859 in __GI_abort () at abort.c:79
 
#2  0x000014fa65815729 in __assert_fail_base (fmt=0x14fa659ab588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55a9812aefc8 "(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference", file=0x55a9812ae8b8 "/test/10.9_dbg/sql/item_subselect.cc", line=1986, function=<optimized out>) at assert.c:92
 
#3  0x000014fa65826fd6 in __GI___assert_fail (assertion=assertion@entry=0x55a9812aefc8 "(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference", file=file@entry=0x55a9812ae8b8 "/test/10.9_dbg/sql/item_subselect.cc", line=line@entry=1986, function=function@entry=0x55a9812aef30 "virtual bool Item_in_subselect::val_bool()") at assert.c:101
 
#4  0x000055a980908cd4 in Item_in_subselect::val_bool (this=0x14fa0c070a60) at /test/10.9_dbg/sql/item_subselect.cc:1986
 
#5  0x000055a9803e8ee5 in Item::val_bool_result (this=<optimized out>) at /test/10.9_dbg/sql/item.h:1783
 
#6  0x000055a9808516c0 in Item_in_optimizer::val_int (this=0x14fa0c0752e8) at /test/10.9_dbg/sql/item_cmpfunc.cc:1637
 
#7  0x000055a9803e8ea9 in Item::val_int_result (this=<optimized out>) at /test/10.9_dbg/sql/item.h:1779
 
#8  0x000055a9808141af in Item_cache_int::cache_value (this=0x14fa0c07da28) at /test/10.9_dbg/sql/item.cc:10125
 
#9  0x000055a9808512fe in Item_in_optimizer::fix_left (this=0x14fa0c07d960, thd=<optimized out>) at /test/10.9_dbg/sql/item_cmpfunc.cc:1335
 
#10 0x000055a98090cb78 in Item_in_subselect::single_value_transformer (this=this@entry=0x14fa0c072648, join=join@entry=0x14fa0c075560) at /test/10.9_dbg/sql/item_subselect.cc:2110
 
#11 0x000055a98090e9ec in Item_in_subselect::select_in_like_transformer (this=0x14fa0c072648, join=0x14fa0c075560) at /test/10.9_dbg/sql/item_subselect.cc:3483
 
#12 0x000055a98090ea61 in Item_in_subselect::select_transformer (this=<optimized out>, join=<optimized out>) at /test/10.9_dbg/sql/item_subselect.cc:2772
 
#13 0x000055a9806c8f0a in check_and_do_in_subquery_rewrites (join=join@entry=0x14fa0c075560) at /test/10.9_dbg/sql/opt_subselect.cc:745
 
#14 0x000055a98058a94b in JOIN::prepare (this=0x14fa0c075560, tables_init=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=skip_order_by@entry=false, group_init=<optimized out>, having_init=<optimized out>, proc_param_init=<optimized out>, select_lex_arg=<optimized out>, unit_arg=<optimized out>) at /test/10.9_dbg/sql/sql_select.cc:1554
 
#15 0x000055a98090fb81 in subselect_single_select_engine::prepare (this=0x14fa0c072870, thd=0x14fa0c000db8) at /test/10.9_dbg/sql/sql_lex.h:1362
 
#16 0x000055a98090f04f in Item_subselect::fix_fields (this=this@entry=0x14fa0c072648, thd_param=thd_param@entry=0x14fa0c000db8, ref=ref@entry=0x14fa0c0728d8) at /test/10.9_dbg/sql/item_subselect.cc:295
 
#17 0x000055a98090f494 in Item_in_subselect::fix_fields (this=0x14fa0c072648, thd_arg=0x14fa0c000db8, ref=0x14fa0c0728d8) at /test/10.9_dbg/sql/item_subselect.cc:3588
 
#18 0x000055a98055a671 in Item::fix_fields_if_needed (ref=<optimized out>, thd=0x14fa0c000db8, this=0x14fa0c072648) at /test/10.9_dbg/sql/item.h:1156
 
#19 Item::fix_fields_if_needed_for_scalar (ref=<optimized out>, thd=0x14fa0c000db8, this=0x14fa0c072648) at /test/10.9_dbg/sql/item.h:1148
 
#20 Item::fix_fields_if_needed_for_order_by (ref=<optimized out>, thd=0x14fa0c000db8, this=0x14fa0c072648) at /test/10.9_dbg/sql/item.h:1156
 
#21 find_order_in_list (thd=thd@entry=0x14fa0c000db8, ref_pointer_array=<optimized out>, tables=tables@entry=0x14fa0c0143a8, order=order@entry=0x14fa0c0728c8, fields=@0x14fa0c014068: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14fa0c014360, last = 0x14fa0c014360, elements = 1}, <No data fields>}, all_fields=@0x14fa0c072dd0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14fa0c014360, last = 0x14fa0c014360, elements = 1}, <No data fields>}, is_group_field=false, add_to_all_fields=true, from_window_spec=false) at /test/10.9_dbg/sql/sql_select.cc:25204
 
#22 0x000055a980586008 in setup_order (thd=thd@entry=0x14fa0c000db8, ref_pointer_array=<optimized out>, tables=tables@entry=0x14fa0c0143a8, fields=@0x14fa0c014068: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14fa0c014360, last = 0x14fa0c014360, elements = 1}, <No data fields>}, all_fields=@0x14fa0c072dd0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14fa0c014360, last = 0x14fa0c014360, elements = 1}, <No data fields>}, order=0x14fa0c0728c8, from_window_spec=false) at /test/10.9_dbg/sql/sql_select.cc:25251
 
#23 0x000055a98058a125 in setup_without_group (reserved=<optimized out>, hidden_group_fields=0x14fa0c072d7f, win_funcs=<optimized out>, win_specs=<optimized out>, group=<optimized out>, order=<optimized out>, conds=0x14fa0c072eb8, all_fields=@0x14fa0c072dd0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14fa0c014360, last = 0x14fa0c014360, elements = 1}, <No data fields>}, fields=<optimized out>, leaves=<optimized out>, tables=<optimized out>, ref_pointer_array=<optimized out>, thd=<optimized out>) at /test/10.9_dbg/sql/sql_select.cc:886
 
#24 JOIN::prepare (this=this@entry=0x14fa0c072a70, tables_init=tables_init@entry=0x14fa0c0143a8, conds_init=conds_init@entry=0x14fa0c014c40, og_num=og_num@entry=1, order_init=order_init@entry=0x14fa0c0728c8, skip_order_by=skip_order_by@entry=false, group_init=<optimized out>, having_init=<optimized out>, proc_param_init=<optimized out>, select_lex_arg=<optimized out>, unit_arg=<optimized out>) at /test/10.9_dbg/sql/sql_select.cc:1454
 
#25 0x000055a9805a1022 in mysql_select (thd=thd@entry=0x14fa0c000db8, tables=0x14fa0c0143a8, fields=@0x14fa0c014068: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14fa0c014360, last = 0x14fa0c014360, elements = 1}, <No data fields>}, conds=0x14fa0c014c40, og_num=1, order=0x14fa0c0728c8, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x14fa0c072a48, unit=0x14fa0c004fd8, select_lex=0x14fa0c013dc8) at /test/10.9_dbg/sql/sql_select.cc:5032
 
#26 0x000055a9805a1240 in handle_select (thd=thd@entry=0x14fa0c000db8, lex=lex@entry=0x14fa0c004f00, result=result@entry=0x14fa0c072a48, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.9_dbg/sql/sql_select.cc:579
 
#27 0x000055a98050cf62 in execute_sqlcom_select (thd=thd@entry=0x14fa0c000db8, all_tables=0x14fa0c0143a8) at /test/10.9_dbg/sql/sql_parse.cc:6261
 
#28 0x000055a980519258 in mysql_execute_command (thd=thd@entry=0x14fa0c000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.9_dbg/sql/sql_parse.cc:3945
 
#29 0x000055a98050723e in mysql_parse (thd=thd@entry=0x14fa0c000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14fa4ce6b330) at /test/10.9_dbg/sql/sql_parse.cc:8037
 
#30 0x000055a98051480c in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14fa0c000db8, packet=packet@entry=0x14fa0c00b6c9 "SELECT c FROM t WHERE c ORDER BY (1 IN ((SELECT c FROM t WHERE c IN (SELECT c FROM t WHERE c IN (SELECT (SELECT 3 FROM t)))))) IN (SELECT c FROM t WHERE c IN (1))", packet_length=packet_length@entry=162, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_class.h:1364
 
#31 0x000055a980516f14 in do_command (thd=0x14fa0c000db8, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_parse.cc:1407
 
#32 0x000055a980676b68 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55a982cdc8b8, put_in_cache=put_in_cache@entry=true) at /test/10.9_dbg/sql/sql_connect.cc:1418
 
#33 0x000055a980677071 in handle_one_connection (arg=0x55a982cdc8b8) at /test/10.9_dbg/sql/sql_connect.cc:1312
 
#34 0x000014fa65d26609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#35 0x000014fa65912133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Comment by Ramesh Sivaraman [ 2022-08-10 ]

Please also test the patch with this not-reduced test case stack.log

Comment by Roel Van de Paar [ 2022-08-10 ]

Updated versions report

CREATE TABLE t (a INT KEY,b CHAR(1)) ENGINE=MyISAM ;
 
INSERT INTO t VALUES (0,'1');
 
SELECT a FROM t WHERE '' IN (SELECT 'x' LIKE a having a LIKE a);

Leads to:

10.10.0 e1caa4bd5e8b4645944b85d4b603bf9fc9ef6ca4 (Debug)

 
mysqld: /test/10.10_dbg/sql/item_subselect.cc:1986: virtual bool Item_in_subselect::val_bool(): Assertion `(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference' failed.

10.10.0 e1caa4bd5e8b4645944b85d4b603bf9fc9ef6ca4 (Debug)

 
Core was generated by `/test/MD290722-mariadb-10.10.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x14614aff7700 (LWP 1749290))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x000014617e3a3859 in __GI_abort () at abort.c:79
 
#2  0x000014617e3a3729 in __assert_fail_base (fmt=0x14617e539588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x559aedfcc4a8 "(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference", file=0x559aedfcbd98 "/test/10.10_dbg/sql/item_subselect.cc", line=1986, function=<optimized out>) at assert.c:92
 
#3  0x000014617e3b4fd6 in __GI___assert_fail (assertion=assertion@entry=0x559aedfcc4a8 "(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference", file=file@entry=0x559aedfcbd98 "/test/10.10_dbg/sql/item_subselect.cc", line=line@entry=1986, function=function@entry=0x559aedfcc410 "virtual bool Item_in_subselect::val_bool()") at assert.c:101
 
#4  0x0000559aed62b40c in Item_in_subselect::val_bool (this=0x1460bc015e48) at /test/10.10_dbg/sql/item_subselect.cc:1986
 
#5  0x0000559aed108f11 in Item::val_bool_result (this=<optimized out>) at /test/10.10_dbg/sql/item.h:1783
 
#6  0x0000559aed5744c4 in Item_in_optimizer::val_int (this=0x1460bc06bee0) at /test/10.10_dbg/sql/item_cmpfunc.cc:1637
 
#7  0x0000559aed2c393d in JOIN::exec_inner (this=this@entry=0x1460bc0169b0) at /test/10.10_dbg/sql/sql_select.cc:4686
 
#8  0x0000559aed2c4930 in JOIN::exec (this=this@entry=0x1460bc0169b0) at /test/10.10_dbg/sql/sql_select.cc:4582
 
#9  0x0000559aed2c26b4 in mysql_select (thd=thd@entry=0x1460bc000db8, tables=0x1460bc0142f8, fields=@0x1460bc013fb8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x1460bc0142b0, last = 0x1460bc0142b0, elements = 1}, <No data fields>}, conds=0x1460bc015e48, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x1460bc016988, unit=0x1460bc004ff0, select_lex=0x1460bc013d18) at /test/10.10_dbg/sql/sql_select.cc:5062
 
#10 0x0000559aed2c2eaa in handle_select (thd=thd@entry=0x1460bc000db8, lex=lex@entry=0x1460bc004f18, result=result@entry=0x1460bc016988, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.10_dbg/sql/sql_select.cc:581
 
#11 0x0000559aed22d258 in execute_sqlcom_select (thd=thd@entry=0x1460bc000db8, all_tables=0x1460bc0142f8) at /test/10.10_dbg/sql/sql_parse.cc:6261
 
#12 0x0000559aed23956a in mysql_execute_command (thd=thd@entry=0x1460bc000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.10_dbg/sql/sql_parse.cc:3945
 
#13 0x0000559aed227534 in mysql_parse (thd=thd@entry=0x1460bc000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14614aff6330) at /test/10.10_dbg/sql/sql_parse.cc:8037
 
#14 0x0000559aed234b1c in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x1460bc000db8, packet=packet@entry=0x1460bc00b6e9 "SELECT a FROM t WHERE '' IN (SELECT 'x' LIKE a having a LIKE a)", packet_length=packet_length@entry=63, blocking=blocking@entry=true) at /test/10.10_dbg/sql/sql_class.h:1366
 
#15 0x0000559aed237226 in do_command (thd=0x1460bc000db8, blocking=blocking@entry=true) at /test/10.10_dbg/sql/sql_parse.cc:1407
 
#16 0x0000559aed398744 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x559af0cd3d88, put_in_cache=put_in_cache@entry=true) at /test/10.10_dbg/sql/sql_connect.cc:1418
 
#17 0x0000559aed398c4d in handle_one_connection (arg=0x559af0cd3d88) at /test/10.10_dbg/sql/sql_connect.cc:1312
 
#18 0x000014617e8b4609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#19 0x000014617e4a0133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.4.26 (dbg), 10.5.17 (dbg), 10.6.9 (dbg), 10.7.5 (dbg), 10.8.4 (dbg), 10.9.2 (dbg), 10.10.0 (dbg)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.3.36 (dbg), 10.3.36 (opt), 10.4.26 (opt), 10.5.17 (opt), 10.6.9 (opt), 10.7.5 (opt), 10.8.4 (opt), 10.9.2 (opt), 10.10.0 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.38 (dbg), 5.7.38 (opt), 8.0.29 (dbg), 8.0.29 (opt)

Comment by Roel Van de Paar [ 2022-12-20 ]

Additional testcase

CREATE TABLE t (t INT);
 
INSERT INTO t VALUES (0),(t IN (SELECT t IN (SELECT 1 FROM (SELECT 1 AS t) AS t WHERE t IN (SELECT t HAVING NOT t))));

This testcase only crashes 10.5 to 10.11, debug, with UniqueID/stack:

(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference|SIGABRT|Item_in_subselect::val_bool|Item::val_bool_result|Item_in_optimizer::val_int|Type_handler_int_result::Item_val_bool

10.4 produces an error instead:

10.4.28 da03d8d99f7ff441cc1079dc074fde204751ef63 (Debug)

 
10.4.28-dbg>INSERT INTO t VALUES (0),(t IN (SELECT t IN (SELECT 1 FROM (SELECT 1 AS t) AS t WHERE t IN (SELECT t HAVING NOT t))));
 
ERROR 1054 (42S22): Unknown column 't' in 'IN/ALL/ANY subquery'

Generated at Thu Feb 08 09:14:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.