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:
None 
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
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.
[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)