MySQL Bugs: #112712: Assertion `!null_value || is_nullable()' failed in MySQL 8.1.0

Bug #112712	Assertion `!null_value \|\| is_nullable()' failed in MySQL 8.1.0
Submitted:	13 Oct 2023 9:11	Modified:	13 Oct 2023 9:48
Reporter:	Wang Ke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S6 (Debug Builds)
Version:	8.1.0, 8.0.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello, I found an assertion failure in MySQL 8.1.0 debug build:

```
mysqld: /home/mysql-8.1.0/sql/item_func.cc:1933: virtual longlong Item_typecast_unsigned::val_int(): Assertion `!null_value || is_nullable()' failed.
2023-10-13T08:54:28Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=d41d0359075d9acaf807cea3acc3b128cdec5219
Thread pointer: 0x6270002bc100
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f0e1e2d5a20 thread_stack 0x100000
/home/mysql-8.1.0-origin/bin/mysqld(__interceptor_backtrace+0x5b) [0x649134b]
/home/mysql-8.1.0-origin/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x10d) [0xbfde8ed]
/home/mysql-8.1.0-origin/bin/mysqld(print_fatal_signal(int)+0x552) [0x8979ee2]
/home/mysql-8.1.0-origin/bin/mysqld(handle_fatal_signal+0x175) [0x897a5c5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390) [0x7f0e4fc46390]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38) [0x7f0e4df2f438]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a) [0x7f0e4df3103a]
/lib/x86_64-linux-gnu/libc.so.6(+0x2dbe7) [0x7f0e4df27be7]
/lib/x86_64-linux-gnu/libc.so.6(+0x2dc92) [0x7f0e4df27c92]
/home/mysql-8.1.0-origin/bin/mysqld(Item_typecast_unsigned::val_int()+0x9c9) [0x68de209]
/home/mysql-8.1.0-origin/bin/mysqld(Arg_comparator::compare_int_unsigned_signed()+0xa4) [0x6d61b14]
/home/mysql-8.1.0-origin/bin/mysqld(Item_func_eq::val_int()+0x93) [0x6d6fb13]
/home/mysql-8.1.0-origin/bin/mysqld() [0x7e3fe60]
/home/mysql-8.1.0-origin/bin/mysqld(JOIN::optimize(bool)+0x65c3) [0x7e2f2b3]
/home/mysql-8.1.0-origin/bin/mysqld(Query_block::optimize(THD*, bool)+0x330) [0x82ca8c0]
/home/mysql-8.1.0-origin/bin/mysqld(Query_expression::optimize(THD*, TABLE*, bool, bool)+0x492) [0x8573972]
/home/mysql-8.1.0-origin/bin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x9a) [0x82c05ea]
/home/mysql-8.1.0-origin/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x13f8) [0x82bd348]
/home/mysql-8.1.0-origin/bin/mysqld(mysql_execute_command(THD*, bool)+0x3875) [0x809efd5]
/home/mysql-8.1.0-origin/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x1b28) [0x8094e68]
/home/mysql-8.1.0-origin/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x377a) [0x808522a]
/home/mysql-8.1.0-origin/bin/mysqld(do_command(THD*)+0x12ee) [0x80902be]
/home/mysql-8.1.0-origin/bin/mysqld() [0x88ff832]
/home/mysql-8.1.0-origin/bin/mysqld() [0xeb1629a]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba) [0x7f0e4fc3c6ba]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7f0e4e00151d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (610000035570): SELECT 1 ca2 FROM t0 ra1 WHERE CAST( ra1.c0 AS UNSIGNED INT ) = 2147483648 AND ra1.c0 = ( SELECT 1 ca0 FROM t1 ra2 ) ORDER BY ra1.c0 DESC
Connection ID (thread ID): 8
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

```

How to repeat:
Test case:

```
CREATE TABLE t0 ( c0 INT NOT NULL , c1 DATETIME DEFAULT ( source_pos_wait( 'fixme' , LENGTH( t0.c0 ) - INSTR( REVERSE( t0.c0 ) , 'fixme' ) , - 1 ) ) DEFAULT ( TIMESTAMPADD( SQL_TSI_MINUTE , 1 , 'x' ) ) , c2 INT DEFAULT NULL , c3 INT ( 1 ) NOT NULL ) ;
CREATE TEMPORARY TABLE t1 ( c4 CHAR ( 1 ) , c5 DATETIME DEFAULT NULL , c6 CHAR ( 1 ) , c7 VARCHAR ( 1 ) , c8 TEXT ) ;
SELECT 1 ca2 FROM t0 ra1 WHERE CAST( ra1.c0 AS UNSIGNED INT ) = 2147483648 AND ra1.c0 = ( SELECT 1 ca0 FROM t1 ra2 ) ORDER BY ra1.c0 DESC ;
```

Hello Wang Ke,

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 bug112712
Logging: ./mtr  --nocheck-testcases bug112712
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 ( c0 INT NOT NULL , c1 DATETIME DEFAULT ( source_pos_wait( 'fixme' , LENGTH( t0.c0 ) - INSTR( REVERSE( t0.c0 ) , 'fixme' ) , - 1 ) ) DEFAULT ( TIMESTAMPADD( SQL_TSI_MINUTE , 1 , 'x' ) ) , c2 INT DEFAULT NULL , c3 INT ( 1 ) NOT NULL ) ;
Warnings:
Warning 1681    Integer display width is deprecated and will be removed in a future release.
CREATE TEMPORARY TABLE t1 ( c4 CHAR ( 1 ) , c5 DATETIME DEFAULT NULL , c6 CHAR ( 1 ) , c7 VARCHAR ( 1 ) , c8 TEXT ) ;
SELECT 1 ca2 FROM t0 ra1 WHERE CAST( ra1.c0 AS UNSIGNED INT ) = 2147483648 AND ra1.c0 = ( SELECT 1 ca0 FROM t1 ra2 ) ORDER BY ra1.c0 DESC ;
ca2
[ 50%] main.bug112712                            [ pass ]     89
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.089 of 8 seconds executing testcases

- debug build - affected

./mtr --nocheck-testcases bug112712 --debug-server
Logging: ./mtr  --nocheck-testcases bug112712 --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 ( c0 INT NOT NULL , c1 DATETIME DEFAULT ( source_pos_wait( 'fixme' , LENGTH( t0.c0 ) - INSTR( REVERSE( t0.c0 ) , 'fixme' ) , - 1 ) ) DEFAULT ( TIMESTAMPADD( SQL_TSI_MINUTE , 1 , 'x' ) ) , c2 INT DEFAULT NULL , c3 INT ( 1 ) NOT NULL ) ;
Warnings:
Warning 1681    Integer display width is deprecated and will be removed in a future release.
CREATE TEMPORARY TABLE t1 ( c4 CHAR ( 1 ) , c5 DATETIME DEFAULT NULL , c6 CHAR ( 1 ) , c7 VARCHAR ( 1 ) , c8 TEXT ) ;
SELECT 1 ca2 FROM t0 ra1 WHERE CAST( ra1.c0 AS UNSIGNED INT ) = 2147483648 AND ra1.c0 = ( SELECT 1 ca0 FROM t1 ra2 ) ORDER BY ra1.c0 DESC ;
[ 50%] main.bug112712                            [ fail ]
        Test ended at 2023-10-13 11:47:32

CURRENT_TEST: main.bug112712
mysqltest: At line 3: Query 'SELECT 1 ca2 FROM t0 ra1 WHERE CAST( ra1.c0 AS UNSIGNED INT ) = 2147483648 AND ra1.c0 = ( SELECT 1 ca0 FROM t1 ra2 ) ORDER BY ra1.c0 DESC ' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query