Bug #115654 | Changing “internal_tmp_mem_storage_engine” returns incorrect results | ||
---|---|---|---|
Submitted: | 20 Jul 2024 14:01 | Modified: | 22 Jul 2024 8:05 |
Reporter: | Chenglin Liang | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 9.0.0, 8.0.38, 8.4.1 | OS: | Ubuntu (Ubuntu 20.04.6 LTS) |
Assigned to: | CPU Architecture: | x86 (5.15.0-113-generic) | |
Tags: | tmp table |
[20 Jul 2024 14:01]
Chenglin Liang
[22 Jul 2024 8:03]
MySQL Verification Team
Hello Liang Chenglin, Thank you for the report and test case. regards, Umesh
[24 Jul 2024 14:15]
huahua xu
For both Heap and Temptable storage engine, the default key algorithm is `HA_KEY_ALG_HASH`. But the calculation method of hash value for key is different. For Temptable storage engine: ``` inline size_t Cell_calculator::hash(const Cell &cell) const { ...... /* For approximate types, 0.0 and -0.0 may have different bit patterns. Treat * all such patterns as belonging to a single value. */ if (m_is_floating_point) { assert(data_length == 4 || data_length == 8); const double val = data_length == 4 ? float4get(data) : float8get(data); if (val == 0.0) return s_zero_hash; return murmur3_32(data, data_length, 0); } ...... } ``` For Heap storage engine: uint64 hp_hashnr(HP_KEYDEF *keydef, const uchar *key) { ...... for (; pos < key; pos++) { nr ^= (uint64)((((uint)nr & 63) + nr2) * ((uint)*pos)) + (nr << 8); nr2 += 3; } ...... } In mysql, 0 is [0, 0, 0, 0], and 0 is [0, 0, 0, 128]. The result would be: Cell_calculator::hash(0) = Cell_calculator::hash(-0) hp_hashnr(0) != hp_hashnr(-0)