unexpected internal error when executing a CTE query

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `tlf5d55361` (
  `col_9` time NOT NULL,
  `col_10` float NOT NULL,
  `col_11` json NOT NULL,
  `col_12` date NOT NULL,
  `col_13` json NOT NULL,
  `col_14` tinyint NOT NULL,
  `col_15` date DEFAULT NULL,
  `col_16` tinyblob NOT NULL,
  `col_17` time DEFAULT '03:51:26',
  PRIMARY KEY (`col_14`,`col_9`,`col_10`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
PARTITION BY RANGE (`col_14`)
(PARTITION `p0` VALUES LESS THAN (-128),
 PARTITION `p1` VALUES LESS THAN (-84),
 PARTITION `p2` VALUES LESS THAN (-41),
 PARTITION `p3` VALUES LESS THAN (-30));

CREATE TABLE `td8d55878` (
  `col_26` datetime DEFAULT NULL,
  `col_27` time DEFAULT NULL,
  `col_28` json DEFAULT NULL,
  `col_29` char(186) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '4-BJKi',
  `col_30` date NOT NULL DEFAULT '1998-07-28',
  `col_31` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_chinese_ci;

WITH `cte_3` (`col_128`) AS (SELECT /*+ USE_INDEX_MERGE(`td8d55878` `tlf5d55361`)*/ MIN(`td8d55878`.`col_26`) AS `r0` FROM (`tlf5d55361`) JOIN `td8d55878` GROUP BY `tlf5d55361`.`col_17` HAVING `tlf5d55361`.`col_17`<='20:22:14.00' OR `tlf5d55361`.`col_17` BETWEEN '21:56:23.00' AND '19:42:43.00' ORDER BY `r0` LIMIT 772780933), `cte_4` (`col_129`) AS (SELECT /*+ HASH_AGG()*/ SUM(`tlf5d55361`.`col_14`) AS `r0` FROM `td8d55878` JOIN `tlf5d55361` ON `tlf5d55361`.`col_17`=`td8d55878`.`col_27` GROUP BY `td8d55878`.`col_30` HAVING ISNULL(`td8d55878`.`col_30`) OR `td8d55878`.`col_30` BETWEEN '2009-09-08' AND '1980-11-17') (SELECT SUM((`cte_4`.`col_129` IN (0.65,564617.3335,45,0.319,0.4427)) IS TRUE) FROM (`cte_4`) JOIN `cte_3`);

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

No error.

3. What did you see instead (Required)

mysql> WITH `cte_3` (`col_128`) AS (SELECT /*+ USE_INDEX_MERGE(`td8d55878` `tlf5d55361`)*/ MIN(`td8d55878`.`col_26`) AS `r0` FROM (`tlf5d55361`) JOIN `td8d55878` GROUP BY `tlf5d55361`.`col_17` HAVING `tlf5d55361`.`col_17`<='20:22:14.00' OR `tlf5d55361`.`col_17` BETWEEN '21:56:23.00' AND '19:42:43.00' ORDER BY `r0` LIMIT 772780933), `cte_4` (`col_129`) AS (SELECT /*+ HASH_AGG()*/ SUM(`tlf5d55361`.`col_14`) AS `r0` FROM `td8d55878` JOIN `tlf5d55361` ON `tlf5d55361`.`col_17`=`td8d55878`.`col_27` GROUP BY `td8d55878`.`col_30` HAVING ISNULL(`td8d55878`.`col_30`) OR `td8d55878`.`col_30` BETWEEN '2009-09-08' AND '1980-11-17') (SELECT SUM((`cte_4`.`col_129` IN (0.65,564617.3335,45,0.319,0.4427)) IS TRUE) FROM (`cte_4`) JOIN `cte_3`);
ERROR 1105 (HY000): Some columns of TopN_64 cannot find the reference from its child(ren)

4. What is your TiDB version? (Required)

bee268d

[hawkingrei]

We can simple this sql like this.

SELECT 
    `subquery_cte_4`.`col_129`
FROM 
    (SELECT 
        SUM(`tlf5d55361`.`col_14`) AS `col_129` 
     FROM 
        `td8d55878` 
     JOIN 
        `tlf5d55361` 
     GROUP BY 
        `td8d55878`.`col_30` 
    ) AS `subquery_cte_4`
JOIN 
    (SELECT 
        MIN(`td8d55878`.`col_26`) AS `col_128` 
     FROM 
        `tlf5d55361` 
     JOIN 
        `td8d55878` 
     GROUP BY 
        `tlf5d55361`.`col_17` 
     HAVING 
        `tlf5d55361`.`col_17` <= '20:22:14.00' 
     ORDER BY 
        `col_128` 
     LIMIT 772780933
    ) AS `subquery_cte_3`;

[hawkingrei]

It is without this bug in v8.5. it is only in the master.

[hawkingrei]

This problem is introduced by #58500

[EricZequan]

To pass CI check check-issue-triage-complete for fix-pr, please add severity/xx tags～🫡