-- schema.sql
SET FOREIGN_KEY_CHECKS=0;
DROP VIEW IF EXISTS v4;
DROP VIEW IF EXISTS v3;
DROP VIEW IF EXISTS v2;
DROP VIEW IF EXISTS v1;
DROP VIEW IF EXISTS v0;
DROP TABLE IF EXISTS t4;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t0;
CREATE TABLE `t0` (
  `id` bigint NOT NULL,
  `k0` date NOT NULL,
  `k1` date NOT NULL,
  `k2` date NOT NULL,
  `k3` bigint NOT NULL,
  `p0` decimal(12,2) NOT NULL,
  `p1` float NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_k2_0` (`k2`),
  KEY `idx_k0_8` (`k0`),
  KEY `idx_p0_14` (`p0`),
  KEY `idx_k1_15` (`k1`),
  KEY `idx_id_28` (`id`),
  KEY `idx_k3_32` (`k3`),
  KEY `idx_k1_k3_33` (`k1`,`k3`),
  KEY `idx_p1_33` (`p1`),
  KEY `idx_k2_k0_k3_35` (`k2`,`k0`,`k3`),
  KEY `idx_k1_p1_35` (`k1`,`p1`),
  KEY `idx_k2_k1_k3_35` (`k2`,`k1`,`k3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `t1` (
  `id` bigint NOT NULL,
  `k0` date NOT NULL,
  `d0` int NOT NULL,
  `d1` varchar(64) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_d1_1` (`d1`),
  KEY `idx_d0_2` (`d0`),
  KEY `idx_id_22` (`id`),
  KEY `idx_k0_30` (`k0`),
  KEY `idx_k0_d0_35` (`k0`,`d0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `t2` (
  `id` bigint NOT NULL,
  `k1` date NOT NULL,
  `k0` date NOT NULL,
  `d0` datetime NOT NULL,
  `d1` date NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_d1_12` (`d1`),
  KEY `idx_d0_21` (`d0`),
  KEY `idx_k1_23` (`k1`),
  KEY `idx_id_31` (`id`),
  KEY `idx_k0_34` (`k0`),
  KEY `idx_d0_d1_k0_35` (`d0`,`d1`,`k0`),
  KEY `idx_d1_d0_35` (`d1`,`d0`),
  KEY `idx_k0_k1_d1_35` (`k0`,`k1`,`d1`),
  KEY `idx_k0_d0_35` (`k0`,`d0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `t3` (
  `id` bigint NOT NULL,
  `k2` date NOT NULL,
  `k0` date NOT NULL,
  `d0` tinyint(1) NOT NULL,
  `d1` datetime NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_d0_6` (`d0`),
  KEY `idx_k0_16` (`k0`),
  KEY `idx_id_19` (`id`),
  KEY `idx_d1_27` (`d1`),
  KEY `idx_k2_29` (`k2`),
  KEY `idx_k0_d0_35` (`k0`,`d0`),
  KEY `idx_k2_k0_d0_35` (`k2`,`k0`,`d0`),
  KEY `idx_d1_d0_35` (`d1`,`d0`),
  KEY `idx_k2_k0_35` (`k2`,`k0`),
  KEY `idx_d1_k2_35` (`d1`,`k2`),
  KEY `idx_k0_d0_d1_35` (`k0`,`d0`,`d1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `t4` (
  `id` bigint NOT NULL,
  `k3` bigint NOT NULL,
  `k0` date NOT NULL,
  `d0` bigint NOT NULL,
  `d1` varchar(64) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_k0_k3_d0_2` (`k0`,`k3`,`d0`),
  KEY `idx_k0_4` (`k0`),
  KEY `idx_id_7` (`id`),
  KEY `idx_d1_24` (`d1`),
  KEY `idx_k3_25` (`k3`),
  KEY `idx_d0_26` (`d0`),
  KEY `idx_d0_k3_35` (`d0`,`k3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `v0` (`cnt`, `sum1`) AS SELECT DISTINCT COUNT(1) AS `cnt`,SUM(`t0`.`p0`) AS `sum1` FROM ((`shiro_fuzz`.`t0` RIGHT JOIN `shiro_fuzz`.`t2` ON (`t0`.`k0`=`t2`.`k0`)) LEFT JOIN `shiro_fuzz`.`t1` ON (`t0`.`k0`=`t1`.`k0`)) LEFT JOIN `shiro_fuzz`.`t3` ON (`t0`.`k0`=`t3`.`k0`) WHERE (NOT EXISTS (SELECT `t4`.`k0` AS `c0` FROM `shiro_fuzz`.`t4` WHERE (`t4`.`k0`=`t0`.`k0`) ORDER BY `t4`.`d0` LIMIT 4) AND EXISTS (SELECT `t2`.`k1` AS `c0` FROM `shiro_fuzz`.`t2` WHERE (`t2`.`k0`=`t0`.`k0`) ORDER BY LENGTH(12),(`t2`.`id`+`t2`.`id`) DESC LIMIT 7));

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `v1` (`c0`) AS SELECT 22 AS `c0` FROM (`shiro_fuzz`.`t0` LEFT JOIN `shiro_fuzz`.`t2` ON (`t0`.`k0`=`t2`.`k0`)) JOIN `shiro_fuzz`.`t4` ON (`t0`.`k0`=`t4`.`k0`) WHERE (`t0`.`k0` IN (_UTF8MB4'2023-04-17',_UTF8MB4'2024-07-13',_UTF8MB4'2023-11-22'));

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `v2` (`cnt`, `sum1`) AS SELECT COUNT(1) AS `cnt`,SUM(`t4`.`k3`) AS `sum1` FROM (((`shiro_fuzz`.`t0` JOIN `shiro_fuzz`.`t3` ON (`t0`.`k0`=`t3`.`k0`)) JOIN `shiro_fuzz`.`t2` ON (`t0`.`k0`=`t2`.`k0`)) RIGHT JOIN `shiro_fuzz`.`t4` ON (`t0`.`k0`=`t4`.`k0`)) JOIN `shiro_fuzz`.`t1` ON (`t0`.`k0`=`t1`.`k0`) WHERE NOT (`t0`.`k0` IN (SELECT `t4`.`k0` AS `c0` FROM `shiro_fuzz`.`t4` WHERE (`t4`.`k0`=`t0`.`k0`)));

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `v3` (`c0`, `c1`) AS SELECT DISTINCT UPPER(_UTF8MB4's76') AS `c0`,_UTF8MB4's64' AS `c1` FROM ((`shiro_fuzz`.`t0` LEFT JOIN `shiro_fuzz`.`t4` ON (`t0`.`k0`=`t4`.`k0`)) RIGHT JOIN `shiro_fuzz`.`t1` ON (`t0`.`k0`=`t1`.`k0`)) RIGHT JOIN `shiro_fuzz`.`t2` ON (`t0`.`k0`=`t2`.`k0`) WHERE NOT (`t1`.`k0` IN (SELECT `t1`.`k0` AS `c0` FROM `shiro_fuzz`.`t1` WHERE (`t1`.`k0`=`t0`.`k0`))) ORDER BY _UTF8MB4's64' DESC,UPPER(_UTF8MB4's76') DESC;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `v4` (`c0`, `c1`) AS SELECT (72*`t0`.`p1`) AS `c0`,38 AS `c1` FROM (((`shiro_fuzz`.`t0` LEFT JOIN `shiro_fuzz`.`t4` ON (`t0`.`k0`=`t4`.`k0`)) JOIN `shiro_fuzz`.`t1` ON (`t0`.`k0`=`t1`.`k0`)) JOIN `shiro_fuzz`.`t3` ON (`t0`.`k0`=`t3`.`k0`)) JOIN `shiro_fuzz`.`t2` ON (`t0`.`k0`=`t2`.`k0`) WHERE (`t0`.`k0` IN (_UTF8MB4'2025-02-11',_UTF8MB4'2024-12-17',_UTF8MB4'2023-12-06'));

SET FOREIGN_KEY_CHECKS=1;

SOURCE reports/case_0001_019c223b-ca47-7df6-bfd3-5118f7a75c78/inserts.sql;

-- original
SELECT t0.id AS c0, 1 AS c1, (SELECT COUNT(1) AS cnt FROM v0 WHERE (v0.cnt = t2.d0) ORDER BY COUNT(1) DESC LIMIT 2) AS c2 FROM t0 LEFT JOIN t1 ON (t0.k0 = t1.k0) LEFT JOIN t4 ON (t0.k0 = t4.k0) JOIN t2 ON (t0.k0 = t2.k0) WHERE (t0.k0 = t2.k0) ORDER BY (SELECT COUNT(1) AS cnt FROM v0 WHERE (v0.cnt = t2.d0) ORDER BY COUNT(1) DESC LIMIT 2) LIMIT 17;

-- rewritten (EET or_false)
SELECT `t0`.`id` AS `c0`,1 AS `c1`,(SELECT COUNT(1) AS `cnt` FROM `v0` WHERE (`v0`.`cnt`=`t2`.`d0`) ORDER BY COUNT(1) DESC LIMIT 2) AS `c2` FROM ((`t0` LEFT JOIN `t1` ON (`t0`.`k0`=`t1`.`k0`) OR 0) LEFT JOIN `t4` ON (`t0`.`k0`=`t4`.`k0`)) JOIN `t2` ON (`t0`.`k0`=`t2`.`k0`) WHERE (`t0`.`k0`=`t2`.`k0`) ORDER BY (SELECT COUNT(1) AS `cnt` FROM `v0` WHERE (`v0`.`cnt`=`t2`.`d0`) ORDER BY COUNT(1) DESC LIMIT 2) LIMIT 17;