Server crash or assertion failure in upon 2nd execution of PS with views and HAVING

CREATE TABLE t (f INT);

INSERT INTO t VALUES (1),(2); # Optional, fails either way

CREATE VIEW v1 AS SELECT 1 AS a;

CREATE VIEW v2 AS SELECT a FROM v1;

PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)";

EXECUTE stmt;

EXECUTE stmt;

# Cleanup

DROP VIEW v1;

DROP VIEW v2;

DROP TABLE t;

mysqld: /data/src/10.4/sql/table.cc:6470: void TABLE_LIST::set_check_materialized(): Assertion `!derived->is_excluded()' failed.

230504 17:12:19 [ERROR] mysqld got signal 6 ;

#9  0x00007fc497253df2 in __GI___assert_fail (assertion=0x55b58203ea60 "!derived->is_excluded()", file=0x55b582039240 "/data/src/10.4/sql/table.cc", line=6470, function=0x55b58203ea00 "void TABLE_LIST::set_check_materialized()") at ./assert/assert.c:101

#10 0x000055b58023deb4 in TABLE_LIST::set_check_materialized (this=0x62b00008e4f8) at /data/src/10.4/sql/table.cc:6470

#11 0x000055b57fdaa1ff in TABLE_LIST::set_materialized_derived (this=0x62b00008e4f8) at /data/src/10.4/sql/table.h:2883

#12 0x000055b580253187 in TABLE_LIST::init_derived (this=0x62b00008e4f8, thd=0x62b00005b208, init_view=true) at /data/src/10.4/sql/table.cc:9241

#13 0x000055b57fe3821e in mysql_derived_init (thd=0x62b00005b208, lex=0x62b00008c238, derived=0x62b00008e4f8) at /data/src/10.4/sql/sql_derived.cc:564

#14 0x000055b57fe35aec in mysql_handle_derived (lex=0x62b00008c238, phases=1) at /data/src/10.4/sql/sql_derived.cc:123

#15 0x000055b57fd8e8de in open_and_lock_tables (thd=0x62b00005b208, options=..., tables=0x62b00008e4f8, derived=true, flags=0, prelocking_strategy=0x7fc48f4daa00) at /data/src/10.4/sql/sql_base.cc:5353

#16 0x000055b57fce8616 in open_and_lock_tables (thd=0x62b00005b208, tables=0x62b00008e4f8, derived=true, flags=0) at /data/src/10.4/sql/sql_base.h:503

#17 0x000055b57ff216a3 in execute_sqlcom_select (thd=0x62b00005b208, all_tables=0x62b00008e4f8) at /data/src/10.4/sql/sql_parse.cc:6384

#18 0x000055b57ff0f638 in mysql_execute_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:3966

#19 0x000055b57ff7ee7f in Prepared_statement::execute (this=0x619000084388, expanded_query=0x7fc48f4dcb30, open_cursor=false) at /data/src/10.4/sql/sql_prepare.cc:5024

#20 0x000055b57ff7a4bf in Prepared_statement::execute_loop (this=0x619000084388, expanded_query=0x7fc48f4dcb30, open_cursor=false, packet=0x0, packet_end=0x0) at /data/src/10.4/sql/sql_prepare.cc:4493

#21 0x000055b57ff74335 in mysql_sql_stmt_execute (thd=0x62b00005b208) at /data/src/10.4/sql/sql_prepare.cc:3577

#22 0x000055b57ff0f67d in mysql_execute_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:3982

#23 0x000055b57ff2b2fb in mysql_parse (thd=0x62b00005b208, rawbuf=0x62b000062228 "EXECUTE stmt", length=12, parser_state=0x7fc48f4de860, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7998

#24 0x000055b57ff017a7 in dispatch_command (command=COM_QUERY, thd=0x62b00005b208, packet=0x62900023f209 "EXECUTE stmt", packet_length=12, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1857

#25 0x000055b57fefe320 in do_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:1378

#26 0x000055b5802fc31c in do_handle_one_connection (connect=0x6080000009a8) at /data/src/10.4/sql/sql_connect.cc:1420

#27 0x000055b5802fbc33 in handle_one_connection (arg=0x6080000009a8) at /data/src/10.4/sql/sql_connect.cc:1324

#28 0x000055b580f6674c in pfs_spawn_thread (arg=0x615000003508) at /data/src/10.4/storage/perfschema/pfs.cc:1869

#29 0x00007fc4972a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442

#30 0x00007fc4973285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

#2  <signal handler called>

#3  0x000055871ef47ec6 in st_select_lex::mark_as_dependent (this=this@entry=0x7fb77407e478, thd=thd@entry=0x7fb774000c58, last=last@entry=0x7fb77407e478, dependency=0x7fb77407e950) at /data/src/10.4/sql/sql_lex.cc:2831

#4  0x000055871f18ce00 in mark_as_dependent (thd=thd@entry=0x7fb774000c58, last=0x7fb77407e478, current=0x7fb77407e478, resolved_item=resolved_item@entry=0x7fb77407e950, mark_item=<optimized out>, suppress_warning_output=suppress_warning_output@entry=false) at /data/src/10.4/sql/item.cc:5119

#5  0x000055871f19d88d in Item_field::fix_outer_field (this=this@entry=0x7fb77407e950, thd=thd@entry=0x7fb774000c58, from_field=from_field@entry=0x7fb784c8f9d0, reference=reference@entry=0x7fb7741283e8) at /data/src/10.4/sql/item.cc:5810

#6  0x000055871f19e347 in Item_field::fix_fields (this=0x7fb77407e950, thd=0x7fb774000c58, reference=0x7fb7741283e8) at /data/src/10.4/sql/item.cc:6013

#7  0x000055871f19f06c in Item::fix_fields_if_needed (ref=0x7fb7741283e8, thd=0x7fb774000c58, this=0x7fb77407e950) at /data/src/10.4/sql/item.h:964

#8  Item::fix_fields_if_needed (ref=0x7fb7741283e8, thd=0x7fb774000c58, this=0x7fb77407e950) at /data/src/10.4/sql/item.h:964

#9  Item_direct_view_ref::fix_fields (this=0x7fb7740883b0, thd=0x7fb774000c58, reference=0x7fb77407bb50) at /data/src/10.4/sql/item.cc:9071

#10 0x000055871ef0eac9 in Item::fix_fields_if_needed (ref=0x7fb77407bb50, thd=0x7fb774000c58, this=0x7fb7740883b0) at /data/src/10.4/sql/item.h:966

#11 Item::fix_fields_if_needed (ref=0x7fb77407bb50, thd=0x7fb774000c58, this=0x7fb7740883b0) at /data/src/10.4/sql/item.h:964

#12 Item::fix_fields_if_needed_for_scalar (ref=0x7fb77407bb50, thd=0x7fb774000c58, this=0x7fb7740883b0) at /data/src/10.4/sql/item.h:970

#13 setup_fields (thd=0x7fb774000c58, ref_pointer_array=..., fields=..., column_usage=column_usage@entry=MARK_COLUMNS_READ, sum_func_list=sum_func_list@entry=0x7fb774010618, pre_fix=<optimized out>, allow_sum_func=<optimized out>) at /data/src/10.4/sql/sql_base.cc:7738

#14 0x000055871efc7a61 in JOIN::prepare (this=this@entry=0x7fb774010300, tables_init=tables_init@entry=0x7fb77407bb90, wild_num=wild_num@entry=0, conds_init=conds_init@entry=0x0, og_num=og_num@entry=0, order_init=order_init@entry=0x0, skip_order_by=<optimized out>, group_init=<optimized out>, having_init=<optimized out>, proc_param_init=<optimized out>, select_lex_arg=<optimized out>, unit_arg=<optimized out>) at /data/src/10.4/sql/sql_select.cc:1308

#15 0x000055871efdb589 in mysql_select (thd=thd@entry=0x7fb774000c58, tables=<optimized out>, wild_num=<optimized out>, fields=..., conds=<optimized out>, og_num=<optimized out>, order=<optimized out>, group=<optimized out>, having=<optimized out>, proc_param=<optimized out>, select_options=<optimized out>, result=<optimized out>, unit=<optimized out>, select_lex=<optimized out>) at /data/src/10.4/sql/sql_select.cc:4774

#16 0x000055871efdb904 in handle_select (thd=thd@entry=0x7fb774000c58, lex=lex@entry=0x7fb774079928, result=result@entry=0x7fb77407e348, setup_tables_done_option=setup_tables_done_option@entry=0) at /data/src/10.4/sql/sql_select.cc:442

#17 0x000055871ef637a0 in execute_sqlcom_select (thd=thd@entry=0x7fb774000c58, all_tables=0x7fb77407bb90) at /data/src/10.4/sql/sql_parse.cc:6463

#18 0x000055871ef720bb in mysql_execute_command (thd=0x7fb774000c58) at /data/src/10.4/sql/sql_parse.cc:3966

#19 0x000055871ef89c66 in Prepared_statement::execute (this=this@entry=0x7fb774115338, expanded_query=expanded_query@entry=0x7fb784c91350, open_cursor=open_cursor@entry=false) at /data/src/10.4/sql/sql_prepare.cc:5024

#20 0x000055871ef89de5 in Prepared_statement::execute_loop (packet=<optimized out>, packet_end=<optimized out>, open_cursor=<optimized out>, expanded_query=0x7fb784c91350, this=0x7fb774115338) at /data/src/10.4/sql/sql_prepare.cc:4493

#21 Prepared_statement::execute_loop (this=0x7fb774115338, expanded_query=0x7fb784c91350, open_cursor=<optimized out>, packet=<optimized out>, packet_end=<optimized out>) at /data/src/10.4/sql/sql_prepare.cc:4442

#22 0x000055871ef8a11b in mysql_sql_stmt_execute (thd=thd@entry=0x7fb774000c58) at /data/src/10.4/sql/sql_prepare.cc:3577

#23 0x000055871ef6fb85 in mysql_execute_command (thd=thd@entry=0x7fb774000c58) at /data/src/10.4/sql/sql_parse.cc:3982

#24 0x000055871ef743e1 in mysql_parse (thd=0x7fb774000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /data/src/10.4/sql/sql_parse.cc:7998

#25 0x000055871ef76915 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fb774000c58, packet=packet@entry=0x7fb774007d59 "EXECUTE stmt", packet_length=packet_length@entry=12, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.4/sql/sql_parse.cc:1958

#26 0x000055871ef78bbf in do_command (thd=0x7fb774000c58) at /data/src/10.4/sql/sql_parse.cc:1378

#27 0x000055871f071c7e in do_handle_one_connection (connect=connect@entry=0x5587222445a8) at /data/src/10.4/sql/sql_connect.cc:1420

#28 0x000055871f071d9d in handle_one_connection (arg=arg@entry=0x5587222445a8) at /data/src/10.4/sql/sql_connect.cc:1324

#29 0x000055871f402fab in pfs_spawn_thread (arg=0x55872219d818) at /data/src/10.4/storage/perfschema/pfs.cc:1869

#30 0x00007fb78aca7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442

#31 0x00007fb78ad285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

commit ccec9b1de95a66b7597bc30e0a60bd61866f225d

Author: Igor Babaev

Date:   Wed Mar 1 22:49:27 2023 -0800

    MDEV-30706 Different results of selects from view and CTE with same definition

    MDEV-30668 Set function aggregated in outer select used in view definition