| Bug #110406 | Storing rows with null in join key in hash table for outer join is useless | ||
|---|---|---|---|
| Submitted: | 17 Mar 2023 4:58 | Modified: | 20 Mar 2023 13:31 |
| Reporter: | Wen He (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | hash join; outer join | ||
[17 Mar 2023 13:01]
MySQL Verification Team
Hi Mr. He, Thank you for the bug report. However, we fail to see what you are reporting. If our code is incorrect, then there would be an OUTER JOIN test case where results would be wrong. All that we can conclude from your report is that there is a one, single function parameter that is not required. That is superfluous ...... That does not constitute a bug ..... Please, let us know whether we misunderstood your report.
[20 Mar 2023 10:09]
Knut Anders Hatlen
I think this is a valid bug report, as it leads to some extra work during building of the hash table, and also some extra buffer usage. (The fix for bug#109211 removes this code, so this issue will probably be gone in MySQL 8.0.33.)
[20 Mar 2023 13:31]
MySQL Verification Team
Hi Mr. He, We have already committed a patch for a very similar bug. However, we shall verify this bug, only in order to be sure that the new patch covers your case as well. Thank you .... This report is now a verified bug.
Description: In the implementation of function `HashJoinIterator::BuildHashTable()`, rows with null in join key will be stored into hash table if the join type is outer. bool HashJoinIterator::BuildHashTable() { ... const bool store_rows_with_null_in_join_key = m_join_type == JoinType::OUTER; ... for (;;) { // Termination condition within loop. int res = m_build_input->Read(); const hash_join_buffer::StoreRowResult store_row_result = m_row_buffer.StoreRow(thd(), reject_duplicate_keys, store_rows_with_null_in_join_key); ... } While at the stage of probing hash table, if a row with null in join key is read from the probe_input, it skips checking the hash table for there will never be a matching row found in the hash table. void HashJoinIterator::LookupProbeRowInHashTable() { ... bool null_in_join_key = ConstructJoinKey( thd(), m_join_conditions, m_probe_input_tables.tables_bitmap(), &m_temporary_row_and_join_key_buffer); if (null_in_join_key) { if (m_join_type == JoinType::ANTI || m_join_type == JoinType::OUTER) { // SQL NULL was found, and we will never find a matching row in the hash // table. Let us indicate that, so that a null-complemented row is // returned. m_current_row = LinkedImmutableString{nullptr}; m_state = State::READING_FIRST_ROW_FROM_HASH_TABLE; } else { SetReadingProbeRowState(); } return; } ... } How to repeat: It dosen't incur any mtr faliure after always setting store_rows_with_null_in_join_key to false. Suggested fix: During building hash table, the `store_rows_with_null_in_join_key` can always be false when join type is outer.