TiFlash treats '+0' and '-0' as different key when do hash join
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
```sql
DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t0 (
c0 FLOAT PRIMARY KEY NOT NULL,
c1 INT,
c2 VARCHAR(500)
);
CREATE TABLE IF NOT EXISTS t1 LIKE t0;
INSERT INTO t0 (c2, c1, c0) VALUES ("eVt%", 1, -1.088348796E9);
INSERT INTO t0 (c2, c1, c0) VALUES ("abc", 2, -2.088348796E9);
INSERT INTO t0 (c2, c1, c0) VALUES ("xyz", 3, -3.088348796E9);
INSERT INTO t0 (c2, c1, c0) VALUES ("eVt%", 4, -41.088348796E9);
INSERT INTO t1 (c0, c1, c2) VALUES (0.390985317943897, 1, '_w');
INSERT INTO t1 (c0, c1, c2) VALUES (0.490123456789012, 1, '_x');
INSERT INTO t1 (c0, c1, c2) VALUES (0.590123456789012, 2, '_y');
INSERT INTO t1 (c0, c1, c2) VALUES (0.190985317943891, 3, '_z');
INSERT INTO t1 (c0, c1, c2) VALUES (0.20985317943892, 2, '_w11');
ALTER TABLE t0 SET TIFLASH REPLICA 1;
ALTER TABLE t1 SET TIFLASH REPLICA 1;
```
### 2. What did you expect to see? (Required)
The same behavior between two queries
### 3. What did you see instead (Required)
```sql
mysql> SELECT /*+ READ_FROM_STORAGE(tikv[t0]) */
-> t0.c0, t0.c1, t0.c2
-> FROM
-> t0
-> WHERE
-> EXISTS (
-> SELECT /*+ READ_FROM_STORAGE(tikv[t1]) */ 1
-> FROM t1
-> WHERE
-> t0.c1 = t1.c1
-> AND (ROUND(t1.c2 - 0.5, 0)) = (((t0.c0 || '') AND t0.c2))
-> )
-> GROUP BY t0.c0, t0.c1, t0.c2
-> HAVING t0.c1 > 0
-> ORDER BY t0.c1 DESC LIMIT 10;
+-------------+------+------+
| c0 | c1 | c2 |
+-------------+------+------+
| -3088348700 | 3 | xyz |
| -2088348800 | 2 | abc |
| -1088348800 | 1 | eVt% |
+-------------+------+------+
3 rows in set, 9 warnings (0.05 sec)
mysql> SELECT /*+ READ_FROM_STORAGE(tiflash[t0]) */
-> t0.c0, t0.c1, t0.c2
-> FROM
-> t0
-> WHERE
-> EXISTS (
-> SELECT /*+ READ_FROM_STORAGE(tiflash[t1]) */ 1
-> FROM t1
-> WHERE
-> t0.c1 = t1.c1
-> AND (ROUND(t1.c2 - 0.5, 0)) = (((t0.c0 || '') AND t0.c2))
-> )
-> GROUP BY t0.c0, t0.c1, t0.c2
-> HAVING t0.c1 > 0
-> ORDER BY t0.c1 DESC LIMIT 10;
Empty set (0.04 sec)
```
### 4. What is your TiDB version? (Required)
TiDB v8.4.0
Contributor guide
Assessment
This issue has not been assessed yet.