planner: can't find column error for a nested join query #65454
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
A new bug after this optimization: #65187
Use SQLs below to reproduce this issue:
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE `t1` (
`id` bigint NOT NULL,
`hcode` text COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
);
CREATE TABLE `t2` (
`id` bigint NOT NULL,
`bid` bigint NOT NULL,
`cid` bigint NOT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
);
CREATE TABLE `t3` (
`id` bigint NOT NULL,
`user_id` bigint NOT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
);
explain format = 'plan_tree' SELECT
(SELECT COUNT(t3.user_id) FROM `t2`
LEFT JOIN `t1` AS `cm` ON `t2`.`cid` = `cm`.`id`
LEFT JOIN `t3` ON `t2`.`bid` = `t3`.`id`
WHERE cm.hcode = t1.hcode ) AS tt
FROM `t1`
WHERE t1.id IN (SELECT MIN(id) FROM `t1` );
2. What did you expect to see? (Required)
No error
3. What did you see instead (Required)
ERROR 1105 (HY000): Can't find column Column#12 in schema Column: [ass.t3.user_id] PKOrUK: [] NullableUK: []
4. What is your TiDB version? (Required)
Master