inner join with specific projection produces wrong result

[jinhui-lai]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 BOOL ZEROFILL AS (-1) VIRTUAL, c1 BLOB);
CREATE TABLE t2 (c2 BOOL);
INSERT IGNORE INTO t0(c1) VALUES (NULL), (NULL);
CREATE INDEX i0 ON t0(c0);
INSERT INTO t2(c2) VALUES (0);
SELECT t2.c2 FROM t2 LEFT OUTER JOIN t0 ON t2.c2 = t0.c0; -- wrong result
+------+
| c2   |
+------+
|    0 |
+------+
1 row in set (0.00 sec) 
SELECT * FROM t2 LEFT OUTER JOIN t0 ON t2.c2 = t0.c0;  -- 'SELECT *' is contradictory with 'SELECT t2.c2' 
+------+------+------------+
| c2   | c0   | c1         |
+------+------+------------+
|    0 |    0 | NULL       |
|    0 |    0 | NULL       |
+------+------+------------+
2 rows in set, 2 warnings (0.01 sec)
DROP INDEX i0 ON t0; -- When drop index i0, i get right result
SELECT t2.c2 FROM t2 LEFT OUTER JOIN t0 ON t2.c2 = t0.c0; 
+------+
| c2   |
+------+
|    0 |
|    0 |
+------+
2 rows in set, 2 warnings (0.00 sec)

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

SELECT t2.c2 FROM t2 LEFT OUTER JOIN t0 ON t2.c2 = t0.c0; 
+------+
| c2   |
+------+
|    0 |
|    0 |
+------+
2 rows in set, 2 warnings (0.00 sec)

3. What did you see instead (Required)

SELECT t2.c2 FROM t2 LEFT OUTER JOIN t0 ON t2.c2 = t0.c0; -- wrong result
+------+
| c2   |
+------+
|    0 |
+------+
1 row in set (0.00 sec) 

4. What is your TiDB version? (Required)

8.0.11-TiDB-v7.5.1 TiDB Server (Apache License 2.0) Community Edition

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 |