github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/recursive_cte/recursive_cte1.result (about) 1 create table employees_mgr(id int primary key not null,name varchar(25)); 2 insert into employees_mgr values(333,'ami'),(198,'lucky'),(29,'jack'),(692,'sammi'); 3 CREATE TABLE emp(id INT PRIMARY KEY NOT NULL,name VARCHAR(100) NOT NULL,manager_id INT NULL,INDEX (manager_id),FOREIGN KEY (manager_id) REFERENCES employees_mgr(id)); 4 INSERT INTO emp VALUES(333, "总经理", NULL), (198, "副总1", 333), (692, "副总2", 333),(29, "主任1", 198),(4610, "职员1", 29),(72, "职员2", 29),(123, "主任2", 692); 5 create table product (id int primary key,p_id int,p_name varchar(25),price decimal(10,3)); 6 insert into product values (3,2,"bed",3560.98),(2,null,"chair",1599.00),(4,1,"desk",2999.99),(5,3,"door",8123.09),(6,3,"mirrors",698.00),(7,4,"tv",5678); 7 with non_cte_1 as(select manager_id,id,name from emp order by manager_id ) select * from non_cte_1; 8 manager_id id name 9 null 333 总经理 10 29 72 职员2 11 29 4610 职员1 12 198 29 主任1 13 333 198 副总1 14 333 692 副总2 15 692 123 主任2 16 with non_cte_2 as(select a.manager_id,b.name as manger_name,a.id,a.name as job_name from emp a join employees_mgr b on a.manager_id = b.id order by manager_id ) select manager_id,count(id) from non_cte_2 group by manager_id having count(id)>1; 17 manager_id count(id) 18 29 2 19 333 2 20 with non_cte_3(manager_id,manager_name,employee_id,employee_name)as (select a.manager_id,b.name as manger_name,a.id,a.name as job_name from emp a join employees_mgr b on a.manager_id = b.id order by manager_id ) select * from non_cte_3 order by employee_id; 21 manager_id manager_name employee_id employee_name 22 198 lucky 29 主任1 23 29 jack 72 职员2 24 692 sammi 123 主任2 25 333 ami 198 副总1 26 333 ami 692 副总2 27 29 jack 4610 职员1 28 with non_cte_4 as (select count(id) as emp_num,manager_id from emp group by manager_id) select manager_id from non_cte_4 where emp_num>1; 29 manager_id 30 29 31 333 32 with non_cte_5(manager_id,job_name,employee_id)as (select a.manager_id,name,id from emp a where exists (select id from employees_mgr b where a.manager_id=b.id))select * from non_cte_5; 33 manager_id job_name employee_id 34 29 职员2 72 35 29 职员1 4610 36 198 主任1 29 37 333 副总1 198 38 333 副总2 692 39 692 主任2 123 40 with non_cte_6(manager_id,job_name,employee_id) as (select a.manager_id,name,id from emp a where exists (select id from employees_mgr b where a.manager_id=b.id))select manager_id,job_name,count(employee_id) as emp_num from non_cte_6 group by manager_id,job_name order by manager_id,job_name; 41 manager_id job_name emp_num 42 29 职员1 1 43 29 职员2 1 44 198 主任1 1 45 333 副总1 1 46 333 副总2 1 47 692 主任2 1 48 with non_cte_7(manager_id,emp_num)as (select manager_id ,count(id) from emp where manager_id is not null group by manager_id) select avg(emp_num) as "average emp per manager" from non_cte_7; 49 average emp per manager 50 1.5000 51 with non_cte_10(id, productID,js,price,old_price) AS (select p.id,p.p_id,'20' as js,p.price+3.65,ceil(p.price) from product p where p.p_id < 100)select id, productID,js,price,old_price from cte_ab_10 order by old_price; 52 SQL parser error: table "cte_ab_10" does not exist 53 truncate table non_cte_6; 54 no such table recursive_cte1.non_cte_6 55 drop table non_cte_7; 56 no such table recursive_cte1.non_cte_7 57 with non_cte_8(manager_id,name) as 58 (select a.manager_id, 59 a.name as job_name 60 from emp a), non_cte_9(id,name) as 61 (select m.id, 62 m.name 63 from employees_mgr m 64 where m.name != "lucky") 65 select a.manager_id,a.name 66 from non_cte_8 a 67 join non_cte_9 b 68 on a.manager_id = b.id 69 order by a.manager_id; 70 manager_id name 71 29 职员2 72 29 职员1 73 333 副总1 74 333 副总2 75 692 主任2 76 with non_cte_8(manager_id,name) as 77 (select a.manager_id, 78 a.name as job_name 79 from emp a), non_cte_9(id,name) as 80 (select m.id, 81 m.name 82 from employees_mgr m 83 where m.name != "lucky"), non_cte_11(id,p_name) as 84 ( 85 select p.id,p.p_name from product p 86 ) 87 select ncte.manager_id,ncte.name from (select a.manager_id,a.name 88 from non_cte_8 a 89 join non_cte_9 b 90 on a.manager_id = b.id) as ncte 91 union all select c.id,c.p_name from non_cte_11 c; 92 manager_id name 93 29 职员2 94 29 职员1 95 333 副总1 96 333 副总2 97 692 主任2 98 2 chair 99 3 bed 100 4 desk 101 5 door 102 6 mirrors 103 7 tv 104 with date as(select manager_id,id,name from emp order by manager_id )select * from date; 105 manager_id id name 106 null 333 总经理 107 29 72 职员2 108 29 4610 职员1 109 198 29 主任1 110 333 198 副总1 111 333 692 副总2 112 692 123 主任2 113 with 111 as(select manager_id,id,name from emp order by manager_id )select * from 111; 114 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 8 near " 111 as(select manager_id,id,name from emp order by manager_id )select * from 111;"; 115 prepare s1 from 'with non_cte_1 as(select manager_id,id,name from emp order by manager_id ) select manager_id,id,name from non_cte_1 where id in (29,72);'; 116 execute s1; 117 manager_id id name 118 29 72 职员2 119 198 29 主任1 120 CREATE TABLE MyEmployees 121 ( 122 EmployeeID SMALLINT PRIMARY KEY NOT NULL, 123 FirstName VARCHAR(30) NOT NULL, 124 LastName VARCHAR(40) NOT NULL, 125 Title VARCHAR(50) NOT NULL, 126 DeptID SMALLINT NOT NULL, 127 ManagerID SMALLINT NULL 128 ); 129 INSERT INTO MyEmployees VALUES 130 (1, 'Ken', 'Sánchez', 'Chief Executive Officer',16, NULL) 131 ,(273, 'Brian', 'Welcker', 'Vice President of Sales', 3, 1) 132 ,(274, 'Stephen', 'Jiang', 'North American Sales Manager', 3, 273) 133 ,(275, 'Michael', 'Blythe', 'Sales Representative', 3, 274) 134 ,(276, 'Linda', 'Mitchell', 'Sales Representative', 3, 274) 135 ,(285, 'Syed', 'Abbas', 'Pacific Sales Manager', 3, 273) 136 ,(286, 'Lynn', 'Tsoflias', 'Sales Representative', 3, 285) 137 ,(16, 'David', 'Bradley', 'Marketing Manager', 4, 273) 138 ,(23, 'Mary', 'Gibson', 'Marketing Specialist', 4, 16); 139 WITH RECURSIVE DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 140 ( 141 SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel 142 FROM MyEmployees 143 WHERE ManagerID IS NULL 144 UNION ALL 145 SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 146 FROM MyEmployees AS e 147 INNER JOIN DirectReports AS d 148 ON e.ManagerID = d.EmployeeID 149 ) 150 SELECT ManagerID, EmployeeID, Title, EmployeeLevel 151 FROM DirectReports 152 ORDER BY ManagerID; 153 ManagerID EmployeeID Title EmployeeLevel 154 null 1 Chief Executive Officer 0 155 1 273 Vice President of Sales 1 156 16 23 Marketing Specialist 3 157 273 16 Marketing Manager 2 158 273 274 North American Sales Manager 2 159 273 285 Pacific Sales Manager 2 160 274 275 Sales Representative 3 161 274 276 Sales Representative 3 162 285 286 Sales Representative 3 163 WITH RECURSIVE DirectReports(Name, Title, EmployeeID, EmployeeLevel) 164 AS (SELECT concat(e.FirstName," ",e.LastName) as name, 165 e.Title, 166 e.EmployeeID, 167 1 as EmployeeLevel 168 FROM MyEmployees AS e 169 WHERE e.ManagerID IS NULL 170 UNION ALL 171 SELECT concat(e.FirstName," ",e.LastName) as name, 172 e.Title, 173 e.EmployeeID, 174 EmployeeLevel + 1 175 FROM MyEmployees AS e 176 JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID 177 ) 178 SELECT EmployeeID, Name, Title, EmployeeLevel 179 FROM DirectReports order by EmployeeID; 180 EmployeeID Name Title EmployeeLevel 181 1 Ken Sánchez Chief Executive Officer 1 182 16 David Bradley Marketing Manager 3 183 23 Mary Gibson Marketing Specialist 4 184 273 Brian Welcker Vice President of Sales 2 185 274 Stephen Jiang North American Sales Manager 3 186 275 Michael Blythe Sales Representative 4 187 276 Linda Mitchell Sales Representative 4 188 285 Syed Abbas Pacific Sales Manager 3 189 286 Lynn Tsoflias Sales Representative 4 190 with recursive cte_ab_11(id, productID,price) AS 191 (select p.id, 192 p.p_id, 193 p.price 194 from product p 195 where p.p_id is null 196 union all 197 select pr.id, 198 pr.p_id, 199 pr.price*12.01 200 from product pr 201 join cte_ab_11 c 202 on pr.id = c.productID 203 group by c.id, c.productID ) 204 select * 205 from cte_ab_11; 206 SQL parser error: not support group by in recursive cte: 'group by c.id, c.productid' 207 WITH RECURSIVE DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 208 ( 209 SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel 210 FROM MyEmployees 211 WHERE ManagerID IS NULL 212 UNION ALL 213 SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 214 FROM MyEmployees AS e 215 INNER JOIN DirectReports AS d 216 ON e.ManagerID = d.EmployeeID 217 ), 218 emp_cte as ( 219 select id,manager_id,name from emp where manager_id is null 220 union all 221 select ec.id,ec.manager_id,ec.name from emp_cte ec join emp e on ec.manager_id = e.id 222 ) 223 SELECT ManagerID, EmployeeID, Title 224 FROM DirectReports union all select id,manager_id,name from emp_cte ORDER BY ManagerID; 225 ManagerID EmployeeID Title 226 null 1 Chief Executive Officer 227 1 273 Vice President of Sales 228 16 23 Marketing Specialist 229 273 16 Marketing Manager 230 273 274 North American Sales Manager 231 273 285 Pacific Sales Manager 232 274 275 Sales Representative 233 274 276 Sales Representative 234 285 286 Sales Representative 235 333 null 总经理 236 prepare stmt from 'with recursive cte_ab_8(id,manager_id,name) as(select p.id,p.manager_id,p.name from emp p where manager_id is null union all select p.id,p.manager_id,p.name from emp p join cte_ab_8 c on p.manager_id= c.id) select * from cte_ab_8'; 237 execute stmt; 238 id manager_id name 239 333 null 总经理 240 198 333 副总1 241 692 333 副总2 242 29 198 主任1 243 123 692 主任2 244 72 29 职员2 245 4610 29 职员1 246 create table cte_insert_table (c1 int,c2 int ,c3 varchar(50),c4 int); 247 insert into cte_insert_table WITH RECURSIVE DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 248 ( 249 SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel 250 FROM MyEmployees 251 WHERE ManagerID IS NULL 252 UNION ALL 253 SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 254 FROM MyEmployees AS e 255 INNER JOIN DirectReports AS d 256 ON e.ManagerID = d.EmployeeID 257 ) 258 SELECT ManagerID, EmployeeID, Title, EmployeeLevel 259 FROM DirectReports 260 ORDER BY ManagerID; 261 select * from cte_insert_table; 262 c1 c2 c3 c4 263 null 1 Chief Executive Officer 0 264 1 273 Vice President of Sales 1 265 16 23 Marketing Specialist 3 266 273 16 Marketing Manager 2 267 273 274 North American Sales Manager 2 268 273 285 Pacific Sales Manager 2 269 274 275 Sales Representative 3 270 274 276 Sales Representative 3 271 285 286 Sales Representative 3 272 update cte_insert_table set c3= (WITH RECURSIVE DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 273 ( 274 SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel 275 FROM MyEmployees 276 WHERE ManagerID IS NULL 277 UNION ALL 278 SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 279 FROM MyEmployees AS e 280 INNER JOIN DirectReports AS d 281 ON e.ManagerID = d.EmployeeID 282 ) 283 select title from DirectReports where ManagerID=16) where c2=274; 284 select * from cte_insert_table; 285 c1 c2 c3 c4 286 null 1 Chief Executive Officer 0 287 1 273 Vice President of Sales 1 288 16 23 Marketing Specialist 3 289 273 16 Marketing Manager 2 290 273 274 Marketing Specialist 2 291 273 285 Pacific Sales Manager 2 292 274 275 Sales Representative 3 293 274 276 Sales Representative 3 294 285 286 Sales Representative 3 295 delete from cte_insert_table where c3 = (WITH RECURSIVE DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 296 ( 297 SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel 298 FROM MyEmployees 299 WHERE ManagerID IS NULL 300 UNION ALL 301 SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 302 FROM MyEmployees AS e 303 INNER JOIN DirectReports AS d 304 ON e.ManagerID = d.EmployeeID 305 ) 306 select Title from DirectReports where ManagerID=16); 307 select * from cte_insert_table; 308 c1 c2 c3 c4 309 null 1 Chief Executive Officer 0 310 1 273 Vice President of Sales 1 311 273 16 Marketing Manager 2 312 273 285 Pacific Sales Manager 2 313 274 275 Sales Representative 3 314 274 276 Sales Representative 3 315 285 286 Sales Representative 3 316 truncate table cte_insert_table; 317 begin; 318 insert into cte_insert_table WITH RECURSIVE DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 319 ( 320 SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel 321 FROM MyEmployees 322 WHERE ManagerID IS NULL 323 UNION ALL 324 SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 325 FROM MyEmployees AS e 326 INNER JOIN DirectReports AS d 327 ON e.ManagerID = d.EmployeeID 328 ) 329 SELECT ManagerID, EmployeeID, Title, EmployeeLevel 330 FROM DirectReports 331 ORDER BY ManagerID; 332 select * from cte_insert_table; 333 c1 c2 c3 c4 334 null 1 Chief Executive Officer 0 335 1 273 Vice President of Sales 1 336 16 23 Marketing Specialist 3 337 273 16 Marketing Manager 2 338 273 274 North American Sales Manager 2 339 273 285 Pacific Sales Manager 2 340 274 275 Sales Representative 3 341 274 276 Sales Representative 3 342 285 286 Sales Representative 3 343 rollback; 344 select * from cte_insert_table; 345 c1 c2 c3 c4 346 create view cte_view as( 347 WITH RECURSIVE DirectReports(Name, Title, EmployeeID, EmployeeLevel) 348 AS (SELECT concat(e.FirstName," ",e.LastName) as name, 349 e.Title, 350 e.EmployeeID, 351 1 as EmployeeLevel 352 FROM MyEmployees AS e 353 WHERE e.ManagerID IS NULL 354 UNION ALL 355 SELECT concat(e.FirstName," ",e.LastName) as name, 356 e.Title, 357 e.EmployeeID, 358 EmployeeLevel + 1 359 FROM MyEmployees AS e 360 JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID 361 ) 362 SELECT EmployeeID, Name, Title, EmployeeLevel 363 FROM DirectReports order by EmployeeID); 364 select * from cte_view order by EmployeeLevel; 365 EmployeeID Name Title EmployeeLevel 366 1 Ken Sánchez Chief Executive Officer 1 367 273 Brian Welcker Vice President of Sales 2 368 16 David Bradley Marketing Manager 3 369 274 Stephen Jiang North American Sales Manager 3 370 285 Syed Abbas Pacific Sales Manager 3 371 23 Mary Gibson Marketing Specialist 4 372 275 Michael Blythe Sales Representative 4 373 276 Linda Mitchell Sales Representative 4 374 286 Lynn Tsoflias Sales Representative 4 375 with recursive cte_ab_1(id, productID,price) as (SELECT p.id, p.p_id, p.price FROM product p where p.p_id is null UNION all SELECT p.id, p.p_id, avg(p.price) FROM product p JOIN cte_ab_1 c ON p.id = c.productID GROUP BY c.id, c.productID )SELECT * FROM cte_ab_1; 376 SQL parser error: not support group by in recursive cte: 'group by c.id, c.productid' 377 with recursive cte_ab_2(id, productID,price) as (SELECT p.id, p.p_id, p.price FROM product p where p.p_id is null UNION all SELECT p.id, p.p_id, sum(p.price) FROM product p JOIN cte_ab_2 c ON p.id = c.productID GROUP BY c.id, c.productID )SELECT * FROM cte_ab_2; 378 SQL parser error: not support group by in recursive cte: 'group by c.id, c.productid' 379 with recursive cte_ab_3(id, productID,price) as (SELECT p.id, p.p_id, p.price FROM product p where p.p_id is null UNION all SELECT p.id, p.p_id, count(p.price) FROM product p JOIN cte_ab_3 c ON p.id = c.productID GROUP BY c.id, c.productID )SELECT * FROM cte_ab_3; 380 SQL parser error: not support group by in recursive cte: 'group by c.id, c.productid' 381 with recursive cte_ab_4(id, productID,price) as (SELECT p.id, p.p_id, p.price FROM product p where p.p_id is null UNION all SELECT p.id, p.p_id, max(p.price) FROM product p JOIN cte_ab_4 c ON p.id = c.productID GROUP BY c.id, c.productID )SELECT * FROM cte_ab_4; 382 SQL parser error: not support group by in recursive cte: 'group by c.id, c.productid' 383 with recursive cte_ab_5(id, productID,price) as (SELECT p.id, p.p_id, p.price FROM product p where p.p_id is null UNION all SELECT p.id, p.p_id, min(p.price) FROM product p JOIN cte_ab_5 c ON p.id = c.productID GROUP BY c.id, c.productID )SELECT * FROM cte_ab_5; 384 SQL parser error: not support group by in recursive cte: 'group by c.id, c.productid' 385 with recursive cte_ab_6(id,manager_id,name) as(select p.id,p.manager_id,p.name from emp p where manager_id is null union all select p.id,p.manager_id,p.name from emp p join cte_ab_6 c on p.manager_id= c.id order by c.id) select * from cte_ab_6 order by id; 386 SQL parser error: not support ORDER BY in recursive cte 387 with recursive cte_ab_7(id,manager_id,name) as(select p.id,p.manager_id,p.name from emp p where manager_id is null union all select distinct p.id,p.manager_id,p.name from emp p join cte_ab_7 c on p.manager_id= c.id) select * from cte_ab_7; 388 SQL parser error: not support DISTINCT in recursive cte 389 with recursive cte_ab_8(id,manager_id,name,levels) as(select p.id,p.manager_id,p.name,0 as level from emp p where manager_id is null union all select p.id,p.manager_id,p.name,levels+1 from emp p join cte_ab_8 c on p.manager_id= c.id limit 5) select * from cte_ab_8; 390 id manager_id name levels 391 333 null 总经理 0 392 198 333 副总1 1 393 692 333 副总2 1 394 29 198 主任1 2 395 123 692 主任2 2 396 with recursive cte_ab_9(id,manager_id,name,levels) as(select p.id,p.manager_id,p.name,0 as level from emp p where manager_id is null union all select p.id,p.manager_id,p.name,levels+1 from emp p where p.manager_id in(select c.id from cte_ab_9 c)) select * from cte_ab_9; 397 SQL parser error: In recursive query block of Recursive Common Table Expression cte_ab_9, the recursive table must be referenced only once, and not in any subquery 398 with recursive cte_ab_10(id,manager_id,name,levels) as(select p.id,p.manager_id,p.name from emp p where manager_id is null union all select p.id,p.manager_id,p.name from emp p join cte_ab_10 c on p.manager_id= c.id ) select * from cte_ab_10 order by id; 399 SQL syntax error: table "cte_ab_10" has 3 columns available but 4 columns specified 400 with non_cte_8(manager_id,name) as(SELECT a.manager_id, a.id,a.name AS job_name FROM emp a), non_cte_9(id,name) as(SELECT m.id, m.name FROM employees_mgr m WHERE m.name != "lucky") select * FROM non_cte_8 a JOIN non_cte_9 b ON a.manager_id = b.id ORDER BY a.manager_id; 401 SQL syntax error: table "non_cte_8" has 3 columns available but 2 columns specified 402 WITH RECURSIVE DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 403 ( 404 SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel 405 FROM MyEmployees 406 WHERE ManagerID IS NULL 407 UNION ALL 408 SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 409 FROM MyEmployees AS e 410 INNER JOIN DirectReports AS d 411 ON e.ManagerID = d.EmployeeID 412 ) 413 update DirectReports set Title='manager assistant' where ManagerID=273; 414 internal error: column 'title' not found in table or the target table directreports of the UPDATE is not updatable