unexpected query result using CTE #57247
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE `tl50cb7440` (
`col_43` decimal(30,30) NOT NULL,
PRIMARY KEY (`col_43`) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY `idx_12` (`col_43`),
KEY `idx_13` (`col_43`),
UNIQUE KEY `idx_14` (`col_43`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `tl50cb7440` VALUES(0.000000000000000000000000000000),(0.400000000000000000000000000000);
WITH `cte_8911` (`col_47665`) AS (SELECT MID(`tl50cb7440`.`col_43`, 6, 9) AS `r0` FROM `tl50cb7440` WHERE `tl50cb7440`.`col_43` IN (0,0) AND `tl50cb7440`.`col_43` IN (0) GROUP BY `tl50cb7440`.`col_43` HAVING `tl50cb7440`.`col_43` IN (0,0) OR `tl50cb7440`.`col_43` BETWEEN 0.84 AND 0) (SELECT 1 FROM `cte_8911` WHERE `cte_8911`.`col_47665`!='');2. What did you expect to see? (Required)
mysql> WITH `cte_8911` (`col_47665`) AS (SELECT MID(`tl50cb7440`.`col_43`, 6, 9) AS `r0` FROM `tl50cb7440` WHERE `tl50cb7440`.`col_43` IN (0,0) AND `tl50cb7440`.`col_43` IN (0) GROUP BY `tl50cb7440`.`col_43` HAVING `tl50cb7440`.`col_43` IN (0,0) OR `tl50cb7440`.`col_43` BETWEEN 0.84 AND 0) (SELECT 1 FROM `cte_8911` WHERE `cte_8911`.`col_47665`!='');
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)3. What did you see instead (Required)
WITH `cte_8911` (`col_47665`) AS (SELECT MID(`tl50cb7440`.`col_43`, 6, 9) AS `r0` FROM `tl50cb7440` WHERE `tl50cb7440`.`col_43` IN (0,0) AND `tl50cb7440`.`col_43` IN (0) GROUP BY `tl50cb7440`.`col_43` HAVING `tl50cb7440`.`col_43` IN (0,0) OR `tl50cb7440`.`col_43` BETWEEN 0.84 AND 0) (SELECT 1 FROM `cte_8911` WHERE `cte_8911`.`col_47665`!='');
Empty set (0.00 sec)4. What is your TiDB version? (Required)
Metadata
Metadata
Assignees
Labels
This bug affects the 6.1.x(LTS) versions.This bug affects the 6.5.x(LTS) versions.This bug affects the 7.1.x(LTS) versions.This bug affects the 7.5.x(LTS) versions.This bug affects the 8.1.x(LTS) versions.This bug affects the 8.5.x(LTS) versions.This bug affects the 9.0.x versions.SIG: PlannerThe issue is confirmed as a bug.