MySQL Bugs: #113385: Intersect decimal precision assertion error

Bug #113385	Intersect decimal precision assertion error
Submitted:	9 Dec 2023 11:48	Modified:	8 Mar 2024 18:31
Reporter:	Pedro Ferreira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S6 (Debug Builds)
Version:	8.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	intersect

Description:
Run these statements:

CREATE TABLE t0 (c0 YEAR);
CREATE TABLE t1 (c0 INT);
(SELECT x FROM (SELECT NULL FROM t1 GROUP BY t1.c0) x(x)) INTERSECT (SELECT t0.c0 FROM t0);

The SELECT query will trigger an assertion error at sql/item.cc:6626
assert(decimal_precision() == 4);

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.

Fixed typo in the title

Hi Mr. Ferreira,

Thank you for your bug report.

We tested your report on 8.0 debug and release builds  and 8.2.0 release build and it worked just fine.

However, with 8.2.0 debug build we repeated the crash:

----------------------
Assertion failed: (decimal_precision() == 4), function tmp_table_field_from_field_type, file item.cc, line 6626.
2023-12-11T10:48:53Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7fdcd0187400
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 = 70000de14f28 thread_stack 0x100000
0   mysqld                              0x000000010e6836ff my_print_stacktrace(unsigned char const*, unsigned long) + 63
1   mysqld                              0x000000010db83c3e print_fatal_signal(int) + 590
2   mysqld                              0x000000010db83dba handle_fatal_signal + 90
3   libsystem_platform.dylib            0x00007ff808f9837d _sigtramp + 29
4   mysqld                              0x000000010e66838b strlength(char const*) + 123
5   libsystem_c.dylib                   0x00007ff808e89a49 abort + 126
6   libsystem_c.dylib                   0x00007ff808e88d30 err + 0
7   mysqld                              0x000000010d5c23a5 Item::tmp_table_field_from_field_type(TABLE*, bool) const + 2021
8   mysqld                              0x000000010d5cc9ed Item_aggregate_type::make_field_by_type(TABLE*, bool) + 429
9   mysqld                              0x000000010da97a8d create_tmp_field(THD*, TABLE*, Item*, Item::Type, Mem_root_array<Func_ptr>*, Field**, Field**, bool, bool, bool, bool) + 701
10  mysqld                              0x000000010da98e31 create_tmp_table(THD*, Temp_table_param*, mem_root_deque<Item*> const&, ORDER*, bool, bool, unsigned long long, unsigned long long, char const*) + 2769
11  mysqld                              0x000000010daa651e Query_result_union::create_result_table(THD*, mem_root_deque<Item*> const&, bool, unsigned long long, char const*, bool, bool, Query_term_set_op*) + 718
12  mysqld                              0x000000010daa73b0 create_tmp_table_for_set_op(THD*, Query_term*, mem_root_deque<Item*>&, unsigned long long) + 272
13  mysqld                              0x000000010daa706f Query_expression::prepare_query_term(THD*, Query_term*, Query_result*, unsigned long long, unsigned long long, int, Mem_root_array<bool>&) + 2511
14  mysqld                              0x000000010daa69aa Query_expression::prepare_query_term(THD*, Query_term*, Query_result*, unsigned long long, unsigned long long, int, Mem_root_array<bool>&) + 778
15  mysqld                              0x000000010daa7c42 Query_expression::prepare(THD*, Query_result*, mem_root_deque<Item*>*, unsigned long long, unsigned long long) + 1922
16  mysqld                              0x000000010da2bb08 Sql_cmd_select::prepare_inner(THD*) + 344
17  mysqld                              0x000000010da2b778 Sql_cmd_dml::prepare(THD*) + 776
18  mysqld                              0x000000010da2be34 Sql_cmd_dml::execute(THD*) + 548
19  mysqld                              0x000000010d9c5d0f mysql_execute_command(THD*, bool) + 3359
20  mysqld                              0x000000010d9c3f09 dispatch_sql_command(THD*, Parser_state*) + 1321
21  mysqld                              0x000000010d9c1040 dispatch_command(THD*, COM_DATA const*, enum_server_command) + 3376
22  mysqld                              0x000000010d9c306c do_command(THD*) + 988
23  mysqld                              0x000000010db68bb4 handle_connection(void*) + 340
24  mysqld                              0x000000010eeeed73 pfs_spawn_thread(void*) + 211
25  libsystem_pthread.dylib             0x00007ff808f6b202 _pthread_start + 99
26  libsystem_pthread.dylib             0x00007ff808f66bab thread_start + 15

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fdcd00eb630): (SELECT x FROM (SELECT NULL FROM t1 GROUP BY t1.c0) x(x)) INTERSECT (SELECT t0.c0 FROM t0)
Connection ID (thread ID): 8
Status: NOT_KILLED
---------------------

This is now a verified bug report for 8.2.0 builds only, on any platform and any CPU.

Since  it crashes only debug builds, this is not a high priority bug.

Documented fix as follows in the MySQL 8.4.0 changelog:

    In the debug server, an intersection comparing columns of
    different types sometimes triggered an assert in sql/item.cc.

Closed.

Thank you, Jon.