Projection makes the result of join operation absolutely wrong!
- 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)
```
-- 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: 7d16cc79e81bbf573124df3fd9351c26963f3e70
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 |
Contributor guide
Assessment
This issue has not been assessed yet.