| 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, 9.3 | OS: | Ubuntu (Ubuntu 20.04.6 LTS) |
| Assigned to: | CPU Architecture: | x86 (5.15.0-113-generic) | |
| Tags: | tmp table | ||
[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)
[30 Jul 11:58]
MySQL Verification Team
Bug #118749 marked as duplicate of this one.
Description: When I switch the internal temporary table engine from TempTable to MEMORY, the subquery returns redundant results How to repeat: CREATE TABLE t0(c0 FLOAT UNIQUE KEY COLUMN_FORMAT DYNAMIC) ; CREATE TABLE t1(c0 FLOAT STORAGE DISK ) ; REPLACE DELAYED INTO t1(c0) VALUES('-0.0'); INSERT IGNORE INTO t1(c0) VALUES("S"), (""), (45), (4), (52); INSERT HIGH_PRIORITY IGNORE INTO t0(c0) VALUES(""); SET SESSION internal_tmp_mem_storage_engine = MEMORY; SELECT * FROM t0; - get [0] SELECT * FROM t1; - get [-0,0,0,45,4,52] SELECT t1.c0 AS ref0 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t0 WHERE t1.c0 = t0.c0); -- get [-0,45,4,52], expect [45,4,52] Suggested fix: The problem may be with the temporary table. If internal_tmp_mem_storage_engine=default, the result is correct.