TiFlash join may got wrong result if fine grained join is enabled, and multiple join key has different types

[windtalker]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t1(id bigint, v1 int);
Query OK, 0 rows affected (0.04 sec)

mysql> create table t2(id bigint unsigned, v1 int);
Query OK, 0 rows affected (0.05 sec)

mysql> create table t3(id bigint, v1 int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(10001, 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(10001, 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(10001, 3);
Query OK, 1 row affected (0.01 sec)

mysql> set tidb_broadcast_join_threshold_size=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set tidb_broadcast_join_threshold_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 set tiflash replica 1;
Query OK, 0 rows affected (0.06 sec)

mysql> alter table t2 set tiflash replica 1;
Query OK, 0 rows affected (0.04 sec)

mysql> alter table t3 set tiflash replica 1;
Query OK, 0 rows affected (0.06 sec)

mysql> set tidb_enforce_mpp=1;
Query OK, 0 rows affected (0.01 sec)

mysql> set tiflash_fine_grained_shuffle_stream_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql>  select /*+ hash_join_build(t3) */ count(*) from t1 straight_join t2 on t1.id = t2.id straight_join t3 on t1.id = t3.id;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

mysql> set tiflash_fine_grained_shuffle_stream_count=-1;
Query OK, 0 rows affected (0.00 sec)

mysql>  select /*+ hash_join_build(t3) */ count(*) from t1 straight_join t2 on t1.id = t2.id straight_join t3 on t1.id = t3.id;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.07 sec)

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

[yibin87]

/assign @yibin87

[yibin87]

/sig execution

[yibin87]

/severity critical

[yibin87]

/label affects-7.1

[yibin87]

/label affects-7.5
/label affects-8.1
/label affects-8.5

[yibin87]

/label affects-6.5

[yibin87]

Users can workaround the issue by set tiflash_fine_grained_shuffle_stream_count = -1. Set the severity to critical for two reasons:

1. Incorrect query results
2. Report by customers

The issue's root cause is the shuffle column type is not equal to the comparing join key type, and the join build key type "Jump back and forth":

1. t1 join t2 on t1.id = t2.id, t1.id is bigint, while t2.id is bigint unsigned, thus choose the common type decimal(20,0) to shuffle. And in this join, the comparing type is also decimal(20,0), so this join result is correct
2. then (t1 join t2) join t3 on t1.id = t3.id, although t1.id and t3.id are both bigint type, optimizer chooses to benefit from the existing shuffle result to reduce one shuffle: the result of t1 join t2 already obeys shuffling cast(t1.id, decimal(20,0)). So optimizer choose to add a shuffle for t3 and the final shuffle key is cast(t3.id, decimal(20,0))
3. From v6.5.0, we introduced FineGrainedShuffle optimization to reuse the hash code generated in shuffle phase in join build phase. In this case, the reused hash code is generated for cast(t3.id, decimal(20,0)); However, the final join equal condition is t1.id = t3.id, both are bigint type, no any type convertion is needed. Thus in join probe phase, the hash code is generated for t1.id directly, which cause the miss match.

[yibin87]

/remove-label may-affects-6.1

[yibin87]

/remove-label affects-6.5