MySQL Bugs: #112105: Create table from prepared statement issues

Bug #112105	Create table from prepared statement issues
Submitted:	18 Aug 2023 2:51	Modified:	18 Aug 2023 8:46
Reporter:	Pedro Ferreira	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.1.0, 8.0.34	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	CREATE TABLE, partition table, prepare statement

Description:
Run these queries:

PREPARE p0 FROM 'CREATE TABLE t1 (c0 INT) PARTITION BY HASH(-NULL)';
EXECUTE p0;
-- ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed (expected error)
EXECUTE p0;
-- Assertion error at sql/item_func.cc:403
-- assert(fixed == 0 || basic_const_item());

However if `-` is removed from the CREATE statement:

PREPARE p0 FROM 'CREATE TABLE t1 (c0 INT) PARTITION BY HASH(NULL)';
EXECUTE p0;
-- ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed (expected error)
EXECUTE p0;
-- No error

The second EXECUTE is successful when it shouldn't.

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the statements above.

Hello Pedro Ferreira,

Thank you for the report and feedback.
Observed that 8.1.0 debug build asserts.

-- release build second time succeeds as you mentioned

Server version: 8.1.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> PREPARE p0 FROM 'CREATE TABLE t1 (c0 INT) PARTITION BY HASH(-NULL)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE p0;
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql> EXECUTE p0;
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql> EXECUTE p0;
Query OK, 0 rows affected (0.03 sec)

-- debug build asserts

bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.1.0-debug MySQL Community Server - GPL - Debug

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> PREPARE p0 FROM 'CREATE TABLE t1 (c0 INT) PARTITION BY HASH(-NULL)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE p0;
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql> -- ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed (expected error)
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE p0;
ERROR 2013 (HY000): Lost connection to MySQL server during query

-bt
(gdb) bt
#0  0x00007f51b2c6caa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000004076b77 in my_write_core(int) ()
#2  0x000000000330766e in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007f51b0fb7387 in raise () from /lib64/libc.so.6
#5  0x00007f51b0fb8a78 in abort () from /lib64/libc.so.6
#6  0x00007f51b0fb01a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f51b0fb0252 in __assert_fail () from /lib64/libc.so.6
#8  0x00000000034b355e in Item_func::fix_fields(THD*, Item**) ()
#9  0x00000000031a1045 in fix_fields_part_func(THD*, Item*, TABLE*, bool, bool) ()
#10 0x00000000031a1543 in fix_partition_func(THD*, TABLE*, bool) ()
#11 0x0000000003294565 in unpack_partition_info(THD*, TABLE*, TABLE_SHARE*, handlerton*, bool) ()
#12 0x00000000032a298c in open_table_from_share(THD*, TABLE_SHARE*, char const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, dd::Table const*) ()
#13 0x00000000034227ac in ha_create_table(THD*, char const*, char const*, char const*, HA_CREATE_INFO*, bool, bool, dd::Table*) ()
#14 0x000000000321ba67 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**) ()
#15 0x000000000322d99c 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**) ()
#16 0x000000000322e0d8 in mysql_create_table_no_lock(THD*, char const*, char const*, HA_CREATE_INFO*, Alter_info*, unsigned int, bool, bool*, handlerton**) ()
#17 0x000000000322e6b3 in mysql_create_table(THD*, Table_ref*, HA_CREATE_INFO*, Alter_info*) ()
#18 0x0000000003655e77 in Sql_cmd_create_table::execute(THD*) ()
#19 0x000000000318eb63 in mysql_execute_command(THD*, bool) ()
#20 0x00000000031bd6b4 in Prepared_statement::execute(THD*, String*, bool) ()
#21 0x00000000031c08af in Prepared_statement::execute_loop(THD*, String*, bool) ()
#22 0x00000000031c1009 in mysql_sql_stmt_execute(THD*) ()
#23 0x000000000318e013 in mysql_execute_command(THD*, bool) ()
#24 0x0000000003192579 in dispatch_sql_command(THD*, Parser_state*) ()
#25 0x0000000003193b7f in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#26 0x0000000003195763 in do_command(THD*) ()
#27 0x00000000032f967c in handle_connection ()
#28 0x000000000482e4e4 in pfs_spawn_thread ()
#29 0x00007f51b2c67ea5 in start_thread () from /lib64/libpthread.so.0
#30 0x00007f51b107fb2d in clone () from /lib64/libc.so.6
(gdb) quit

regards,
Umesh

-- 8.0.34 affected as well 

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> PREPARE p0 FROM 'CREATE TABLE t1 (c0 INT) PARTITION BY HASH(-NULL)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE p0;
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql> EXECUTE p0;
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql> EXECUTE p0;
Query OK, 0 rows affected (0.02 sec)

-- debug build asserts

 bt
#0  0x00007fe4abf57aa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x000000000402c28e in my_write_core(int) ()
#2  0x00000000032d235e in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007fe4aa2a2387 in raise () from /lib64/libc.so.6
#5  0x00007fe4aa2a3a78 in abort () from /lib64/libc.so.6
#6  0x00007fe4aa29b1a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007fe4aa29b252 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000347b286 in Item_func::fix_fields(THD*, Item**) ()
#9  0x0000000003171220 in fix_fields_part_func(THD*, Item*, TABLE*, bool, bool) ()
#10 0x000000000317171e in fix_partition_func(THD*, TABLE*, bool) ()
#11 0x0000000003263bb5 in unpack_partition_info(THD*, TABLE*, TABLE_SHARE*, handlerton*, bool) ()
#12 0x00000000032720f3 in open_table_from_share(THD*, TABLE_SHARE*, char const*, unsigned int, unsigned int, unsigned int, TABLE*, bool, dd::Table const*) ()
#13 0x00000000033ec502 in ha_create_table(THD*, char const*, char const*, char const*, HA_CREATE_INFO*, bool, bool, dd::Table*) ()
#14 0x00000000031ec969 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**) ()
#15 0x00000000031feaa3 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**) ()
#16 0x00000000031ff1df in mysql_create_table_no_lock(THD*, char const*, char const*, HA_CREATE_INFO*, Alter_info*, unsigned int, bool, bool*, handlerton**) ()
#17 0x00000000031ff7ba in mysql_create_table(THD*, Table_ref*, HA_CREATE_INFO*, Alter_info*) ()
#18 0x00000000036117cf in Sql_cmd_create_table::execute(THD*) ()
#19 0x000000000315edb4 in mysql_execute_command(THD*, bool) ()
#20 0x000000000318d7fb in Prepared_statement::execute(THD*, String*, bool) ()
#21 0x00000000031909f7 in Prepared_statement::execute_loop(THD*, String*, bool) ()
#22 0x0000000003191151 in mysql_sql_stmt_execute(THD*) ()
#23 0x000000000315e264 in mysql_execute_command(THD*, bool) ()
#24 0x00000000031626a2 in dispatch_sql_command(THD*, Parser_state*) ()
#25 0x0000000003163cd5 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#26 0x000000000316596c in do_command(THD*) ()
#27 0x00000000032c437c in handle_connection ()
#28 0x00000000047e1af5 in pfs_spawn_thread ()
#29 0x00007fe4abf52ea5 in start_thread () from /lib64/libpthread.so.0
#30 0x00007fe4aa36ab2d in clone () from /lib64/libc.so.6
(gdb)