Assertion failure when moving equality from having to where

CREATE TABLE t (a CHAR(3)) CHARSET=sjis;

INSERT INTO t VALUES ('foo'),('bar');

SELECT LOAD_FILE('') AS f, a FROM t GROUP BY f, a HAVING f = a OR a != 'qux';

# Cleanup

DROP TABLE t;

mysqld: /data/src/10.4/sql/item.h:5224: virtual bool Item_func_or_sum::with_subquery() const: Assertion `fixed' failed.

210308 16:53:28 [ERROR] mysqld got signal 6 ;

#7  0x00007f4f1abfef36 in __GI___assert_fail (assertion=0x55ffc9ac2d1f "fixed", file=0x55ffc9ac29e3 "/data/src/10.4/sql/item.h", line=5224, function=0x55ffc9ac2d28 "virtual bool Item_func_or_sum::with_subquery() const") at assert.c:101

#8  0x000055ffc8d473a2 in Item_func_or_sum::with_subquery (this=0x7f4f04014d98) at /data/src/10.4/sql/item.h:5224

#9  0x000055ffc8e4ad11 in remove_const (join=0x7f4f04016c00, first_order=0x7f4f040157e8, cond=0x7f4f040161e0, change_list=true, simple_order=0x7f4f04016edc) at /data/src/10.4/sql/sql_select.cc:14158

#10 0x000055ffc8e2744d in JOIN::optimize_inner (this=0x7f4f04016c00) at /data/src/10.4/sql/sql_select.cc:2256

#11 0x000055ffc8e24e85 in JOIN::optimize (this=0x7f4f04016c00) at /data/src/10.4/sql/sql_select.cc:1620

#12 0x000055ffc8e303e3 in mysql_select (thd=0x7f4f04000d90, tables=0x7f4f04014ff8, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x0, group=0x7f4f040157e8, having=0x7f4f040161e0, proc_param=0x0, select_options=2147748608, result=0x7f4f04016bd8, unit=0x7f4f04004cc0, select_lex=0x7f4f04014860) at /data/src/10.4/sql/sql_select.cc:4693

#13 0x000055ffc8e1fde3 in handle_select (thd=0x7f4f04000d90, lex=0x7f4f04004c00, result=0x7f4f04016bd8, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:410

#14 0x000055ffc8de4e8f in execute_sqlcom_select (thd=0x7f4f04000d90, all_tables=0x7f4f04014ff8) at /data/src/10.4/sql/sql_parse.cc:6444

#15 0x000055ffc8ddb5d3 in mysql_execute_command (thd=0x7f4f04000d90) at /data/src/10.4/sql/sql_parse.cc:3963

#16 0x000055ffc8de8eb5 in mysql_parse (thd=0x7f4f04000d90, rawbuf=0x7f4f04014768 "SELECT LOAD_FILE('') AS f, a FROM t GROUP BY f, a HAVING f = a OR a != 'qux'", length=76, parser_state=0x7f4f14bcf550, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7985

#17 0x000055ffc8dd5121 in dispatch_command (command=COM_QUERY, thd=0x7f4f04000d90, packet=0x7f4f0400abc1 "SELECT LOAD_FILE('') AS f, a FROM t GROUP BY f, a HAVING f = a OR a != 'qux'", packet_length=76, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1855

#18 0x000055ffc8dd3989 in do_command (thd=0x7f4f04000d90) at /data/src/10.4/sql/sql_parse.cc:1373

#19 0x000055ffc8f63507 in do_handle_one_connection (connect=0x55ffcc8e9c30) at /data/src/10.4/sql/sql_connect.cc:1412

#20 0x000055ffc8f63250 in handle_one_connection (arg=0x55ffcc8e9c30) at /data/src/10.4/sql/sql_connect.cc:1316

#21 0x000055ffc9987ba8 in pfs_spawn_thread (arg=0x55ffcc8c2ca0) at /data/src/10.4/storage/perfschema/pfs.cc:1869

#22 0x00007f4f1b47f609 in start_thread (arg=<optimized out>) at pthread_create.c:477

#23 0x00007f4f1acea293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

CREATE TABLE t (a CHAR(3)) CHARSET=sjis;

INSERT INTO t VALUES ('foo'),('bar');

SELECT LOAD_FILE('') AS f, a FROM t GROUP BY f, a HAVING f = a;

# Cleanup

DROP TABLE t;

mysqld: /data/src/10.4/sql/item.h:6144: Item_copy::Item_copy(THD*, Item*): Assertion `i->is_fixed()' failed.

210308 16:54:49 [ERROR] mysqld got signal 6 ;

#7  0x00007ff14adc7f36 in __GI___assert_fail (assertion=0x559115e63139 "i->is_fixed()", file=0x559115e62603 "/data/src/10.4/sql/item.h", line=6144, function=0x559115e63148 "Item_copy::Item_copy(THD*, Item*)") at assert.c:101

#8  0x00005591151f18c6 in Item_copy::Item_copy (this=0x7ff134017ab0, thd=0x7ff134000d90, i=0x7ff134014d78) at /data/src/10.4/sql/item.h:6144

#9  0x00005591151f1c15 in Item_copy_string::Item_copy_string (this=0x7ff134017ab0, thd=0x7ff134000d90, item_arg=0x7ff134014d78) at /data/src/10.4/sql/item.h:6211

#10 0x000055911537d211 in Type_handler::create_item_copy (this=0x5591166efd18 <type_handler_long_blob>, thd=0x7ff134000d90, item=0x7ff134014d78) at /data/src/10.4/sql/sql_type.cc:3965

#11 0x00005591151e1a45 in setup_copy_fields (thd=0x7ff134000d90, param=0x7ff1340168e8, ref_pointer_array=..., res_selected_fields=..., res_all_fields=..., elements=2, all_fields=...) at /data/src/10.4/sql/sql_select.cc:25128

#12 0x00005591151a64a3 in JOIN::make_aggr_tables_info (this=0x7ff1340166f0) at /data/src/10.4/sql/sql_select.cc:3609

#13 0x00005591151a4055 in JOIN::optimize_stage2 (this=0x7ff1340166f0) at /data/src/10.4/sql/sql_select.cc:3056

#14 0x00005591151a1677 in JOIN::optimize_inner (this=0x7ff1340166f0) at /data/src/10.4/sql/sql_select.cc:2303

#15 0x000055911519ee85 in JOIN::optimize (this=0x7ff1340166f0) at /data/src/10.4/sql/sql_select.cc:1620

#16 0x00005591151aa3e3 in mysql_select (thd=0x7ff134000d90, tables=0x7ff134014fd8, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x0, group=0x7ff1340157c8, having=0x7ff134015ba0, proc_param=0x0, select_options=2147748608, result=0x7ff1340166c8, unit=0x7ff134004cc0, select_lex=0x7ff134014840) at /data/src/10.4/sql/sql_select.cc:4693

#17 0x0000559115199de3 in handle_select (thd=0x7ff134000d90, lex=0x7ff134004c00, result=0x7ff1340166c8, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:410

#18 0x000055911515ee8f in execute_sqlcom_select (thd=0x7ff134000d90, all_tables=0x7ff134014fd8) at /data/src/10.4/sql/sql_parse.cc:6444

#19 0x00005591151555d3 in mysql_execute_command (thd=0x7ff134000d90) at /data/src/10.4/sql/sql_parse.cc:3963

#20 0x0000559115162eb5 in mysql_parse (thd=0x7ff134000d90, rawbuf=0x7ff134014768 "SELECT LOAD_FILE('') AS f, a FROM t GROUP BY f, a HAVING f = a", length=62, parser_state=0x7ff144d98550, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7985

#21 0x000055911514f121 in dispatch_command (command=COM_QUERY, thd=0x7ff134000d90, packet=0x7ff13400abc1 "SELECT LOAD_FILE('') AS f, a FROM t GROUP BY f, a HAVING f = a", packet_length=62, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1855

#22 0x000055911514d989 in do_command (thd=0x7ff134000d90) at /data/src/10.4/sql/sql_parse.cc:1373

#23 0x00005591152dd507 in do_handle_one_connection (connect=0x559118622c30) at /data/src/10.4/sql/sql_connect.cc:1412

#24 0x00005591152dd250 in handle_one_connection (arg=0x559118622c30) at /data/src/10.4/sql/sql_connect.cc:1316

#25 0x0000559115d01ba8 in pfs_spawn_thread (arg=0x5591185fbca0) at /data/src/10.4/storage/perfschema/pfs.cc:1869

#26 0x00007ff14b648609 in start_thread (arg=<optimized out>) at pthread_create.c:477

#27 0x00007ff14aeb3293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

EXPLAIN EXTENDED SELECT LOAD_FILE('') AS f, a FROM t GROUP BY f, a HAVING f = a OR a != 'qux';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary; Using filesort

Warnings:

Note	1003	select load_file('') AS `f`,`test`.`t`.`a` AS `a` from `test`.`t` where `test`.`t`.`a` = <cache>(convert(load_file('') using sjis)) or `test`.`t`.`a` <> 'qux' group by load_file(''),`test`.`t`.`a` having 1

EXPLAIN EXTENDED SELECT LOAD_FILE('') AS f, a FROM t GROUP BY f, a HAVING f = a;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE

Warnings:

Note	1003	select load_file('') AS `f`,`test`.`t`.`a` AS `a` from `test`.`t` group by load_file(''),`test`.`t`.`a` having 1

commit 1c97cd339e9513b152727f386573c8c048db0281

Author: Igor Babaev

Date:   Fri Jan 3 11:15:00 2020 -0800

    MDEV-21184 Assertion `used_tables_cache == 0' failed in Item_func::fix_fields

               with condition_pushdown_from_having