Can't find column XXX in schema Column

[xingyong]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE poi_clearing_time_topic (
effective_date datetime DEFAULT NULL ,
clearing_time int(11) DEFAULT NULL
);

SELECT
GROUP_CONCAT(stlmnt_hour order by effective_date DESC)
-- GROUP_CONCAT(stlmnt_hour )
FROM
(
SELECT
(COALESCE(pct.clearing_time, 0)/3600000) AS stlmnt_hour
,COALESCE(pct.effective_date, '1970-01-01 08:00:00') AS effective_date
FROM
poi_clearing_time_topic pct
ORDER BY
pct.effective_date DESC
) a;

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

no error.

3. What did you see instead (Required)

the sql returns error: Can't find column a.effective_date in schema Column: [stlmnt_hour,effective_date,test.pct.effective_date] Unique key: []

4. What is your TiDB version? (Required)

3.0, 5.0, 6.1 (maybe all versions)

[Defined2014]

Maybe same as #41957, PTAL @xuyifangreeneyes

[seiya-annie]

/found community