Unexpected Result for NATURAL JOIN in TiFlash

[bajinsheng]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 DECIMAL ZEROFILL);
CREATE TABLE t1 (c0 BLOB);
INSERT IGNORE INTO t0 VALUES(-1749300172);
INSERT INTO t1 VALUES('aa'), ('-0.0');
ALTER TABLE t0 SET TIFLASH REPLICA 1;
ALTER TABLE t1 SET TIFLASH REPLICA 1;
SELECT COUNT(*) FROM t1 NATURAL JOIN t0; -- {1}

This query should return {2} if we execute the query immediately after ALTER statements.
However, if we wait until the tables t0 and t1 are copied into TIFLASH (5 seconds in my machine) and then execute the query, it returns {1}.

2. What did you expect to see? (Required)

{2}

3. What did you see instead (Required)

{1}

4. What is your TiDB version? (Required)

tiup playground nightly --host 0.0.0.0 --kv 3 --tiflash 3

8.0.11-TiDB-v9.0.0-beta.1.pre-554-gb1a5536a64-dirty

[windtalker]

The root cause is the join key is double, and both t1.c0 and t0.c0 will be casted to double before join
for t1.c0:

mysql> select cast(t1.c0 as double) from t1;
+-----------------------+
| cast(t1.c0 as double) |
+-----------------------+
|                     0 |
|                    -0 |
+-----------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> select cast(t0.c0 as double) from t0;
+-----------------------+
| cast(t0.c0 as double) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.01 sec)

As you can see, t1.c0 is 0 and -0, and t0.c0 is 0. In TiFlash, 0 and -0 is treated as different and in TiDB 0 and -0 is treated as the same value.

[windtalker]

Open an issue in TiFlash repo: pingcap/tiflash#10144, close this one as duplicated.