Overview
TencentDB for MySQL may produce incorrect results when multi-table LEFT JOIN queries are executed using Hash Join. This issue originates from the Hash Join optimizer feature introduced in MySQL 8.0.18, which can corrupt the correctness of query result sets under specific conditions.
Issue Details
Issue Description
Queries that meet the following conditions may return incorrect results:
1. Use LEFT JOIN to connect multiple tables.
2. The optimizer employs the Hash Join algorithm.
Triggering Condition
In MySQL version 8.0.18, Hash Join was introduced as the default Join algorithm, replacing the original Block Nested Loop (BNL). The issue occurs through the following path:
LEFT JOIN + multiple tables
↓
The optimizer selects Hash Join (MySQL 8.0.18+).
↓
Insufficient join_buffer_size
↓
Hash Join spills to disk.
↓
Row data is repeatedly stored/loaded in the buffer.
↓
The record[0] cached by EQRefIterator is corrupted by AggregateIterator.
↓
NULL rows are lost, leading to incorrect query results.
Problems
The number of rows returned by the query is incorrect.
The NULL rows that should be supplemented in a LEFT JOIN are lost.
The data aggregation result is abnormal.
Affected Kernel Versions
Versions of MySQL 8.0 prior to 20260430.
Solutions
Emergency Mitigation Plan
If you encounter this issue, you can temporarily work around it using the following methods:
SET optimizer_switch='block_nested_loop=off';
This setting disables Hash Join, causing queries to fall back to the traditional Nested Loop algorithm. While this may sacrifice some performance, it ensures the correctness of the results.
Long-Term Version Fix (Recommended)