MySQL Bugs: #115233: Case query assertion error

Bug #115233	Case query assertion error
Submitted:	5 Jun 2024 16:10	Modified:	5 Aug 2024 22:19
Reporter:	Pedro Ferreira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S6 (Debug Builds)
Version:	8.4	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	case ... when, NOW(), null

Description:
Run this query:

SELECT CASE WHEN 1 THEN now() ELSE x.x END FROM (SELECT NULL) x(x);

An assertion is reported at mysys/my_time.cc:1257:
int my_useconds_to_str(char*, unsigned int, unsigned int): Assertion `dec <= DATETIME_MAX_DECIMALS' failed.

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 query above.

Hi Mr. Ferreira,

Thank you , very much , for your bug report.

We managed to repeat it and this is the info that we get, including the stacktrace:

Assertion failed: (dec <= DATETIME_MAX_DECIMALS), function my_useconds_to_str, file my_time.cc, line 1257.
2024-06-05T16:30:37Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f9e3f928400
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 = 70000c440eb8 thread_stack 0x100000
0   mysqld-debug                        0x00000001073ccbfa my_print_stacktrace(unsigned char const*, unsigned long) + 90
1   mysqld-debug                        0x00000001061213a3 print_fatal_signal(int, __siginfo*) + 675
2   mysqld-debug                        0x0000000106121088 handle_fatal_signal(int, __siginfo*, void*) + 120
3   libsystem_platform.dylib            0x00007ff800c9bfdd _sigtramp + 29
4   mysqld-debug                        0x0000000108afc038 write_two_digits(int, char*)::writer + 76
5   libsystem_c.dylib                   0x00007ff800b92a79 abort + 126
6   libsystem_c.dylib                   0x00007ff800b91d68 err + 0
7   mysqld-debug                        0x0000000107ffd7a7 my_useconds_to_str(char*, unsigned int, unsigned int) + 71
8   mysqld-debug                        0x0000000107ffd92a my_datetime_to_str(MYSQL_TIME const&, char*, unsigned int) + 74
9   mysqld-debug                        0x0000000105b71723 Protocol_text::store_datetime(MYSQL_TIME const&, unsigned int)::$_0::operator()(char*) const + 35
10  mysqld-debug                        0x0000000105b6f29c bool store_temporal<Protocol_text::store_datetime(MYSQL_TIME const&, unsigned int)::$_0>(Protocol_text::store_datetime(MYSQL_TIME const&, unsigned int)::$_0, String*) + 92
11  mysqld-debug                        0x0000000105b6f235 Protocol_text::store_datetime(MYSQL_TIME const&, unsigned int) + 181
12  mysqld-debug                        0x00000001056ced68 Item::send(Protocol*, String*) + 952
13  mysqld-debug                        0x0000000105cd9483 THD::send_result_set_row(mem_root_deque<Item*> const&) + 355
14  mysqld-debug                        0x0000000105b74bcf Query_result_send::send_data(THD*, mem_root_deque<Item*> const&) + 95
15  mysqld-debug                        0x0000000105f8f1c7 Query_expression::ExecuteIteratorQuery(THD*) + 2407
16  mysqld-debug                        0x0000000105f8f634 Query_expression::execute(THD*) + 324
17  mysqld-debug                        0x0000000105eaf393 Sql_cmd_dml::execute_inner(THD*) + 403
18  mysqld-debug                        0x0000000105eae5c3 Sql_cmd_dml::execute(THD*) + 2403
19  mysqld-debug                        0x0000000105e037cb mysql_execute_command(THD*, bool) + 31179
20  mysqld-debug                        0x0000000105df9b70 dispatch_sql_command(THD*, Parser_state*) + 2784
21  mysqld-debug                        0x0000000105df569b dispatch_command(THD*, COM_DATA const*, enum_server_command) + 9307
22  mysqld-debug                        0x0000000105df8156 do_command(THD*) + 2118
23  mysqld-debug                        0x00000001060ef854 handle_connection(void*) + 500
24  mysqld-debug                        0x0000000108301334 pfs_spawn_thread(void*) + 324
25  libsystem_pthread.dylib             0x00007ff800c6d18b _pthread_start + 99
26  libsystem_pthread.dylib             0x00007ff800c68ae3 thread_start + 15

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f9e3f98f630): SELECT CASE WHEN 1 THEN now() ELSE x.x END FROM (SELECT NULL) x(x)
Connection ID (thread ID): 9
Status: NOT_KILLED

Release binary works just fine.

Since this is debug binary, your severity is set quite correctly.

Verified as reported.

Thanks a lot.

Documented fix as follows in the MySQL 9.1.0 changelog:

    A query such as SELECT CASE WHEN 1 THEN NOW() ELSE x.x END FROM
    (SELECT NULL) x(x) led an assert. This occurred because a
    generated column description used a temporal type with a
    fractional seconds precision of 31, which is an invalid
    precision value. The invalid precision came from type
    aggregation, where one of the underlying items to the CASE
    expression was a NULL expression, which has a decimal precision
    equal 31, meaning an undefined precision.

    The problem is fixed by not aggregating types from expressions
    that have a NULL type.

    This issue did not occur with numeric and string arguments,
    since they cannot be aggregated directly into a temporal type.

    This issue did not occur with MySQL 8.0, or with MySQL 8.4
    release binaries.

Closed.

Thank you, Jon.