incorrect query result using subquery

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t62b91e77` (
  `col_16` time DEFAULT '14:07:58',
  KEY `idx_12_1` (`col_16`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t62b91e77` VALUES('20:07:16'),('21:22:32'),('02:11:53'),('21:22:32'),('01:57:20'),('23:16:26'),('16:52:55'),('21:22:32'),('21:22:32'),('23:50:06'),('14:14:00'),('21:22:32'),('01:53:05'),('21:22:32'),('21:22:32'),('23:10:40'),('07:03:54'),('18:16:30'),('01:43:17'),('13:32:12'),('19:38:27'),('21:22:32'),('18:15:24'),('21:22:32'),('21:22:32'),('18:20:00'),('21:22:32'),('21:22:32'),('01:57:14'),('16:57:00'),('22:00:40'),('21:56:48'),('20:43:00'),('11:12:31'),('13:42:11'),('04:29:12'),('00:40:06'),('15:48:40'),('20:45:36'),('06:15:20'),('00:57:19'),('20:27:39'),('01:51:40'),('18:30:26'),('18:12:38'),('14:00:05'),('10:03:06'),('01:44:07'),('06:59:39'),('09:18:09'),('11:13:22'),('05:21:12'),(NULL),('09:49:19'),('08:01:05'),('02:36:28'),('00:39:22'),('18:19:46'),('08:28:54'),('14:56:49'),('13:40:00'),('19:08:08'),('20:49:53'),('15:57:50'),('11:35:47');

SELECT `st_117`.`r3` AS `r0` FROM `t62b91e77` JOIN (SELECT MAKE_SET(0, `t62b91e77`.`col_16`, `t62b91e77`.`col_16`) AS `r0`,`t62b91e77`.`col_16` AS `r1`,SUBSTR(`t62b91e77`.`col_16`, 1) AS `r2`,SPACE(1) AS `r3`,UCASE(`t62b91e77`.`col_16`) AS `r4` FROM `t62b91e77` WHERE `t62b91e77`.`col_16`!='19:38:27.00' LIMIT 101047997) AS `st_117` ON `t62b91e77`.`col_16`=`st_117`.`r2` WHERE `t62b91e77`.`col_16` IN ('16:57:00.00');

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

One row in result

3. What did you see instead (Required)

Empty result

4. What is your TiDB version? (Required)

3d42e344f33f32b21162aedb941cab96c3e47422

[hawkingrei]

This is the simplified SQL

SELECT `st_117`.`r3` 
FROM `t62b91e77`
JOIN
  (SELECT SUBSTR(`t62b91e77`.`col_16`, 1) AS `r2`,
          SPACE(1) AS `r3`
   FROM `t62b91e77`
   WHERE `t62b91e77`.`col_16`!='19:38:27.00'
   LIMIT 101047997) AS `st_117` ON `t62b91e77`.`col_16`=`st_117`.`r2`
WHERE `t62b91e77`.`col_16` IN ('16:57:00.00');

[hawkingrei]

It is possible that it is a same problem with #53857