MySQL Bugs: #112478: Assertion Failure in /mysql-8.0.34/sql/sql

Bug #112478	Assertion Failure in /mysql-8.0.34/sql/sql_derived.cc:524
Submitted:	26 Sep 2023 10:24	Modified:	28 Feb 16:31
Reporter:	xin wen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S6 (Debug Builds)
Version:	8.0.34	OS:	Ubuntu (20.04)
Assigned to:		CPU Architecture:	Any

Description:
Run these queries:

CREATE TABLE t0 ( c44 DECIMAL ( 31 ) DEFAULT ( 16 ) ) ;
INSERT INTO t0 VALUES ( DEFAULT ) , ( DEFAULT ) ;
ALTER TABLE t0 ADD COLUMN c25 INT AFTER c44 ;
INSERT INTO t0 VALUES ( 126 , 48 ) , ( -99 , -44 ) ;
SELECT t3 . c19 AS c55 FROM ( SELECT RANK ( ) OVER ( PARTITION BY t0 . c44 NOT LIKE ~ FLOOR ( ASCII ( -10 ) ^ HEX ( RAND ( ) IN ( RAND ( ) ) ) ) IS NULL ) IN ( 116 , -70 , -34 ) = ALL ( SELECT c44 AS c46 FROM t0 ) AS c8 , COUNT( * ) = ( WITH t1 AS ( SELECT SQL_NO_CACHE c25 AS c17 FROM t0 ) SELECT c12 AS c29 FROM ( SELECT c25 AS c12 FROM t0 GROUP BY c25 EXCEPT SELECT c25 + -98 AS c44 FROM t1 WHERE c44 < -128 AND c44 < -69 ) AS t2 CROSS JOIN t0 WHERE c12 = -27 LIMIT 1 ) << RAND ( ) BETWEEN 61 AND -31 AS c19 FROM t0 GROUP BY c44 , c25 ) AS t3 JOIN t0 ON COS ( -32 ) << FORMAT ( t0 . c25 LIKE FLOOR ( 88 ) IS NOT NULL , 43 ) * RAND ( ) = t0 . c25 ;

Will trigger assertion failure:
/home/wx/mysql-8.0.34/sql/sql_derived.cc:524: copy_field_info(THD*, Item*, Item*)::<lambda(Item*)>: Assertion `depended_from == nullptr || depended_from == ident->depended_from || depended_from == ident->context->query_block' failed.

GDB info:
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007ffff6af2859 in __GI_abort () at abort.c:79
#2  0x00007ffff6af2729 in __assert_fail_base (fmt=0x7ffff6c88588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55555e0b1d60 "depended_from == nullptr || depended_from == ident->depended_from || depended_from == ident->context->query_block", file=0x55555e0b3d20 "/home/wx/mysql-8.0.34/sql/sql_derived.cc", line=524, function=<optimized out>) at assert.c:92
#3  0x00007ffff6b03fd6 in __GI___assert_fail ( assertion=assertion@entry=0x55555e0b1d60 "depended_from == nullptr || depended_from == ident->depended_from || depended_from == ident->context->query_block", file=file@entry=0x55555e0b3d20 "/home/wx/mysql-8.0.34/sql/sql_derived.cc", line=line@entry=524, function=function@entry=0x55555e0b1a60 "copy_field_info(THD*, Item*, Item*)::<lambda(Item*)>") at assert.c:101
#4  0x000055555a1bb2a7 in <lambda(Item*)>::operator()(Item *) const (__closure=0x7fffc707adb0, inner_item=0x6120003301f0) at /home/wx/mysql-8.0.34/sql/sql_derived.cc:532
#5  0x000055555a1bb462 in Item::walk_helper_thunk<copy_field_info(THD*, Item*, Item*)::<lambda(Item*)> >(uchar *) (this=<optimized out>, arg=<optimized out>) at /home/wx/mysql-8.0.34/sql/item.h:2460
#6  0x0000555559c7e63b in Item_ref::walk (this=0x6120003301f0, processor=<optimized out>, walk=<optimized out>, arg=<optimized out>) at /home/wx/mysql-8.0.34/sql/item.h:5835
#7  0x0000555559c7e6bf in Item_ref::walk (this=0x613000e10fb0, processor=<optimized out>, walk=<optimized out>, arg=<optimized out>) at /home/wx/mysql-8.0.34/sql/item.h:5835
#8  0x0000555559d3e19a in Item_func::walk (this=<optimized out>, processor= (bool (Item::*)(class Item * const, unsigned char *)) 0x55555a1bb44c <Item::walk_helper_thunk<copy_field_info(THD*, Item*, Item*)::<lambda(Item*)> >(uchar *)>, walk=<optimized out>, argument=<optimized out>) at /home/wx/mysql-8.0.34/sql/item_func.cc:617
#9  0x000055555a1b8db5 in WalkItem<copy_field_info(THD*, Item*, Item*)::<lambda(Item*)> >(Item *, enum_walk, <lambda(Item*)> &&) (item=item@entry=0x612000317d70, walk=walk@entry=enum_walk::PREFIX, functor=...) at /home/wx/mysql-8.0.34/sql/item.h:3572
#10 0x000055555a1b9882 in copy_field_info (thd=thd@entry=0x6270002bf900, orig_expr=orig_expr@entry=0x612000317d70, cloned_expr=cloned_expr@entry=0x612000332770) at /home/wx/mysql-8.0.34/sql/sql_derived.cc:519
#11 0x000055555a1c2be9 in Query_block::clone_expression (this=this@entry=0x6190005406b0, thd=0x6270002bf900, item=item@entry=0x612000317d70) at /home/wx/mysql-8.0.34/sql/sql_derived.cc:760
#12 0x0000555559c63c8a in Item_field::replace_with_derived_expr (this=<optimized out>, arg=<optimized out>) at /home/wx/mysql-8.0.34/sql/item.cc:1069
#13 0x0000555559c2e61b in Item::transform (this=0x613000e11bf0, transformer=<optimized out>, arg=<optimized out>) at /home/wx/mysql-8.0.34/sql/item.cc:776
#14 0x0000555559d38e38 in Item_func::transform (this=this@entry=0x6150004079b0, transformer=&virtual table offset 992, argument=<optimized out>) at /home/wx/mysql-8.0.34/sql/item_func.cc:659
#15 0x000055555a1ba96f in Condition_pushdown::replace_columns_in_cond (this=this@entry=0x7fffc707b450, cond=cond@entry=0x7fffc707b478, is_having=is_having@entry=false) at /home/wx/mysql-8.0.34/sql/sql_derived.cc:1400
#16 0x000055555a1c33e5 in Condition_pushdown::make_cond_for_derived (this=this@entry=0x7fffc707b450) at /home/wx/mysql-8.0.34/sql/sql_derived.cc:1083
#17 0x0000555559558b5d in Query_block::push_conditions_to_derived_tables (this=0x61900053deb0, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:625
#18 0x0000555559558bc4 in Query_block::push_conditions_to_derived_tables (this=<optimized out>, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:641
#19 0x0000555559558bc4 in Query_block::push_conditions_to_derived_tables (this=this@entry=0x6190005361b0, thd=thd@entry=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:641
#20 0x00005555595781fd in Query_block::apply_local_transforms (this=this@entry=0x6190005361b0, thd=thd@entry=0x6270002bf900, prune=prune@entry=true) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:830
#21 0x000055555958e6f1 in Query_block::prepare (this=this@entry=0x6190005361b0, thd=thd@entry=0x6270002bf900, insert_field_list=insert_field_list@entry=0x0) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:589
#22 0x00005555595b0c63 in Sql_cmd_select::prepare_inner (this=0x60b0001582a0, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:650
#23 0x00005555595cd20b in Sql_cmd_dml::prepare (this=0x60b0001582a0, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:566
#24 0x00005555595cda8d in Sql_cmd_dml::execute (this=0x60b0001582a0, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:718
#25 0x00005555594bd568 in mysql_execute_command (thd=thd@entry=0x6270002bf900, first_level=first_level@entry=true) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:4719
#26 0x00005555594c0bfe in dispatch_sql_command (thd=0x6270002bf900, parser_state=parser_state@entry=0x7fffc707ec00) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:5368
#27 0x00005555594c38e2 in dispatch_command (thd=thd@entry=0x6270002bf900, com_data=com_data@entry=0x7fffc7080200, command=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:2054
#28 0x00005555594c7392 in do_command (thd=thd@entry=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:1439
#29 0x000055555989b7be in handle_connection (arg=arg@entry=0x6030001624c0) at /home/wx/mysql-8.0.34/sql/conn_handler/connection_handler_per_thread.cc:302
#30 0x000055555cc724e9 in pfs_spawn_thread (arg=0x614000140a60) at /home/wx/mysql-8.0.34/storage/perfschema/pfs.cc:3042
#31 0x00007ffff7568609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#32 0x00007ffff6bef133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

How to repeat:
Run the queries above.

Hello xin wen,

Thank you for the report and test case.
Observed that 8.0.34 debug build is affected.

regards,
Umesh

Documented fix in the MySQL 8.4.0 changelog as follows:

    An assertion in sql/sql_derived.cc that checked whether a
    referenced item in an Item_ref object had consistent outer
    reference information failed when the reference was of type
    OUTER_REF. For objects of type Item_outer_ref, dependency
    information is set for the Item_outer_ref object and the
    original expression that this reference points to, but an
    intermediate reference object between the Item_outer_ref and the
    original expression did not have this information.

Closed.