ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
DateHow to reproduceBehaviorFixed date
2
5.06with 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 panic5.06
3
5.06with 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_1unknow column5.06
4
5.06with 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_256panic5.06
5
5.06create 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;		WA5.06
6
5.06create 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;		index out of range5.06
7
5.06with 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;Column 'col_2' in field list is ambiguous5.06
8
5.06with recursive cte (c1) as (select 1), cte1 (c2) as (select 1 union select c1 + 1 from cte, cte1) select * from cte, cte1;goroutine stack exceeds 1000000000-byte limit5.06
9
5.06with 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 ;WA5.06
10
5.06with recursive cte1 (c1, c2) as (select 1, '1' union select concat(c1, 1), c2 + 1 from cte1 where c1 < 100) select * from cte1;index out of range5.06
11
5.06with 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_85.06
12
5.06with recursive qn as (select 1 from dual union all select 1 from dual) select * from qn;index out of range5.06
13
5.06with 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;syntax error
14
5.06with 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;index out of range5.06
15
5.06drop table 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;		WA5.06
16
5.07with 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 < 5index out of range5.07
17
5.07with 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_2345index out of range5.08
18
5.07with 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_21unkonw column
19
5.07with cte1 (c1) as (select 1) select * from cte1 as b, cte1 as a;column is ambiguous5.07
20
5.07WITH RECURSIVE qn AS
(
select 1
union all
select 3, 0 from qn
)
select * from qn;		index out of range5.07
21
5.07with recursive cte1 as (select 1 union all (select 1 from cte1 limit 10)) select * from cte1;

WA
5.08
22
5.07with recursive cte1 as (select 1 union all select 1 from cte1 limit 10) select * from cte1;WA
23
5.07with recursive qn as (select 123 as a union all select null from qn where a is not null) select * from qn;WA
Null related.
5.08
24
5.07WITH RECURSIVE qn AS
(
select 1,0 as col from t1
union distinct
select 3, 0*(@c:=@c+1) from qn where @c<1
union all
select 3, 0*(@d:=@d+1) from qn where @d<1
)
select * from qn;

This version of MySQL doesn't yet support 'recursive query blocks with UNION DISTINCT then UNION ALL, in recursive Common Table Expression'		MySQL: gives error.

TiDB runs normally.		Not fix
25
5.07with recursive q (b) as (select 1, 1 union all select 1, 1 from q) select b from q;err msg not same.5.08
26
5.07drop 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
);		WA5.11
27
5.07drop 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;		test.qn2' doesn't exist5.10
28
5.07select (with qn as (select "with") select * from qn) as scal_subq
from dual;		Table 'test.qn' doesn't exist5.10
29
5.07drop 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;		results are different with MySQL.5.10
30
5.07drop 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;		MySQL will give an error.
31
5.10drop 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
);		WA5.11
32
5.12WITH revenue0(supplier_no , total_revenue) AS (

SELECT l_suppkey, SUM(l_extendedprice * (1 - l_discount))

FROM lineitem

WHERE l_shipdate >= '1996-01-01'

AND l_shipdate < DATE_ADD('2010-01-01', INTERVAL '90' DAY)

GROUP BY l_suppkey )

SELECT s_suppkey, s_name, s_address, s_phone, total_revenue

FROM supplier, revenue0

WHERE s_suppkey = supplier_no

AND total_revenue = (SELECT MAX(total_revenue) FROM revenue0)

ORDER BY s_suppkey;

explain anazlye, count is wrong.
33
5.12column pruning is not work for CTE5.12
34
5.14hash join with two CTE return warning.Not fix
35
5.14CREATE 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;		TiDB return error5.14
36
5.14drop table if exists t1;
create table t1(c1 bigint unsigned);
insert into t1 values(0);
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;		dead lock.
defer resTbl.Unlock()		5.19
37
5.17Slow log return incorrect planSlow log return incorrect plan5.18
38
5.19drop table if exists t1;
create table t1(a int);
insert into t1 values(2),(3);
with t1 as (select 36 as col from t1 where a=3) select * from t1;
insert into t1 with t1 as (select 36 as col from t1) select * from t1;		DML related.
invalid memory address.		5.31
39
5.20with recursive cte(a,b) as (select 1, concat('a', 1) union select a+1, concat(b, 1) from cte where a < 5) select * from cte;waNot fix
40
5.27drop table if exists t1;
create table t1(c1 int);
with cte1(c1) as (select 1) update t1 set c1 = 2 where c1 in (select c1 from cte1); 		TiDB return error5.31
41
5.28drop table if exists folks;
create table folks(id int, name char(32), dob date, father int, mother int);
insert into folks values
(100, 'Me', '2000-01-01', 20, 30),
(20, 'Dad', '1970-02-02', 10, 9),
(30, 'Mom', '1975-03-03', 8, 7),
(10, 'Grandpa Bill', '1940-04-05', null, null),
(9, 'Grandma Ann', '1941-10-15', null, null),
(25, 'Uncle Jim', '1968-11-18', 8, 7),
(98, 'Sister Amy', '2001-06-20', 20, 30),
(7, 'Grandma Sally', '1943-08-23', null, 6),
(8, 'Grandpa Ben', '1940-10-21', null, null),
(6, 'Grandgrandma Martha', '1923-05-17', null, null),
(67, 'Cousin Eddie', '1992-02-28', 25, 27),
(27, 'Auntie Melinda', '1971-03-29', null, null);
with recursive ancestors(id,name,dob)
as
(
with
father(child_id,id,name,dob)
as
(
select folks.id, f.id, f.name, f.dob
from folks, folks f
where folks.father=f.id

),
mother(child_id,id,name,dob)
as
(
select folks.id, m.id, m.name, m.dob
from folks, folks m
where folks.mother=m.id

)
select folks.id, folks.name, folks.dob
from folks
where name='Me'
union
select f.id, f.name, f.dob
from ancestors a, father f
where f.child_id=a.id
union
select m.id, m.name, m.dob
from ancestors a, mother m
where m.child_id=a.id

)
select ancestors.name, ancestors.dob from ancestors;		TiDB: ERROR 1054 (42S22): Unknown column 'ancestors.name' in 'field list'

MySQL: OK
42
5.29drop table if exists t1;
create table t1 (a int);
insert into t1 values (2), (1), (4), (3);
explain with recursive cte as
( select * from t1 union select s1.* from t1 as s1, cte where s1.i1 = cte.i2 )
select * from t1 as t;		TiDB: Unknown column xxx
MySQL: OK
Won't fix
43
6.4https://github.com/pingcap/tidb/issues/25174data race6.4
44
6.8drop table if exists t1;
create table t1(c1 int primary key);
insert into t1 values(1), (2), (3);
create view vv as with recursive cte1(c1) as (select c1 from t1 where c1 = 2 union select c1 + 1 c1 from cte1 where c1 < 3) select * from cte1;
desc vv;
https://github.com/pingcap/tidb/issues/25235		TiDB: c1 is primay key also nullable.
45
6.8with recursive cte1(c1) as (select 'a' union select c1 + 1 c1 from cte1) select * from cte1;TiDB doesn't return error in strict mode.
MySQL does.

6.8Won't fix.
46
6.8CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `b` (`b`)
)；
create SESSION binding for with cte as (select b from t) select * from cte using with cte as (select b from t use index(b)) select * from cte;		Error
47
6.8drop table if exists t;
create table t(a int, b int);
insert into t values (1, 1),(2, 2),(3, 3),(1, 1),(2, 2),(3, 3);
with recursive cte(a) as (select a from t union all select a + 1 from cte limit 10) select * from cte;		WA
master 已合并
5.1 暂未合并		6.9
48
6.16drop table if exists t1, t2;
create table t1(c1 int);
insert into t1 values(1), (2), (1), (2);
create table t2(c1 int primary key);
insert into t2 values(1), (2), (3);
with recursive cte1(c1) as (select c1 from t1 union select c1 + 1 c1 from t1 where c1 < 3) select /*+ MERGE_JOIN(dt1, dt2) */ * from cte1 dt1 left join t1 dt2 on dt1.c1 = dt2.c1 order by dt1.c1, dt2.c1;		TiDB: ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100