MySQL Bugs: #112615: Subquery refcount assertion error

Bug #112615	Subquery refcount assertion error
Submitted:	4 Oct 2023 15:03	Modified:	7 Aug 2024 12:42
Reporter:	Pedro Ferreira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S6 (Debug Builds)
Version:	8.1.0	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	sub-query

Description:
This is a follow-up to Bug #111492.

Run this query:

SELECT 1 FROM (SELECT 1) y(y) JOIN (SELECT 1) z(z) ON (SELECT 1 FROM (SELECT 1) x(x) HAVING 1 IN (SELECT min(x.x) FROM (SELECT 1) w(w))) WHERE 1 = 2;

It will trigger an assertion error at sql/item.h:3286
assert(m_ref_count > 0);

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.

Hello Pedro Ferreira,

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

regards,
Umesh

- 8.0.34

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

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
SELECT 1 FROM (SELECT 1) y(y) JOIN (SELECT 1) z(z) ON (SELECT 1 FROM (SELECT 1) x(x) HAVING 1 IN (SELECT min(x.x) FROM (SELECT 1) w(w))) WHERE 1 = 2;
[ 50%] main.bug112615                            [ fail ]
        Test ended at 2023-10-05 08:01:57

CURRENT_TEST: main.bug112615
mysqltest: At line 1: Query 'SELECT 1 FROM (SELECT 1) y(y) JOIN (SELECT 1) z(z) ON (SELECT 1 FROM (SELECT 1) x(x) HAVING 1 IN (SELECT min(x.x) FROM (SELECT 1) w(w))) WHERE 1 = 2' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

-bt
#0  0x00007f831f92baa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x000000000402c28e in my_write_core(int) ()
#2  0x00000000032d235e in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007f831dc76387 in raise () from /lib64/libc.so.6
#5  0x00007f831dc77a78 in abort () from /lib64/libc.so.6
#6  0x00007f831dc6f1a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f831dc6f252 in __assert_fail () from /lib64/libc.so.6
#8  0x00000000031aae16 in Item::decrement_ref_count() ()
#9  0x0000000003425db5 in Item_ref::clean_up_after_removal(unsigned char*) ()
#10 0x000000000342d0d7 in Item_ref::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#11 0x0000000003474cf8 in Item_func::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#12 0x000000000312dda7 in Query_block::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#13 0x000000000322a142 in Query_expression::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#14 0x00000000034c526d in Item_subselect::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#15 0x00000000034c52cb in Item_in_subselect::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#16 0x0000000003474cf8 in Item_func::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#17 0x000000000312dda7 in Query_block::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#18 0x000000000322a142 in Query_expression::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#19 0x00000000034c526d in Item_subselect::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#20 0x0000000003474cf8 in Item_func::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) ()
#21 0x0000000003444426 in Item_cond::fix_fields(THD*, Item**) ()
#22 0x00000000031a30ff in Query_block::simplify_joins(THD*, mem_root_deque<Table_ref*>*, bool, bool, Item**, unsigned int*) [clone .localalias] ()
#23 0x00000000031a2f55 in Query_block::simplify_joins(THD*, mem_root_deque<Table_ref*>*, bool, bool, Item**, unsigned int*) [clone .localalias] ()
#24 0x00000000031a3802 in Query_block::apply_local_transforms(THD*, bool) [clone .localalias] ()
#25 0x00000000031aaa5b in Query_block::prepare(THD*, mem_root_deque<Item*>*) ()
#26 0x00000000031b6a51 in Sql_cmd_select::prepare_inner(THD*) ()
#27 0x00000000031c05c5 in Sql_cmd_dml::prepare(THD*) ()
#28 0x00000000031c08ed in Sql_cmd_dml::execute(THD*) ()
#29 0x0000000003161263 in mysql_execute_command(THD*, bool) ()
#30 0x00000000031626a2 in dispatch_sql_command(THD*, Parser_state*) ()
#31 0x0000000003163cd5 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#32 0x000000000316596c in do_command(THD*) ()
#33 0x00000000032c437c in handle_connection ()
#34 0x00000000047e1af5 in pfs_spawn_thread ()
#35 0x00007f831f926ea5 in start_thread () from /lib64/libpthread.so.0
#36 0x00007f831dd3eb2d in clone () from /lib64/libc.so.6

Documented fix as follows in the MySQL 9.1.0 changelog:

    A join on subqueries which themselves used subqueries in their
    HAVING and FROM clauses led to an assert in sql/item.h.

    The problem occurred when removing a scalar subquery with a
    HAVING clause after having decided that the subquery was part of
    a predicate that could be eliminated. The HAVING clause pointed
    to an aggregation object (Item_sum_min), indirectly accessed
    through an Item_aggregate_ref that was added by
    Item::split_sum_func2(). When removing the subquery, the
    reference count for the Item_aggregate_ref was decremented
    without ever having been incremented.

    We fix this by incrementing the object's reference count when it
    is created.

Closed.