MySQL Bugs: #113097: query bug racecondition

Bug #113097	query bug racecondition
Submitted:	16 Nov 2023 0:18	Modified:	19 Jun 10:09
Reporter:	Ivan L'olivier	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.35	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
A query should be returning a value but it's not.
If I change almost anything in the query, it starts working.

How to repeat:
CREATE TABLE temp (createdAt datetime, timezone varchar(64));

INSERT INTO temp VALUES ('2022-02-15 16:47:12', null);

SELECT
  *
FROM
  (
    SELECT
      coalesce(timezone, '+00:00') tz,
      convert_tz(createdAt, '+00:00', (select tz)) ca,
      if(createdAt, if((select 1) = 1, 1, 1), 1) random
    FROM temp
  ) aux
   WHERE ca = '2022-02-15 16:47:12';

Suggested fix:
I have no idea what is going on.. I imagine some race conditions 🤷‍♂️

Hello Ivan L'olivier,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

I was looking for a bug to tackle in a large and unfamiliar codebase using time-travel debugging tools (Undo), I traced the execution of the failing query in this bug and discovered that during WHERE condition pushdown to derived tables, the optimizer seems to incorrectly serializes column references when cloning expressions.

The issue occurs in the clone_expression() function (sql/sql_derived.cc:572) which uses a serialize-and-reparse approach. During serialization, Item_ref::print() outputs just the column alias "tz" instead of the full COALESCE expression when the reference is an alias for a function.  Note that outputting "(select tz)" at this point would also cause the same eventual failure.

Root Cause

The original query uses (select tz) as a clever workaround to reference the COALESCE expression without repeating it in the definition of the ca column. However, during condition pushdown, Item_ref::print() (sql/item.cc:8373) has this condition:

if (!const_item() && m_alias_of_expr &&
    ref_item()->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF &&
    table_name == nullptr && item_name.ptr())

This causes the function to print just "tz" instead of the full COALESCE expression during serialization which would ultimately produce the correct results
When reparsed, "tz" is invalid at this level (it's being defined in the same SELECT list)
Subsequently, this invalid "tz" reference gets erroneously resolved to temp.timezone instead of the original COALESCE expression
This causes convert_tz() to receive NULL instead of '+00:00' (the COALESCE result) as the 3rd argument, which is why the result is empty for the supplied query.

Proposed Fix

Add a check to exclude function expressions just being output as the reference item_name during derived table condition reparsing:

--- item.cc.orig	2025-05-28 22:28:59.309902171 +0100
+++ item.cc	2025-06-17 14:47:43.662819360 +0100
@@ -8366,16 +8366,17 @@
 }
 
 void Item_ref::print(const THD *thd, String *str,
                      enum_query_type query_type) const {
   if (m_ref_item == nullptr)  // Unresolved reference: print reference
     return Item_ident::print(thd, str, query_type);
 
   if (!const_item() && m_alias_of_expr &&
+      (ref_item()->type() != Item::FUNC_ITEM || !thd->lex->reparse_derived_table_condition) &&
       ref_item()->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF &&
       table_name == nullptr && item_name.ptr()) {
     Simple_cstring str1 = ref_item()->real_item()->item_name;
     append_identifier(thd, str, str1.ptr(), str1.length());
   } else {
     ref_item()->print(thd, str, query_type);
   }
 }

This ensures that:
- During normal operations (HAVING/WHERE printing): column aliases are preserved as expected
- During derived table condition pushdown: function expressions are fully serialized, preserving the (select tz) workaround

The fix is minimal and targeted, affecting only the specific case where expressions are serialized for condition pushdown.
Testing

The patch fixes the reported issue and passes all tests in the "main" test suite for me (with 1-2 pre-existing failures unrelated to this change). I am unable to run the complete MySQL test suite at this time, so additional validation by the MySQL team would be appreciated. There may well be a better place to address this failure, I am not familiar with the code.

Despite my lack of familiarity, the fix feels conservative, it only changes the behaviour when reparse_derived_table_condition is set, which occurs specifically during the problematic condition pushdown serialization in this bug.

Hello Chris Croft-White,

Thank you for the feedback.
Confirmed from the development team that this issue has been already fixed by an internally reported bug Bug #35710183.  Per change log - 

Documented fix as follows in the MySQL 8.0.36 and 8.3.0 changelogs:
    Some HAVING queries did not produce expected results.

Also - https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-36.html

regards,
Umesh