1 2--echo # 3--echo # check errors 4--echo # 5 6--error ER_DUP_FIELDNAME 7WITH RECURSIVE cte AS ( 8 SELECT 1 AS a UNION ALL 9 SELECT NULL FROM cte WHERE a IS NOT NULL) 10 CYCLE a, a RESTRICT 11SELECT * FROM cte; 12 13--error ER_BAD_FIELD_ERROR 14WITH RECURSIVE cte AS ( 15 SELECT 1 AS a UNION ALL 16 SELECT NULL FROM cte WHERE a IS NOT NULL) 17 CYCLE b RESTRICT 18SELECT * FROM cte; 19 20--error ER_PARSE_ERROR 21WITH cte AS ( 22 SELECT 1 AS a UNION ALL 23 SELECT NULL FROM cte WHERE a IS NOT NULL) 24 CYCLE b RESTRICT 25SELECT * FROM cte; 26 27 28--echo # 29--echo # A degenerate case 30--echo # 31 32WITH RECURSIVE cte AS ( 33 SELECT 1 AS a, 2 as b) 34 CYCLE b RESTRICT 35SELECT * FROM cte; 36 37 38--echo # 39--echo # A simple case 40--echo # 41 42WITH RECURSIVE cte AS ( 43 SELECT 1 AS a, 2 as b UNION ALL 44 SELECT 2, 2 FROM cte WHERE a IS NOT NULL) 45 CYCLE b RESTRICT 46SELECT * FROM cte; 47 48 49--echo # 50--echo # MDEV-20632 case (with fixed syntax) 51--echo # 52 53create table t1 (from_ int, to_ int); 54insert into t1 values (1,2), (1,100), (2,3), (3,4), (4,1); 55 56WITH RECURSIVE cte (depth, from_, to_) as ( 57 SELECT 0,1,1 58 UNION 59 SELECT depth+1, t1.from_, t1.to_ 60 FROM t1, cte WHERE t1.from_ = cte.to_ 61) CYCLE from_, to_ RESTRICT 62select * from cte; 63 64create view v1 as WITH RECURSIVE cte (depth, from_, to_) as ( 65 SELECT 0,1,1 66 UNION 67 SELECT depth+1, t1.from_, t1.to_ 68 FROM t1, cte WHERE t1.from_ = cte.to_ 69) CYCLE from_, to_ RESTRICT 70select * from cte; 71 72show create view v1; 73 74select * from v1; 75 76delete from t1; 77 78insert into t1 values (1,2), (1,NULL), (NULL,NULL), (NULL, 1); 79 80select * from v1; 81 82drop view v1; 83 84drop table t1; 85 86 87--echo # 88--echo # A simple blob case 89--echo # 90 91create table t1 (a int, b text); 92insert into t1 values (1, "a"); 93 94WITH RECURSIVE cte AS ( 95 SELECT a, b from t1 UNION ALL 96 SELECT a, b FROM cte WHERE a IS NOT NULL) 97 CYCLE b RESTRICT 98SELECT * FROM cte; 99 100drop table t1; 101 102--echo # 103--echo # check bit types 104--echo # 105 106create table t1 (from_ bit(3), to_ bit(3)); 107insert into t1 values (1,2), (1,7), (2,3), (3,4), (4,1); 108 109WITH RECURSIVE cte (depth, from_, to_) as ( 110 SELECT 0,1,1 111 UNION 112 SELECT depth+1, t1.from_, t1.to_ 113 FROM t1, cte WHERE t1.from_ = cte.to_ 114) CYCLE from_, to_ RESTRICT 115select * from cte; 116drop table t1; 117 118--echo # 119--echo # check bit types with BLOBs (TEXT) 120--echo # 121 122create table t1 (from_ bit(3), to_ bit(3), load_ text); 123insert into t1 values (1,2,"A"), (1,7,"A"), (2,3,"A"), (3,4,"A"), (4,1,"A"); 124 125WITH RECURSIVE cte (depth, from_, to_, load_) as ( 126 SELECT 0,1,1,"A" 127 UNION 128 SELECT depth+1, t1.from_, t1.to_, t1.load_ 129 FROM t1, cte WHERE t1.from_ = cte.to_ 130) CYCLE from_, to_, load_ RESTRICT 131select * from cte; 132insert into t1 values (4,1,"B"); 133WITH RECURSIVE cte (depth, from_, to_, load_) as ( 134 SELECT 0,1,1,"A" 135 UNION 136 SELECT depth+1, t1.from_, t1.to_, t1.load_ 137 FROM t1, cte WHERE t1.from_ = cte.to_ 138) CYCLE from_, to_, load_ RESTRICT 139select * from cte; 140 141drop table t1; 142 143 144