MySQL Bugs: #112464: Assertion Failure in /mysql-8.0.34/sql/handler.cc:7908

Bug #112464	Assertion Failure in /mysql-8.0.34/sql/handler.cc:7908
Submitted:	26 Sep 2023 9:01	Modified:	26 Sep 2023 9:09
Reporter:	xin wen	Email Updates:
Status:	Verified	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 ( CONSTRAINT cc0 UNIQUE i0 ( c22 ) , c22 INT ) ;
INSERT INTO t0 VALUES ( 55 ) , ( 86 ) ;
CREATE VIEW v0 AS SELECT c22 AS c35 , c22 AS c49 , -10 AS c13 FROM t0 ;
WITH t1 AS ( SELECT NTH_VALUE ( c22 , 7726526410074227196 ) FROM FIRST RESPECT NULLS OVER w0 AS c17 FROM t0 WINDOW w0 AS ( PARTITION BY c22 ) EXCEPT SELECT ( QUOTE ( -26 ) << CONVERT ( t0 . c22 , UNSIGNED ) | RAND ( ) + EXP ( c22 ) ^ RAND ( ) ) + -116 AS c34 FROM t0 WHERE c22 < 31 ORDER BY CHAR_LENGTH ( -4 ) | EXP ( RAND ( ) IN ( 37 , 101 , -25 ) = SOME ( SELECT c22 AS c28 FROM t0 ) ) ) SELECT t0 . c22 AS c14 FROM t1 JOIN t0 ON t0 . c22 = t0 . c22 WHERE t1 . c17 = 60 ;

Will trigger assertion failure:
/home/wx/mysql-8.0.34/sql/handler.cc:7908: int handler::ha_external_lock(THD*, int): Assertion `inited == NONE || table->open_by_handler' 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=0x55555df47480 "inited == NONE || table->open_by_handler", file=0x55555df41c40 "/home/wx/mysql-8.0.34/sql/handler.cc", line=7908, function=<optimized out>) at assert.c:92
#3  0x00007ffff6b03fd6 in __GI___assert_fail (assertion=assertion@entry=0x55555df47480 "inited == NONE || table->open_by_handler", file=file@entry=0x55555df41c40 "/home/wx/mysql-8.0.34/sql/handler.cc", line=line@entry=7908, function=function@entry=0x55555df47340 "int handler::ha_external_lock(THD*, int)") at assert.c:101
#4  0x0000555559ba6481 in handler::ha_external_lock (this=0x622000355930, thd=thd@entry=0x6270002bf900, lock_type=lock_type@entry=2) at /home/wx/mysql-8.0.34/sql/handler.cc:7910
#5  0x0000555559fed157 in unlock_external (thd=thd@entry=0x6270002bf900, table=0x608000cadd68, table@entry=0x608000cadd58, count=<optimized out>, count@entry=4) at /home/wx/mysql-8.0.34/sql/lock.cc:618
#6  0x0000555559fef7f6 in mysql_unlock_read_tables (thd=<optimized out>, sql_lock=0x608000cadd40) at /home/wx/mysql-8.0.34/sql/lock.cc:466
#7  0x00005555595c0a9c in JOIN::join_free (this=this@entry=0x6190004fd3b0) at /home/wx/mysql-8.0.34/sql/sql_select.cc:3688
#8  0x000055555970a3ac in Query_expression::<lambda()>::operator()(void) const (__closure=0x7fffc7075ca8) at /home/wx/mysql-8.0.34/sql/sql_union.cc:1755
#9  0x000055555970a571 in Scope_guard<Query_expression::ExecuteIteratorQuery(THD*)::<lambda()> >::~Scope_guard(void) (this=<optimized out>, __in_chrg=<optimized out>) at /home/wx/mysql-8.0.34/include/scope_guard.h:39
#10 0x0000555559713bbf in Query_expression::ExecuteIteratorQuery (this=this@entry=0x6120002dbbf0, thd=thd@entry=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_union.cc:1770
#11 0x0000555559713ecd in Query_expression::execute (this=this@entry=0x6120002dbbf0, thd=thd@entry=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_union.cc:1823
#12 0x00005555595b1f79 in Sql_cmd_dml::execute_inner (this=0x60b0001508f0, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:1022
#13 0x00005555595ce209 in Sql_cmd_dml::execute (this=0x60b0001508f0, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:793
#14 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
#15 0x00005555594c0bfe in dispatch_sql_command (thd=0x6270002bf900, parser_state=parser_state@entry=0x7fffc7078c00) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:5368
#16 0x00005555594c38e2 in dispatch_command (thd=thd@entry=0x6270002bf900, com_data=com_data@entry=0x7fffc707a200, command=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:2054
#17 0x00005555594c7392 in do_command (thd=thd@entry=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:1439
#18 0x000055555989b7be in handle_connection (arg=arg@entry=0x6030001657c0) at /home/wx/mysql-8.0.34/sql/conn_handler/connection_handler_per_thread.cc:302
#19 0x000055555cc724e9 in pfs_spawn_thread (arg=0x614000140a60) at /home/wx/mysql-8.0.34/storage/perfschema/pfs.cc:3042
#20 0x00007ffff7568609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#21 0x00007ffff6bef133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Observed that only debug build is affected.

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

-- release build - not affected
./mtr --nocheck-testcases bug112464
Logging: ./mtr  --nocheck-testcases bug112464
MySQL Version 8.0.34
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.34/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0 ( CONSTRAINT cc0 UNIQUE i0 ( c22 ) , c22 INT ) ;
INSERT INTO t0 VALUES ( 55 ) , ( 86 ) ;
CREATE VIEW v0 AS SELECT c22 AS c35 , c22 AS c49 , -10 AS c13 FROM t0 ;
WITH t1 AS ( SELECT NTH_VALUE ( c22 , 7726526410074227196 ) FROM FIRST RESPECT NULLS OVER w0 AS c17 FROM t0 WINDOW w0 AS ( PARTITION BY c22 ) EXCEPT SELECT ( QUOTE ( -26 ) << CONVERT ( t0 . c22 , UNSIGNED ) | RAND ( ) + EXP ( c22 ) ^ RAND ( ) ) + -116 AS c34 FROM t0 WHERE c22 < 31 ORDER BY CHAR_LENGTH ( -4 ) | EXP ( RAND ( ) IN ( 37 , 101 , -25 ) = SOME ( SELECT c22 AS c28 FROM t0 ) ) ) SELECT t0 . c22 AS c14 FROM t1 JOIN t0 ON t0 . c22 = t0 . c22 WHERE t1 . c17 = 60 ;
c14
[ 50%] main.bug112464                            [ pass ]    104
[100%] shutdown_report                           [ pass ]

-- debug build
./mtr --nocheck-testcases bug112464 --debug-server
Logging: ./mtr  --nocheck-testcases bug112464 --debug-server
MySQL Version 8.0.34
Checking supported features
 - Binaries are debug compiled
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.34/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0 ( CONSTRAINT cc0 UNIQUE i0 ( c22 ) , c22 INT ) ;
INSERT INTO t0 VALUES ( 55 ) , ( 86 ) ;
CREATE VIEW v0 AS SELECT c22 AS c35 , c22 AS c49 , -10 AS c13 FROM t0 ;
WITH t1 AS ( SELECT NTH_VALUE ( c22 , 7726526410074227196 ) FROM FIRST RESPECT NULLS OVER w0 AS c17 FROM t0 WINDOW w0 AS ( PARTITION BY c22 ) EXCEPT SELECT ( QUOTE ( -26 ) << CONVERT ( t0 . c22 , UNSIGNED ) | RAND ( ) + EXP ( c22 ) ^ RAND ( ) ) + -116 AS c34 FROM t0 WHERE c22 < 31 ORDER BY CHAR_LENGTH ( -4 ) | EXP ( RAND ( ) IN ( 37 , 101 , -25 ) = SOME ( SELECT c22 AS c28 FROM t0 ) ) ) SELECT t0 . c22 AS c14 FROM t1 JOIN t0 ON t0 . c22 = t0 . c22 WHERE t1 . c17 = 60 ;
[ 50%] main.bug112464                            [ fail ]
        Test ended at 2023-09-26 11:09:05

CURRENT_TEST: main.bug112464
mysqltest: At line 4: Query 'WITH t1 AS ( SELECT NTH_VALUE ( c22 , 7726526410074227196 ) FROM FIRST RESPECT NULLS OVER w0 AS c17 FROM t0 WINDOW w0 AS ( PARTITION BY c22 ) EXCEPT SELECT ( QUOTE ( -26 ) << CONVERT ( t0 . c22 , UNSIGNED ) | RAND ( ) + EXP ( c22 ) ^ RAND ( ) ) + -116 AS c34 FROM t0 WHERE c22 < 31 ORDER BY CHAR_LENGTH ( -4 ) | EXP ( RAND ( ) IN ( 37 , 101 , -25 ) = SOME ( SELECT c22 AS c28 FROM t0 ) ) ) SELECT t0 . c22 AS c14 FROM t1 JOIN t0 ON t0 . c22 = t0 . c22 WHERE t1 . c17 = 60 ' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query