Same query results are inconsistent on TiKV and TiFlash when using `NATURAL RIGHT JOIN t0 GROUP BY`.

[fenfeng9]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

It may require multiple executions to reproduce.

prepare data

drop table if exists t0;
drop table if exists t1;
CREATE TABLE t0(c0 CHAR );
CREATE TABLE t1(c0 BOOL ZEROFILL );
INSERT IGNORE  INTO t1 VALUES (false) ON DUPLICATE KEY UPDATE c0=t1.c0;
INSERT IGNORE  INTO t0(c0) VALUES ('8') ON DUPLICATE KEY UPDATE c0=t0.c0;
INSERT DELAYED INTO t0 VALUES ('Y'), ('1');
ALTER TABLE t0 SET TIFLASH REPLICA 1;
ALTER TABLE t1 SET TIFLASH REPLICA 1;

execute query

SELECT /*+ read_from_storage(tikv[t1]) */  t1.c0 FROM  t1 NATURAL RIGHT JOIN t0 GROUP BY '0.8538272502788982';
SELECT /*+ read_from_storage(tiflash[t1]) */  t1.c0 FROM  t1 NATURAL RIGHT JOIN t0 GROUP BY '0.8538272502788982';

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

The results of the two queries are equal.

3. What did you see instead (Required)

Query1:

Query2:

4. What is your TiDB version? (Required)

Release Version: v8.4.0
Edition: Community
Git Commit Hash: 1a9f0fa
Git Branch: HEAD
UTC Build Time: 2024-11-07 15:18:43
GoVersion: go1.23.2
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[yibin87]

Confirmed this is not a bug:
First, the group by "0.853" clause will be compiled to a firstrow function:

mysql> explain SELECT /*+ read_from_storage(tikv[t1]) */  t1.c0 FROM  t1 NATURAL RIGHT JOIN t0 GROUP BY '0.8538272502788982'; 
+--------------------------------+---------+-----------+---------------+----------------------------------------------------------------+
| id                             | estRows | task      | access object | operator info                                                  |
+--------------------------------+---------+-----------+---------------+----------------------------------------------------------------+
| HashAgg_14                     | 1.00    | root      |               | group by:1, funcs:firstrow(test.t1.c0)->test.t1.c0             |
| └─HashJoin_21                  | 3.00    | root      |               | right outer join, equal:[eq(Column#5, Column#6)]               |
|   ├─Projection_24(Build)       | 1.00    | root      |               | test.t1.c0, cast(test.t1.c0, double UNSIGNED BINARY)->Column#5 |
|   │ └─TableReader_27           | 1.00    | root      |               | data:TableFullScan_26                                          |
|   │   └─TableFullScan_26       | 1.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                 |
|   └─Projection_28(Probe)       | 3.00    | root      |               | cast(test.t0.c0, double BINARY)->Column#6                      |
|     └─TableReader_31           | 3.00    | root      |               | data:TableFullScan_30                                          |
|       └─TableFullScan_30       | 3.00    | cop[tikv] | table:t0      | keep order:false, stats:pseudo                                 |
+--------------------------------+---------+-----------+---------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)

However, the first row of the output "Natural Right Join" is not guaranteed by semantics:

mysql> SELECT /*+ read_from_storage(tiflash[t0,t1]) */  t1.c0 FROM  t1 NATURAL RIGHT JOIN t0;
+------+
| c0   |
+------+
| NULL |
|    0 |
| NULL |
+------+
3 rows in set (0.01 sec)

mysql> SELECT /*+ read_from_storage(tikv[t1]) */  t1.c0 FROM  t1 NATURAL RIGHT JOIN t0;
+------+
| c0   |
+------+
|    0 |
| NULL |
| NULL |
+------+
3 rows in set, 1 warning (0.00 sec)

[yibin87]

/remove-type bug

[yibin87]

/close

[ti-chi-bot]

@yibin87: Closing this issue.

In response to this:

/close

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes-sigs/prow repository.