unexpected unknown column

[XuHuaiyu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t1 ( id INTEGER, sample_name1 VARCHAR(100), sample_name2 VARCHAR(100), PRIMARY KEY(id) );

INSERT INTO t1 ( id, sample_name1, sample_name2 ) VALUES ( 1, 'aaaa', 'bbbb' ), ( 2, 'cccc', 'dddd' );

(
	SELECT sample_name1 AS testname FROM t1
)
UNION
(
	SELECT sample_name2 AS testname FROM t1 C ORDER BY (SELECT T.sample_name1 FROM t1 T WHERE T.id = C.id)
)
;

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

mysql> (
    -> SELECT sample_name1 AS testname FROM t1
    -> )
    -> UNION
    -> (
    -> SELECT sample_name2 AS testname FROM t1 C ORDER BY (SELECT T.sample_name1 FROM t1 T WHERE T.id = C.id)
    -> )
    -> ;
+----------+
| testname |
+----------+
| aaaa     |
| cccc     |
| bbbb     |
| dddd     |
+----------+
4 rows in set (0.00 sec)

3. What did you see instead (Required)

tidb> (
    -> SELECT sample_name1 AS testname FROM t1
    -> )
    -> UNION
    -> (
    -> SELECT sample_name2 AS testname FROM t1 C ORDER BY (SELECT T.sample_name1 FROM t1 T WHERE T.id = C.id)
    -> )
    -> ;
ERROR 1054 (42S22): Unknown column 'c.id' in 'where clause'

4. What is your TiDB version? (Required)

7e15333 in master

[time-and-fate]

This issue is very similar to #24444.
From the viewpoint of our implementation, it's more like a feature than a bug fix.
We need to design a plan to support this kind of correlated subquery.
So I prefer to convert it to an enhancement.

[time-and-fate]

/type enhancement

[time-and-fate]

/remove-type bug

[time-and-fate]

/remove-severity major