MySQL Bugs: #111403: Assertion `!col_val->item_expression->null

Bug #111403	Assertion `!col_val->item_expression->null_value' failed
Submitted:	13 Jun 2023 18:16	Modified:	4 Jul 2023 0:31
Reporter:	Yu Liang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	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) (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 v0(c1 INT);
CREATE TABLE v2 PARTITION BY LIST COLUMNS ( v2 ) ( PARTITION v2 VALUES IN ( MICROSECOND ( 'abc' ) ) ) AS TABLE v0 ;
```

The last query returns error: 

```
mysql> CREATE TABLE v2 PARTITION BY LIST COLUMNS ( v2 ) ( PARTITION v2 VALUES IN ( MICROSECOND ( 'abc' ) ) ) AS TABLE v0 ;
mysqld: /home/mysql/mysql-server/sql/dd/dd_table.cc:1380: bool dd::add_part_col_vals(partition_info *, part_elem_value *, uint, dd::Partition *, const HA_CREATE_INFO *, const List<Create_field> &, String *): Assertion `!col_val->item_expression->null_value' failed.
2023-06-13T18:11:40Z 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: 0xffff04001040
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 = ffffa84ff538 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) [0xffffad3a37a0]
/lib/aarch64-linux-gnu/libc.so.6(gsignal+0xe0) [0xffffaca17d78]
/lib/aarch64-linux-gnu/libc.so.6(abort+0x114) [0xffffaca04aac]
/lib/aarch64-linux-gnu/libc.so.6(+0x2d490) [0xffffaca11490]
/lib/aarch64-linux-gnu/libc.so.6(+0x2d4f4) [0xffffaca114f4]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x458f780]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x4588f08]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x4570c0c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(dd::create_dd_user_table(THD*, dd::Schema const&, std::__cxx11::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, HA_CREATE_INFO*, List<Create_field> const&, KEY const*, unsigned int, Alter_info::enum_enable_or_disable, FOREIGN_KEY const*, unsigned int, Mem_root_array<Sql_check_constraint_spec*> const*, handler*)+0x510) [0x45671f8]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(dd::create_table(THD*, dd::Schema const&, std::__cxx11::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, HA_CREATE_INFO*, List<Create_field> const&, KEY const*, unsigned int, Alter_info::enum_enable_or_disable, FOREIGN_KEY const*, unsigned int, Mem_root_array<Sql_check_constraint_spec*> const*, handler*)+0x124) [0x45750e8]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x17d717c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(mysql_create_table_no_lock(THD*, char const*, char const*, HA_CREATE_INFO*, Alter_info*, unsigned int, bool, bool*, handlerton**)+0x14c0) [0x17d17ec]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Query_result_create::create_table_for_query_block(THD*)+0x11c4) [0x25bfee0]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Sql_cmd_create_table::execute(THD*)+0x1d18) [0x2573704]
/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) [0xffffad349624]
/lib/aarch64-linux-gnu/libc.so.6(+0xd149c) [0xffffacab549c]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (ffff040243c0): CREATE TABLE v2 PARTITION BY LIST COLUMNS ( v2 ) ( PARTITION v2 VALUES IN ( MICROSECOND ( 'abc' ) ) ) AS TABLE v0
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 -DUSE_LD_GOLD=1 -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 v0(c1 INT);
CREATE TABLE v2 PARTITION BY LIST COLUMNS ( v2 ) ( PARTITION v2 VALUES IN ( MICROSECOND ( 'abc' ) ) ) AS TABLE v0 ;
```

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 only debug build is affected.

regards,
Umesh

-- release build

./mtr bug111403 --nocheck-testcases
Logging: ./mtr  bug111403 --nocheck-testcases
MySQL Version 8.0.33
Checking supported features
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 v0(c1 INT);
CREATE TABLE v2 PARTITION BY LIST COLUMNS ( v2 ) ( PARTITION v2 VALUES IN ( MICROSECOND ( 'abc' ) ) ) AS TABLE v0 ;
[ 50%] main.bug111403                            [ fail ]
        Test ended at 2023-06-14 06:36:14

CURRENT_TEST: main.bug111403
mysqltest: At line 5: Query 'CREATE TABLE v2 PARTITION BY LIST COLUMNS ( v2 ) ( PARTITION v2 VALUES IN ( MICROSECOND ( 'abc' ) ) ) AS TABLE v0 ' failed.
ERROR 1488 (HY000): Field in list of fields for partition function not found in table

-- debug build - affected

./mtr bug111403 --nocheck-testcases --debug-server
Logging: ./mtr  bug111403 --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 v0(c1 INT);
CREATE TABLE v2 PARTITION BY LIST COLUMNS ( v2 ) ( PARTITION v2 VALUES IN ( MICROSECOND ( 'abc' ) ) ) AS TABLE v0 ;
[ 50%] main.bug111403                            [ fail ]
        Test ended at 2023-06-14 06:36:48

CURRENT_TEST: main.bug111403
mysqltest: At line 5: Query 'CREATE TABLE v2 PARTITION BY LIST COLUMNS ( v2 ) ( PARTITION v2 VALUES IN ( MICROSECOND ( 'abc' ) ) ) AS TABLE v0 ' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

-bt
#0  0x00007f1baf5f2aa1 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  0x00007f1bad82c387 in raise () from /lib64/libc.so.6
#5  0x00007f1bad82da78 in abort () from /lib64/libc.so.6
#6  0x00007f1bad8251a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f1bad825252 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000003dc0ed2 in dd::add_part_col_vals(partition_info*, p_elem_val*, unsigned int, dd::Partition*, HA_CREATE_INFO const*, List<Create_field> const&, String*) ()
#9  0x0000000003dc307a in dd::fill_dd_partition_from_create_info(THD*, dd::Table*, HA_CREATE_INFO const*, List<Create_field> const&, partition_info*) ()
#10 0x0000000003dc72fd in dd::fill_dd_table_from_create_info(THD*, dd::Table*, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, HA_CREATE_INFO const*, List<Create_field> const&, KEY const*, unsigned int, Alter_info::enum_enable_or_disable, FOREIGN_KEY const*, unsigned int, Mem_root_array<Sql_check_constraint_spec*> const*, handler*) ()
#11 0x0000000003dc7e96 in dd::create_dd_user_table(THD*, dd::Schema const&, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, HA_CREATE_INFO*, List<Create_field> const&, KEY const*, unsigned int, Alter_info::enum_enable_or_disable, FOREIGN_KEY const*, unsigned int, Mem_root_array<Sql_check_constraint_spec*> const*, handler*) ()
#12 0x0000000003dc81dc in dd::create_table(THD*, dd::Schema const&, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, HA_CREATE_INFO*, List<Create_field> const&, KEY const*, unsigned int, Alter_info::enum_enable_or_disable, FOREIGN_KEY const*, unsigned int, Mem_root_array<Sql_check_constraint_spec*> const*, handler*) ()
#13 0x000000000314f05d in rea_create_base_table(THD*, char const*, dd::Schema const&, char const*, char const*, HA_CREATE_INFO*, List<Create_field>&, unsigned int, KEY*, Alter_info::enum_enable_or_disable, unsigned int, FOREIGN_KEY*, Mem_root_array<Sql_check_constraint_spec*> const*, handler*, bool, bool, partition_info*, bool*, std::unique_ptr<dd::Table, std::default_delete<dd::Table> >*, handlerton**) ()
#14 0x0000000003161535 in create_table_impl(THD*, dd::Schema const&, char const*, char const*, char const*, char const*, HA_CREATE_INFO*, Alter_info*, bool, unsigned int, bool, bool, bool, bool*, KEY**, unsigned int*, Alter_info::enum_enable_or_disable, FOREIGN_KEY**, unsigned int*, FOREIGN_KEY*, unsigned int, dd::Table const*, unsigned int, std::unique_ptr<dd::Table, std::default_delete<dd::Table> >*, handlerton**) ()
#15 0x0000000003161c61 in mysql_create_table_no_lock(THD*, char const*, char const*, HA_CREATE_INFO*, Alter_info*, unsigned int, bool, bool*, handlerton**) ()
#16 0x000000000358cff1 in create_table_from_items(THD*, HA_CREATE_INFO*, Table_ref*, Alter_info*, mem_root_deque<Item*> const&, handlerton**) ()
#17 0x000000000358d1d4 in Query_result_create::create_table_for_query_block(THD*) ()
#18 0x0000000003578678 in populate_table(THD*, LEX*) ()
#19 0x000000000357936a in Sql_cmd_create_table::execute(THD*) ()
#20 0x00000000030c24af in mysql_execute_command(THD*, bool) ()
#21 0x00000000030c5dc9 in dispatch_sql_command(THD*, Parser_state*) ()
#22 0x00000000030c752b in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#23 0x00000000030c926e in do_command(THD*) ()
#24 0x0000000003227bb7 in handle_connection ()
#25 0x0000000004743af9 in pfs_spawn_thread ()
#26 0x00007f1baf5edea5 in start_thread () from /lib64/libpthread.so.0
#27 0x00007f1bad8f4b2d in clone () from /lib64/libc.so.6

The bug seems to be introduced in commit: 3b2288bf1102bd420668f38a072785ef10b8e0b5. The bug shares the same bug introduced commit with bug #111322. It is very possible that the two reported bugs share the same bug root cause.