Assertion `(engine->uncacheable() & ~8) || ! engine->is_executed() || with_recursive_reference' failed in Item_in_subselect::val_bool

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;

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

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

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