cte.test

set tidb_cost_model_version=1;
drop database if exists cte1;
create database cte1;
# case 1
drop table if exists tbl_0;
create table tbl_0(a int);
with recursive cte_0 (col_10,col_11,col_12) AS ( select 1, 2,3 from tbl_0 UNION select col_10 + 1,col_11 + 1,col_12 + 1 from cte_0 where col_10 < 10 ) select * from cte_0;
# case 2
drop table if exists tbl_1;
CREATE TABLE `tbl_1` (
  `col_5` decimal(47,21) NOT NULL DEFAULT '5308.880000000000000000000',
  `col_6` enum('Alice','Bob','Charlie','David') DEFAULT NULL,
  `col_7` float NOT NULL,
  `col_8` bigint NOT NULL DEFAULT '-688199144806783096',
  `col_9` varchar(248) NOT NULL,
  PRIMARY KEY (`col_5`,`col_7`,`col_9`,`col_8`),
  UNIQUE KEY `idx_4` (`col_8`),
  UNIQUE KEY `idx_7` (`col_5`,`col_7`,`col_8`),
  UNIQUE KEY `idx_9` (`col_9`,`col_8`),
  UNIQUE KEY `idx_3` (`col_9`(3),`col_8`),
  UNIQUE KEY `idx_8` (`col_7`,`col_6`,`col_8`,`col_5`),
  KEY `idx_5` (`col_7`),
  KEY `idx_6` (`col_7`),
  KEY `idx_10` (`col_9`,`col_5`),
  KEY `idx_11` (`col_5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY HASH (`col_8`)
PARTITIONS 4 */;
with recursive cte_1 (col_13,col_14,col_15,col_16,col_17) AS ( with recursive cte_2 (col_18,col_19,col_20,col_21,col_22,col_23,col_24) AS ( select 1, 2,col_8,4,5,6,7 from tbl_1 ) select col_19,col_18,col_22,col_23,col_21 from cte_2 UNION ALL select col_13 + 1,col_14 + 1,col_15 + 1,col_16 + 1,col_17 + 1 from cte_1 where col_13 < 10 ) select * from cte_1;
# case 3
with recursive cte_256 (col_969,col_970,col_971) AS ( with recursive cte_257 (col_972,col_973,col_974,col_975) AS ( select 1, 2,col_8,4 from tbl_1 UNION select col_972 + 1,col_973 + 1,col_974 + 1,col_975 + 1 from cte_257 where col_972 < 10 ) select col_975,col_974,col_973 from cte_257 UNION DISTINCT select col_969 + 1,col_970 + 1,col_971 + 1 from cte_256 where col_969 < 10 ) select * from cte_256;
# case 4
drop table if exists tbl_2, tbl_3;
create table tbl_2 ( col_4 char(246) collate utf8_unicode_ci not null , col_5 char(253) collate utf8mb4_unicode_ci  )  ;
create table tbl_3 ( col_6 char(207) collate utf8mb4_unicode_ci , col_7 int unsigned not null  )  ;
insert into tbl_2 values ( "0",null ) ;
insert into tbl_2 values ( "1","0" ) ;
insert into tbl_2 values ( "1","1" ) ;
insert into tbl_2 values ( "0","0" ) ;
insert into tbl_2 values ( "0","1" ) ;
insert into tbl_3 values ( "1",0 ) ;
insert into tbl_3 values ( "1",1 ) ;
insert into tbl_3 values ( "0",0 ) ;
insert into tbl_3 values ( "0",1 ) ;
with recursive tbl_2 (col_64,col_65,col_66,col_67) AS ( select 1, col_6,col_6,4 from tbl_3 UNION DISTINCT select col_64 + 1,concat(col_65, 1),col_66 + 1,concat(col_67, 1) from tbl_2 where col_64 < 5 ) select * from tbl_2 order by col_64;
# case 5
drop table if exists tbl_3, tbl_4;
create table tbl_3 ( col_6 int not null , col_7 char(95) collate utf8_general_ci  )  ;
create table tbl_4 ( col_8 char collate utf8_unicode_ci , col_9 char collate utf8mb4_bin  )  ;
insert into tbl_3 values ( 0,"1" ) ;
insert into tbl_4 values ( "1","0" ) ;
with recursive cte_2245 (col_8692,col_8693) AS ( select 1, col_7 from tbl_3 UNION  select col_8692 + 1,concat(col_8693, 1) from cte_2245 where col_8692 < 5 ) , cte_2246 (col_8694,col_8695,col_8696,col_8697) AS ( with recursive cte_2247 (col_8698,col_8699,col_8700,col_8701) AS ( select 1, cast("2" as char(20)),3,col_8 from tbl_4 ) select col_8698,col_8699,col_8700,col_8701 from cte_2247 UNION  select col_8694 + 1,col_8695 + 1,col_8696 + 1,col_8697 + 1 from cte_2246 where col_8694 < 5 ) select * from cte_2245,cte_2246 order by col_8692,col_8693,col_8696,col_8695,col_8697,col_8694;
# case 6
with recursive cte2 as (select 1 as col_1, 2 as col_2) select c1.col_1, c2.col_2 from cte2 as c1, cte2 as c2 where c2.col_2 = 1;
# case 7
with recursive cte (c1) as (select 1), cte1 (c2) as (select 1 union select c1 + 1 from cte, cte1) select * from cte, cte1;
# case 8
--error 1054
with recursive tbl_0 (col_943,col_944,col_945,col_946,col_947) AS ( with recursive tbl_0 (col_948,col_949,col_950,col_951,col_952) AS ( select 1, 2,3,4,5 UNION ALL select col_948 + 1,col_949 + 1,col_950 + 1,col_951 + 1,col_952 + 1 from tbl_0 where col_948 < 5 ) select col_948,col_949,col_951,col_950,col_952 from tbl_0 UNION ALL select col_943 + 1,col_944 + 1,col_945 + 1,col_946 + 1,col_947 + 1 from tbl_0 where col_943 < 5 ) select * from tbl_0;
# case 9
with recursive cte1 (c1, c2) as (select 1, '1' union select concat(c1, 1), c2 + 1 from cte1 where c1 < 100) select * from cte1;
# case 10
with recursive cte_8 (col_51,col_52,col_53,col_54) AS ( with recursive cte_9 (col_55,col_56,col_57,col_58) AS ( select 1, 2,3,4 UNION ALL select col_55 + 1,col_56 + 1,col_57 + 1,col_58 + 1 from cte_9 where col_55 < 5 ) select col_55,col_57,col_56,col_58 from cte_9 UNION DISTINCT select col_51 + 1,col_52 + 1,col_53 + 1,col_54 + 1 from cte_8 where col_51 < 5 ) select * from cte_8;
# case 11
with recursive qn as (select 1 from dual union all select 1 from dual) select * from qn;
# case 12
with recursive qn as (select 1 as a from dual group by a union all select a+1 from qn where a<3) select * from qn;
# case 13
with recursive qn as ((select 1 as a from dual order by a) union all select a+1 from qn where a<3) select * from qn;
# case 14
drop table if exists employees;
CREATE TABLE employees (
ID INT PRIMARY KEY,
NAME VARCHAR(100),
MANAGER_ID INT,
INDEX (MANAGER_ID),
FOREIGN KEY (MANAGER_ID) REFERENCES employees(ID)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),
(198, "John", 333),
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);
WITH RECURSIVE employees_extended AS (SELECT ID, NAME, MANAGER_ID, CAST(ID AS CHAR(200)) AS PATH FROM employees WHERE NAME='Pierre' UNION ALL SELECT S.ID, S.NAME, S.MANAGER_ID, CONCAT(M.PATH, ",", S.ID) FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID)   SELECT * FROM employees_extended;
# case 15
with recursive cte (c1) as (select 1), cte1 (c2) as (select 1 union select c1 + 1 from cte where c1 < 10) select * from cte where c1 < 5;
# case 16
with recursive cte_581 (col_2343,col_2344,col_2345) AS ( select 1, '2',cast('3' as char(20))) , cte_582 (col_2346,col_2347,col_2348) AS ( select 1, 2, 3) select * from cte_581 as cte_as_583,cte_582 as cte_as_584,cte_582 as cte_as_585 order by cte_as_583.col_2343,cte_as_585.col_2348,cte_as_584.col_2346,cte_as_584.col_2348,cte_as_583.col_2344,cte_as_584.col_2347,cte_as_585.col_2346,cte_as_585.col_2347,cte_as_583.col_2345;
# case 17
with recursive tbl_3 (col_19,col_20,col_21,col_22) AS ( select 1, 2,3,4 UNION  select col_19 + 1,col_20 + 1,col_21 + 1,concat(col_22, 1) from tbl_3 where col_19 < 5 ) , cte_4 (col_23,col_24,col_25,col_26) AS ( select 1, 2,cast("3" as char(20)),4 UNION DISTINCT select col_23 + 1,col_24 + 1,concat(col_25, 1),col_26 + 1 from cte_4 where col_23 < 5 ) select * from tbl_3 as cte_as_3,cte_4 as cte_as_4,tbl_3 as cte_as_5 order by cte_as_3.col_19,cte_as_4.col_23,cte_as_4.col_25,cte_as_4.col_24,cte_as_4.col_26,cte_as_3.col_20,cte_as_5.col_22,cte_as_3.col_21,cte_as_5.col_20,cte_as_3.col_22,cte_as_5.col_19,cte_as_5.col_21;
# case 18
with cte1 (c1) as (select 1) select * from cte1 as b, cte1 as a;
# case 19
--error 1222
WITH RECURSIVE qn AS
(
select 1
union all
select 3, 0 from qn
)
select * from qn;
# case 20
--error 1235
with recursive cte1 as (select 1 union all (select 1 from cte1 limit 10)) select * from cte1;
# case 21
# TODO: uncomment this case after we support limit
# with recursive cte1 as (select 1 union all select 1 from cte1 limit 10) select * from cte1;
# case 22
with recursive qn as (select 123 as a union all select null from qn where a is not null) select * from qn;
# case 23
--error 1353
with recursive q (b) as (select 1, 1 union all select 1, 1 from q) select b from q;
# case 24
drop table if exists t1;
create table t1(a int);
insert into t1 values(1);
insert into t1 values(2);
SELECT *
FROM
t1 dt
WHERE
EXISTS(
  WITH RECURSIVE qn AS (SELECT a*0 AS b UNION ALL SELECT b+1 FROM qn WHERE b=0)
  SELECT * FROM qn WHERE b=a
  );
# case 25
drop table if exists t1;
create table t1 (a int);
insert into t1 values (1);
SELECT (WITH qn AS (SELECT 10*a as a FROM t1),
        qn2 AS (SELECT 3*a AS b FROM qn)                                                                                                                                                                                                                                              SELECT * from qn2 LIMIT 1)
FROM t1;
# case 26
select (with qn as (select "with") select * from qn) as scal_subq
from dual;
# case 27
drop table if exists t1;
create table t1 (a int);                                                                                                                                                                                                                                                      insert into t1 values(1), (2), (3);
with q as (select * from t1)
select /*+ merge(q) no_merge(q1) */ * from q, q q1 where q.a=1 and q1.a=2;
# case 28
drop table if exists t1;                                                                                                                                                                                                                                                      create table t1 (a int, b int);
with qn as (select a, b from t1) select b from qn group by a;
# case 29
drop table if exists t1;
create table t1(a int);
insert into t1 values(1);
insert into t1 values(2);
SELECT *
FROM
t1 dt
WHERE
EXISTS(
  WITH RECURSIVE qn AS (SELECT a*0+1 AS b UNION ALL SELECT b+1 FROM qn WHERE b=0)
  SELECT * FROM qn WHERE b=1
  );
# case 30
drop table if exists tbl_1;
CREATE TABLE `tbl_1` (
  `col_2` char(65) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `col_3` int(11) NOT NULL
);
with recursive cte_8932 (col_34891,col_34892) AS ( with recursive cte_8932 (col_34893,col_34894,col_34895) AS ( with  tbl_1 (col_34896,col_34897,col_34898,col_34899) AS ( select 1, "2",3,col_3 from tbl_1 ) select cte_as_8958.col_34896,cte_as_8958.col_34898,cte_as_8958.col_34899 from tbl_1 as cte_as_8958 UNION DISTINCT select col_34893 + 1,concat(col_34894, 1),col_34895 + 1 from cte_8932 where col_34893 < 5 ) select cte_as_8959.col_34893,cte_as_8959.col_34895 from cte_8932 as cte_as_8959 ) select * from cte_8932 as cte_as_8960 order by cte_as_8960.col_34891,cte_as_8960.col_34892;
# case 31
drop table if exists t1;
create table t1(c1 bigint unsigned);
insert into t1 values(0);
--error 1690
with recursive cte1 as (select c1 - 1 c1 from t1 union all select c1 - 1 c1 from cte1 where c1 != 0) select * from cte1 dt1, cte1 dt2;
# case 32
drop table if exists t;
create table t(a int, b int, key (b));
desc with cte as (select * from t) select * from cte;
create SESSION binding for with cte as (select * from t) select * from cte using with cte as (select * from t use index(b)) select * from cte;
desc with cte as (select * from t) select * from cte;
desc with cte as (select * from t use index()) select * from cte;
# case 33
drop table if exists t1, tpk;
create table t1(c1 int);
insert into t1 values(1), (2), (1), (2);
create table tpk(c1 int primary key);
insert into tpk values(1), (2), (3);

--echo // Expect a Sort operator on CTE.
explain with cte1 as (select c1 from t1) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;
with cte1 as (select c1 from t1) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;

--echo // Sort should not exist, because tpk.c1 is pk. Not the best plan for now(#25674).
explain with cte1 as (select c1 from tpk) select /*+ MERGE_JOIN(dt1, dt2) */ * from tpk dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;
with cte1 as (select c1 from tpk) select /*+ MERGE_JOIN(dt1, dt2) */ * from tpk dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;

--echo // Sort should not exist, because we have order by in CTE definition. Not the best plan for now(#25674).
explain with cte1 as (select c1 from t1 order by c1) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;
with cte1 as (select c1 from t1 order by c1) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;

--echo // Expect a Sort operator on CTE. Because it's recursive.
explain with recursive cte1 as (select c1 from t1 union select c1 +1 c1 from cte1 where c1 < 3) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;
with recursive cte1 as (select c1 from t1 union select c1 +1 c1 from cte1 where c1 < 3) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;

--echo // Expect a Sort operator on CTE. Because it's recursive.
explain with recursive cte1 as (select c1 from tpk union select c1 +1 c1 from cte1 where c1 < 3) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;
with recursive cte1 as (select c1 from tpk union select c1 +1 c1 from cte1 where c1 < 3) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;

--echo // Got order by in CTE definition
--echo // Expect Sort operator in CTE definition.
explain with cte1 as (select c1 from t1 order by c1) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;
with cte1 as (select c1 from t1 order by c1) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;

--echo // Sort should not exist, because tpk is ordered. Not the best plan for now(#25674).
explain with cte1 as (select c1 from tpk order by c1) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;
with cte1 as (select c1 from tpk order by c1) select /*+ MERGE_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = dt1.c1 order by 1, 2;

--echo // HashJoin. No need to sort
explain with cte1 as (select c1 from t1) select /*+ HASH_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = 1 order by 1, 2;
with cte1 as (select c1 from t1) select /*+ HASH_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = 1 order by 1, 2;

explain with cte1 as (select c1 from tpk) select /*+ HASH_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = 1 order by 1, 2;
with cte1 as (select c1 from tpk) select /*+ HASH_JOIN(dt1, dt2) */ * from t1 dt1 inner join cte1 dt2 on dt2.c1 = 1 order by 1, 2;

--echo // Use same CTE.
drop table if exists tpk1, tpk;
create table tpk(c1 int primary key);
insert into tpk values(1), (2), (3);
create table tpk1(c1 int primary key);
insert into tpk1 values(1), (2), (3);
explain with cte1 as (select c1 from tpk) select /*+ merge_join(dt1, dt2) */ * from tpk1 dt1 inner join cte1 dt2 inner join cte1 dt3 on dt1.c1 = dt2.c1 and dt2.c1 = dt3.c1;
with cte1 as (select c1 from tpk) select /*+ merge_join(dt1, dt2) */ * from tpk1 dt1 inner join cte1 dt2 inner join cte1 dt3 on dt1.c1 = dt2.c1 and dt2.c1 = dt3.c1;
#case 34
--echo // Test CTE as inner side of Apply
drop table if exists t1, t2;
create table t1(c1 int, c2 int);
insert into t1 values(2, 1);
insert into t1 values(2, 2);
create table t2(c1 int, c2 int);
insert into t2 values(1, 1);
insert into t2 values(3, 2);
explain select * from t1 where c1 > all(with cte1 as (select c1 from t2 where t2.c2 = t1.c2) select c1 from cte1);
select * from t1 where c1 > all(with cte1 as (select c1 from t2 where t2.c2 = t1.c2) select c1 from cte1);

--echo // Test semi apply.
insert into t1 values(2, 3);
explain select * from t1 where exists(with cte1 as (select c1 from t2 where t2.c2 = t1.c2) select c1 from cte1);
select * from t1 where exists(with cte1 as (select c1 from t2 where t2.c2 = t1.c2) select c1 from cte1);

--echo // Same as above, but test recursive cte.
explain select * from t1 where c1 > all(with recursive cte1 as (select c1 from t2 where t2.c2 = t1.c2 union all select c1+1 as c1 from cte1 limit 1) select c1 from cte1);
select * from t1 where c1 > all(with recursive cte1 as (select c1 from t2 where t2.c2 = t1.c2 union all select c1+1 as c1 from cte1 limit 1) select c1 from cte1);

explain select * from t1 where exists(with recursive cte1 as (select c1 from t2 where t2.c2 = t1.c2 union all select c1+1 as c1 from cte1 limit 10) select c1 from cte1);
select * from t1 where exists(with recursive cte1 as (select c1 from t2 where t2.c2 = t1.c2 union all select c1+1 as c1 from cte1 limit 10) select c1 from cte1);

--echo // Test correlated col is in recursive part.
explain select * from t1 where c1 > all(with recursive cte1 as (select c1, c2 from t2 union all select c1+1 as c1, c2+1 as c2 from cte1 where cte1.c2=t1.c2) select c1 from cte1);
select * from t1 where c1 > all(with recursive cte1 as (select c1, c2 from t2 union all select c1+1 as c1, c2+1 as c2 from cte1 where cte1.c2=t1.c2) select c1 from cte1);

explain select * from t1 where exists(with recursive cte1 as (select c1, c2 from t2 union all select c1+1 as c1, c2+1 as c2 from cte1 where cte1.c2=t1.c2) select c1 from cte1);
select * from t1 where exists(with recursive cte1 as (select c1, c2 from t2 union all select c1+1 as c1, c2+1 as c2 from cte1 where cte1.c2=t1.c2) select c1 from cte1);
# Some cases to Test Create View With CTE and checkout Database
# With name is the same as the table name
use cte;
drop table if exists t1, t2;
drop view if exists v1;
create table t1 (a int);
insert into t1 values (0), (1), (2), (3), (4);
create table t2 (a int);
insert into t2 values (1), (2), (3), (4), (5);
drop view if exists v1,v2;
create view v1 as with t1 as (select a from t2 where t2.a=3 union select t2.a+1 from t1,t2 where t1.a=t2.a) select * from t1 order by a desc limit 5;
create view v2 as with recursive t1 as ( select a from t2 where t2.a=3 union select t2.a+1 from t1,t2 where t1.a=t2.a) select * from t1 order by a desc limit 5;
use cte1;
select * from cte.v1;
select * from cte.v2;
# case
use cte;
drop table if exists t ,t1, t2;
create table t(a int);
insert into t values (0);
create table t1 (b int);
insert into t1 values (0);
create table t2 (c int);
insert into t2 values (0);
drop view if exists v1;
create view v1 as with t1 as (with t11 as (select * from t) select * from t1, t2) select * from t1;
use cte1;
select * from cte.v1;
# case
use cte;
drop table if exists t11111;
create table t11111 (d int);
insert into t11111 values (123), (223), (323);
drop view if exists v1;
create view v1 as WITH t123 AS (WITH t11111 AS ( SELECT * FROM t1 ) SELECT ( WITH t2 AS ( SELECT ( WITH t23 AS ( SELECT * FROM t11111 ) SELECT * FROM t23 LIMIT 1 ) FROM t11111 ) SELECT *  FROM t2  LIMIT 1  )  FROM t11111, t2 ) SELECT * FROM t11111;
use cte1;
select * from cte.v1;
# case
use cte;
drop table if exists t1;
create table t1 (a int);
insert into t1 values (1);
drop view if exists v1;
create view v1 as SELECT (WITH qn AS (SELECT 10*a as a FROM t1),qn2 AS (SELECT 3*a AS b FROM qn) SELECT * from qn2 LIMIT 1) FROM t1;
use cte1;
select * from cte.v1;
# case
use cte;
drop table if exists t1,t2;
create table t1 (a int);
insert into t1 values (0), (1);
create table t2 (b int);
insert into t2 values  (4), (5);
drop view if exists v1;
create view v1 as with t1 as (with t11 as (select * from t1) select * from t1, t2) select * from t1;
use cte1;
select * from cte.v1;
# case
CREATE TABLE `t_cqmg3b` (
  `wkey` int(11) DEFAULT NULL,
  `pkey` int(11) NOT NULL,
  `c_anpf_c` int(11) DEFAULT NULL,
  `c_b_fp_c` text DEFAULT NULL,
  `c_ndccfb` int(11) DEFAULT NULL,
  `c_8rswc` int(11) DEFAULT NULL,
  PRIMARY KEY (`pkey`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `t_cqmg3b` VALUES (102,556000,NULL,'vbwxgc',NULL,NULL),(102,557000,NULL,'bfblud',NULL,NULL),(102,558000,NULL,'c6drnb',NULL,NULL),(102,559000,NULL,'fo_ezc',NULL,NULL),(102,560000,NULL,'btdes',NULL,NULL),(102,561000,NULL,'gy6zc',NULL,NULL),(102,562000,NULL,'9cyx9c',NULL,NULL),(102,563000,NULL,NULL,NULL,NULL),(103,564000,NULL,NULL,NULL,NULL),(103,565000,NULL,NULL,NULL,NULL),(103,566000,NULL,NULL,NULL,NULL),(103,567000,NULL,NULL,NULL,NULL),(103,568000,NULL,NULL,NULL,NULL),(103,569000,NULL,NULL,NULL,NULL),(103,570000,NULL,NULL,NULL,NULL),(105,578000,NULL,'fmicvd',NULL,NULL),(105,579000,NULL,'_tflkc',NULL,NULL),(105,580000,NULL,'xhovz',NULL,NULL),(105,581000,NULL,'n5bak',NULL,NULL),(105,582000,NULL,'gszus',NULL,NULL),(105,583000,NULL,'ewvydd',NULL,NULL),(105,584000,NULL,'fbzr0d',NULL,NULL),(107,590000,NULL,'8kgdf',NULL,NULL),(107,591000,NULL,'28v4bc',NULL,NULL),(107,592000,NULL,'evujpb',NULL,NULL),(107,593000,NULL,'8nkbzd',NULL,NULL),(107,594000,NULL,NULL,NULL,NULL),(109,599000,NULL,'1zswm',NULL,NULL),(109,600000,NULL,'gxlzrc',NULL,NULL),(109,601000,NULL,'xmedjc',NULL,NULL),(110,602000,NULL,'jwym6',NULL,NULL),(110,603000,NULL,NULL,NULL,NULL),(110,604000,NULL,'pcckxd',NULL,NULL),(111,605000,NULL,'lhvvp',NULL,NULL),(111,606000,NULL,'5eyidd',NULL,NULL),(111,607000,NULL,'l8azic',NULL,NULL),(111,608000,NULL,'_lmxx',NULL,NULL),(112,609000,NULL,'cstovb',NULL,NULL),(112,610000,NULL,'9bcdjc',NULL,NULL),(112,611000,NULL,'7zofhc',NULL,NULL),(112,612000,NULL,'pe2a3',NULL,NULL),(112,613000,NULL,'xtoet',NULL,NULL),(112,614000,NULL,'unvnj',NULL,NULL),(112,615000,NULL,'fj4v1b',NULL,NULL);
CREATE TABLE `t_dnmxh` (
  `wkey` int(11) DEFAULT NULL,
  `pkey` int(11) NOT NULL,
  `c_xhsndb` double DEFAULT NULL,
  PRIMARY KEY (`pkey`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `t_dnmxh` VALUES (104,571000,NULL),(104,572000,44.37),(104,573000,59.91),(104,574000,91.5),(104,575000,9.53),(104,576000,92.4),(104,577000,47.96),(106,585000,NULL),(106,586000,NULL),(106,587000,NULL),(106,588000,NULL),(106,589000,NULL),(108,595000,13.35),(108,596000,13.51),(108,597000,47.51),(108,598000,NULL),(113,616000,24.73),(113,617000,NULL),(113,618000,92.6),(113,619000,NULL),(113,620000,91.65),(113,621000,100.46),(113,622000,31.3),(113,623000,63.81);
WITH cte_0 AS (select distinct ref_0.wkey as c0, ref_0.pkey as c1, ref_0.c_xhsndb as c2 from t_dnmxh as ref_0 where (1 <= ( select ref_1.pkey not in ( select ref_5.wkey as c0 from t_dnmxh as ref_5 where (ref_5.wkey < ( select ref_6.pkey as c0 from t_cqmg3b as ref_6 where 88 between 96 and 76)) ) as c0 from (t_cqmg3b as ref_1 left outer join t_dnmxh as ref_2 on (ref_1.wkey = ref_2.wkey )) where ref_0.c_xhsndb is NULL union select 33 <= 91 as c0 from t_cqmg3b as ref_8 ))), cte_1 AS (select ref_9.wkey as c0, ref_9.pkey as c1, ref_9.c_anpf_c as c2, ref_9.c_b_fp_c as c3, ref_9.c_ndccfb as c4, ref_9.c_8rswc as c5 from t_cqmg3b as ref_9) select count(1) from cte_0 as ref_10 where case when 56 < 50 then case when 100 in ( select distinct ref_11.c4 as c0 from cte_1 as ref_11 where (ref_11.c4 > ( select ref_13.pkey as c0 from t_dnmxh as ref_13 where (ref_13.wkey > ( select distinct ref_11.c1 as c0 from cte_0 as ref_14)) )) or (1 = 1)) then null else null end else '7mxv6' end not like 'ki4%vc';
#case
with cte1 as (select 1), cte2 as (select 2) select * from cte1 union (with cte2 as (select 3) select * from cte2 union all select * from cte2) order by 1;
explain with cte1 as (select 1), cte2 as (select 2) select * from cte1 union (with cte2 as (select 3) select * from cte2 union all select * from cte2);