MySQL Bugs: #110818: ANY assertion error

Bug #110818	ANY assertion error
Submitted:	26 Apr 2023 12:39	Modified:	28 Apr 2023 7:45
Reporter:	Pedro Ferreira	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S6 (Debug Builds)
Version:	8.0.33	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	ANY, subquery

Description:
Run these queries:

CREATE TABLE t0(c0 INT);
INSERT INTO t0(c0) VALUES (NULL);
SELECT CAST(t0.c0 > ANY(SELECT 1 FROM t0) AS SIGNED) FROM t0;

The SELECT query will trigger an assertion error at sql/item_func.cc:1879
assert(is_nullable());

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.0.33 debug build is affected.

regards,
Umesh

- release build not affected

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

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0(c0 INT);
INSERT INTO t0(c0) VALUES (NULL);
SELECT CAST(t0.c0 > ANY(SELECT 1 FROM t0) AS SIGNED) FROM t0;
CAST(t0.c0 > ANY(SELECT 1 FROM t0) AS SIGNED)
NULL
[ 50%] main.bug110818                            [ pass ]     86
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.086 of 11 seconds executing testcases

Completed: All 2 tests were successful.

- debug build affected

./mtr bug110818 --nocheck-testcases --debug-server
Logging: ./mtr  bug110818 --nocheck-testcases --debug-server
MySQL Version 8.0.33
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.0.33/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0(c0 INT);
INSERT INTO t0(c0) VALUES (NULL);
SELECT CAST(t0.c0 > ANY(SELECT 1 FROM t0) AS SIGNED) FROM t0;
[ 50%] main.bug110818                            [ fail ]
        Test ended at 2023-04-28 09:44:39

CURRENT_TEST: main.bug110818
mysqltest: At line 3: Query 'SELECT CAST(t0.c0 > ANY(SELECT 1 FROM t0) AS SIGNED) FROM t0' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

-bt

#0  0x00007fc77151daa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000003f9296e in my_write_core(int) ()
#2  0x00000000032365f5 in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007fc76f757387 in raise () from /lib64/libc.so.6
#5  0x00007fc76f758a78 in abort () from /lib64/libc.so.6
#6  0x00007fc76f7501a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007fc76f750252 in __assert_fail () from /lib64/libc.so.6
#8  0x00000000033efa2f in Item_typecast_signed::val_int() ()
#9  0x000000000337f22e in Item::send(Protocol*, String*) ()
#10 0x000000000302cf25 in THD::send_result_set_row(mem_root_deque<Item*> const&) ()
#11 0x0000000003527fe5 in Query_result_send::send_data(THD*, mem_root_deque<Item*> const&) ()
#12 0x0000000003190a33 in Query_expression::ExecuteIteratorQuery(THD*) ()
#13 0x0000000003190b92 in Query_expression::execute(THD*) ()
#14 0x000000000311a12a in Sql_cmd_dml::execute_inner(THD*) ()
#15 0x0000000003123873 in Sql_cmd_dml::execute(THD*) ()
#16 0x00000000030c4964 in mysql_execute_command(THD*, bool) ()
#17 0x00000000030c5dc9 in dispatch_sql_command(THD*, Parser_state*) ()
#18 0x00000000030c752b in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#19 0x00000000030c926e in do_command(THD*) ()
#20 0x0000000003227bb7 in handle_connection ()
#21 0x0000000004743af9 in pfs_spawn_thread ()
#22 0x00007fc771518ea5 in start_thread () from /lib64/libpthread.so.0
#23 0x00007fc76f81fb2d in clone () from /lib64/libc.so.6