Projection makes the result of join operation absolutely wrong!

[jinhui-lai]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

-- docker run -d --name tidb -p 4000:4000 pingcap/tidb:latest
-- docker run -it --network="host" mysql:latest mysql -h 127.0.0.1 -P 4000 -u root

CREATE TABLE t0(c0 FLOAT  AS (NULL) VIRTUAL NOT NULL , c1 FLOAT);
CREATE TABLE t2(c2 FLOAT );
INSERT IGNORE INTO t0(c1) VALUES (1);
CREATE INDEX i0 ON t0(c0);
INSERT IGNORE INTO t2(c2) VALUES (0);
SELECT t2.c2 FROM t2 RIGHT JOIN t0 ON t2.c2 = t0.c0;   -- absolutely wrong
+------+
| c2   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
SELECT * FROM  t2 RIGHT JOIN t0 ON t2.c2 = t0.c0;  -- The result of 'SELECT *' is absolutely contradictory with 'SELECT t2.c2' 
+------+----+------+
| c2   | c0 | c1   |
+------+----+------+
|    0 |  0 |    1 |
+------+----+------+
1 row in set (0.00 sec)

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

SELECT t2.c2 FROM t2 RIGHT JOIN t0 ON t2.c2 = t0.c0; 
+------+
| c2   |
+------+
|    0 | 
+------+

3. What did you see instead (Required)

SELECT t2.c2 FROM t2 RIGHT JOIN t0 ON t2.c2 = t0.c0; 
+------+
| c2   |
+------+
| NULL |
+------+

4. What is your TiDB version? (Required)

| Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc7
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:28:32
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: unistore |

[Defined2014]

It's correct on TiKV env, but will panic on unistore env.

mysql> SELECT t2.c2 FROM t2 RIGHT JOIN t0 ON t2.c2 = t0.c0;
ERROR 1105 (HY000): runtime error: index out of range [0] with length 0

stack

[2025/03/20 10:40:38.303 +08:00] [INFO] [conn.go:1192] ["command dispatched failed"] [conn=2097154] [session_alias=] [connInfo="id:2097154, addr:127.0.0.1:41658 status:10, collation:utf8mb4_0900_ai_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="SELECT t2.c2 FROM t2 RIGHT JOIN t0 ON t2.c2 = t0.c0"] [txn_mode=PESSIMISTIC] [timestamp=456769781970239488] [err="runtime error: index out of range [0] with length 0
github.com/pingcap/errors.AddStack
	/home/jason/go/pkg/mod/github.com/pingcap/[email protected]/errors.go:178
github.com/pingcap/errors.Trace
	/home/jason/go/pkg/mod/github.com/pingcap/[email protected]/juju_adaptor.go:15
github.com/pingcap/tidb/pkg/util.GetRecoverError
	/home/jason/tidb/pkg/util/util.go:312
github.com/pingcap/tidb/pkg/executor/join.(*ProbeWorkerV2).handleProbeWorkerPanic
	/home/jason/tidb/pkg/executor/join/hash_join_v2.go:858
github.com/pingcap/tidb/pkg/util.(*WaitGroupWrapper).RunWithRecover.func1.1
	/home/jason/tidb/pkg/util/wait_group_wrapper.go:195
runtime.gopanic
	/usr/local/go/src/runtime/panic.go:792
runtime.goPanicIndex
	/usr/local/go/src/runtime/panic.go:115
github.com/pingcap/tidb/pkg/util/codec.SerializeKeys.func1
	/home/jason/tidb/pkg/util/codec/codec.go:431
github.com/pingcap/tidb/pkg/util/codec.SerializeKeys
	/home/jason/tidb/pkg/util/codec/codec.go:458
github.com/pingcap/tidb/pkg/executor/join.(*baseJoinProbe).SetChunkForProbe
	/home/jason/tidb/pkg/executor/join/base_join_probe.go:253
github.com/pingcap/tidb/pkg/executor/join.(*outerJoinProbe).SetChunkForProbe
	/home/jason/tidb/pkg/executor/join/outer_join_probe.go:80
github.com/pingcap/tidb/pkg/executor/join.(*ProbeWorkerV2).processOneProbeChunk
	/home/jason/tidb/pkg/executor/join/hash_join_v2.go:932
github.com/pingcap/tidb/pkg/executor/join.(*ProbeWorkerV2).runJoinWorker
	/home/jason/tidb/pkg/executor/join/hash_join_v2.go:1008
github.com/pingcap/tidb/pkg/executor/join.(*HashJoinV2Exec).startProbeJoinWorkers.func1
	/home/jason/tidb/pkg/executor/join/hash_join_v2.go:836
github.com/pingcap/tidb/pkg/util.(*WaitGroupWrapper).RunWithRecover.func1
	/home/jason/tidb/pkg/util/wait_group_wrapper.go:199
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1700"]