Description:
A AND B
=>
B AND A AND B
In this case, B is the predicate:
sq29.col_1 IN (SELECT ... FROM t3 AS t56 ... CROSS JOIN t2 AS t58 ...)
The original query contains one copy of this predicate. The mutated query contains two identical copies. Both queries return the same result, but the optimizer keeps two subquery/materialization paths in the mutated plan instead of eliminating the duplicate predicate.
Correctness check on the compressed reproducer:
original result: 1 row, COUNT(*) = 0
mutated result: 1 row, COUNT(*) = 0
rows equal: true
Observed timing on the compressed reproducer:
original median: 3.313 ms
mutated median: 181.389 ms
ratio original/mutated: 0.0183
The larger original test case showed the same behavior:
original median: 6.036 ms
mutated median: 188.291 ms
ratio original/mutated: 0.0321
EXPLAIN evidence from the larger case showed that planning is also much slower:
EXPLAIN FORMAT=JSON original median: 16.212 ms
EXPLAIN FORMAT=JSON mutated median: 193.784 ms
Plan comparison from the larger case:
original:
t56 mentions: 9
t58 mentions: 3
materialize-related operations: 31
operation_count: 386
mutated:
t56 mentions: 18
t58 mentions: 6
materialize-related operations: 39
operation_count: 460
The mutated plan contains two duplicated subquery paths, observed as <subquery54> and <subquery83>.
Expected behavior:
The optimizer should recognize the duplicated deterministic predicate as redundant, or otherwise avoid planning/materializing the same IN-subquery twice.
Actual behavior:
The optimizer preserves both copies of the same IN-subquery predicate, producing duplicate subquery/materialization paths and causing a stable severe planning/runtime regression.
How to repeat:
Run the following SQL on MySQL 9.5.0. It creates a self-contained schema named pair595_repro, loads a reduced deterministic data set, and provides both the original and mutated queries.
-- Setup SQL
DROP DATABASE IF EXISTS `pair595_repro`;
CREATE DATABASE `pair595_repro`;
USE `pair595_repro`;
SET FOREIGN_KEY_CHECKS = 0;
-- DDL for t1
CREATE TABLE `t1` (
`c1` int NOT NULL AUTO_INCREMENT,
`c2` varchar(255) NOT NULL,
`c3` varchar(255) DEFAULT NULL,
`c4` int DEFAULT NULL,
`c5` date NOT NULL,
`c6` varchar(10) NOT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `idx_t1_pk` (`c1`),
KEY `idx_t1_c6` (`c6`),
KEY `idx_t1_c3` (`c3`(63)),
KEY `idx_t1_c2_c5` (`c2`(63),`c5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Data for t1: 36 short rows
INSERT INTO `t1` (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES
(1, 'sample_1', 'sample_1', 1, '2026-01-01', 'sample_1'),
(2, 'sample_2', 'sample_2', 2, '2026-01-01', 'sample_2'),
(3, 'sample_3', 'sample_3', 3, '2026-01-01', 'sample_3'),
(4, 'sample_4', 'sample_4', 4, '2026-01-01', 'sample_4'),
(5, 'sample_5', 'sample_5', 5, '2026-01-01', 'sample_5'),
(6, 'sample_6', 'sample_6', 6, '2026-01-01', 'sample_6'),
(7, 'sample_7', 'sample_7', 7, '2026-01-01', 'sample_7'),
(8, 'sample_8', 'sample_8', 8, '2026-01-01', 'sample_8'),
(9, 'sample_9', 'sample_9', 9, '2026-01-01', 'sample_9'),
(10, 'sample_10', 'sample_10', 10, '2026-01-01', 'sample_0'),
(11, 'sample_11', 'sample_11', 11, '2026-01-01', 'sample_1'),
(12, 'sample_12', 'sample_12', 12, '2026-01-01', 'sample_2'),
(13, 'sample_13', 'sample_13', 13, '2026-01-01', 'sample_3'),
(14, 'sample_14', 'sample_14', 14, '2026-01-01', 'sample_4'),
(15, 'sample_15', 'sample_15', 15, '2026-01-01', 'sample_5'),
(16, 'sample_16', 'sample_16', 16, '2026-01-01', 'sample_6'),
(17, 'sample_17', 'sample_17', 17, '2026-01-01', 'sample_7'),
(18, 'sample_18', 'sample_18', 18, '2026-01-01', 'sample_8'),
(19, 'sample_19', 'sample_19', 19, '2026-01-01', 'sample_9'),
(20, 'sample_20', 'sample_20', 20, '2026-01-01', 'sample_0'),
(21, 'sample_21', 'sample_21', 21, '2026-01-01', 'sample_1'),
(22, 'sample_22', 'sample_22', 22, '2026-01-01', 'sample_2'),
(23, 'sample_23', 'sample_23', 23, '2026-01-01', 'sample_3'),
(24, 'sample_24', 'sample_24', 24, '2026-01-01', 'sample_4'),
(25, 'sample_25', 'sample_25', 25, '2026-01-01', 'sample_5'),
(26, 'sample_26', 'sample_26', 26, '2026-01-01', 'sample_6'),
(27, 'sample_27', 'sample_27', 27, '2026-01-01', 'sample_7'),
(28, 'sample_28', 'sample_28', 28, '2026-01-01', 'sample_8'),
(29, 'sample_29', 'sample_29', 29, '2026-01-01', 'sample_9'),
(30, 'sample_30', 'sample_30', 30, '2026-01-01', 'sample_0'),
(31, 'sample_31', 'sample_31', 31, '2026-01-01', 'sample_1'),
(32, 'sample_32', 'sample_32', 32, '2026-01-01', 'sample_2'),
(33, 'sample_33', 'sample_33', 33, '2026-01-01', 'sample_3'),
(34, 'sample_34', 'sample_34', 34, '2026-01-01', 'sample_4'),
(35, 'sample_35', 'sample_35', 35, '2026-01-01', 'sample_5'),
(36, 'sample_36', 'sample_36', 36, '2026-01-01', 'sample_6');
-- DDL for t2
CREATE TABLE `t2` (
`c1` int NOT NULL AUTO_INCREMENT,
`c2` int NOT NULL,
`c3` decimal(10,2) NOT NULL,
`c4` varchar(50) NOT NULL,
`c5` date NOT NULL,
`c6` mediumtext,
`c7` longtext,
`c8` mediumblob,
`c9` longblob,
`c10` enum('value1','value2','value3') DEFAULT NULL,
`c11` set('a','b','c','d') DEFAULT NULL,
`c12` bit(8) DEFAULT NULL,
`c13` datetime DEFAULT NULL,
`c14` float(8,2) DEFAULT NULL,
`c15` double(12,4) DEFAULT NULL,
`c16` json DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `idx_t2_pk` (`c1`),
KEY `idx_t2_c4` (`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Data for t2: 4 short rows
INSERT INTO `t2` (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES
(1, 10, 1.50, 'sample_1', '2026-01-01', 'sample_1', '1', 'value1', 'a', X'01', '2026-01-01 00:00:00', 1.00, 1.0000),
(2, 20, 2.50, 'sample_2', '2026-01-01', 'sample_2', '2', 'value1', 'a', X'02', '2026-01-01 00:00:00', 2.00, 2.0000),
(3, 30, 3.50, 'sample_3', '2026-01-01', 'sample_3', '3', 'value1', 'a', X'03', '2026-01-01 00:00:00', 3.00, 3.0000),
(4, 40, 4.50, 'sample_4', '2026-01-01', 'sample_4', '4', 'value1', 'a', X'04', '2026-01-01 00:00:00', 4.00, 4.0000);
-- DDL for t3
CREATE TABLE `t3` (
`c1` int NOT NULL AUTO_INCREMENT,
`c2` int NOT NULL,
`c3` int NOT NULL,
`c4` year NOT NULL,
`c5` datetime DEFAULT NULL,
`c6` tinyint DEFAULT NULL,
`c7` smallint DEFAULT NULL,
`c8` mediumint DEFAULT NULL,
`c9` bigint DEFAULT NULL,
`c10` longtext,
`c11` geometry DEFAULT NULL,
`c12` tinytext,
`c13` tinyblob,
`c14` set('x','y','z') DEFAULT NULL,
`c15` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `idx_t3_pk` (`c1`),
KEY `idx_t3_c4` (`c4`),
KEY `idx_t3_c6` (`c6`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Data for t3: intentionally empty
SET FOREIGN_KEY_CHECKS = 1;
-- Original query. Expected result: one row with cnt = 0.
WITH cte_1 AS (SELECT LOG(10, t2.c3) AS col_1, (t2.c1 + t2.c7) AS col_2 FROM t2 AS t1 INNER JOIN t3 AS t2 ON (((t1.c2 >= t2.c1) OR (t1.c2 >= 26)) AND (t1.c13 <> t2.c5)) LEFT JOIN (SELECT t5.c9 AS col_1, (SELECT DEGREES(t9.c4) AS col_1 FROM t3 AS t9 WHERE NOT (t9.c4 IS NULL) LIMIT 1) AS col_2, LAST_VALUE(t5.c7) OVER (ORDER BY t5.c5 DESC) AS col_3 FROM t1 AS t4 LEFT JOIN t2 AS t5 ON (((NOT t5.c2 BETWEEN 45 AND 87 AND t5.c7 IN (SELECT t6.c6 AS col_1 FROM t2 AS t6 INNER JOIN t3 AS t7 ON (NOT (t6.c3 IS NULL) AND (t6.c7 = t7.c12)) CROSS JOIN t1 AS t8 ON (t6.c15 <= t8.c4) WHERE ((NOT t6.c14 BETWEEN 47 AND 96 OR (t6.c1 >= 78)) OR (t6.c4 = 'sample_87')))) AND (t4.c4 = 98)) AND NOT t5.c1 BETWEEN 37 AND 46) WHERE ((t4.c4 <> 85) AND NOT t5.c8 IS NULL)) AS sq3 ON (((NOT t2.c10 IN (SELECT t10.c11 AS col_1 FROM t2 AS t10 RIGHT JOIN t3 AS t11 ON (((NOT t11.c14 IS NULL OR (t10.c14 > t11.c1)) OR t11.c5 IN (SELECT BIT_OR(t12.c1) AS col_1 FROM t3 AS t12 WHERE NOT (t12.c1 BETWEEN 48 AND 90))) AND EXISTS(SELECT t13.c5 AS col_1 FROM t3 AS t13 RIGHT JOIN t2 AS t14 ON (t14.c5 = '2023-01-01') WHERE (NOT REGEXP_LIKE(t14.c7, '[a-zA-Z0-9]{3,12}')))) CROSS JOIN (SELECT ((t16.c3 > 56) AND (NOT t16.c4 LIKE 'sample_74%')) AS col_1, t16.c4 AS col_2, ACOS(t16.c1) AS col_3, ((t16.c13 <> '2023-01-01') AND (t16.c13 <> '2023-01-01')) AS col_4, STDDEV_POP(t16.c14) AS col_5 FROM t2 AS t16 WHERE (t16.c4 <> 'sample_40') GROUP BY ((t16.c3 > 56) AND (NOT t16.c4 LIKE 'sample_74%')), t16.c3, t16.c4, ACOS(t16.c1), t16.c1, ((t16.c13 <> '2023-01-01') AND (t16.c13 <> '2023-01-01')), t16.c13) AS sq15 ON (NOT ((t10.c2 >= sq15.col_3)) OR (t10.c3 = sq15.col_5)) WHERE (((NOT t10.c11 LIKE '%sample_81') AND NOT t10.c3 BETWEEN 34 AND 82) AND (t11.c10 <> 'sample_41'))) AND (t2.c12 = sq3.col_3)) AND (t2.c1 > sq3.col_2)) AND (t2.c6 >= sq3.col_2)) WHERE ((((EXISTS(SELECT HOUR(t17.c5) AS col_1 FROM t1 AS t17 LEFT JOIN t2 AS t18 ON (NOT t18.c4 LIKE '%sample_41%') LEFT JOIN (SELECT RANK() OVER (PARTITION BY t20.c2 ORDER BY t20.c2 DESC) AS col_1, t20.c6 AS col_2, t20.c6 AS col_3, t20.c6 AS col_4, SECOND(t20.c5) AS col_5 FROM t1 AS t20 WHERE (t20.c6 <> 'sample_62') LIMIT 18) AS sq19 ON t17.c3 IN (SELECT NULLIF(t21.c3, t22.c9) AS col_1 FROM t3 AS t21 CROSS JOIN t2 AS t22 ON (t21.c9 < t22.c14) LEFT JOIN t1 AS t23 ON (t23.c4 <> 5) WHERE (((t22.c4 LIKE '%sample_15') OR (t22.c3 < 40)) OR NOT t21.c1 BETWEEN 49 AND 76)) WHERE ((t18.c14 IN (SELECT t24.c14 AS col_1 FROM t2 AS t24 WHERE (t24.c3 BETWEEN 33 AND 43 AND (NOT t24.c10 LIKE 'sample_77%'))) OR NOT t17.c3 IN (SELECT DAYNAME(t27.c5) AS col_1 FROM t3 AS t25 LEFT JOIN t1 AS t26 ON (t25.c6 >= t26.c4) RIGHT JOIN t2 AS t27 ON (((t25.c1 > t27.c15) AND (t27.c1 > 65)) AND NOT t25.c9 BETWEEN 20 AND 31) WHERE (((t27.c14 > 82) AND t25.c2 BETWEEN 48 AND 71) AND (t27.c15 <> 58)) ORDER BY t25.c15 ASC)) AND NOT t17.c1 BETWEEN 33 AND 46)) AND (t1.c7 LIKE '%sample_75')) OR NOT t2.c5 BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59') OR (t2.c2 < 70)) AND t2.c10 IS NULL) ORDER BY t2.c4 DESC) SELECT COUNT(*) AS cnt FROM t2 AS t28 LEFT JOIN (SELECT t34.c4 AS col_1, 0 AS col_2, t31.c11 AS col_3, 0 AS col_4, NULL AS col_5 FROM t3 AS t30 CROSS JOIN t2 AS t31 ON EXISTS(SELECT STR_TO_DATE(sq32.col_2, sq32.col_2) AS col_1 FROM (SELECT CAST(t33.c1 AS DECIMAL(10, 2)) AS col_1, SCHEMA() AS col_2, STDDEV_SAMP(t33.c1) AS col_3, LENGTH(t33.c3) AS col_4 FROM t1 AS t33 WHERE (REGEXP_LIKE(t33.c6, '^sample_[0-9]+$')) GROUP BY CAST(t33.c1 AS DECIMAL(10, 2)), t33.c1, LENGTH(t33.c3), t33.c3 LIMIT 22) AS sq32 ORDER BY sq32.col_4 DESC) INNER JOIN t1 AS t34 ON (NOT ((t31.c13 = '2023-01-01')) AND EXISTS(SELECT t35.c5 AS col_1 FROM t2 AS t35 INNER JOIN (SELECT t37.c7 AS col_1, t37.c15 AS col_2, t37.c11 AS col_3, t37.c7 AS col_4 FROM t2 AS t37 WHERE (t37.c5 <> '2023-01-01')) AS sq36 ON NOT ((t35.c11 <> sq36.col_3)) CROSS JOIN cte_1 AS t38 ON (((t35.c3 BETWEEN 46 AND 89 AND (t35.c15 > t38.col_1)) AND t35.c11 IN (SELECT STDDEV_SAMP(t39.c4) AS col_1 FROM t1 AS t39 WHERE NOT t39.c1 BETWEEN 6 AND 25)) AND (t35.c14 > t38.col_1)) WHERE (t35.c2 BETWEEN 2 AND 10 AND sq36.col_1 IN (SELECT t40.c2 AS col_1 FROM t1 AS t40 CROSS JOIN t3 AS t41 ON (NOT t41.c12 IS NULL OR NOT t41.c15 BETWEEN 43 AND 68))) LIMIT 29)) WHERE ((t30.c7 < 76) AND (t31.c1 >= 9)) GROUP BY t34.c4, t31.c11, (t30.c7 - t31.c15), t30.c7, t31.c15 HAVING (t31.c11 < 21) ORDER BY col_2 DESC, col_4 DESC LIMIT 8) AS sq29 ON (t28.c14 >= sq29.col_1) WHERE (((t28.c13 = '2023-01-01') AND EXISTS(SELECT DEGREES(t54.col_2) AS col_1 FROM cte_1 AS t54 WHERE (t54.col_2 = ALL (SELECT LN(t55.c3) AS col_1 FROM t2 AS t55 ORDER BY t55.c13 ASC)))) AND sq29.col_1 IN (SELECT t57.col_1 AS col_1 FROM t3 AS t56 INNER JOIN cte_1 AS t57 ON (((t56.c14 LIKE '%sample_51') OR (t56.c1 <= t57.col_2)) AND t56.c4 BETWEEN 11 AND 55) CROSS JOIN t2 AS t58 ON (((t56.c12 = t58.c6) AND (t56.c6 < t58.c3)) OR t56.c14 IS NULL) WHERE (NOT t56.c5 BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' AND NOT t56.c7 BETWEEN 26 AND 53)));
-- Mutated query. Expected result: one row with cnt = 0, but much slower.
WITH cte_1 AS (SELECT LOG(10, t2.c3) AS col_1, (t2.c1 + t2.c7) AS col_2 FROM t2 AS t1 INNER JOIN t3 AS t2 ON (((t1.c2 >= t2.c1) OR (t1.c2 >= 26)) AND (t1.c13 <> t2.c5)) LEFT JOIN (SELECT t5.c9 AS col_1, (SELECT DEGREES(t9.c4) AS col_1 FROM t3 AS t9 WHERE NOT (t9.c4 IS NULL) LIMIT 1) AS col_2, LAST_VALUE(t5.c7) OVER (ORDER BY t5.c5 DESC) AS col_3 FROM t1 AS t4 LEFT JOIN t2 AS t5 ON (((NOT t5.c2 BETWEEN 45 AND 87 AND t5.c7 IN (SELECT t6.c6 AS col_1 FROM t2 AS t6 INNER JOIN t3 AS t7 ON (NOT (t6.c3 IS NULL) AND (t6.c7 = t7.c12)) CROSS JOIN t1 AS t8 ON (t6.c15 <= t8.c4) WHERE ((NOT t6.c14 BETWEEN 47 AND 96 OR (t6.c1 >= 78)) OR (t6.c4 = 'sample_87')))) AND (t4.c4 = 98)) AND NOT t5.c1 BETWEEN 37 AND 46) WHERE ((t4.c4 <> 85) AND NOT t5.c8 IS NULL)) AS sq3 ON (((NOT t2.c10 IN (SELECT t10.c11 AS col_1 FROM t2 AS t10 RIGHT JOIN t3 AS t11 ON (((NOT t11.c14 IS NULL OR (t10.c14 > t11.c1)) OR t11.c5 IN (SELECT BIT_OR(t12.c1) AS col_1 FROM t3 AS t12 WHERE NOT (t12.c1 BETWEEN 48 AND 90))) AND EXISTS(SELECT t13.c5 AS col_1 FROM t3 AS t13 RIGHT JOIN t2 AS t14 ON (t14.c5 = '2023-01-01') WHERE (NOT REGEXP_LIKE(t14.c7, '[a-zA-Z0-9]{3,12}')))) CROSS JOIN (SELECT ((t16.c3 > 56) AND (NOT t16.c4 LIKE 'sample_74%')) AS col_1, t16.c4 AS col_2, ACOS(t16.c1) AS col_3, ((t16.c13 <> '2023-01-01') AND (t16.c13 <> '2023-01-01')) AS col_4, STDDEV_POP(t16.c14) AS col_5 FROM t2 AS t16 WHERE (t16.c4 <> 'sample_40') GROUP BY ((t16.c3 > 56) AND (NOT t16.c4 LIKE 'sample_74%')), t16.c3, t16.c4, ACOS(t16.c1), t16.c1, ((t16.c13 <> '2023-01-01') AND (t16.c13 <> '2023-01-01')), t16.c13) AS sq15 ON (NOT ((t10.c2 >= sq15.col_3)) OR (t10.c3 = sq15.col_5)) WHERE (((NOT t10.c11 LIKE '%sample_81') AND NOT t10.c3 BETWEEN 34 AND 82) AND (t11.c10 <> 'sample_41'))) AND (t2.c12 = sq3.col_3)) AND (t2.c1 > sq3.col_2)) AND (t2.c6 >= sq3.col_2)) WHERE ((((EXISTS(SELECT HOUR(t17.c5) AS col_1 FROM t1 AS t17 LEFT JOIN t2 AS t18 ON (NOT t18.c4 LIKE '%sample_41%') LEFT JOIN (SELECT RANK() OVER (PARTITION BY t20.c2 ORDER BY t20.c2 DESC) AS col_1, t20.c6 AS col_2, t20.c6 AS col_3, t20.c6 AS col_4, SECOND(t20.c5) AS col_5 FROM t1 AS t20 WHERE (t20.c6 <> 'sample_62') LIMIT 18) AS sq19 ON t17.c3 IN (SELECT NULLIF(t21.c3, t22.c9) AS col_1 FROM t3 AS t21 CROSS JOIN t2 AS t22 ON (t21.c9 < t22.c14) LEFT JOIN t1 AS t23 ON (t23.c4 <> 5) WHERE (((t22.c4 LIKE '%sample_15') OR (t22.c3 < 40)) OR NOT t21.c1 BETWEEN 49 AND 76)) WHERE ((t18.c14 IN (SELECT t24.c14 AS col_1 FROM t2 AS t24 WHERE (t24.c3 BETWEEN 33 AND 43 AND (NOT t24.c10 LIKE 'sample_77%'))) OR NOT t17.c3 IN (SELECT DAYNAME(t27.c5) AS col_1 FROM t3 AS t25 LEFT JOIN t1 AS t26 ON (t25.c6 >= t26.c4) RIGHT JOIN t2 AS t27 ON (((t25.c1 > t27.c15) AND (t27.c1 > 65)) AND NOT t25.c9 BETWEEN 20 AND 31) WHERE (((t27.c14 > 82) AND t25.c2 BETWEEN 48 AND 71) AND (t27.c15 <> 58)) ORDER BY t25.c15 ASC)) AND NOT t17.c1 BETWEEN 33 AND 46)) AND (t1.c7 LIKE '%sample_75')) OR NOT t2.c5 BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59') OR (t2.c2 < 70)) AND t2.c10 IS NULL) ORDER BY t2.c4 DESC) SELECT COUNT(*) AS cnt FROM t2 AS t28 LEFT JOIN (SELECT t34.c4 AS col_1, 0 AS col_2, t31.c11 AS col_3, 0 AS col_4, NULL AS col_5 FROM t3 AS t30 CROSS JOIN t2 AS t31 ON EXISTS(SELECT STR_TO_DATE(sq32.col_2, sq32.col_2) AS col_1 FROM (SELECT CAST(t33.c1 AS DECIMAL(10, 2)) AS col_1, SCHEMA() AS col_2, STDDEV_SAMP(t33.c1) AS col_3, LENGTH(t33.c3) AS col_4 FROM t1 AS t33 WHERE (REGEXP_LIKE(t33.c6, '^sample_[0-9]+$')) GROUP BY CAST(t33.c1 AS DECIMAL(10, 2)), t33.c1, LENGTH(t33.c3), t33.c3 LIMIT 22) AS sq32 ORDER BY sq32.col_4 DESC) INNER JOIN t1 AS t34 ON (NOT ((t31.c13 = '2023-01-01')) AND EXISTS(SELECT t35.c5 AS col_1 FROM t2 AS t35 INNER JOIN (SELECT t37.c7 AS col_1, t37.c15 AS col_2, t37.c11 AS col_3, t37.c7 AS col_4 FROM t2 AS t37 WHERE (t37.c5 <> '2023-01-01')) AS sq36 ON NOT ((t35.c11 <> sq36.col_3)) CROSS JOIN cte_1 AS t38 ON (((t35.c3 BETWEEN 46 AND 89 AND (t35.c15 > t38.col_1)) AND t35.c11 IN (SELECT STDDEV_SAMP(t39.c4) AS col_1 FROM t1 AS t39 WHERE NOT t39.c1 BETWEEN 6 AND 25)) AND (t35.c14 > t38.col_1)) WHERE (t35.c2 BETWEEN 2 AND 10 AND sq36.col_1 IN (SELECT t40.c2 AS col_1 FROM t1 AS t40 CROSS JOIN t3 AS t41 ON (NOT t41.c12 IS NULL OR NOT t41.c15 BETWEEN 43 AND 68))) LIMIT 29)) WHERE ((t30.c7 < 76) AND (t31.c1 >= 9)) GROUP BY t34.c4, t31.c11, (t30.c7 - t31.c15), t30.c7, t31.c15 HAVING (t31.c11 < 21) ORDER BY col_2 DESC, col_4 DESC LIMIT 8) AS sq29 ON (t28.c14 >= sq29.col_1) WHERE (sq29.col_1 IN (SELECT t57.col_1 AS col_1 FROM t3 AS t56 INNER JOIN cte_1 AS t57 ON (((t56.c14 LIKE '%sample_51') OR (t56.c1 <= t57.col_2)) AND t56.c4 BETWEEN 11 AND 55) CROSS JOIN t2 AS t58 ON (((t56.c12 = t58.c6) AND (t56.c6 < t58.c3)) OR t56.c14 IS NULL) WHERE (NOT t56.c5 BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' AND NOT t56.c7 BETWEEN 26 AND 53)) AND ((t28.c13 = '2023-01-01') AND EXISTS(SELECT DEGREES(t54.col_2) AS col_1 FROM cte_1 AS t54 WHERE (t54.col_2 = ALL (SELECT LN(t55.c3) AS col_1 FROM t2 AS t55 ORDER BY t55.c13 ASC)))) AND sq29.col_1 IN (SELECT t57.col_1 AS col_1 FROM t3 AS t56 INNER JOIN cte_1 AS t57 ON (((t56.c14 LIKE '%sample_51') OR (t56.c1 <= t57.col_2)) AND t56.c4 BETWEEN 11 AND 55) CROSS JOIN t2 AS t58 ON (((t56.c12 = t58.c6) AND (t56.c6 < t58.c3)) OR t56.c14 IS NULL) WHERE (NOT t56.c5 BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' AND NOT t56.c7 BETWEEN 26 AND 53)));
-- Optional plan check:
-- Run EXPLAIN before each SELECT above. The mutated plan should contain duplicate subquery/materialization paths for the duplicated IN-subquery predicate.
© 2026, Oracle Corporation and/or its affiliates
Description: A AND B => B AND A AND B In this case, B is the predicate: sq29.col_1 IN (SELECT ... FROM t3 AS t56 ... CROSS JOIN t2 AS t58 ...) The original query contains one copy of this predicate. The mutated query contains two identical copies. Both queries return the same result, but the optimizer keeps two subquery/materialization paths in the mutated plan instead of eliminating the duplicate predicate. Correctness check on the compressed reproducer: original result: 1 row, COUNT(*) = 0 mutated result: 1 row, COUNT(*) = 0 rows equal: true Observed timing on the compressed reproducer: original median: 3.313 ms mutated median: 181.389 ms ratio original/mutated: 0.0183 The larger original test case showed the same behavior: original median: 6.036 ms mutated median: 188.291 ms ratio original/mutated: 0.0321 EXPLAIN evidence from the larger case showed that planning is also much slower: EXPLAIN FORMAT=JSON original median: 16.212 ms EXPLAIN FORMAT=JSON mutated median: 193.784 ms Plan comparison from the larger case: original: t56 mentions: 9 t58 mentions: 3 materialize-related operations: 31 operation_count: 386 mutated: t56 mentions: 18 t58 mentions: 6 materialize-related operations: 39 operation_count: 460 The mutated plan contains two duplicated subquery paths, observed as <subquery54> and <subquery83>. Expected behavior: The optimizer should recognize the duplicated deterministic predicate as redundant, or otherwise avoid planning/materializing the same IN-subquery twice. Actual behavior: The optimizer preserves both copies of the same IN-subquery predicate, producing duplicate subquery/materialization paths and causing a stable severe planning/runtime regression. How to repeat: Run the following SQL on MySQL 9.5.0. It creates a self-contained schema named pair595_repro, loads a reduced deterministic data set, and provides both the original and mutated queries. -- Setup SQL DROP DATABASE IF EXISTS `pair595_repro`; CREATE DATABASE `pair595_repro`; USE `pair595_repro`; SET FOREIGN_KEY_CHECKS = 0; -- DDL for t1 CREATE TABLE `t1` ( `c1` int NOT NULL AUTO_INCREMENT, `c2` varchar(255) NOT NULL, `c3` varchar(255) DEFAULT NULL, `c4` int DEFAULT NULL, `c5` date NOT NULL, `c6` varchar(10) NOT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `idx_t1_pk` (`c1`), KEY `idx_t1_c6` (`c6`), KEY `idx_t1_c3` (`c3`(63)), KEY `idx_t1_c2_c5` (`c2`(63),`c5`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Data for t1: 36 short rows INSERT INTO `t1` (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`) VALUES (1, 'sample_1', 'sample_1', 1, '2026-01-01', 'sample_1'), (2, 'sample_2', 'sample_2', 2, '2026-01-01', 'sample_2'), (3, 'sample_3', 'sample_3', 3, '2026-01-01', 'sample_3'), (4, 'sample_4', 'sample_4', 4, '2026-01-01', 'sample_4'), (5, 'sample_5', 'sample_5', 5, '2026-01-01', 'sample_5'), (6, 'sample_6', 'sample_6', 6, '2026-01-01', 'sample_6'), (7, 'sample_7', 'sample_7', 7, '2026-01-01', 'sample_7'), (8, 'sample_8', 'sample_8', 8, '2026-01-01', 'sample_8'), (9, 'sample_9', 'sample_9', 9, '2026-01-01', 'sample_9'), (10, 'sample_10', 'sample_10', 10, '2026-01-01', 'sample_0'), (11, 'sample_11', 'sample_11', 11, '2026-01-01', 'sample_1'), (12, 'sample_12', 'sample_12', 12, '2026-01-01', 'sample_2'), (13, 'sample_13', 'sample_13', 13, '2026-01-01', 'sample_3'), (14, 'sample_14', 'sample_14', 14, '2026-01-01', 'sample_4'), (15, 'sample_15', 'sample_15', 15, '2026-01-01', 'sample_5'), (16, 'sample_16', 'sample_16', 16, '2026-01-01', 'sample_6'), (17, 'sample_17', 'sample_17', 17, '2026-01-01', 'sample_7'), (18, 'sample_18', 'sample_18', 18, '2026-01-01', 'sample_8'), (19, 'sample_19', 'sample_19', 19, '2026-01-01', 'sample_9'), (20, 'sample_20', 'sample_20', 20, '2026-01-01', 'sample_0'), (21, 'sample_21', 'sample_21', 21, '2026-01-01', 'sample_1'), (22, 'sample_22', 'sample_22', 22, '2026-01-01', 'sample_2'), (23, 'sample_23', 'sample_23', 23, '2026-01-01', 'sample_3'), (24, 'sample_24', 'sample_24', 24, '2026-01-01', 'sample_4'), (25, 'sample_25', 'sample_25', 25, '2026-01-01', 'sample_5'), (26, 'sample_26', 'sample_26', 26, '2026-01-01', 'sample_6'), (27, 'sample_27', 'sample_27', 27, '2026-01-01', 'sample_7'), (28, 'sample_28', 'sample_28', 28, '2026-01-01', 'sample_8'), (29, 'sample_29', 'sample_29', 29, '2026-01-01', 'sample_9'), (30, 'sample_30', 'sample_30', 30, '2026-01-01', 'sample_0'), (31, 'sample_31', 'sample_31', 31, '2026-01-01', 'sample_1'), (32, 'sample_32', 'sample_32', 32, '2026-01-01', 'sample_2'), (33, 'sample_33', 'sample_33', 33, '2026-01-01', 'sample_3'), (34, 'sample_34', 'sample_34', 34, '2026-01-01', 'sample_4'), (35, 'sample_35', 'sample_35', 35, '2026-01-01', 'sample_5'), (36, 'sample_36', 'sample_36', 36, '2026-01-01', 'sample_6'); -- DDL for t2 CREATE TABLE `t2` ( `c1` int NOT NULL AUTO_INCREMENT, `c2` int NOT NULL, `c3` decimal(10,2) NOT NULL, `c4` varchar(50) NOT NULL, `c5` date NOT NULL, `c6` mediumtext, `c7` longtext, `c8` mediumblob, `c9` longblob, `c10` enum('value1','value2','value3') DEFAULT NULL, `c11` set('a','b','c','d') DEFAULT NULL, `c12` bit(8) DEFAULT NULL, `c13` datetime DEFAULT NULL, `c14` float(8,2) DEFAULT NULL, `c15` double(12,4) DEFAULT NULL, `c16` json DEFAULT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `idx_t2_pk` (`c1`), KEY `idx_t2_c4` (`c4`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Data for t2: 4 short rows INSERT INTO `t2` (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`) VALUES (1, 10, 1.50, 'sample_1', '2026-01-01', 'sample_1', '1', 'value1', 'a', X'01', '2026-01-01 00:00:00', 1.00, 1.0000), (2, 20, 2.50, 'sample_2', '2026-01-01', 'sample_2', '2', 'value1', 'a', X'02', '2026-01-01 00:00:00', 2.00, 2.0000), (3, 30, 3.50, 'sample_3', '2026-01-01', 'sample_3', '3', 'value1', 'a', X'03', '2026-01-01 00:00:00', 3.00, 3.0000), (4, 40, 4.50, 'sample_4', '2026-01-01', 'sample_4', '4', 'value1', 'a', X'04', '2026-01-01 00:00:00', 4.00, 4.0000); -- DDL for t3 CREATE TABLE `t3` ( `c1` int NOT NULL AUTO_INCREMENT, `c2` int NOT NULL, `c3` int NOT NULL, `c4` year NOT NULL, `c5` datetime DEFAULT NULL, `c6` tinyint DEFAULT NULL, `c7` smallint DEFAULT NULL, `c8` mediumint DEFAULT NULL, `c9` bigint DEFAULT NULL, `c10` longtext, `c11` geometry DEFAULT NULL, `c12` tinytext, `c13` tinyblob, `c14` set('x','y','z') DEFAULT NULL, `c15` tinyint(1) DEFAULT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `idx_t3_pk` (`c1`), KEY `idx_t3_c4` (`c4`), KEY `idx_t3_c6` (`c6`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Data for t3: intentionally empty SET FOREIGN_KEY_CHECKS = 1; -- Original query. Expected result: one row with cnt = 0. WITH cte_1 AS (SELECT LOG(10, t2.c3) AS col_1, (t2.c1 + t2.c7) AS col_2 FROM t2 AS t1 INNER JOIN t3 AS t2 ON (((t1.c2 >= t2.c1) OR (t1.c2 >= 26)) AND (t1.c13 <> t2.c5)) LEFT JOIN (SELECT t5.c9 AS col_1, (SELECT DEGREES(t9.c4) AS col_1 FROM t3 AS t9 WHERE NOT (t9.c4 IS NULL) LIMIT 1) AS col_2, LAST_VALUE(t5.c7) OVER (ORDER BY t5.c5 DESC) AS col_3 FROM t1 AS t4 LEFT JOIN t2 AS t5 ON (((NOT t5.c2 BETWEEN 45 AND 87 AND t5.c7 IN (SELECT t6.c6 AS col_1 FROM t2 AS t6 INNER JOIN t3 AS t7 ON (NOT (t6.c3 IS NULL) AND (t6.c7 = t7.c12)) CROSS JOIN t1 AS t8 ON (t6.c15 <= t8.c4) WHERE ((NOT t6.c14 BETWEEN 47 AND 96 OR (t6.c1 >= 78)) OR (t6.c4 = 'sample_87')))) AND (t4.c4 = 98)) AND NOT t5.c1 BETWEEN 37 AND 46) WHERE ((t4.c4 <> 85) AND NOT t5.c8 IS NULL)) AS sq3 ON (((NOT t2.c10 IN (SELECT t10.c11 AS col_1 FROM t2 AS t10 RIGHT JOIN t3 AS t11 ON (((NOT t11.c14 IS NULL OR (t10.c14 > t11.c1)) OR t11.c5 IN (SELECT BIT_OR(t12.c1) AS col_1 FROM t3 AS t12 WHERE NOT (t12.c1 BETWEEN 48 AND 90))) AND EXISTS(SELECT t13.c5 AS col_1 FROM t3 AS t13 RIGHT JOIN t2 AS t14 ON (t14.c5 = '2023-01-01') WHERE (NOT REGEXP_LIKE(t14.c7, '[a-zA-Z0-9]{3,12}')))) CROSS JOIN (SELECT ((t16.c3 > 56) AND (NOT t16.c4 LIKE 'sample_74%')) AS col_1, t16.c4 AS col_2, ACOS(t16.c1) AS col_3, ((t16.c13 <> '2023-01-01') AND (t16.c13 <> '2023-01-01')) AS col_4, STDDEV_POP(t16.c14) AS col_5 FROM t2 AS t16 WHERE (t16.c4 <> 'sample_40') GROUP BY ((t16.c3 > 56) AND (NOT t16.c4 LIKE 'sample_74%')), t16.c3, t16.c4, ACOS(t16.c1), t16.c1, ((t16.c13 <> '2023-01-01') AND (t16.c13 <> '2023-01-01')), t16.c13) AS sq15 ON (NOT ((t10.c2 >= sq15.col_3)) OR (t10.c3 = sq15.col_5)) WHERE (((NOT t10.c11 LIKE '%sample_81') AND NOT t10.c3 BETWEEN 34 AND 82) AND (t11.c10 <> 'sample_41'))) AND (t2.c12 = sq3.col_3)) AND (t2.c1 > sq3.col_2)) AND (t2.c6 >= sq3.col_2)) WHERE ((((EXISTS(SELECT HOUR(t17.c5) AS col_1 FROM t1 AS t17 LEFT JOIN t2 AS t18 ON (NOT t18.c4 LIKE '%sample_41%') LEFT JOIN (SELECT RANK() OVER (PARTITION BY t20.c2 ORDER BY t20.c2 DESC) AS col_1, t20.c6 AS col_2, t20.c6 AS col_3, t20.c6 AS col_4, SECOND(t20.c5) AS col_5 FROM t1 AS t20 WHERE (t20.c6 <> 'sample_62') LIMIT 18) AS sq19 ON t17.c3 IN (SELECT NULLIF(t21.c3, t22.c9) AS col_1 FROM t3 AS t21 CROSS JOIN t2 AS t22 ON (t21.c9 < t22.c14) LEFT JOIN t1 AS t23 ON (t23.c4 <> 5) WHERE (((t22.c4 LIKE '%sample_15') OR (t22.c3 < 40)) OR NOT t21.c1 BETWEEN 49 AND 76)) WHERE ((t18.c14 IN (SELECT t24.c14 AS col_1 FROM t2 AS t24 WHERE (t24.c3 BETWEEN 33 AND 43 AND (NOT t24.c10 LIKE 'sample_77%'))) OR NOT t17.c3 IN (SELECT DAYNAME(t27.c5) AS col_1 FROM t3 AS t25 LEFT JOIN t1 AS t26 ON (t25.c6 >= t26.c4) RIGHT JOIN t2 AS t27 ON (((t25.c1 > t27.c15) AND (t27.c1 > 65)) AND NOT t25.c9 BETWEEN 20 AND 31) WHERE (((t27.c14 > 82) AND t25.c2 BETWEEN 48 AND 71) AND (t27.c15 <> 58)) ORDER BY t25.c15 ASC)) AND NOT t17.c1 BETWEEN 33 AND 46)) AND (t1.c7 LIKE '%sample_75')) OR NOT t2.c5 BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59') OR (t2.c2 < 70)) AND t2.c10 IS NULL) ORDER BY t2.c4 DESC) SELECT COUNT(*) AS cnt FROM t2 AS t28 LEFT JOIN (SELECT t34.c4 AS col_1, 0 AS col_2, t31.c11 AS col_3, 0 AS col_4, NULL AS col_5 FROM t3 AS t30 CROSS JOIN t2 AS t31 ON EXISTS(SELECT STR_TO_DATE(sq32.col_2, sq32.col_2) AS col_1 FROM (SELECT CAST(t33.c1 AS DECIMAL(10, 2)) AS col_1, SCHEMA() AS col_2, STDDEV_SAMP(t33.c1) AS col_3, LENGTH(t33.c3) AS col_4 FROM t1 AS t33 WHERE (REGEXP_LIKE(t33.c6, '^sample_[0-9]+$')) GROUP BY CAST(t33.c1 AS DECIMAL(10, 2)), t33.c1, LENGTH(t33.c3), t33.c3 LIMIT 22) AS sq32 ORDER BY sq32.col_4 DESC) INNER JOIN t1 AS t34 ON (NOT ((t31.c13 = '2023-01-01')) AND EXISTS(SELECT t35.c5 AS col_1 FROM t2 AS t35 INNER JOIN (SELECT t37.c7 AS col_1, t37.c15 AS col_2, t37.c11 AS col_3, t37.c7 AS col_4 FROM t2 AS t37 WHERE (t37.c5 <> '2023-01-01')) AS sq36 ON NOT ((t35.c11 <> sq36.col_3)) CROSS JOIN cte_1 AS t38 ON (((t35.c3 BETWEEN 46 AND 89 AND (t35.c15 > t38.col_1)) AND t35.c11 IN (SELECT STDDEV_SAMP(t39.c4) AS col_1 FROM t1 AS t39 WHERE NOT t39.c1 BETWEEN 6 AND 25)) AND (t35.c14 > t38.col_1)) WHERE (t35.c2 BETWEEN 2 AND 10 AND sq36.col_1 IN (SELECT t40.c2 AS col_1 FROM t1 AS t40 CROSS JOIN t3 AS t41 ON (NOT t41.c12 IS NULL OR NOT t41.c15 BETWEEN 43 AND 68))) LIMIT 29)) WHERE ((t30.c7 < 76) AND (t31.c1 >= 9)) GROUP BY t34.c4, t31.c11, (t30.c7 - t31.c15), t30.c7, t31.c15 HAVING (t31.c11 < 21) ORDER BY col_2 DESC, col_4 DESC LIMIT 8) AS sq29 ON (t28.c14 >= sq29.col_1) WHERE (((t28.c13 = '2023-01-01') AND EXISTS(SELECT DEGREES(t54.col_2) AS col_1 FROM cte_1 AS t54 WHERE (t54.col_2 = ALL (SELECT LN(t55.c3) AS col_1 FROM t2 AS t55 ORDER BY t55.c13 ASC)))) AND sq29.col_1 IN (SELECT t57.col_1 AS col_1 FROM t3 AS t56 INNER JOIN cte_1 AS t57 ON (((t56.c14 LIKE '%sample_51') OR (t56.c1 <= t57.col_2)) AND t56.c4 BETWEEN 11 AND 55) CROSS JOIN t2 AS t58 ON (((t56.c12 = t58.c6) AND (t56.c6 < t58.c3)) OR t56.c14 IS NULL) WHERE (NOT t56.c5 BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' AND NOT t56.c7 BETWEEN 26 AND 53))); -- Mutated query. Expected result: one row with cnt = 0, but much slower. WITH cte_1 AS (SELECT LOG(10, t2.c3) AS col_1, (t2.c1 + t2.c7) AS col_2 FROM t2 AS t1 INNER JOIN t3 AS t2 ON (((t1.c2 >= t2.c1) OR (t1.c2 >= 26)) AND (t1.c13 <> t2.c5)) LEFT JOIN (SELECT t5.c9 AS col_1, (SELECT DEGREES(t9.c4) AS col_1 FROM t3 AS t9 WHERE NOT (t9.c4 IS NULL) LIMIT 1) AS col_2, LAST_VALUE(t5.c7) OVER (ORDER BY t5.c5 DESC) AS col_3 FROM t1 AS t4 LEFT JOIN t2 AS t5 ON (((NOT t5.c2 BETWEEN 45 AND 87 AND t5.c7 IN (SELECT t6.c6 AS col_1 FROM t2 AS t6 INNER JOIN t3 AS t7 ON (NOT (t6.c3 IS NULL) AND (t6.c7 = t7.c12)) CROSS JOIN t1 AS t8 ON (t6.c15 <= t8.c4) WHERE ((NOT t6.c14 BETWEEN 47 AND 96 OR (t6.c1 >= 78)) OR (t6.c4 = 'sample_87')))) AND (t4.c4 = 98)) AND NOT t5.c1 BETWEEN 37 AND 46) WHERE ((t4.c4 <> 85) AND NOT t5.c8 IS NULL)) AS sq3 ON (((NOT t2.c10 IN (SELECT t10.c11 AS col_1 FROM t2 AS t10 RIGHT JOIN t3 AS t11 ON (((NOT t11.c14 IS NULL OR (t10.c14 > t11.c1)) OR t11.c5 IN (SELECT BIT_OR(t12.c1) AS col_1 FROM t3 AS t12 WHERE NOT (t12.c1 BETWEEN 48 AND 90))) AND EXISTS(SELECT t13.c5 AS col_1 FROM t3 AS t13 RIGHT JOIN t2 AS t14 ON (t14.c5 = '2023-01-01') WHERE (NOT REGEXP_LIKE(t14.c7, '[a-zA-Z0-9]{3,12}')))) CROSS JOIN (SELECT ((t16.c3 > 56) AND (NOT t16.c4 LIKE 'sample_74%')) AS col_1, t16.c4 AS col_2, ACOS(t16.c1) AS col_3, ((t16.c13 <> '2023-01-01') AND (t16.c13 <> '2023-01-01')) AS col_4, STDDEV_POP(t16.c14) AS col_5 FROM t2 AS t16 WHERE (t16.c4 <> 'sample_40') GROUP BY ((t16.c3 > 56) AND (NOT t16.c4 LIKE 'sample_74%')), t16.c3, t16.c4, ACOS(t16.c1), t16.c1, ((t16.c13 <> '2023-01-01') AND (t16.c13 <> '2023-01-01')), t16.c13) AS sq15 ON (NOT ((t10.c2 >= sq15.col_3)) OR (t10.c3 = sq15.col_5)) WHERE (((NOT t10.c11 LIKE '%sample_81') AND NOT t10.c3 BETWEEN 34 AND 82) AND (t11.c10 <> 'sample_41'))) AND (t2.c12 = sq3.col_3)) AND (t2.c1 > sq3.col_2)) AND (t2.c6 >= sq3.col_2)) WHERE ((((EXISTS(SELECT HOUR(t17.c5) AS col_1 FROM t1 AS t17 LEFT JOIN t2 AS t18 ON (NOT t18.c4 LIKE '%sample_41%') LEFT JOIN (SELECT RANK() OVER (PARTITION BY t20.c2 ORDER BY t20.c2 DESC) AS col_1, t20.c6 AS col_2, t20.c6 AS col_3, t20.c6 AS col_4, SECOND(t20.c5) AS col_5 FROM t1 AS t20 WHERE (t20.c6 <> 'sample_62') LIMIT 18) AS sq19 ON t17.c3 IN (SELECT NULLIF(t21.c3, t22.c9) AS col_1 FROM t3 AS t21 CROSS JOIN t2 AS t22 ON (t21.c9 < t22.c14) LEFT JOIN t1 AS t23 ON (t23.c4 <> 5) WHERE (((t22.c4 LIKE '%sample_15') OR (t22.c3 < 40)) OR NOT t21.c1 BETWEEN 49 AND 76)) WHERE ((t18.c14 IN (SELECT t24.c14 AS col_1 FROM t2 AS t24 WHERE (t24.c3 BETWEEN 33 AND 43 AND (NOT t24.c10 LIKE 'sample_77%'))) OR NOT t17.c3 IN (SELECT DAYNAME(t27.c5) AS col_1 FROM t3 AS t25 LEFT JOIN t1 AS t26 ON (t25.c6 >= t26.c4) RIGHT JOIN t2 AS t27 ON (((t25.c1 > t27.c15) AND (t27.c1 > 65)) AND NOT t25.c9 BETWEEN 20 AND 31) WHERE (((t27.c14 > 82) AND t25.c2 BETWEEN 48 AND 71) AND (t27.c15 <> 58)) ORDER BY t25.c15 ASC)) AND NOT t17.c1 BETWEEN 33 AND 46)) AND (t1.c7 LIKE '%sample_75')) OR NOT t2.c5 BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59') OR (t2.c2 < 70)) AND t2.c10 IS NULL) ORDER BY t2.c4 DESC) SELECT COUNT(*) AS cnt FROM t2 AS t28 LEFT JOIN (SELECT t34.c4 AS col_1, 0 AS col_2, t31.c11 AS col_3, 0 AS col_4, NULL AS col_5 FROM t3 AS t30 CROSS JOIN t2 AS t31 ON EXISTS(SELECT STR_TO_DATE(sq32.col_2, sq32.col_2) AS col_1 FROM (SELECT CAST(t33.c1 AS DECIMAL(10, 2)) AS col_1, SCHEMA() AS col_2, STDDEV_SAMP(t33.c1) AS col_3, LENGTH(t33.c3) AS col_4 FROM t1 AS t33 WHERE (REGEXP_LIKE(t33.c6, '^sample_[0-9]+$')) GROUP BY CAST(t33.c1 AS DECIMAL(10, 2)), t33.c1, LENGTH(t33.c3), t33.c3 LIMIT 22) AS sq32 ORDER BY sq32.col_4 DESC) INNER JOIN t1 AS t34 ON (NOT ((t31.c13 = '2023-01-01')) AND EXISTS(SELECT t35.c5 AS col_1 FROM t2 AS t35 INNER JOIN (SELECT t37.c7 AS col_1, t37.c15 AS col_2, t37.c11 AS col_3, t37.c7 AS col_4 FROM t2 AS t37 WHERE (t37.c5 <> '2023-01-01')) AS sq36 ON NOT ((t35.c11 <> sq36.col_3)) CROSS JOIN cte_1 AS t38 ON (((t35.c3 BETWEEN 46 AND 89 AND (t35.c15 > t38.col_1)) AND t35.c11 IN (SELECT STDDEV_SAMP(t39.c4) AS col_1 FROM t1 AS t39 WHERE NOT t39.c1 BETWEEN 6 AND 25)) AND (t35.c14 > t38.col_1)) WHERE (t35.c2 BETWEEN 2 AND 10 AND sq36.col_1 IN (SELECT t40.c2 AS col_1 FROM t1 AS t40 CROSS JOIN t3 AS t41 ON (NOT t41.c12 IS NULL OR NOT t41.c15 BETWEEN 43 AND 68))) LIMIT 29)) WHERE ((t30.c7 < 76) AND (t31.c1 >= 9)) GROUP BY t34.c4, t31.c11, (t30.c7 - t31.c15), t30.c7, t31.c15 HAVING (t31.c11 < 21) ORDER BY col_2 DESC, col_4 DESC LIMIT 8) AS sq29 ON (t28.c14 >= sq29.col_1) WHERE (sq29.col_1 IN (SELECT t57.col_1 AS col_1 FROM t3 AS t56 INNER JOIN cte_1 AS t57 ON (((t56.c14 LIKE '%sample_51') OR (t56.c1 <= t57.col_2)) AND t56.c4 BETWEEN 11 AND 55) CROSS JOIN t2 AS t58 ON (((t56.c12 = t58.c6) AND (t56.c6 < t58.c3)) OR t56.c14 IS NULL) WHERE (NOT t56.c5 BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' AND NOT t56.c7 BETWEEN 26 AND 53)) AND ((t28.c13 = '2023-01-01') AND EXISTS(SELECT DEGREES(t54.col_2) AS col_1 FROM cte_1 AS t54 WHERE (t54.col_2 = ALL (SELECT LN(t55.c3) AS col_1 FROM t2 AS t55 ORDER BY t55.c13 ASC)))) AND sq29.col_1 IN (SELECT t57.col_1 AS col_1 FROM t3 AS t56 INNER JOIN cte_1 AS t57 ON (((t56.c14 LIKE '%sample_51') OR (t56.c1 <= t57.col_2)) AND t56.c4 BETWEEN 11 AND 55) CROSS JOIN t2 AS t58 ON (((t56.c12 = t58.c6) AND (t56.c6 < t58.c3)) OR t56.c14 IS NULL) WHERE (NOT t56.c5 BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' AND NOT t56.c7 BETWEEN 26 AND 53))); -- Optional plan check: -- Run EXPLAIN before each SELECT above. The mutated plan should contain duplicate subquery/materialization paths for the duplicated IN-subquery predicate.