Wrong results with correlated subquery

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1,t2;
CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(b INT,c INT);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1,2), (2,3);
SELECT (SELECT 1 FROM t1 WHERE a=b AND c=1) FROM t2;

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

mysql> SELECT (SELECT 1 FROM t1 WHERE a=b AND c=1) FROM t2;
+--------------------------------------+
| (SELECT 1 FROM t1 WHERE a=b AND c=1) |
+--------------------------------------+
|                                 NULL |
|                                 NULL |
+--------------------------------------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> SELECT (SELECT 1 FROM t1 WHERE a=b AND c=1) FROM t2;
+--------------------------------------+
| (SELECT 1 FROM t1 WHERE a=b AND c=1) |
+--------------------------------------+
|                                    1 |
|                                    1 |
+--------------------------------------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.5.0-alpha-168-g23f7e51ae
Edition: Community
Git Commit Hash: 23f7e51ae01287fa7f811e1462d8987e4d7727a4
Git Branch: master
UTC Build Time: 2022-01-27 17:39:21
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[morgo]

Verified as described. This looks to be a bug in the 'correlated' part of the subquery:

tidb> SELECT (SELECT 1 FROM t1 WHERE c=1) FROM t2;
+------------------------------+
| (SELECT 1 FROM t1 WHERE c=1) |
+------------------------------+
|                         NULL |
|                         NULL |
+------------------------------+
2 rows in set (0.00 sec)

tidb> SELECT (SELECT 1 FROM t1 WHERE a=b AND c=1) FROM t2;
+--------------------------------------+
| (SELECT 1 FROM t1 WHERE a=b AND c=1) |
+--------------------------------------+
|                                    1 |
|                                    1 |
+--------------------------------------+
2 rows in set (0.00 sec)

[AilinKid]

Verified as described. This looks to be a bug in the 'correlated' part of the subquery:

tidb> SELECT (SELECT 1 FROM t1 WHERE c=1) FROM t2;
+------------------------------+
| (SELECT 1 FROM t1 WHERE c=1) |
+------------------------------+
|                         NULL |
|                         NULL |
+------------------------------+
2 rows in set (0.00 sec)

tidb> SELECT (SELECT 1 FROM t1 WHERE a=b AND c=1) FROM t2;
+--------------------------------------+
| (SELECT 1 FROM t1 WHERE a=b AND c=1) |
+--------------------------------------+
|                                    1 |
|                                    1 |
+--------------------------------------+
2 rows in set (0.00 sec)

seems "SELECT (SELECT a FROM t1 WHERE a=b AND c=1) FROM t2;" can make it.
the number 1 should be explained as position parameter rather than constant value