Reference Resolution Fixed Status Check Failure (resolve_ref_in_select_and_group)

Reproduction Steps

CREATE TABLE v0 (v1 INT, v2 INT, v3 INT);

INSERT INTO v0 ( v1 ) VALUES ( NULL ) , ( v1 + v1 LIKE 'x' ) , ( ( CASE WHEN v1 = 24 THEN 0 ELSE v1 IN ( SELECT
v1 FROM ( SELECT ( SELECT v1 GROUP BY v1 ORDER BY v1 * 0 ) , -1 , 55 FROM v0 GROUP BY v1 , v1 ) AS v2 ) END ) );

Note: The user-provided SQL SELECT v1 FROM (SELECT v1 FROM v0) AS t WHERE v1 IN (SELECT v1); did not trigger the
bug. A more complex SQL from historical logs was needed.

Stack Trace

#0 __pthread_kill_implementation
#1 raise
#2 abort
#3 __assert_fail_base.cold
#4 __assert_fail
#5 resolve_ref_in_select_and_group (sql/item.cc:5870)
#6 Item_ref::fix_fields (sql/item.cc:8432)
#7 Item_field::fix_fields (sql/item.cc:6426)
#8 Item::fix_fields_if_needed (sql/item.h:1113)
#9 Item_func::fix_fields (sql/item_func.cc:362)
#10 Item::fix_fields_if_needed_for_order_by (sql/item.h:1130)
#11 find_order_in_list (sql/sql_select.cc:28521)
#12 setup_order (sql/sql_select.cc:28568)
#13 setup_without_group (sql/sql_select.cc:964)
#14 JOIN::prepare (sql/sql_select.cc:1577)
#15 subselect_single_select_engine::prepare (sql/item_subselect.cc:3981)
#16 Item_subselect::fix_fields (sql/item_subselect.cc:294)
...

From AI:
Root Cause Analysis

The issue occurs in resolve_ref_in_select_and_group() when resolving field references:

// sql/item.cc:5860-5871

  if (select_ref != not_found_item && !ambiguous_fields)

  {

    DBUG_ASSERT(*select_ref != 0);

    if (!select->ref_pointer_array[counter])

    {

      my_error(ER_ILLEGAL_REFERENCE, MYF(0),

               ref->name.str, "forward reference in item list");

      return NULL;

    }

    DBUG_ASSERT((*select_ref)->fixed());  // <-- Assertion fails here

    return &select->ref_pointer_array[counter];

  }

Problem Flow:
1. Complex nested subquery with derived tables and ORDER BY/GROUP BY clauses
2. During fix_fields processing, field references need to be resolved
3. When resolving a reference, the target Item should already be "fixed"
4. However, in certain scenarios (forward references, nested derived tables), the target Item has not yet been
fixed
5. This causes the assertion (*select_ref)->fixed() to fail

Related MDEVs:

• MDEV-38473: Incorrect Empty Set with HAVING clause when SELECT and GROUP BY use different aliases - may contain
  relevant fix
• MDEV-31632: Unresolvable outer reference causes null pointer exception

mysql> CREATE TABLE v0 (v1 INT, v2 INT, v3 INT);
ERROR 1050 (42S01): Table 'v0' already exists
mysql>
mysql> INSERT INTO v0 ( v1 ) VALUES ( NULL ) , ( v1 + v1 LIKE 'x' ) , ( ( CASE WHEN v1 = 24 THEN 0 ELSE v1 IN ( SELECT
-> v1 FROM ( SELECT ( SELECT v1 GROUP BY v1 ORDER BY v1 * 0 ) , -1 , 55 FROM v0 GROUP BY v1 , v1 ) AS v2 ) END ) );

ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on '21.91.3.238:3307' (111)
ERROR:
Can't connect to the server