Bug #111807 SELECT query assertion error
Submitted: 19 Jul 2023 7:34 Modified: 19 Jul 2023 8:16
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S6 (Debug Builds)
Version:8.1.0, 8.0.34 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:x86 (x86_64)
Tags: subquery

[19 Jul 2023 7:34] Pedro Ferreira
Description:
Run these queries:

CREATE TABLE t0 (c0 MEDIUMINT UNSIGNED);
SELECT 1 FROM (SELECT 1) x(x) JOIN t0 WHERE least(t0.c0, t0.c0) AND (-unix_timestamp(), 1) = (t0.c0, x.x);

The SELECT query will trigger an assertion error at sql/item_func.cc:3973:
assert(!unsigned_flag || !arg_val.is_negative());

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.
[19 Jul 2023 8:16] MySQL Verification Team
Hello Pedro Ferreira,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh
[19 Jul 2023 8:24] MySQL Verification Team
- 8.0.34, 8.1.0 debug builds affected

./mtr bug111807 --nocheck-testcases --debug-server
Logging: ./mtr  bug111807 --nocheck-testcases --debug-server
MySQL Version 8.1.0
Checking supported features
 - Binaries are debug compiled
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.1.0/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0 (c0 MEDIUMINT UNSIGNED);
SELECT 1 FROM (SELECT 1) x(x) JOIN t0 WHERE least(t0.c0, t0.c0) AND (-unix_timestamp(), 1) = (t0.c0, x.x);
[ 50%] main.bug111807                            [ fail ]
        Test ended at 2023-07-19 10:07:19

CURRENT_TEST: main.bug111807
mysqltest: At line 2: Query 'SELECT 1 FROM (SELECT 1) x(x) JOIN t0 WHERE least(t0.c0, t0.c0) AND (-unix_timestamp(), 1) = (t0.c0, x.x)' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

-
Thread 1 (Thread 0x7fb1007ee700 (LWP 4327)):
#0  0x00007fb112c04aa1 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  0x00007fb110f4f387 in raise () from /lib64/libc.so.6
#5  0x00007fb110f50a78 in abort () from /lib64/libc.so.6
#6  0x00007fb110f481a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007fb110f48252 in __assert_fail () from /lib64/libc.so.6
#8  0x00000000034b6773 in Item_func_min_max::int_op() ()
#9  0x00000000034afec0 in Item_func_numhybrid::val_int() ()
#10 0x00000000034b0ad3 in Item_func_min_max::val_int() ()
#11 0x000000000346d05e in Arg_comparator::compare_int_signed_unsigned() ()
#12 0x000000000347cc11 in Arg_comparator::compare() ()
#13 0x000000000346be47 in Item_func_ne::val_int() ()
#14 0x000000000344c1a6 in Item::val_bool() ()
#15 0x00000000034b3387 in eval_const_cond(THD*, Item*, bool*) ()
#16 0x000000000316e5b3 in remove_eq_conds(THD*, Item*, Item**, Item::cond_result*) [clone .localalias] ()
#17 0x000000000316e48c in remove_eq_conds(THD*, Item*, Item**, Item::cond_result*) [clone .localalias] ()
#18 0x000000000317c60a in optimize_cond(THD*, Item**, COND_EQUAL**, mem_root_deque<Table_ref*>*, Item::cond_result*) ()
#19 0x000000000317fcca in JOIN::optimize(bool) ()
#20 0x00000000031e6c87 in Query_block::optimize(THD*, bool) ()
#21 0x000000000325c579 in Query_expression::optimize(THD*, TABLE*, bool, bool) ()
#22 0x00000000031e68af in Sql_cmd_dml::execute_inner(THD*) ()
#23 0x00000000031f03d3 in Sql_cmd_dml::execute(THD*) ()
#24 0x0000000003191136 in mysql_execute_command(THD*, bool) ()
#25 0x0000000003192579 in dispatch_sql_command(THD*, Parser_state*) ()
#26 0x0000000003193b7f in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#27 0x0000000003195763 in do_command(THD*) ()
#28 0x00000000032f967c in handle_connection ()
#29 0x000000000482e4e4 in pfs_spawn_thread ()
#30 0x00007fb112bffea5 in start_thread () from /lib64/libpthread.so.0
#31 0x00007fb111017b2d in clone () from /lib64/libc.so.6