MySQL Bugs: #112919: hash_set_operations optimizer off assertion error

Bug #112919	hash_set_operations optimizer off assertion error
Submitted:	1 Nov 2023 13:54	Modified:	28 Feb 2024 17:09
Reporter:	Pedro Ferreira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S6 (Debug Builds)
Version:	8.2.0	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	hash_set_operations

Description:
Run these queries:

PREPARE p0 FROM '(SELECT 3) EXCEPT (SELECT 1)';
SET SESSION optimizer_switch = 'hash_set_operations=off';
EXECUTE p0;

The EXECUTE will trigger an assertion error at sql/sql_select.cc:835
assert(thd->is_error() || thd->killed);

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

Hello Pedro Ferreira,

Thank you for the report and feedback.
Observed that 8.2.0 debug build is affected.

regards,
Umesh

-- 8.2.0 debug build

#0  0x00007f9f53db1aa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x00000000040c60c1 in my_write_core(int) ()
#2  0x000000000334a9c2 in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007f9f520fb387 in raise () from /lib64/libc.so.6
#5  0x00007f9f520fca78 in abort () from /lib64/libc.so.6
#6  0x00007f9f520f41a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f9f520f4252 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000323294b in Sql_cmd_dml::execute(THD*) ()
#9  0x00000000031d13b9 in mysql_execute_command(THD*, bool) ()
#10 0x00000000031fdcf1 in Prepared_statement::execute(THD*, String*, bool) ()
#11 0x0000000003200f39 in Prepared_statement::execute_loop(THD*, String*, bool) ()
#12 0x00000000032016a3 in mysql_sql_stmt_execute(THD*) ()
#13 0x00000000031ce26c in mysql_execute_command(THD*, bool) ()
#14 0x00000000031d284f in dispatch_sql_command(THD*, Parser_state*) ()
#15 0x00000000031d3f63 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#16 0x00000000031d5c7b in do_command(THD*) ()
#17 0x000000000333c9b6 in handle_connection ()
#18 0x00000000048a7ae8 in pfs_spawn_thread ()
#19 0x00007f9f53dacea5 in start_thread () from /lib64/libpthread.so.0
#20 0x00007f9f521c3b2d in clone () from /lib64/libc.so.6

Documented fix as follows in the MySQL 8.4.0 changelog:

    SET SESSION optimizer_switch = 'hash_set_operations=off' after
    preparing a statement led to an assertion in sql/sql_select.cc
    when trying to execute the statement.

Closed.