MySQL Bugs: #111445: virtual longlong Item_typecast_signed::val_int(): Assertion `is

Bug #111445	virtual longlong Item_typecast_signed::val_int(): Assertion `is_nullable()' fail
Submitted:	15 Jun 2023 19:51	Modified:	29 Jun 2023 19:32
Reporter:	Yu Liang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S6 (Debug Builds)
Version:	8.0.33	OS:	Ubuntu (20.04 LTS)
Assigned to:		CPU Architecture:	x86 (Intel(R) Core(TM) i7-10700 CPU)

Description:
The latest version of the MySQL Server (version 8.0.33 debug build) (git commit hash: ea7087d8850) crashes with Assertion Failure when executing the following query:

Config from "/etc/mysql/conf.d/mysql.cnf":

```
[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"
```

```sql
drop database if exists test123;
create database test123;
use test123;
CREATE TABLE IF NOT EXISTS v0 ( c1 INT , c2 ENUM ( 0x0 ) ASCII BINARY PRIMARY KEY ) ;
INSERT v0 SET c1 = DEFAULT ;
UPDATE v0 SET c1 = 0x0 WHERE CONVERT ( c2 MEMBER OF ( c2 ) , SIGNED INT ) ;
```

In the debug build, the server crashes with the following stack trace: 

```
mysql> UPDATE v2 SET c3 = 0x0 WHERE CONVERT ( c4 MEMBER OF ( c4 ) , SIGNED INT ) ;
mysqld: /home/mysql/mysql-server/sql/item_func.cc:1879: virtual longlong Item_typecast_signed::val_int(): Assertion `is_nullable()' failed.
2023-06-15T19:48:26Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=1d55d4671b7469352fc317efb0121b604c26443d
Thread pointer: 0xfffee8001040
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 = ffff8c59c538 thread_stack 0x100000
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x60) [0x4e6bc30]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(print_fatal_signal(int)+0x340) [0x1a89320]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(handle_fatal_signal+0x16c) [0x1a896dc]
linux-vdso.so.1(__kernel_rt_sigreturn+0) [0xffff91c417a0]
/lib/aarch64-linux-gnu/libc.so.6(gsignal+0xe0) [0xffff912b5d78]
/lib/aarch64-linux-gnu/libc.so.6(abort+0x114) [0xffff912a2aac]
/lib/aarch64-linux-gnu/libc.so.6(+0x2d490) [0xffff912af490]
/lib/aarch64-linux-gnu/libc.so.6(+0x2d4f4) [0xffff912af4f4]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Item_typecast_signed::val_int()+0x254) [0x1fc5570]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Arg_comparator::compare_int_signed()+0x1b4) [0x1ee41e8]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Item_func_ne::val_int()+0xb4) [0x1eec288]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Sql_cmd_update::update_single_table(THD*)+0x3f80) [0x18c1054]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Sql_cmd_update::execute_inner(THD*)+0x1b4) [0x18c9714]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Sql_cmd_dml::execute(THD*)+0x7a0) [0x1737e1c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(mysql_execute_command(THD*, bool)+0x3884) [0x162cd40]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x1124) [0x16252c0]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x5434) [0x161e224]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(do_command(THD*)+0xb8c) [0x16228f4]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x1a59a64]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x655c1a0]
/lib/aarch64-linux-gnu/libpthread.so.0(+0x7624) [0xffff91be7624]
/lib/aarch64-linux-gnu/libc.so.6(+0xd149c) [0xffff9135349c]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (fffee80243c0): UPDATE v2 SET c3 = 0x0 WHERE CONVERT ( c4 MEMBER OF ( c4 ) , SIGNED INT )
Connection ID (thread ID): 8
Status: NOT_KILLED
```

How to repeat:
Steps to repeat the Assertion Failure:
1. Download the MySQL Server source code from the official github repo: `https://github.com/mysql/mysql-server`
2. Checkout to the latest mysql released version: 8.0.33 (hash: `ea7087d8850`)
3. Compile MySQL using the command: 

```
mkdir -p bld
cd bld
cmake .. -DDOWNLOAD_BOOST=1 -DWITH_BOOST=../boost -DWITH_UNIT_TESTS=OFF -DWITH_DEBUG=1
make
```

4. Run the MySQL Server with command: 

```
./bin/mysqld --basedir=$(pwd) --datadir=$(pwd)/data_all/ori_data --port=7000  --socket=/tmp/mysql_0.sock --mysqlx=OFF --performance_schema=OFF
```

5. Setup the MySQL Server config in the path: "/etc/mysql/conf.d/mysql.cnf"

```
[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"
```

6. Run the MySQL Client with the PoC:

```
./bin/mysql --port=7000 --user=root --socket=/tmp/mysql_0.sock < poc_0.sql
```

where `poc_0.sql` is:

```sql
drop database if exists test123;
create database test123;
use test123;
CREATE TABLE IF NOT EXISTS v0 ( c1 INT , c2 ENUM ( 0x0 ) ASCII BINARY PRIMARY KEY ) ;
INSERT v0 SET c1 = DEFAULT ;
UPDATE v0 SET c1 = 0x0 WHERE CONVERT ( c2 MEMBER OF ( c2 ) , SIGNED INT ) ;
```

Suggested fix:
The server should continue running instead of crashing by Assertion Failure.

Hello Yu Liang,

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

regards,
Umesh

-- release build - not affected

Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
 - WARNING: Using the 'mysql-test/var' symlink
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.33/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
drop database if exists test123;
Warnings:
Note    1008    Can't drop database 'test123'; database doesn't exist
create database test123;
use test123;
CREATE TABLE IF NOT EXISTS v0 ( c1 INT , c2 ENUM ( 0x0 ) ASCII BINARY PRIMARY KEY ) ;
Warnings:
Warning 1287    'ASCII' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
Warning 1287    'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
INSERT v0 SET c1 = DEFAULT ;
UPDATE v0 SET c1 = 0x0 WHERE CONVERT ( c2 MEMBER OF ( c2 ) , SIGNED INT ) ;
[ 50%] main.bug111445                            [ fail ]
        Test ended at 2023-06-16 06:50:39

CURRENT_TEST: main.bug111445
mysqltest: At line 6: Query 'UPDATE v0 SET c1 = 0x0 WHERE CONVERT ( c2 MEMBER OF ( c2 ) , SIGNED INT ) ' failed.
ERROR 3146 (22032): Invalid data type for JSON data in argument 2 to function member of; a JSON string or JSON type is required.
safe_process[21435]: Child process: 21436, exit: 1

 - the logfile can be found in '/export/home/tmp/ushastry/mysql-8.0.33/mysql-test/var/log/main.bug111445/bug111445.log'

[100%] shutdown_report                           [ pass ]

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

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
drop database if exists test123;
Warnings:
Note    1008    Can't drop database 'test123'; database doesn't exist
create database test123;
use test123;
CREATE TABLE IF NOT EXISTS v0 ( c1 INT , c2 ENUM ( 0x0 ) ASCII BINARY PRIMARY KEY ) ;
Warnings:
Warning 1287    'ASCII' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
Warning 1287    'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
INSERT v0 SET c1 = DEFAULT ;
UPDATE v0 SET c1 = 0x0 WHERE CONVERT ( c2 MEMBER OF ( c2 ) , SIGNED INT ) ;
[ 50%] main.bug111445                            [ fail ]
        Test ended at 2023-06-16 06:51:02

CURRENT_TEST: main.bug111445
mysqltest: At line 6: Query 'UPDATE v0 SET c1 = 0x0 WHERE CONVERT ( c2 MEMBER OF ( c2 ) , SIGNED INT ) ' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

#0  0x00007f2129f02aa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000003f9296e in my_write_core(int) ()
#2  0x00000000032365f5 in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007f212813c387 in raise () from /lib64/libc.so.6
#5  0x00007f212813da78 in abort () from /lib64/libc.so.6
#6  0x00007f21281351a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f2128135252 in __assert_fail () from /lib64/libc.so.6
#8  0x00000000033efa2f in Item_typecast_signed::val_int() ()
#9  0x000000000339cf28 in Arg_comparator::compare_int_signed() ()
#10 0x00000000033ace4f in Arg_comparator::compare() ()
#11 0x000000000339be03 in Item_func_ne::val_int() ()
#12 0x000000000319a9c8 in Sql_cmd_update::update_single_table(THD*) ()
#13 0x000000000319b373 in Sql_cmd_update::execute_inner(THD*) ()
#14 0x0000000003123873 in Sql_cmd_dml::execute(THD*) ()
#15 0x00000000030c24af in mysql_execute_command(THD*, bool) ()
#16 0x00000000030c5dc9 in dispatch_sql_command(THD*, Parser_state*) ()
#17 0x00000000030c752b in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#18 0x00000000030c926e in do_command(THD*) ()
#19 0x0000000003227bb7 in handle_connection ()
#20 0x0000000004743af9 in pfs_spawn_thread ()
#21 0x00007f2129efdea5 in start_thread () from /lib64/libpthread.so.0
#22 0x00007f2128204b2d in clone () from /lib64/libc.so.6

Posted by developer:
 
Work was done for test suite. No changelog entry needed.