dev.mysql.com

  • Fixed an issue relating to subquery_to_derived. (Bug #39525738)

  • A query using the hypergraph optimizer with GROUP BY ... WITH ROLLUP on a constant expression and an empty USE INDEX FOR GROUP BY() hint could cause an assertion failure during optimization.

    As of this release, the optimizer recognizes that constant grouping requires no index ordering, allowing a valid streaming aggregate plan to be selected. (Bug #39505077)

  • Fixed an issue relating to ROLLUP queries. (Bug #39501528)

  • Fixed an issue related to error handling in aggregation of decimal data. (Bug #39474810)

  • Fixed an issue related to executing EXPLAIN on a LEFT JOIN. (Bug #39461426)

  • Some temporal and geospatial expressions could trigger assertion failures. (Bug #39279682, Bug #39280411)

  • Fixed an issue relating to error-handling. (Bug #39272322)

  • Fixed an issue relating to user-variable handling. (Bug #39243027)

  • Under certain circumstances, predicate-heavy queries using the hypergraph optimizer could trigger an assertion failure. (Bug #39181175)

  • Fixed an issue relating to Common Table Expressions with subquery_to_derived enabled. (Bug #39179197)

  • Fixed an issue in ExtractValue(). (Bug #39138426)

  • Complex queries using the hypergraph optimizer could spend unnecessary planning time repeatedly checking for a secondary-engine row-count hook, even when no secondary engine was installed.

    The optimizer now caches this hook lookup once per query, reducing planning overhead. (Bug #39015463)

  • Opening a table after updating its histogram could trigger an assertion failure because metadata locks for histograms were released incorrectly.

    Histogram metadata locks are now released only for the table currently being opened. (Bug #38940484)

  • Fixed an issue relating to dynamic scan execution.

    Our thanks to Mengchu Shi and the team from Alibaba for the contribution. (Bug #38657550)

  • Queries which used BETWEEN with constant bounds could show an unnecessary filter operation above an otherwise exact index range scan when using the hypergraph optimizer.

    As of this release, the optimizer recognizes these range conditions as exact and removes the redundant filter step. (Bug #37152269)

  • Queries involving materialized derived tables could choose an index-lookup plan even when the cost of building the temporary index outweighed its benefit.

    As of this release, the hypergraph optimizer includes temporary-index build cost when evaluating these plans, improving plan selection for derived-table queries. (Bug #36957877)

  • SELECT DISTINCT ... LIMIT queries, which used materialization-based duplicate removal, could process all distinct rows before applying the limit, causing performance degradation when only a small number of unique rows was requested.

    The hypergraph optimizer now passes the query limit to duplicate removal, allowing it to stop once it has found the required number of unique rows. Sort-based DISTINCT plans forced by SQL_BIG_RESULT are unchanged. (Bug #36720017)

  • Hash joins could perform unnecessary disk spill I/O for queries with a low LIMIT, even when only a small number of result rows was needed.

    As of this release, the hypergraph optimizer considers both spill-to-disk and in-memory hash join plans, allowing cost-based optimization to favor the faster in-memory option when appropriate. (Bug #36684053)

  • ORDER BY expressions using stored functions with side effects or non-deterministic functions such as RAND() could be evaluated more than once per row by the hypergraph optimizer, resulting in unexpected repeated side effects.

    As of this release, rows are materialized before sorting in affected queries so these expressions are evaluated only once per row. Top-level UPDATE and DELETE statements, and queries optimized for secondary engines, are unaffected. (Bug #36578540)

  • Queries that referenced the same common table expression (CTE) from multiple query blocks could generate duplicate internal temporary-index names. When the CTE was materialized on disk, InnoDB could select the wrong internal index, resulting in a crash, assertion failure, “table does not exist” error, or incorrect query result.

    Internal temporary-index names are now regenerated when indexes are moved between slots, ensuring each generated index remains uniquely and consistently named.

    Our thanks to Wang Zhengmao for the contribution. (Bug #36492739)

  • Under certain circumstances, filters using IFNULL returned incorrect results. (Bug #36448705)

  • UNION ALL evaluated children in textual order. With EXISTS or LIMIT, this could cause expensive branches to be evaluated first, when a cheaper branch could produce a row immediately. (Bug #35767452)

  • A negated IS TRUE condition involving a BETWEEN predicate could return incorrect results for DECIMAL values because NULL-state handling was not initialized correctly. (Bug #120540, Bug #39444505)

  • AVG() of functions such as ASCII() could return a truncated value when used with SQL_BUFFER_RESULT, because the calculated decimal precision did not reserve space for a sign.

    As of this release, decimal precision calculations correctly account for signed values, preventing truncation of valid results such as 255.0000. (Bug #120465, Bug #39392429)

  • Due to a fix in a previous version, IFNULL() did not handle NULL correctly. (Bug #120429, Bug #39347654)

    References: This issue is a regression of: Bug #37285902.

  • A fix in a previous version caused an issue where a WHERE equality condition could fail to match an existing row when a scalar subquery and its outer query referenced the same CTE.

    This fix prevents cleanup of derived-table temporary keys from a query block that does not own them, preserving correct key metadata during outer-query optimization and ensuring correct result matching. (Bug #120403, Bug #39321676)

    References: This issue is a regression of: Bug #35912840.

  • UNION and UNION ALL queries with pushed-down WHERE conditions involving AND and IS NULL could return incorrect rows.

    Condition pushdown now preserves WHERE predicate semantics in set-operation branches. (Bug #119780, Bug #38888307)

  • Queries involving nested merged views and outer joins could return incorrect results when a view column containing a constant expression was evaluated before outer-join null completion.

    The optimizer retains the correct inner-table dependency for merged view columns, ensuring outer-join expressions and related WHERE conditions are evaluated at the proper stage. (Bug #119499, Bug #38729126)

    References: This issue is a regression of: Bug #35854686.

  • Queries that filtered an indexed column with IS NULL could perform an unnecessary filesort when the same column appeared in ORDER BY.

    As of this release, the optimizer recognizes IS NULL as a constant condition for order-by simplification, allowing index order to be used when applicable.

    Our thanks to Kishore Kumar for the contribution. (Bug #119442, Bug #38700622)

  • Queries with ORDER BY, a low LIMIT, and a reverse range scan on a composite index could return incorrect results if the optimizer removed the sort operation prematurely.

    As of this release, the optimizer accounts for all key parts required for ordering before selecting a reverse index range scan.

    Our thanks to Somrak Monpengpinij for the contribution. (Bug #119198, Bug #38564020)

  • CAST(NULLIF(...) AS DATETIME) could return an incorrect result because temporal values produced by NULLIF() were not evaluated with the appropriate temporal type handling.

    Temporal evaluation is now handled correctly for IF() and NULLIF() expressions. As a result, NULLIF() applied to a YEAR expression now preserves the YEAR data type; related aggregate expressions may therefore return DECIMAL rather than DOUBLE.

    Our thanks to Ximin Liang for the contribution. (Bug #118053, Bug #37875484)

  • FORCE INDEX on a unique index could still perform an index dive for IS NULL predicates, contrary to the expected behavior of forcing a single index.

    The optimizer now skips the index dive in this case while continuing to use index statistics only when they are valid for the selected range.

    Our thanks to Kaiwang Chen and the team from Tencent for the contribution. (Bug #117791, Bug #37751691)

  • Temporal results returned by GREATEST(), LEAST(), or COALESCE() could report an incorrect collation through the client protocol and the COLLATION() function. Temporal values use the binary collation, but these multi-purpose functions did not apply the required translation. Temporal results from these functions now correctly report the binary collation.

    Our thanks to Dirkjan Bussink for the contribution. (Bug #117543, Bug #37626671)

  • Queries using CONST access on a materialized temporary table could return incorrect results after an in-memory temporary table was converted to an on-disk table.

    As of this release, the temporary table's read state is reset after conversion, ensuring the expected rows remain accessible.

    Our thanks to Jingqi Tian for the contribution. (Bug #116741, Bug #37308710)

  • Some LEFT JOIN queries with OR conditions displayed dynamic range access in EXPLAIN, but still performed full scans of the inner table at execution time. This occurred because the optimizer did not recognize eligible outer-join predicates when evaluating range access.

    The optimizer now considers these predicates for dynamic range analysis, allowing eligible LEFT JOIN queries to use an index range scan re-planned for each outer row instead of repeatedly scanning the full inner table.

    Our thanks to Hope Lee for the contribution. (Bug #113288, Bug #36061036)

  • For some LEFT JOIN queries, an optimizer rewrite could prevent a non-equality condition from being applied while building a hash join. As a result, more rows than necessary could be added to the hash table before filtering.

    The optimizer now remaps eligible predicates to the build-side table when possible, allowing earlier filtering and more efficient hash join execution without changing inner-join filtering behavior.

    Our thanks to Wen He and Yicheng Wei for their contribution. (Bug #112198, Bug #35749693)

  • Queries that assigned names to derived-table columns produced by GROUP BY ... WITH ROLLUP could report an “Unknown column” error when a window function ordered by one of those names.

    Materialized rollup fields now retain their derived-table column names, allowing these references to resolve correctly.

    Our thanks to Hope Lee and the team at AliBaba for the contribution. (Bug #112112, Bug #35722845)

  • Using a view created with one or more CTE definitions could result in a syntax error.

    Our thanks to Vitali Vinahradski for the contribution. (Bug #111559, Bug #35536775)

  • A cached table could retain temporary-NULL state after a multi-table UPDATE involving a trigger, causing later valid statements to warn or fail under strict SQL mode. Errors were returned similar to the following: 

    ERROR 1048 (23000): Column 'columnName' cannot be null

    Our thanks to Shun Yi and the team from Alibaba for the contribution. (Bug #110962, Bug #35373634)

  • TIMESTAMPDIFF() returned unexpected results when used with a WHERE clause. (Bug #108143, Bug #34499767)

  • The explain_filename() implementation was simplified by removing redundant code.

    Our thanks to Hiroyuki Sasagawa for the contribution. (Bug #104169, Bug #33064492)

Read the original on dev.mysql.com ↗