1create table t1 (a int, b varchar(32)); 2insert into t1 values 3(4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd'); 4insert into t1 values 5(3,'eee'), (7,'bb'), (1,'fff'), (4,'ggg'); 6with recursive 7t as 8( 9select * from t1 where t1.b >= 'c' 10 union 11select * from r 12), 13r as 14( 15select * from t 16union 17select t1.* from t1,r where r.a+1 = t1.a 18) 19select * from r; 20ERROR HY000: Unacceptable mutual recursion with anchored table 't' 21with recursive 22a1(a,b) as 23(select * from t1 where t1.a>3 24union 25select * from b1 where b1.a >3 26union 27select * from c1 where c1.a>3), 28b1(a,b) as 29(select * from a1 where a1.b > 'ccc' 30union 31select * from c1 where c1.b > 'ddd'), 32c1(a,b) as 33(select * from a1 where a1.a<6 and a1.b< 'zz' 34union 35select * from b1 where b1.b > 'auu') 36select * from c1; 37ERROR HY000: Unacceptable mutual recursion with anchored table 'a1' 38drop table t1; 39# WITH RECURSIVE vs just WITH 40create table t1 (a int); 41insert into t1 values 42(0), (1), (2), (3), (4); 43create table t2 (a int); 44insert into t2 values 45(1), (2), (3), (4), (5); 46# just WITH : s refers to t defined after s 47with 48s(a) as (select t.a + 10 from t), 49t(a) as (select t1.a from t1) 50select * from s; 51ERROR 42S02: Table 'test.t' doesn't exist 52# WITH RECURSIVE: s refers to t defined after s 53with recursive 54s(a) as (select t.a + 10 from t), 55t(a) as (select t1.a from t1) 56select * from s; 57a 5810 5911 6012 6113 6214 63# just WITH : defined t1 is non-recursive and uses base tables t1,t2 64with 65t1 as 66( 67select a from t2 where t2.a=3 68union 69select t2.a from t1,t2 where t1.a+1=t2.a 70) 71select * from t1; 72a 733 741 752 764 775 78explain 79with 80t1 as 81( 82select a from t2 where t2.a=3 83union 84select t2.a from t1,t2 where t1.a+1=t2.a 85) 86select * from t1; 87id select_type table type possible_keys key key_len ref rows Extra 881 PRIMARY <derived2> ALL NULL NULL NULL NULL 30 892 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where 903 UNION t1 ALL NULL NULL NULL NULL 5 913 UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 92NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 93#WITH RECURSIVE : defined t1 is recursive and uses only base table t2 94with recursive 95t1 as 96( 97select a from t2 where t2.a=3 98union 99select t2.a from t1,t2 where t1.a+1=t2.a 100) 101select * from t1; 102a 1033 1044 1055 106explain 107with recursive 108t1 as 109( 110select a from t2 where t2.a=3 111union 112select t2.a from t1,t2 where t1.a+1=t2.a 113) 114select * from t1; 115id select_type table type possible_keys key key_len ref rows Extra 1161 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 1172 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where 1183 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 5 1193 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 120NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 121# just WITH : types of t1 columns are determined by all parts of union 122create view v1 as 123with 124t1 as 125( 126select a from t2 where t2.a=3 127union 128select t2.a+1 from t1,t2 where t1.a=t2.a 129) 130select * from t1; 131show columns from v1; 132Field Type Null Key Default Extra 133a bigint(12) YES NULL 134# WITH RECURSIVE : types of t1 columns are determined by anchor parts 135create view v2 as 136with recursive 137t1 as 138( 139select a from t2 where t2.a=3 140union 141select t2.a+1 from t1,t2 where t1.a=t2.a 142) 143select * from t1; 144show columns from v2; 145Field Type Null Key Default Extra 146a int(11) YES NULL 147drop view v1,v2; 148drop table t1,t2; 149create table folks(id int, name char(32), dob date, father int, mother int); 150insert into folks values 151(100, 'Me', '2000-01-01', 20, 30), 152(20, 'Dad', '1970-02-02', 10, 9), 153(30, 'Mom', '1975-03-03', 8, 7), 154(10, 'Grandpa Bill', '1940-04-05', null, null), 155(9, 'Grandma Ann', '1941-10-15', null, null), 156(25, 'Uncle Jim', '1968-11-18', 8, 7), 157(98, 'Sister Amy', '2001-06-20', 20, 30), 158(7, 'Grandma Sally', '1943-08-23', null, 6), 159(8, 'Grandpa Ben', '1940-10-21', null, null), 160(6, 'Grandgrandma Martha', '1923-05-17', null, null), 161(67, 'Cousin Eddie', '1992-02-28', 25, 27), 162(27, 'Auntie Melinda', '1971-03-29', null, null); 163# simple recursion with one anchor and one recursive select 164# the anchor is the first select in the specification 165with recursive 166ancestors 167as 168( 169select * 170from folks 171where name = 'Me' and dob = '2000-01-01' 172 union 173select p.id, p.name, p.dob, p.father, p.mother 174from folks as p, ancestors AS a 175where p.id = a.father or p.id = a.mother 176) 177select * from ancestors; 178id name dob father mother 179100 Me 2000-01-01 20 30 18020 Dad 1970-02-02 10 9 18130 Mom 1975-03-03 8 7 18210 Grandpa Bill 1940-04-05 NULL NULL 1839 Grandma Ann 1941-10-15 NULL NULL 1847 Grandma Sally 1943-08-23 NULL 6 1858 Grandpa Ben 1940-10-21 NULL NULL 1866 Grandgrandma Martha 1923-05-17 NULL NULL 187# simple recursion with one anchor and one recursive select 188# the anchor is the last select in the specification 189with recursive 190ancestors 191as 192( 193select p.* 194from folks as p, ancestors AS a 195where p.id = a.father or p.id = a.mother 196union 197select * 198from folks 199where name = 'Me' and dob = '2000-01-01' 200) 201select * from ancestors; 202id name dob father mother 203100 Me 2000-01-01 20 30 20420 Dad 1970-02-02 10 9 20530 Mom 1975-03-03 8 7 20610 Grandpa Bill 1940-04-05 NULL NULL 2079 Grandma Ann 1941-10-15 NULL NULL 2087 Grandma Sally 1943-08-23 NULL 6 2098 Grandpa Ben 1940-10-21 NULL NULL 2106 Grandgrandma Martha 1923-05-17 NULL NULL 211# simple recursion with one anchor and one recursive select 212# the anchor is the first select in the specification 213with recursive 214ancestors 215as 216( 217select * 218from folks 219where name = 'Cousin Eddie' 220 union 221select p.* 222from folks as p, ancestors as a 223where p.id = a.father or p.id = a.mother 224) 225select * from ancestors; 226id name dob father mother 22767 Cousin Eddie 1992-02-28 25 27 22825 Uncle Jim 1968-11-18 8 7 22927 Auntie Melinda 1971-03-29 NULL NULL 2307 Grandma Sally 1943-08-23 NULL 6 2318 Grandpa Ben 1940-10-21 NULL NULL 2326 Grandgrandma Martha 1923-05-17 NULL NULL 233# simple recursion with or in anchor and or in recursive part 234with recursive 235ancestors 236as 237( 238select * 239from folks 240where name = 'Me' or name='Sister Amy' 241 union 242select p.* 243from folks as p, ancestors as a 244where p.id = a.father or p.id = a.mother 245) 246select * from ancestors; 247id name dob father mother 248100 Me 2000-01-01 20 30 24998 Sister Amy 2001-06-20 20 30 25020 Dad 1970-02-02 10 9 25130 Mom 1975-03-03 8 7 25210 Grandpa Bill 1940-04-05 NULL NULL 2539 Grandma Ann 1941-10-15 NULL NULL 2547 Grandma Sally 1943-08-23 NULL 6 2558 Grandpa Ben 1940-10-21 NULL NULL 2566 Grandgrandma Martha 1923-05-17 NULL NULL 257# two recursive definition, one uses another 258with recursive 259prev_gen 260as 261( 262select folks.* 263from folks, prev_gen 264where folks.id=prev_gen.father or folks.id=prev_gen.mother 265union 266select * 267from folks 268where name='Me' 269), 270ancestors 271as 272( 273select * 274from folks 275where name='Me' 276 union 277select * 278from ancestors 279union 280select * 281from prev_gen 282) 283select ancestors.name, ancestors.dob from ancestors; 284name dob 285Me 2000-01-01 286Dad 1970-02-02 287Mom 1975-03-03 288Grandpa Bill 1940-04-05 289Grandma Ann 1941-10-15 290Grandma Sally 1943-08-23 291Grandpa Ben 1940-10-21 292Grandgrandma Martha 1923-05-17 293# recursive definition with two attached non-recursive 294with recursive 295ancestors(id,name,dob) 296as 297( 298with 299father(child_id,id,name,dob) 300as 301( 302select folks.id, f.id, f.name, f.dob 303from folks, folks f 304where folks.father=f.id 305), 306mother(child_id,id,name,dob) 307as 308( 309select folks.id, m.id, m.name, m.dob 310from folks, folks m 311where folks.mother=m.id 312) 313select folks.id, folks.name, folks.dob 314from folks 315where name='Me' 316 union 317select f.id, f.name, f.dob 318from ancestors a, father f 319where f.child_id=a.id 320union 321select m.id, m.name, m.dob 322from ancestors a, mother m 323where m.child_id=a.id 324) 325select ancestors.name, ancestors.dob from ancestors; 326name dob 327Me 2000-01-01 328Dad 1970-02-02 329Mom 1975-03-03 330Grandpa Bill 1940-04-05 331Grandpa Ben 1940-10-21 332Grandma Ann 1941-10-15 333Grandma Sally 1943-08-23 334Grandgrandma Martha 1923-05-17 335# simple recursion with one anchor and one recursive select 336# the anchor is the first select in the specification 337with recursive 338descendants 339as 340( 341select * 342from folks 343where name = 'Grandpa Bill' 344 union 345select folks.* 346from folks, descendants as d 347where d.id=folks.father or d.id=folks.mother 348) 349select * from descendants; 350id name dob father mother 35110 Grandpa Bill 1940-04-05 NULL NULL 35220 Dad 1970-02-02 10 9 353100 Me 2000-01-01 20 30 35498 Sister Amy 2001-06-20 20 30 355# simple recursion with one anchor and one recursive select 356# the anchor is the first select in the specification 357with recursive 358descendants 359as 360( 361select * 362from folks 363where name = 'Grandma Sally' 364 union 365select folks.* 366from folks, descendants as d 367where d.id=folks.father or d.id=folks.mother 368) 369select * from descendants; 370id name dob father mother 3717 Grandma Sally 1943-08-23 NULL 6 37230 Mom 1975-03-03 8 7 37325 Uncle Jim 1968-11-18 8 7 374100 Me 2000-01-01 20 30 37598 Sister Amy 2001-06-20 20 30 37667 Cousin Eddie 1992-02-28 25 27 377# simple recursive table used three times in the main query 378with recursive 379ancestors 380as 381( 382select * 383from folks 384where name = 'Me' and dob = '2000-01-01' 385 union 386select p.* 387from folks as p, ancestors AS a 388where p.id = a.father OR p.id = a.mother 389) 390select * 391from ancestors t1, ancestors t2 392where exists (select * from ancestors a 393where a.father=t1.id AND a.mother=t2.id); 394id name dob father mother id name dob father mother 39520 Dad 1970-02-02 10 9 30 Mom 1975-03-03 8 7 39610 Grandpa Bill 1940-04-05 NULL NULL 9 Grandma Ann 1941-10-15 NULL NULL 3978 Grandpa Ben 1940-10-21 NULL NULL 7 Grandma Sally 1943-08-23 NULL 6 398# simple recursive table used three times in the main query 399with 400ancestor_couples(husband, h_dob, wife, w_dob) 401as 402( 403with recursive 404ancestors 405as 406( 407select * 408from folks 409where name = 'Me' 410 union 411select p.* 412from folks as p, ancestors AS a 413where p.id = a.father OR p.id = a.mother 414) 415select t1.name, t1.dob, t2.name, t2.dob 416from ancestors t1, ancestors t2 417where exists (select * from ancestors a 418where a.father=t1.id AND a.mother=t2.id) 419) 420select * from ancestor_couples; 421husband h_dob wife w_dob 422Dad 1970-02-02 Mom 1975-03-03 423Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 424Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 425# simple recursion with two selects in recursive part 426with recursive 427ancestors 428as 429( 430select * 431from folks 432where name = 'Me' 433 union 434select p.* 435from folks as p, ancestors as fa 436where p.id = fa.father 437union 438select p.* 439from folks as p, ancestors as ma 440where p.id = ma.mother 441) 442select * from ancestors; 443id name dob father mother 444100 Me 2000-01-01 20 30 44520 Dad 1970-02-02 10 9 44630 Mom 1975-03-03 8 7 44710 Grandpa Bill 1940-04-05 NULL NULL 4488 Grandpa Ben 1940-10-21 NULL NULL 4499 Grandma Ann 1941-10-15 NULL NULL 4507 Grandma Sally 1943-08-23 NULL 6 4516 Grandgrandma Martha 1923-05-17 NULL NULL 452# mutual recursion with renaming 453with recursive 454ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 455w_id, w_name, w_dob, w_father, w_mother) 456as 457( 458select h.*, w.* 459from folks h, folks w, coupled_ancestors a 460where a.father = h.id AND a.mother = w.id 461union 462select h.*, w.* 463from folks v, folks h, folks w 464where v.name = 'Me' and 465(v.father = h.id AND v.mother= w.id) 466), 467coupled_ancestors (id, name, dob, father, mother) 468as 469( 470select h_id, h_name, h_dob, h_father, h_mother 471from ancestor_couples 472union 473select w_id, w_name, w_dob, w_father, w_mother 474from ancestor_couples 475) 476select h_name, h_dob, w_name, w_dob 477from ancestor_couples; 478h_name h_dob w_name w_dob 479Dad 1970-02-02 Mom 1975-03-03 480Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 481Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 482# mutual recursion with union all 483with recursive 484ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 485w_id, w_name, w_dob, w_father, w_mother) 486as 487( 488select h.*, w.* 489from folks h, folks w, coupled_ancestors a 490where a.father = h.id AND a.mother = w.id 491union 492select h.*, w.* 493from folks v, folks h, folks w 494where v.name = 'Me' and 495(v.father = h.id AND v.mother= w.id) 496), 497coupled_ancestors (id, name, dob, father, mother) 498as 499( 500select h_id, h_name, h_dob, h_father, h_mother 501from ancestor_couples 502union all 503select w_id, w_name, w_dob, w_father, w_mother 504from ancestor_couples 505) 506select h_name, h_dob, w_name, w_dob 507from ancestor_couples; 508h_name h_dob w_name w_dob 509Dad 1970-02-02 Mom 1975-03-03 510Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 511Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 512# mutual recursion with renaming 513with recursive 514ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 515w_id, w_name, w_dob, w_father, w_mother) 516as 517( 518select h.*, w.* 519from folks h, folks w, coupled_ancestors a 520where a.father = h.id AND a.mother = w.id 521union 522select h.*, w.* 523from folks v, folks h, folks w 524where v.name = 'Me' and 525(v.father = h.id AND v.mother= w.id) 526), 527coupled_ancestors (id, name, dob, father, mother) 528as 529( 530select h_id, h_name, h_dob, h_father, h_mother 531from ancestor_couples 532union 533select w_id, w_name, w_dob, w_father, w_mother 534from ancestor_couples 535) 536select h_name, h_dob, w_name, w_dob 537from ancestor_couples; 538h_name h_dob w_name w_dob 539Dad 1970-02-02 Mom 1975-03-03 540Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 541Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 542# mutual recursion with union all 543with recursive 544ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 545w_id, w_name, w_dob, w_father, w_mother) 546as 547( 548select h.*, w.* 549from folks h, folks w, coupled_ancestors a 550where a.father = h.id AND a.mother = w.id 551), 552coupled_ancestors (id, name, dob, father, mother) 553as 554( 555select * 556from folks 557where name = 'Me' 558 union all 559select h_id, h_name, h_dob, h_father, h_mother 560from ancestor_couples 561union all 562select w_id, w_name, w_dob, w_father, w_mother 563from ancestor_couples 564) 565select h_name, h_dob, w_name, w_dob 566from ancestor_couples; 567h_name h_dob w_name w_dob 568Dad 1970-02-02 Mom 1975-03-03 569Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 570Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 571# mutual recursion with one select in the first definition 572with recursive 573ancestor_couple_ids(h_id, w_id) 574as 575( 576select a.father, a.mother 577from coupled_ancestors a 578where a.father is not null and a.mother is not null 579), 580coupled_ancestors (id, name, dob, father, mother) 581as 582( 583select * 584from folks 585where name = 'Me' 586 union all 587select p.* 588from folks p, ancestor_couple_ids fa 589where p.id = fa.h_id 590union all 591select p.* 592from folks p, ancestor_couple_ids ma 593where p.id = ma.w_id 594) 595select * 596from ancestor_couple_ids; 597h_id w_id 59820 30 59910 9 6008 7 601# join of a mutually recursive table with base tables 602with recursive 603ancestor_couple_ids(h_id, w_id) 604as 605( 606select a.father, a.mother 607from coupled_ancestors a 608where a.father is not null and a.mother is not null 609), 610coupled_ancestors (id, name, dob, father, mother) 611as 612( 613select * 614from folks 615where name = 'Me' 616 union all 617select p.* 618from folks p, ancestor_couple_ids fa 619where p.id = fa.h_id 620union all 621select p.* 622from folks p, ancestor_couple_ids ma 623where p.id = ma.w_id 624) 625select h.name, h.dob, w.name, w.dob 626from ancestor_couple_ids c, folks h, folks w 627where c.h_id = h.id and c.w_id= w.id; 628name dob name dob 629Dad 1970-02-02 Mom 1975-03-03 630Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 631Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 632# join of two mutually recursive tables 633with recursive 634ancestor_couple_ids(h_id, w_id) 635as 636( 637select a.father, a.mother 638from coupled_ancestors a 639where a.father is not null and a.mother is not null 640), 641coupled_ancestors (id, name, dob, father, mother) 642as 643( 644select * 645from folks 646where name = 'Me' 647 union all 648select p.* 649from folks p, ancestor_couple_ids fa 650where p.id = fa.h_id 651union all 652select p.* 653from folks p, ancestor_couple_ids ma 654where p.id = ma.w_id 655) 656select h.name, h.dob, w.name, w.dob 657from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w 658where c.h_id = h.id and c.w_id= w.id; 659name dob name dob 660Dad 1970-02-02 Mom 1975-03-03 661Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 662Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 663explain extended 664with recursive 665ancestor_couple_ids(h_id, w_id) 666as 667( 668select a.father, a.mother 669from coupled_ancestors a 670where a.father is not null and a.mother is not null 671), 672coupled_ancestors (id, name, dob, father, mother) 673as 674( 675select * 676from folks 677where name = 'Me' 678 union all 679select p.* 680from folks p, ancestor_couple_ids fa 681where p.id = fa.h_id 682union all 683select p.* 684from folks p, ancestor_couple_ids ma 685where p.id = ma.w_id 686) 687select h.name, h.dob, w.name, w.dob 688from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w 689where c.h_id = h.id and c.w_id= w.id; 690id select_type table type possible_keys key key_len ref rows filtered Extra 6911 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 6921 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00 6931 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00 6943 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 6954 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 6964 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 6975 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 6985 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 699NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL 7002 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where 701Warnings: 702Note 1003 with recursive ancestor_couple_ids(`h_id`,`w_id`) as (/* select#2 */ select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors(`id`,`name`,`dob`,`father`,`mother`) as (/* select#3 */ select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all /* select#4 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all /* select#5 */ select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)/* select#1 */ select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id` 703# simple mutual recursion 704with recursive 705ancestor_couple_ids(h_id, w_id) 706as 707( 708select a.father, a.mother 709from coupled_ancestors a 710), 711coupled_ancestors (id, name, dob, father, mother) 712as 713( 714select * 715from folks 716where name = 'Me' 717 union all 718select p.* 719from folks p, ancestor_couple_ids fa 720where p.id = fa.h_id 721union all 722select p.* 723from folks p, ancestor_couple_ids ma 724where p.id = ma.w_id 725) 726select * 727from ancestor_couple_ids; 728h_id w_id 72920 30 73010 9 7318 7 732NULL NULL 733NULL NULL 734NULL NULL 735NULL 6 736NULL NULL 737# join of two mutually recursive tables 738with recursive 739ancestor_couple_ids(h_id, w_id) 740as 741( 742select a.father, a.mother 743from coupled_ancestors a 744), 745coupled_ancestors (id, name, dob, father, mother) 746as 747( 748select * 749from folks 750where name = 'Me' 751 union all 752select p.* 753from folks p, ancestor_couple_ids fa 754where p.id = fa.h_id 755union all 756select p.* 757from folks p, ancestor_couple_ids ma 758where p.id = ma.w_id 759) 760select h.name, h.dob, w.name, w.dob 761from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w 762where c.h_id = h.id and c.w_id= w.id; 763name dob name dob 764Dad 1970-02-02 Mom 1975-03-03 765Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 766Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 767# execution of prepared query using a recursive table 768prepare stmt1 from " 769with recursive 770ancestors 771as 772( 773 select * 774 from folks 775 where name = 'Me' and dob = '2000-01-01' 776 union 777 select p.id, p.name, p.dob, p.father, p.mother 778 from folks as p, ancestors AS a 779 where p.id = a.father or p.id = a.mother 780) 781select * from ancestors; 782"; 783execute stmt1; 784id name dob father mother 785100 Me 2000-01-01 20 30 78620 Dad 1970-02-02 10 9 78730 Mom 1975-03-03 8 7 78810 Grandpa Bill 1940-04-05 NULL NULL 7899 Grandma Ann 1941-10-15 NULL NULL 7907 Grandma Sally 1943-08-23 NULL 6 7918 Grandpa Ben 1940-10-21 NULL NULL 7926 Grandgrandma Martha 1923-05-17 NULL NULL 793execute stmt1; 794id name dob father mother 795100 Me 2000-01-01 20 30 79620 Dad 1970-02-02 10 9 79730 Mom 1975-03-03 8 7 79810 Grandpa Bill 1940-04-05 NULL NULL 7999 Grandma Ann 1941-10-15 NULL NULL 8007 Grandma Sally 1943-08-23 NULL 6 8018 Grandpa Ben 1940-10-21 NULL NULL 8026 Grandgrandma Martha 1923-05-17 NULL NULL 803deallocate prepare stmt1; 804# view using a recursive table 805create view v1 as 806with recursive 807ancestors 808as 809( 810select * 811from folks 812where name = 'Me' and dob = '2000-01-01' 813 union 814select p.id, p.name, p.dob, p.father, p.mother 815from folks as p, ancestors AS a 816where p.id = a.father or p.id = a.mother 817) 818select * from ancestors; 819show create view v1; 820View Create View character_set_client collation_connection 821v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' and `test`.`folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci 822select * from v1; 823id name dob father mother 824100 Me 2000-01-01 20 30 82520 Dad 1970-02-02 10 9 82630 Mom 1975-03-03 8 7 82710 Grandpa Bill 1940-04-05 NULL NULL 8289 Grandma Ann 1941-10-15 NULL NULL 8297 Grandma Sally 1943-08-23 NULL 6 8308 Grandpa Ben 1940-10-21 NULL NULL 8316 Grandgrandma Martha 1923-05-17 NULL NULL 832create view v2 as 833with recursive 834ancestors 835as 836( 837select * 838from folks 839where name = 'Me' 840 union 841select p.* 842from folks as p, ancestors as fa 843where p.id = fa.father 844union 845select p.* 846from folks as p, ancestors as ma 847where p.id = ma.mother 848) 849select * from ancestors; 850show create view v2; 851View Create View character_set_client collation_connection 852v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci 853select * from v2; 854id name dob father mother 855100 Me 2000-01-01 20 30 85620 Dad 1970-02-02 10 9 85730 Mom 1975-03-03 8 7 85810 Grandpa Bill 1940-04-05 NULL NULL 8598 Grandpa Ben 1940-10-21 NULL NULL 8609 Grandma Ann 1941-10-15 NULL NULL 8617 Grandma Sally 1943-08-23 NULL 6 8626 Grandgrandma Martha 1923-05-17 NULL NULL 863drop view v1,v2; 864explain extended 865with recursive 866ancestors 867as 868( 869select * 870from folks 871where name = 'Me' and dob = '2000-01-01' 872 union 873select p.id, p.name, p.dob, p.father, p.mother 874from folks as p, ancestors AS a 875where p.id = a.father or p.id = a.mother 876) 877select * from ancestors; 878id select_type table type possible_keys key key_len ref rows filtered Extra 8791 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 8802 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where 8813 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 8823 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join) 883NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 884Warnings: 885Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' and `test`.`folks`.`dob` = DATE'2000-01-01' union /* select#3 */ select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestors` `a` where `a`.`father` = `p`.`id` or `a`.`mother` = `p`.`id`)/* select#1 */ select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` 886# recursive spec with two anchor selects and two recursive ones 887with recursive 888ancestor_ids (id) 889as 890( 891select father from folks where name = 'Me' 892 union 893select mother from folks where name = 'Me' 894 union 895select father from folks, ancestor_ids a where folks.id = a.id 896union 897select mother from folks, ancestor_ids a where folks.id = a.id 898), 899ancestors 900as 901( 902select p.* from folks as p, ancestor_ids as a 903where p.id = a.id 904) 905select * from ancestors; 906id name dob father mother 90720 Dad 1970-02-02 10 9 90830 Mom 1975-03-03 8 7 90910 Grandpa Bill 1940-04-05 NULL NULL 9109 Grandma Ann 1941-10-15 NULL NULL 9117 Grandma Sally 1943-08-23 NULL 6 9128 Grandpa Ben 1940-10-21 NULL NULL 9136 Grandgrandma Martha 1923-05-17 NULL NULL 914# recursive spec using union all 915with recursive 916ancestors 917as 918( 919select * 920from folks 921where name = 'Me' 922 union all 923select p.* 924from folks as p, ancestors as fa 925where p.id = fa.father 926union all 927select p.* 928from folks as p, ancestors as ma 929where p.id = ma.mother 930) 931select * from ancestors; 932id name dob father mother 933100 Me 2000-01-01 20 30 93420 Dad 1970-02-02 10 9 93530 Mom 1975-03-03 8 7 93610 Grandpa Bill 1940-04-05 NULL NULL 9378 Grandpa Ben 1940-10-21 NULL NULL 9389 Grandma Ann 1941-10-15 NULL NULL 9397 Grandma Sally 1943-08-23 NULL 6 9406 Grandgrandma Martha 1923-05-17 NULL NULL 941with recursive 942ancestor_ids (id, generation) 943as 944( 945select father, 1 from folks where name = 'Me' and father is not null 946union all 947select mother, 1 from folks where name = 'Me' and mother is not null 948union all 949select father, fa.generation+1 from folks, ancestor_ids fa 950where folks.id = fa.id and (father not in (select id from ancestor_ids)) 951union all 952select mother, ma.generation+1 from folks, ancestor_ids ma 953where folks.id = ma.id and (mother not in (select id from ancestor_ids)) 954) 955select generation, name from ancestor_ids a, folks 956where a.id = folks.id; 957ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'ancestor_ids' 958set standard_compliant_cte=0; 959with recursive 960ancestor_ids (id, generation) 961as 962( 963select father from folks where name = 'Me' and father is not null 964union all 965select mother from folks where name = 'Me' and mother is not null 966union all 967select father, fa.generation+1 from folks, ancestor_ids fa 968where folks.id = fa.id and (father not in (select id from ancestor_ids)) 969union all 970select mother, ma.generation+1 from folks, ancestor_ids ma 971where folks.id = ma.id and (mother not in (select id from ancestor_ids)) 972) 973select generation, name from ancestor_ids a, folks 974where a.id = folks.id; 975ERROR HY000: WITH column list and SELECT field list have different column counts 976with recursive 977ancestor_ids (id, generation) 978as 979( 980select father, 1 from folks where name = 'Me' and father is not null 981union all 982select mother, 1 from folks where name = 'Me' and mother is not null 983union all 984select father, fa.generation+1 from folks, ancestor_ids fa 985where folks.id = fa.id and father is not null and 986(father not in (select id from ancestor_ids)) 987union all 988select mother, ma.generation+1 from folks, ancestor_ids ma 989where folks.id = ma.id and mother is not null and 990(mother not in (select id from ancestor_ids)) 991) 992select generation, name from ancestor_ids a, folks 993where a.id = folks.id; 994generation name 9951 Dad 9961 Mom 9972 Grandpa Bill 9982 Grandma Ann 9992 Grandma Sally 10002 Grandpa Ben 10013 Grandgrandma Martha 1002set standard_compliant_cte=1; 1003with recursive 1004coupled_ancestor_ids (id) 1005as 1006( 1007select father from folks where name = 'Me' and father is not null 1008union 1009select mother from folks where name = 'Me' and mother is not null 1010union 1011select n.father 1012from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n 1013where folks.father = fa.id and folks.mother = ma.id and 1014(fa.id = n.id or ma.id = n.id) and 1015n.father is not null and n.mother is not null 1016union 1017select n.mother 1018from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n 1019where folks.father = fa.id and folks.mother = ma.id and 1020(fa.id = n.id or ma.id = n.id) and 1021n.father is not null and n.mother is not null 1022) 1023select p.* from coupled_ancestor_ids a, folks p 1024where a.id = p.id; 1025ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'coupled_ancestor_ids' 1026set statement standard_compliant_cte=0 for 1027with recursive 1028coupled_ancestor_ids (id) 1029as 1030( 1031select father from folks where name = 'Me' and father is not null 1032union 1033select mother from folks where name = 'Me' and mother is not null 1034union 1035select n.father 1036from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n 1037where folks.father = fa.id and folks.mother = ma.id and 1038(fa.id = n.id or ma.id = n.id) and 1039n.father is not null and n.mother is not null 1040union 1041select n.mother 1042from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n 1043where folks.father = fa.id and folks.mother = ma.id and 1044(fa.id = n.id or ma.id = n.id) and 1045n.father is not null and n.mother is not null 1046) 1047select p.* from coupled_ancestor_ids a, folks p 1048where a.id = p.id; 1049id name dob father mother 105020 Dad 1970-02-02 10 9 105130 Mom 1975-03-03 8 7 105210 Grandpa Bill 1940-04-05 NULL NULL 10539 Grandma Ann 1941-10-15 NULL NULL 10547 Grandma Sally 1943-08-23 NULL 6 10558 Grandpa Ben 1940-10-21 NULL NULL 1056with recursive 1057ancestor_ids (id) 1058as 1059( 1060select father from folks where name = 'Me' 1061 union 1062select mother from folks where name = 'Me' 1063 union 1064select father from folks left join ancestor_ids a on folks.id = a.id 1065union 1066select mother from folks left join ancestor_ids a on folks.id = a.id 1067), 1068ancestors 1069as 1070( 1071select p.* from folks as p, ancestor_ids as a 1072where p.id = a.id 1073) 1074select * from ancestors; 1075ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'ancestor_ids' 1076set statement standard_compliant_cte=0 for 1077with recursive 1078ancestor_ids (id) 1079as 1080( 1081select father from folks where name = 'Me' 1082 union 1083select mother from folks where name = 'Me' 1084 union 1085select father from folks left join ancestor_ids a on folks.id = a.id 1086union 1087select mother from folks left join ancestor_ids a on folks.id = a.id 1088), 1089ancestors 1090as 1091( 1092select p.* from folks as p, ancestor_ids as a 1093where p.id = a.id 1094) 1095select * from ancestors; 1096id name dob father mother 109720 Dad 1970-02-02 10 9 109830 Mom 1975-03-03 8 7 109910 Grandpa Bill 1940-04-05 NULL NULL 11009 Grandma Ann 1941-10-15 NULL NULL 110125 Uncle Jim 1968-11-18 8 7 11027 Grandma Sally 1943-08-23 NULL 6 11038 Grandpa Ben 1940-10-21 NULL NULL 11046 Grandgrandma Martha 1923-05-17 NULL NULL 110527 Auntie Melinda 1971-03-29 NULL NULL 1106with recursive 1107ancestor_ids (id, generation) 1108as 1109( 1110select father, 1 from folks where name = 'Me' 1111 union 1112select mother, 1 from folks where name = 'Me' 1113 union 1114select father, a.generation+1 from folks, ancestor_ids a 1115where folks.id = a.id 1116union 1117select mother, a.generation+1 from folks, ancestor_ids a 1118where folks.id = a.id 1119), 1120ancestors 1121as 1122( 1123select generation, name from folks as p, ancestor_ids as a 1124where p.id = a.id 1125) 1126select * from ancestors; 1127generation name 11281 Dad 11291 Mom 11302 Grandpa Bill 11312 Grandma Ann 11322 Grandma Sally 11332 Grandpa Ben 11343 Grandgrandma Martha 1135with recursive 1136ancestor_ids (id, generation) 1137as 1138( 1139select father, 1 from folks where name = 'Me' 1140 union 1141select mother, 1 from folks where name = 'Me' 1142 union 1143select max(father), max(a.generation)+1 from folks, ancestor_ids a 1144where folks.id = a.id 1145group by a.generation 1146union 1147select max(mother), max(a.generation)+1 from folks, ancestor_ids a 1148where folks.id = a.id 1149group by a.generation 1150), 1151ancestors 1152as 1153( 1154select generation, name from folks as p, ancestor_ids as a 1155where p.id = a.id 1156) 1157select * from ancestors; 1158ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'ancestor_ids' 1159set statement standard_compliant_cte=0 for 1160with recursive 1161ancestor_ids (id, generation) 1162as 1163( 1164select father, 1 from folks where name = 'Me' 1165 union 1166select mother, 1 from folks where name = 'Me' 1167 union 1168select max(father), a.generation+1 from folks, ancestor_ids a 1169where folks.id = a.id 1170group by a.generation 1171union 1172select max(mother), a.generation+1 from folks, ancestor_ids a 1173where folks.id = a.id 1174group by a.generation 1175), 1176ancestors 1177as 1178( 1179select generation, name from folks as p, ancestor_ids as a 1180where p.id = a.id 1181) 1182select * from ancestors; 1183generation name 11841 Dad 11851 Mom 11862 Grandpa Bill 11872 Grandma Ann 1188set statement max_recursive_iterations=1 for 1189with recursive 1190ancestor_ids (id, generation) 1191as 1192( 1193select father, 1 from folks where name = 'Me' 1194 union 1195select mother, 1 from folks where name = 'Me' 1196 union 1197select father, a.generation+1 from folks, ancestor_ids a 1198where folks.id = a.id 1199union 1200select mother, a.generation+1 from folks, ancestor_ids a 1201where folks.id = a.id 1202), 1203ancestors 1204as 1205( 1206select generation, name from folks as p, ancestor_ids as a 1207where p.id = a.id 1208) 1209select * from ancestors; 1210generation name 12111 Dad 12121 Mom 12132 Grandpa Bill 12142 Grandma Ann 12152 Grandma Sally 12162 Grandpa Ben 1217# query with recursive tables using key access 1218alter table folks add primary key (id); 1219explain 1220with recursive 1221ancestors 1222as 1223( 1224select * 1225from folks 1226where name = 'Me' 1227 union 1228select p.* 1229from folks as p, ancestors as fa 1230where p.id = fa.father 1231union 1232select p.* 1233from folks as p, ancestors as ma 1234where p.id = ma.mother 1235) 1236select * from ancestors; 1237id select_type table type possible_keys key key_len ref rows Extra 12381 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 12392 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 12403 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12 12413 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2 12424 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12 12434 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2 1244NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL 1245with recursive 1246ancestors 1247as 1248( 1249select * 1250from folks 1251where name = 'Me' 1252 union 1253select p.* 1254from folks as p, ancestors as fa 1255where p.id = fa.father 1256union 1257select p.* 1258from folks as p, ancestors as ma 1259where p.id = ma.mother 1260) 1261select * from ancestors; 1262id name dob father mother 1263100 Me 2000-01-01 20 30 126420 Dad 1970-02-02 10 9 126530 Mom 1975-03-03 8 7 126610 Grandpa Bill 1940-04-05 NULL NULL 12678 Grandpa Ben 1940-10-21 NULL NULL 12689 Grandma Ann 1941-10-15 NULL NULL 12697 Grandma Sally 1943-08-23 NULL 6 12706 Grandgrandma Martha 1923-05-17 NULL NULL 1271# 1272# EXPLAIN FORMAT=JSON on a query where one recursive CTE uses another: 1273# 1274explain 1275with recursive 1276prev_gen 1277as 1278( 1279select folks.* 1280from folks, prev_gen 1281where folks.id=prev_gen.father or folks.id=prev_gen.mother 1282union 1283select * 1284from folks 1285where name='Me' 1286), 1287ancestors 1288as 1289( 1290select * 1291from folks 1292where name='Me' 1293 union 1294select * 1295from ancestors 1296union 1297select * 1298from prev_gen 1299) 1300select ancestors.name, ancestors.dob from ancestors; 1301id select_type table type possible_keys key key_len ref rows Extra 13021 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 13034 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 13046 UNION <derived3> ALL NULL NULL NULL NULL 12 13055 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 1306NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 13073 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 13082 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12 13092 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1310NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL 1311explain FORMAT=JSON 1312with recursive 1313prev_gen 1314as 1315( 1316select folks.* 1317from folks, prev_gen 1318where folks.id=prev_gen.father or folks.id=prev_gen.mother 1319union 1320select * 1321from folks 1322where name='Me' 1323), 1324ancestors 1325as 1326( 1327select * 1328from folks 1329where name='Me2' 1330 union 1331select * 1332from ancestors where id < 234 1333union 1334select * 1335from prev_gen where id < 345 1336) 1337select ancestors.name, ancestors.dob from ancestors; 1338EXPLAIN 1339{ 1340 "query_block": { 1341 "select_id": 1, 1342 "table": { 1343 "table_name": "<derived4>", 1344 "access_type": "ALL", 1345 "rows": 24, 1346 "filtered": 100, 1347 "materialized": { 1348 "query_block": { 1349 "recursive_union": { 1350 "table_name": "<union4,6,5>", 1351 "access_type": "ALL", 1352 "query_specifications": [ 1353 { 1354 "query_block": { 1355 "select_id": 4, 1356 "table": { 1357 "table_name": "folks", 1358 "access_type": "ALL", 1359 "rows": 12, 1360 "filtered": 100, 1361 "attached_condition": "folks.`name` = 'Me2'" 1362 } 1363 } 1364 }, 1365 { 1366 "query_block": { 1367 "select_id": 6, 1368 "operation": "UNION", 1369 "table": { 1370 "table_name": "<derived3>", 1371 "access_type": "ALL", 1372 "rows": 12, 1373 "filtered": 100, 1374 "attached_condition": "prev_gen.`id` < 345", 1375 "materialized": { 1376 "query_block": { 1377 "recursive_union": { 1378 "table_name": "<union3,2>", 1379 "access_type": "ALL", 1380 "query_specifications": [ 1381 { 1382 "query_block": { 1383 "select_id": 3, 1384 "table": { 1385 "table_name": "folks", 1386 "access_type": "ALL", 1387 "rows": 12, 1388 "filtered": 100, 1389 "attached_condition": "folks.`name` = 'Me'" 1390 } 1391 } 1392 }, 1393 { 1394 "query_block": { 1395 "select_id": 2, 1396 "operation": "UNION", 1397 "table": { 1398 "table_name": "folks", 1399 "access_type": "ALL", 1400 "possible_keys": ["PRIMARY"], 1401 "rows": 12, 1402 "filtered": 100 1403 }, 1404 "block-nl-join": { 1405 "table": { 1406 "table_name": "<derived3>", 1407 "access_type": "ALL", 1408 "rows": 12, 1409 "filtered": 100 1410 }, 1411 "buffer_type": "flat", 1412 "buffer_size": "686", 1413 "join_type": "BNL", 1414 "attached_condition": "prev_gen.father = folks.`id` or prev_gen.mother = folks.`id`" 1415 } 1416 } 1417 } 1418 ] 1419 } 1420 } 1421 } 1422 } 1423 } 1424 }, 1425 { 1426 "query_block": { 1427 "select_id": 5, 1428 "operation": "UNION", 1429 "table": { 1430 "table_name": "<derived4>", 1431 "access_type": "ALL", 1432 "rows": 24, 1433 "filtered": 100, 1434 "attached_condition": "ancestors.`id` < 234" 1435 } 1436 } 1437 } 1438 ] 1439 } 1440 } 1441 } 1442 } 1443 } 1444} 1445# 1446explain format=json 1447with recursive 1448ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 1449w_id, w_name, w_dob, w_father, w_mother) 1450as 1451( 1452select h.*, w.* 1453from folks h, folks w, coupled_ancestors a 1454where a.father = h.id AND a.mother = w.id 1455union 1456select h.*, w.* 1457from folks v, folks h, folks w 1458where v.name = 'Me' and 1459(v.father = h.id AND v.mother= w.id) 1460), 1461coupled_ancestors (id, name, dob, father, mother) 1462as 1463( 1464select h_id, h_name, h_dob, h_father, h_mother 1465from ancestor_couples 1466union all 1467select w_id, w_name, w_dob, w_father, w_mother 1468from ancestor_couples 1469) 1470select h_name, h_dob, w_name, w_dob 1471from ancestor_couples; 1472EXPLAIN 1473{ 1474 "query_block": { 1475 "select_id": 1, 1476 "table": { 1477 "table_name": "<derived3>", 1478 "access_type": "ALL", 1479 "rows": 12, 1480 "filtered": 100, 1481 "materialized": { 1482 "query_block": { 1483 "recursive_union": { 1484 "table_name": "<union3,2>", 1485 "access_type": "ALL", 1486 "query_specifications": [ 1487 { 1488 "query_block": { 1489 "select_id": 3, 1490 "table": { 1491 "table_name": "v", 1492 "access_type": "ALL", 1493 "rows": 12, 1494 "filtered": 100, 1495 "attached_condition": "v.`name` = 'Me' and v.father is not null and v.mother is not null" 1496 }, 1497 "table": { 1498 "table_name": "h", 1499 "access_type": "eq_ref", 1500 "possible_keys": ["PRIMARY"], 1501 "key": "PRIMARY", 1502 "key_length": "4", 1503 "used_key_parts": ["id"], 1504 "ref": ["test.v.father"], 1505 "rows": 1, 1506 "filtered": 100 1507 }, 1508 "table": { 1509 "table_name": "w", 1510 "access_type": "eq_ref", 1511 "possible_keys": ["PRIMARY"], 1512 "key": "PRIMARY", 1513 "key_length": "4", 1514 "used_key_parts": ["id"], 1515 "ref": ["test.v.mother"], 1516 "rows": 1, 1517 "filtered": 100 1518 } 1519 } 1520 }, 1521 { 1522 "query_block": { 1523 "select_id": 2, 1524 "operation": "UNION", 1525 "table": { 1526 "table_name": "<derived4>", 1527 "access_type": "ALL", 1528 "rows": 2, 1529 "filtered": 100, 1530 "attached_condition": "a.father is not null and a.mother is not null" 1531 }, 1532 "table": { 1533 "table_name": "h", 1534 "access_type": "eq_ref", 1535 "possible_keys": ["PRIMARY"], 1536 "key": "PRIMARY", 1537 "key_length": "4", 1538 "used_key_parts": ["id"], 1539 "ref": ["a.father"], 1540 "rows": 1, 1541 "filtered": 100 1542 }, 1543 "table": { 1544 "table_name": "w", 1545 "access_type": "eq_ref", 1546 "possible_keys": ["PRIMARY"], 1547 "key": "PRIMARY", 1548 "key_length": "4", 1549 "used_key_parts": ["id"], 1550 "ref": ["a.mother"], 1551 "rows": 1, 1552 "filtered": 100 1553 } 1554 } 1555 } 1556 ] 1557 } 1558 } 1559 } 1560 } 1561 } 1562} 1563create table my_ancestors 1564with recursive 1565ancestor_ids (id) 1566as 1567( 1568select father from folks where name = 'Me' 1569 union 1570select mother from folks where name = 'Me' 1571 union 1572select father from folks, ancestor_ids a where folks.id = a.id 1573union 1574select mother from folks, ancestor_ids a where folks.id = a.id 1575) 1576select p.* from folks as p, ancestor_ids as a where p.id = a.id; 1577select * from my_ancestors; 1578id name dob father mother 157920 Dad 1970-02-02 10 9 158030 Mom 1975-03-03 8 7 158110 Grandpa Bill 1940-04-05 NULL NULL 15829 Grandma Ann 1941-10-15 NULL NULL 15837 Grandma Sally 1943-08-23 NULL 6 15848 Grandpa Ben 1940-10-21 NULL NULL 15856 Grandgrandma Martha 1923-05-17 NULL NULL 1586delete from my_ancestors; 1587insert into my_ancestors 1588with recursive 1589ancestor_ids (id) 1590as 1591( 1592select father from folks where name = 'Me' 1593 union 1594select mother from folks where name = 'Me' 1595 union 1596select father from folks, ancestor_ids a where folks.id = a.id 1597union 1598select mother from folks, ancestor_ids a where folks.id = a.id 1599) 1600select p.* from folks as p, ancestor_ids as a where p.id = a.id; 1601select * from my_ancestors; 1602id name dob father mother 160320 Dad 1970-02-02 10 9 160430 Mom 1975-03-03 8 7 160510 Grandpa Bill 1940-04-05 NULL NULL 16069 Grandma Ann 1941-10-15 NULL NULL 16077 Grandma Sally 1943-08-23 NULL 6 16088 Grandpa Ben 1940-10-21 NULL NULL 16096 Grandgrandma Martha 1923-05-17 NULL NULL 1610drop table my_ancestors; 1611WITH RECURSIVE 1612positions(i) AS ( 1613VALUES(0) 1614UNION SELECT ALL 1615i+1 FROM positions WHERE i < 4*4-1 1616), 1617solutions(board, n_queens) AS ( 1618SELECT REPEAT('-', 4*4), 0 1619FROM positions 1620UNION 1621SELECT 1622concat(substr(board, 1, i),'*',substr(board, i+2)),n_queens + 1 AS n_queens 1623FROM positions AS ps, solutions 1624WHERE n_queens < 4 1625AND substr(board,1,i) != '*' 1626 AND NOT EXISTS ( 1627SELECT 1 FROM positions WHERE 1628substr(board,i+1,1) = '*' AND 1629( 1630i % 4 = ps.i % 4 OR 1631i div 4 = ps.i div 4 OR 1632i div 4 + (i % 4) = ps.i div 4 + (ps.i % 4) OR 1633i div 4 - (i % 4) = ps.i div 4 - (ps.i % 4) 1634) 1635) 1636) 1637SELECT regexp_replace(board,concat('(',REPEAT('.', 4),')'),'\\1\n') n_queens FROM solutions WHERE n_queens = 4; 1638n_queens 1639--*- 1640*--- 1641---* 1642-*-- 1643 1644-*-- 1645---* 1646*--- 1647--*- 1648 1649# 1650# MDEV-10883: execution of prepared statement from SELECT 1651# with recursive CTE that renames columns 1652# 1653prepare stmt from" 1654with recursive 1655ancestor_ids (id) 1656as 1657( 1658 select father from folks where name = 'Me' 1659 union 1660 select mother from folks where name = 'Me' 1661 union 1662 select father from folks, ancestor_ids a where folks.id = a.id 1663 union 1664 select mother from folks, ancestor_ids a where folks.id = a.id 1665) 1666select p.* from folks as p, ancestor_ids as a where p.id = a.id; 1667"; 1668execute stmt; 1669id name dob father mother 167020 Dad 1970-02-02 10 9 167130 Mom 1975-03-03 8 7 167210 Grandpa Bill 1940-04-05 NULL NULL 16739 Grandma Ann 1941-10-15 NULL NULL 16747 Grandma Sally 1943-08-23 NULL 6 16758 Grandpa Ben 1940-10-21 NULL NULL 16766 Grandgrandma Martha 1923-05-17 NULL NULL 1677deallocate prepare stmt; 1678# 1679# MDEV-10881: execution of prepared statement from 1680# CREATE ... SELECT, INSERT ... SELECT 1681# 1682prepare stmt from" 1683create table my_ancestors 1684with recursive 1685ancestor_ids (id) 1686as 1687( 1688 select father from folks where name = 'Me' 1689 union 1690 select mother from folks where name = 'Me' 1691 union 1692 select father from folks, ancestor_ids a where folks.id = a.id 1693 union 1694 select mother from folks, ancestor_ids a where folks.id = a.id 1695) 1696select p.* from folks as p, ancestor_ids as a where p.id = a.id; 1697"; 1698execute stmt; 1699deallocate prepare stmt; 1700select * from my_ancestors; 1701id name dob father mother 170220 Dad 1970-02-02 10 9 170330 Mom 1975-03-03 8 7 170410 Grandpa Bill 1940-04-05 NULL NULL 17059 Grandma Ann 1941-10-15 NULL NULL 17067 Grandma Sally 1943-08-23 NULL 6 17078 Grandpa Ben 1940-10-21 NULL NULL 17086 Grandgrandma Martha 1923-05-17 NULL NULL 1709delete from my_ancestors; 1710prepare stmt from" 1711insert into my_ancestors 1712with recursive 1713ancestor_ids (id) 1714as 1715( 1716 select father from folks where name = 'Me' 1717 union 1718 select mother from folks where name = 'Me' 1719 union 1720 select father from folks, ancestor_ids a where folks.id = a.id 1721 union 1722 select mother from folks, ancestor_ids a where folks.id = a.id 1723) 1724select p.* from folks as p, ancestor_ids as a where p.id = a.id; 1725"; 1726execute stmt; 1727deallocate prepare stmt; 1728select * from my_ancestors; 1729id name dob father mother 173020 Dad 1970-02-02 10 9 173130 Mom 1975-03-03 8 7 173210 Grandpa Bill 1940-04-05 NULL NULL 17339 Grandma Ann 1941-10-15 NULL NULL 17347 Grandma Sally 1943-08-23 NULL 6 17358 Grandpa Ben 1940-10-21 NULL NULL 17366 Grandgrandma Martha 1923-05-17 NULL NULL 1737drop table my_ancestors; 1738# 1739# MDEV-10933: WITH clause together with SELECT in parenthesis 1740# CREATE SELECT 1741# 1742create table my_ancestors 1743( 1744with recursive 1745ancestor_ids (id) 1746as 1747( 1748select father from folks where name = 'Me' 1749 union 1750select mother from folks where name = 'Me' 1751 union 1752select father from folks, ancestor_ids a where folks.id = a.id 1753union 1754select mother from folks, ancestor_ids a where folks.id = a.id 1755) 1756select p.* from folks as p, ancestor_ids as a where p.id = a.id 1757); 1758select * from my_ancestors; 1759id name dob father mother 176020 Dad 1970-02-02 10 9 176130 Mom 1975-03-03 8 7 176210 Grandpa Bill 1940-04-05 NULL NULL 17639 Grandma Ann 1941-10-15 NULL NULL 17647 Grandma Sally 1943-08-23 NULL 6 17658 Grandpa Ben 1940-10-21 NULL NULL 17666 Grandgrandma Martha 1923-05-17 NULL NULL 1767drop table my_ancestors; 1768drop table folks; 1769# 1770# MDEV-10372: [bb-10.2-mdev9864 tree] EXPLAIN with recursive CTE enters endless recursion 1771# 1772create table t1(a int); 1773insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1774explain format=json 1775with recursive t as (select a from t1 union select a+10 from t where a < 1000) 1776select * from t; 1777EXPLAIN 1778{ 1779 "query_block": { 1780 "select_id": 1, 1781 "table": { 1782 "table_name": "<derived2>", 1783 "access_type": "ALL", 1784 "rows": 10, 1785 "filtered": 100, 1786 "materialized": { 1787 "query_block": { 1788 "recursive_union": { 1789 "table_name": "<union2,3>", 1790 "access_type": "ALL", 1791 "query_specifications": [ 1792 { 1793 "query_block": { 1794 "select_id": 2, 1795 "table": { 1796 "table_name": "t1", 1797 "access_type": "ALL", 1798 "rows": 10, 1799 "filtered": 100 1800 } 1801 } 1802 }, 1803 { 1804 "query_block": { 1805 "select_id": 3, 1806 "operation": "UNION", 1807 "table": { 1808 "table_name": "<derived2>", 1809 "access_type": "ALL", 1810 "rows": 10, 1811 "filtered": 100, 1812 "attached_condition": "t.a < 1000" 1813 } 1814 } 1815 } 1816 ] 1817 } 1818 } 1819 } 1820 } 1821 } 1822} 1823drop table t1; 1824# 1825# MDEV-10737: recursive union with several anchors at the end 1826# 1827WITH RECURSIVE cte(n) AS 1828( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 ) 1829SELECT * FROM cte; 1830n 18311 18322 18333 18344 18355 1836# 1837# MDEV-10736: recursive definition with anchor over a table with blob 1838# 1839CREATE TABLE t1 (f VARCHAR(1024)); 1840WITH RECURSIVE cte(f) AS 1841(SELECT t1.f FROM t1 UNION ALL SELECT cte.f FROM cte) 1842SELECT * FROM cte as t; 1843f 1844DROP TABLE t1; 1845# 1846# MDEV-10899: mergeable derived in the spec of recursive CTE 1847# 1848create table t1 (a int); 1849insert into t1 values 1850(0), (1), (2), (3), (4); 1851create table t2 (a int); 1852insert into t2 values 1853(1), (2), (3), (4), (5); 1854with recursive 1855t1 as 1856( 1857select x.a from (select a from t2 where t2.a=3) x 1858union 1859select t2.a from t1,t2 where t1.a+1=t2.a 1860) 1861select * from t1; 1862a 18633 18644 18655 1866explain 1867with recursive 1868t1 as 1869( 1870select x.a from (select a from t2 where t2.a=3) x 1871union 1872select t2.a from t1,t2 where t1.a+1=t2.a 1873) 1874select * from t1; 1875id select_type table type possible_keys key key_len ref rows Extra 18761 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 18772 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where 18784 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 5 18794 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 1880NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL 1881drop table t1,t2; 1882# 1883# MDEV-11278: non-mergeable view in the spec of recursive CTE 1884# 1885create table t1 (a int); 1886insert into t1 values 1887(0), (1), (2), (3), (4); 1888create table t2 (a int); 1889insert into t2 values 1890(1), (2), (3), (4), (5); 1891create view v1 as 1892select a from t2 where a < 3 1893union 1894select a from t2 where a > 4; 1895with recursive 1896t1 as 1897( 1898select a from v1 where a=1 1899union 1900select v1.a from t1,v1 where t1.a+1=v1.a 1901) 1902select * from t1; 1903a 19041 19052 1906drop view v1; 1907drop table t1,t2; 1908# 1909# MDEV-11259: recursive CTE with concatenation operation 1910# 1911DROP TABLE IF EXISTS edges; 1912Warnings: 1913Note 1051 Unknown table 'test.edges' 1914CREATE TABLE edges( 1915a int(10) unsigned NOT NULL, 1916b int(10) unsigned NOT NULL, 1917PRIMARY KEY (a,b), 1918KEY b(b) 1919); 1920INSERT INTO edges 1921VALUES (1,3),(2,1),(2,4),(3,4),(3,5),(3,6),(4,7),(5,1),(5,6),(6,1); 1922DROP TABLE IF EXISTS edges2; 1923Warnings: 1924Note 1051 Unknown table 'test.edges2' 1925CREATE VIEW edges2 (a, b) AS 1926SELECT a, b FROM edges UNION ALL SELECT b, a FROM edges; 1927WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS 1928( SELECT a, b, 1 AS distance, 1929concat(a, '.', b, '.') AS path_string 1930FROM edges 1931UNION ALL 1932SELECT tc.a, e.b, tc.distance + 1, 1933concat(tc.path_string, e.b, '.') AS path_string 1934FROM edges AS e 1935JOIN transitive_closure AS tc 1936ON e.a = tc.b 1937WHERE tc.path_string NOT LIKE concat('%', e.b, '.%') 1938) 1939SELECT * FROM transitive_closure 1940ORDER BY a, b, distance; 1941a b distance path_string 19421 3 1 1.3. 19431 4 2 1.3.4. 19441 5 2 1.3.5. 19451 6 2 1.3.6. 19461 6 3 1.3.5.6. 19471 7 3 1.3.4.7. 19482 1 1 2.1. 19492 3 2 2.1.3. 19502 4 1 2.4. 19512 4 3 2.1.3.4. 19522 5 3 2.1.3.5. 19532 6 3 2.1.3.6. 19542 6 4 2.1.3.5.6. 19552 7 2 2.4.7. 19562 7 4 2.1.3.4.7. 19573 1 2 3.5.1. 19583 1 2 3.6.1. 19593 1 3 3.5.6.1. 19603 4 1 3.4. 19613 5 1 3.5. 19623 6 1 3.6. 19633 6 2 3.5.6. 19643 7 2 3.4.7. 19654 7 1 4.7. 19665 1 1 5.1. 19675 1 2 5.6.1. 19685 3 2 5.1.3. 19695 3 3 5.6.1.3. 19705 4 3 5.1.3.4. 19715 4 4 5.6.1.3.4. 19725 6 1 5.6. 19735 6 3 5.1.3.6. 19745 7 4 5.1.3.4.7. 19755 7 5 5.6.1.3.4.7. 19766 1 1 6.1. 19776 3 2 6.1.3. 19786 4 3 6.1.3.4. 19796 5 3 6.1.3.5. 19806 7 4 6.1.3.4.7. 1981WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS 1982( SELECT a, b, 1 AS distance, 1983concat(a, '.', b, '.') AS path_string 1984FROM edges 1985WHERE a = 1 -- source 1986UNION ALL 1987SELECT tc.a, e.b, tc.distance + 1, 1988concat(tc.path_string, e.b, '.') AS path_string 1989FROM edges AS e 1990JOIN transitive_closure AS tc ON e.a = tc.b 1991WHERE tc.path_string NOT LIKE concat('%', e.b, '.%') 1992) 1993SELECT * FROM transitive_closure 1994WHERE b = 6 -- destination 1995ORDER BY a, b, distance; 1996a b distance path_string 19971 6 2 1.3.6. 19981 6 3 1.3.5.6. 1999WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS 2000( SELECT a, b, 1 AS distance, 2001concat(a, '.', b, '.') AS path_string 2002FROM edges2 2003UNION ALL 2004SELECT tc.a, e.b, tc.distance + 1, 2005concat(tc.path_string, e.b, '.') AS path_string 2006FROM edges2 AS e 2007JOIN transitive_closure AS tc ON e.a = tc.b 2008WHERE tc.path_string NOT LIKE concat('%', e.b, '.%') 2009) 2010SELECT * FROM transitive_closure 2011ORDER BY a, b, distance; 2012a b distance path_string 20131 2 1 1.2. 20141 2 3 1.3.4.2. 20151 2 4 1.5.3.4.2. 20161 2 4 1.6.3.4.2. 20171 2 5 1.5.6.3.4.2. 20181 2 5 1.6.5.3.4.2. 20191 3 1 1.3. 20201 3 2 1.5.3. 20211 3 2 1.6.3. 20221 3 3 1.2.4.3. 20231 3 3 1.5.6.3. 20241 3 3 1.6.5.3. 20251 4 2 1.2.4. 20261 4 2 1.3.4. 20271 4 3 1.5.3.4. 20281 4 3 1.6.3.4. 20291 4 4 1.5.6.3.4. 20301 4 4 1.6.5.3.4. 20311 5 1 1.5. 20321 5 2 1.3.5. 20331 5 2 1.6.5. 20341 5 3 1.3.6.5. 20351 5 3 1.6.3.5. 20361 5 4 1.2.4.3.5. 20371 5 5 1.2.4.3.6.5. 20381 6 1 1.6. 20391 6 2 1.3.6. 20401 6 2 1.5.6. 20411 6 3 1.3.5.6. 20421 6 3 1.5.3.6. 20431 6 4 1.2.4.3.6. 20441 6 5 1.2.4.3.5.6. 20451 7 3 1.2.4.7. 20461 7 3 1.3.4.7. 20471 7 4 1.5.3.4.7. 20481 7 4 1.6.3.4.7. 20491 7 5 1.5.6.3.4.7. 20501 7 5 1.6.5.3.4.7. 20512 1 1 2.1. 20522 1 3 2.4.3.1. 20532 1 4 2.4.3.5.1. 20542 1 4 2.4.3.6.1. 20552 1 5 2.4.3.5.6.1. 20562 1 5 2.4.3.6.5.1. 20572 3 2 2.1.3. 20582 3 2 2.4.3. 20592 3 3 2.1.5.3. 20602 3 3 2.1.6.3. 20612 3 4 2.1.5.6.3. 20622 3 4 2.1.6.5.3. 20632 4 1 2.4. 20642 4 3 2.1.3.4. 20652 4 4 2.1.5.3.4. 20662 4 4 2.1.6.3.4. 20672 4 5 2.1.5.6.3.4. 20682 4 5 2.1.6.5.3.4. 20692 5 2 2.1.5. 20702 5 3 2.1.3.5. 20712 5 3 2.1.6.5. 20722 5 3 2.4.3.5. 20732 5 4 2.1.3.6.5. 20742 5 4 2.1.6.3.5. 20752 5 4 2.4.3.1.5. 20762 5 4 2.4.3.6.5. 20772 5 5 2.4.3.1.6.5. 20782 5 5 2.4.3.6.1.5. 20792 6 2 2.1.6. 20802 6 3 2.1.3.6. 20812 6 3 2.1.5.6. 20822 6 3 2.4.3.6. 20832 6 4 2.1.3.5.6. 20842 6 4 2.1.5.3.6. 20852 6 4 2.4.3.1.6. 20862 6 4 2.4.3.5.6. 20872 6 5 2.4.3.1.5.6. 20882 6 5 2.4.3.5.1.6. 20892 7 2 2.4.7. 20902 7 4 2.1.3.4.7. 20912 7 5 2.1.5.3.4.7. 20922 7 5 2.1.6.3.4.7. 20932 7 6 2.1.5.6.3.4.7. 20942 7 6 2.1.6.5.3.4.7. 20953 1 1 3.1. 20963 1 2 3.5.1. 20973 1 2 3.6.1. 20983 1 3 3.4.2.1. 20993 1 3 3.5.6.1. 21003 1 3 3.6.5.1. 21013 2 2 3.1.2. 21023 2 2 3.4.2. 21033 2 3 3.5.1.2. 21043 2 3 3.6.1.2. 21053 2 4 3.5.6.1.2. 21063 2 4 3.6.5.1.2. 21073 4 1 3.4. 21083 4 3 3.1.2.4. 21093 4 4 3.5.1.2.4. 21103 4 4 3.6.1.2.4. 21113 4 5 3.5.6.1.2.4. 21123 4 5 3.6.5.1.2.4. 21133 5 1 3.5. 21143 5 2 3.1.5. 21153 5 2 3.6.5. 21163 5 3 3.1.6.5. 21173 5 3 3.6.1.5. 21183 5 4 3.4.2.1.5. 21193 5 5 3.4.2.1.6.5. 21203 6 1 3.6. 21213 6 2 3.1.6. 21223 6 2 3.5.6. 21233 6 3 3.1.5.6. 21243 6 3 3.5.1.6. 21253 6 4 3.4.2.1.6. 21263 6 5 3.4.2.1.5.6. 21273 7 2 3.4.7. 21283 7 4 3.1.2.4.7. 21293 7 5 3.5.1.2.4.7. 21303 7 5 3.6.1.2.4.7. 21313 7 6 3.5.6.1.2.4.7. 21323 7 6 3.6.5.1.2.4.7. 21334 1 2 4.2.1. 21344 1 2 4.3.1. 21354 1 3 4.3.5.1. 21364 1 3 4.3.6.1. 21374 1 4 4.3.5.6.1. 21384 1 4 4.3.6.5.1. 21394 2 1 4.2. 21404 2 3 4.3.1.2. 21414 2 4 4.3.5.1.2. 21424 2 4 4.3.6.1.2. 21434 2 5 4.3.5.6.1.2. 21444 2 5 4.3.6.5.1.2. 21454 3 1 4.3. 21464 3 3 4.2.1.3. 21474 3 4 4.2.1.5.3. 21484 3 4 4.2.1.6.3. 21494 3 5 4.2.1.5.6.3. 21504 3 5 4.2.1.6.5.3. 21514 5 2 4.3.5. 21524 5 3 4.2.1.5. 21534 5 3 4.3.1.5. 21544 5 3 4.3.6.5. 21554 5 4 4.2.1.3.5. 21564 5 4 4.2.1.6.5. 21574 5 4 4.3.1.6.5. 21584 5 4 4.3.6.1.5. 21594 5 5 4.2.1.3.6.5. 21604 5 5 4.2.1.6.3.5. 21614 6 2 4.3.6. 21624 6 3 4.2.1.6. 21634 6 3 4.3.1.6. 21644 6 3 4.3.5.6. 21654 6 4 4.2.1.3.6. 21664 6 4 4.2.1.5.6. 21674 6 4 4.3.1.5.6. 21684 6 4 4.3.5.1.6. 21694 6 5 4.2.1.3.5.6. 21704 6 5 4.2.1.5.3.6. 21714 7 1 4.7. 21725 1 1 5.1. 21735 1 2 5.3.1. 21745 1 2 5.6.1. 21755 1 3 5.3.6.1. 21765 1 3 5.6.3.1. 21775 1 4 5.3.4.2.1. 21785 1 5 5.6.3.4.2.1. 21795 2 2 5.1.2. 21805 2 3 5.3.1.2. 21815 2 3 5.3.4.2. 21825 2 3 5.6.1.2. 21835 2 4 5.1.3.4.2. 21845 2 4 5.3.6.1.2. 21855 2 4 5.6.3.1.2. 21865 2 4 5.6.3.4.2. 21875 2 5 5.1.6.3.4.2. 21885 2 5 5.6.1.3.4.2. 21895 3 1 5.3. 21905 3 2 5.1.3. 21915 3 2 5.6.3. 21925 3 3 5.1.6.3. 21935 3 3 5.6.1.3. 21945 3 4 5.1.2.4.3. 21955 3 5 5.6.1.2.4.3. 21965 4 2 5.3.4. 21975 4 3 5.1.2.4. 21985 4 3 5.1.3.4. 21995 4 3 5.6.3.4. 22005 4 4 5.1.6.3.4. 22015 4 4 5.3.1.2.4. 22025 4 4 5.6.1.2.4. 22035 4 4 5.6.1.3.4. 22045 4 5 5.3.6.1.2.4. 22055 4 5 5.6.3.1.2.4. 22065 6 1 5.6. 22075 6 2 5.1.6. 22085 6 2 5.3.6. 22095 6 3 5.1.3.6. 22105 6 3 5.3.1.6. 22115 6 5 5.1.2.4.3.6. 22125 6 5 5.3.4.2.1.6. 22135 7 3 5.3.4.7. 22145 7 4 5.1.2.4.7. 22155 7 4 5.1.3.4.7. 22165 7 4 5.6.3.4.7. 22175 7 5 5.1.6.3.4.7. 22185 7 5 5.3.1.2.4.7. 22195 7 5 5.6.1.2.4.7. 22205 7 5 5.6.1.3.4.7. 22215 7 6 5.3.6.1.2.4.7. 22225 7 6 5.6.3.1.2.4.7. 22236 1 1 6.1. 22246 1 2 6.3.1. 22256 1 2 6.5.1. 22266 1 3 6.3.5.1. 22276 1 3 6.5.3.1. 22286 1 4 6.3.4.2.1. 22296 1 5 6.5.3.4.2.1. 22306 2 2 6.1.2. 22316 2 3 6.3.1.2. 22326 2 3 6.3.4.2. 22336 2 3 6.5.1.2. 22346 2 4 6.1.3.4.2. 22356 2 4 6.3.5.1.2. 22366 2 4 6.5.3.1.2. 22376 2 4 6.5.3.4.2. 22386 2 5 6.1.5.3.4.2. 22396 2 5 6.5.1.3.4.2. 22406 3 1 6.3. 22416 3 2 6.1.3. 22426 3 2 6.5.3. 22436 3 3 6.1.5.3. 22446 3 3 6.5.1.3. 22456 3 4 6.1.2.4.3. 22466 3 5 6.5.1.2.4.3. 22476 4 2 6.3.4. 22486 4 3 6.1.2.4. 22496 4 3 6.1.3.4. 22506 4 3 6.5.3.4. 22516 4 4 6.1.5.3.4. 22526 4 4 6.3.1.2.4. 22536 4 4 6.5.1.2.4. 22546 4 4 6.5.1.3.4. 22556 4 5 6.3.5.1.2.4. 22566 4 5 6.5.3.1.2.4. 22576 5 1 6.5. 22586 5 2 6.1.5. 22596 5 2 6.3.5. 22606 5 3 6.1.3.5. 22616 5 3 6.3.1.5. 22626 5 5 6.1.2.4.3.5. 22636 5 5 6.3.4.2.1.5. 22646 7 3 6.3.4.7. 22656 7 4 6.1.2.4.7. 22666 7 4 6.1.3.4.7. 22676 7 4 6.5.3.4.7. 22686 7 5 6.1.5.3.4.7. 22696 7 5 6.3.1.2.4.7. 22706 7 5 6.5.1.2.4.7. 22716 7 5 6.5.1.3.4.7. 22726 7 6 6.3.5.1.2.4.7. 22736 7 6 6.5.3.1.2.4.7. 22747 1 3 7.4.2.1. 22757 1 3 7.4.3.1. 22767 1 4 7.4.3.5.1. 22777 1 4 7.4.3.6.1. 22787 1 5 7.4.3.5.6.1. 22797 1 5 7.4.3.6.5.1. 22807 2 2 7.4.2. 22817 2 4 7.4.3.1.2. 22827 2 5 7.4.3.5.1.2. 22837 2 5 7.4.3.6.1.2. 22847 2 6 7.4.3.5.6.1.2. 22857 2 6 7.4.3.6.5.1.2. 22867 3 2 7.4.3. 22877 3 4 7.4.2.1.3. 22887 3 5 7.4.2.1.5.3. 22897 3 5 7.4.2.1.6.3. 22907 3 6 7.4.2.1.5.6.3. 22917 3 6 7.4.2.1.6.5.3. 22927 4 1 7.4. 22937 5 3 7.4.3.5. 22947 5 4 7.4.2.1.5. 22957 5 4 7.4.3.1.5. 22967 5 4 7.4.3.6.5. 22977 5 5 7.4.2.1.3.5. 22987 5 5 7.4.2.1.6.5. 22997 5 5 7.4.3.1.6.5. 23007 5 5 7.4.3.6.1.5. 23017 5 6 7.4.2.1.3.6.5. 23027 5 6 7.4.2.1.6.3.5. 23037 6 3 7.4.3.6. 23047 6 4 7.4.2.1.6. 23057 6 4 7.4.3.1.6. 23067 6 4 7.4.3.5.6. 23077 6 5 7.4.2.1.3.6. 23087 6 5 7.4.2.1.5.6. 23097 6 5 7.4.3.1.5.6. 23107 6 5 7.4.3.5.1.6. 23117 6 6 7.4.2.1.3.5.6. 23127 6 6 7.4.2.1.5.3.6. 2313WITH RECURSIVE transitive_closure(a, b, distance, path_string) 2314AS 2315( SELECT a, b, 1 AS distance, 2316concat(a, '.', b, '.') AS path_string 2317FROM edges2 2318UNION ALL 2319SELECT tc.a, e.b, tc.distance + 1, 2320concat(tc.path_string, e.b, '.') AS path_string 2321FROM edges2 AS e 2322JOIN transitive_closure AS tc ON e.a = tc.b 2323WHERE tc.path_string NOT LIKE concat('%', e.b, '.%') 2324) 2325SELECT a, b, min(distance) AS dist FROM transitive_closure 2326GROUP BY a, b 2327ORDER BY a, dist, b; 2328a b dist 23291 2 1 23301 3 1 23311 4 2 23321 5 1 23331 6 1 23341 7 3 23352 1 1 23362 3 2 23372 4 1 23382 5 2 23392 6 2 23402 7 2 23413 1 1 23423 2 2 23433 4 1 23443 5 1 23453 6 1 23463 7 2 23474 1 2 23484 2 1 23494 3 1 23504 5 2 23514 6 2 23524 7 1 23535 1 1 23545 2 2 23555 3 1 23565 4 2 23575 6 1 23585 7 3 23596 1 1 23606 2 2 23616 3 1 23626 4 2 23636 5 1 23646 7 3 23657 1 3 23667 2 2 23677 3 2 23687 4 1 23697 5 3 23707 6 3 2371DROP VIEW edges2; 2372DROP TABLE edges; 2373# 2374# MDEV-11674: recursive CTE table that cannot be stored 2375# in a heap table 2376# 2377create table t1 (id int, test_data varchar(36)); 2378insert into t1(id, test_data) 2379select id, test_data 2380from ( 2381with recursive data_generator(id, test_data) as ( 2382select 1 as id, uuid() as test_data 2383union all 2384select id + 1, uuid() from data_generator where id < 150000 2385) 2386select * from data_generator 2387) as a; 2388drop table t1; 2389# 2390# MDEV-10773: ANALYZE for query with recursive CTE 2391# 2392analyze format=json 2393with recursive src(counter) as 2394(select 1 2395union 2396select counter+1 from src where counter<10 2397) select * from src; 2398ANALYZE 2399{ 2400 "query_block": { 2401 "select_id": 1, 2402 "r_loops": 1, 2403 "r_total_time_ms": "REPLACED", 2404 "table": { 2405 "table_name": "<derived2>", 2406 "access_type": "ALL", 2407 "r_loops": 1, 2408 "rows": 2, 2409 "r_rows": 10, 2410 "r_table_time_ms": "REPLACED", 2411 "r_other_time_ms": "REPLACED", 2412 "filtered": 100, 2413 "r_filtered": 100, 2414 "materialized": { 2415 "query_block": { 2416 "recursive_union": { 2417 "table_name": "<union2,3>", 2418 "access_type": "ALL", 2419 "r_loops": 0, 2420 "r_rows": null, 2421 "query_specifications": [ 2422 { 2423 "query_block": { 2424 "select_id": 2, 2425 "table": { 2426 "message": "No tables used" 2427 } 2428 } 2429 }, 2430 { 2431 "query_block": { 2432 "select_id": 3, 2433 "operation": "UNION", 2434 "r_loops": 10, 2435 "r_total_time_ms": "REPLACED", 2436 "table": { 2437 "table_name": "<derived2>", 2438 "access_type": "ALL", 2439 "r_loops": 10, 2440 "rows": 2, 2441 "r_rows": 1, 2442 "r_table_time_ms": "REPLACED", 2443 "r_other_time_ms": "REPLACED", 2444 "filtered": 100, 2445 "r_filtered": 90, 2446 "attached_condition": "src.counter < 10" 2447 } 2448 } 2449 } 2450 ] 2451 } 2452 } 2453 } 2454 } 2455 } 2456} 2457# 2458# mdev-12360: recursive reference in left operand of LEFT JOIN 2459# 2460create table folks(id int, name char(32), dob date, father int, mother int); 2461insert into folks values 2462(100, 'Me', '2000-01-01', 20, 30), 2463(20, 'Dad', '1970-02-02', 10, 9), 2464(30, 'Mom', '1975-03-03', 8, 7), 2465(10, 'Grandpa Bill', '1940-04-05', null, null), 2466(9, 'Grandma Ann', '1941-10-15', null, null), 2467(25, 'Uncle Jim', '1968-11-18', 8, 7), 2468(98, 'Sister Amy', '2001-06-20', 20, 30), 2469(7, 'Grandma Sally', '1943-08-23', null, 6), 2470(8, 'Grandpa Ben', '1940-10-21', null, null), 2471(6, 'Grandgrandma Martha', '1923-05-17', null, null), 2472(67, 'Cousin Eddie', '1992-02-28', 25, 27), 2473(27, 'Auntie Melinda', '1971-03-29', null, null); 2474with recursive 2475ancestor_ids (id) 2476as 2477( 2478select father from folks where name = 'Me' 2479 union 2480select mother from folks where name = 'Me' 2481 union 2482select father from ancestor_ids as a left join folks on folks.id = a.id 2483union 2484select mother from ancestor_ids as a left join folks on folks.id = a.id 2485), 2486ancestors 2487as 2488( 2489select p.* from folks as p, ancestor_ids as a 2490where p.id = a.id 2491) 2492select * from ancestors; 2493id name dob father mother 249420 Dad 1970-02-02 10 9 249530 Mom 1975-03-03 8 7 249610 Grandpa Bill 1940-04-05 NULL NULL 24979 Grandma Ann 1941-10-15 NULL NULL 24987 Grandma Sally 1943-08-23 NULL 6 24998 Grandpa Ben 1940-10-21 NULL NULL 25006 Grandgrandma Martha 1923-05-17 NULL NULL 2501drop table folks; 2502# 2503# mdev-12368: crash with mutually recursive CTE 2504# that arenot Standard compliant 2505# 2506create table value_nodes (v char(4)); 2507create table module_nodes(m char(4)); 2508create table module_arguments(m char(4), v char(4)); 2509create table module_results(m char(4), v char(4)); 2510with recursive 2511reached_values as 2512( 2513select v from value_nodes where v in ('v3','v7','v9') 2514union 2515select module_results.v from module_results, applied_modules 2516where module_results.m = applied_modules.m 2517), 2518applied_modules as 2519( 2520select module_nodes.m 2521from 2522module_nodes 2523left join 2524( 2525module_arguments 2526left join 2527reached_values 2528on module_arguments.v = reached_values.v 2529) 2530on reached_values.v is null and 2531module_nodes.m = module_arguments.m 2532where module_arguments.m is null 2533) 2534select * from reached_values; 2535ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'applied_modules' 2536drop table value_nodes, module_nodes, module_arguments, module_results; 2537# 2538# mdev-12375: query using one of two mutually recursive CTEs 2539# whose non-recursive part returns an empty set 2540# 2541create table value_nodes (v char(4)); 2542insert into value_nodes values 2543('v1'), ('v2'), ('v3'), ('v4'), ('v5'), ('v6'), ('v7'), ('v8'), ('v9'), 2544('v10'), ('v11'), ('v12'), ('v13'), ('v14'), ('v15'), ('v16'); 2545create table module_nodes(m char(4)); 2546insert into module_nodes values 2547('m1'), ('m2'), ('m3'), ('m4'), ('m5'), ('m6'), ('m7'); 2548create table module_arguments(m char(4), v char(4)); 2549insert into module_arguments values 2550('m1','v3'), ('m1','v9'), 2551('m2','v4'), ('m2','v3'), ('m2','v7'), 2552('m3','v6'), 2553('m4','v4'), ('m4','v1'), 2554('m5','v10'), ('m5','v8'), ('m5','v3'), 2555('m6','v8'), ('m6','v1'), 2556('m7','v11'), ('m7','v12'); 2557create table module_results(m char(4), v char(4)); 2558insert into module_results values 2559('m1','v4'), 2560('m2','v1'), ('m2','v6'), 2561('m3','v10'), 2562('m4','v8'), 2563('m5','v11'), ('m5','v9'), 2564('m6','v12'), ('m6','v4'), 2565('m7','v2'); 2566set statement max_recursive_iterations=2, standard_compliant_cte=0 for 2567with recursive 2568reached_values as 2569( 2570select v from value_nodes where v in ('v3','v7','v9') 2571union 2572select module_results.v from module_results, applied_modules 2573where module_results.m = applied_modules.m 2574), 2575applied_modules as 2576( 2577select * from module_nodes where 1=0 2578union 2579select module_nodes.m 2580from 2581module_nodes 2582left join 2583( 2584module_arguments 2585left join 2586reached_values 2587on module_arguments.v = reached_values.v 2588) 2589on reached_values.v is null and 2590module_nodes.m = module_arguments.m 2591where module_arguments.m is null 2592) 2593select * from applied_modules; 2594m 2595m1 2596m2 2597drop table value_nodes, module_nodes, module_arguments, module_results; 2598# 2599# mdev-12519: recursive references in subqueries 2600# 2601create table t1 (lp char(4) not null, rp char(4) not null); 2602insert into t1 values 2603('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5'), 2604('p2','p7'), ('p7','p8'), ('p8','p3'), ('p8','p4'); 2605set standard_compliant_cte=0; 2606with recursive 2607reachables(p) as 2608( 2609select lp from t1 where lp = 'p1' 2610 union 2611select t1.rp from reachables, t1 2612where t1.lp = reachables.p 2613) 2614select * from reachables; 2615p 2616p1 2617p2 2618p3 2619p7 2620p4 2621p8 2622p5 2623with recursive 2624reachables(p) as 2625( 2626select lp from t1 where lp = 'p1' 2627 union 2628select t1.rp from reachables, t1 2629where 'p3' not in (select * from reachables) and 2630t1.lp = reachables.p 2631) 2632select * from reachables; 2633p 2634p1 2635p2 2636p3 2637p7 2638with recursive 2639reachables(p) as 2640( 2641select lp from t1 where lp = 'p1' 2642 union 2643select t1.rp from reachables, t1 2644where 'p3' not in (select p from reachables where p <= 'p5' 2645 union 2646select p from reachables where p > 'p5') and 2647t1.lp = reachables.p 2648) 2649select * from reachables; 2650p 2651p1 2652p2 2653p3 2654p7 2655prepare stmt from " 2656with recursive 2657reachables(p) as 2658( 2659 select lp from t1 where lp = 'p1' 2660 union 2661 select t1.rp from reachables, t1 2662 where 'p3' not in (select p from reachables where p <= 'p5' 2663 union 2664 select p from reachables where p > 'p5') and 2665 t1.lp = reachables.p 2666) 2667select * from reachables; 2668"; 2669execute stmt; 2670p 2671p1 2672p2 2673p3 2674p7 2675execute stmt; 2676p 2677p1 2678p2 2679p3 2680p7 2681deallocate prepare stmt; 2682drop table t1; 2683create table objects(v char(4) not null); 2684insert into objects values 2685('v1'), ('v2'), ('v3'), ('v4'), ('v5'), 2686('v6'), ('v7'), ('v8'), ('v9'), ('v10'); 2687create table modules(m char(4) not null); 2688insert into modules values 2689('m1'), ('m2'), ('m3'), ('m4'); 2690create table module_arguments(m char(4) not null, v char(4) not null); 2691insert into module_arguments values 2692('m1','v3'), ('m1','v9'), 2693('m2','v4'), ('m2','v7'), 2694('m3','v6'), ('m4','v2'); 2695create table module_results(m char(4) not null, v char(4) not null); 2696insert into module_results values 2697('m1','v4'), 2698('m2','v1'), ('m2','v6'), 2699('m3','v10'), ('m4','v7'); 2700set standard_compliant_cte=0; 2701with recursive 2702reached_objects as 2703( 2704select v, 'init' as m from objects where v in ('v3','v7','v9') 2705union 2706select module_results.v, module_results.m from module_results, applied_modules 2707where module_results.m = applied_modules.m 2708), 2709applied_modules as 2710( 2711select * from modules where 1=0 2712union 2713select modules.m 2714from 2715modules 2716where 2717not exists (select * from module_arguments 2718where module_arguments.m = modules.m and 2719module_arguments.v not in 2720(select v from reached_objects)) 2721) 2722select * from reached_objects; 2723v m 2724v3 init 2725v7 init 2726v9 init 2727v4 m1 2728v1 m2 2729v6 m2 2730v10 m3 2731with recursive 2732reached_objects as 2733( 2734select v, 'init' as m from objects where v in ('v3','v7','v9') 2735union 2736select module_results.v, module_results.m from module_results, applied_modules 2737where module_results.m = applied_modules.m 2738), 2739applied_modules as 2740( 2741select * from modules where 1=0 2742union 2743select modules.m 2744from 2745modules 2746where 2747'v6' not in (select v from reached_objects) and 2748not exists (select * from module_arguments 2749where module_arguments.m = modules.m and 2750module_arguments.v not in 2751(select v from reached_objects)) 2752) 2753select * from reached_objects; 2754v m 2755v3 init 2756v7 init 2757v9 init 2758v4 m1 2759v1 m2 2760v6 m2 2761prepare stmt from " 2762with recursive 2763reached_objects as 2764( 2765 select v, 'init' as m from objects where v in ('v3','v7','v9') 2766 union 2767 select module_results.v, module_results.m from module_results, applied_modules 2768 where module_results.m = applied_modules.m 2769), 2770applied_modules as 2771( 2772 select * from modules where 1=0 2773 union 2774 select modules.m 2775 from 2776 modules 2777 where 2778 'v6' not in (select v from reached_objects) and 2779 not exists (select * from module_arguments 2780 where module_arguments.m = modules.m and 2781 module_arguments.v not in 2782 (select v from reached_objects)) 2783) 2784select * from reached_objects; 2785"; 2786execute stmt; 2787v m 2788v3 init 2789v7 init 2790v9 init 2791v4 m1 2792v1 m2 2793v6 m2 2794execute stmt; 2795v m 2796v3 init 2797v7 init 2798v9 init 2799v4 m1 2800v1 m2 2801v6 m2 2802deallocate prepare stmt; 2803drop table objects, modules, module_arguments, module_results; 2804set standard_compliant_cte=default; 2805select @@standard_compliant_cte; 2806@@standard_compliant_cte 28071 2808# 2809# mdev-12554: impossible where in recursive select 2810# 2811CREATE TABLE t1 (i int); 2812INSERT INTO t1 VALUES (1),(2); 2813WITH RECURSIVE 2814cte(f) AS ( SELECT i FROM t1 UNION SELECT f FROM t1, cte WHERE 1=0 ) 2815SELECT * FROM cte; 2816f 28171 28182 2819DROP TABLE t1; 2820# 2821# mdev-12556: recursive execution uses Aria temporary tables 2822# 2823CREATE TABLE t (c1 varchar(255), c2 tinytext); 2824INSERT INTO t VALUES ('a','a'),('b','b'),('c','c'),('d','d'); 2825ANALYZE WITH RECURSIVE cte(f) AS ( 2826SELECT c1 FROM t 2827UNION 2828SELECT c1 FROM t, cte 2829) SELECT COUNT(*) FROM cte; 2830id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 28311 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 28322 DERIVED t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 28333 RECURSIVE UNION t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 28343 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 Using join buffer (flat, BNL join) 2835NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL 2836WITH RECURSIVE cte(f) AS ( 2837SELECT c1 FROM t 2838UNION 2839SELECT c1 FROM t, cte 2840) SELECT COUNT(*) FROM cte; 2841COUNT(*) 28424 2843ANALYZE WITH RECURSIVE cte(f) AS ( 2844SELECT c2 FROM t 2845UNION 2846SELECT c2 FROM t, cte 2847) SELECT COUNT(*) FROM cte; 2848id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 28491 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 28502 DERIVED t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 28513 RECURSIVE UNION t ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 28523 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 Using join buffer (flat, BNL join) 2853NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL 2854WITH RECURSIVE cte(f) AS ( 2855SELECT c2 FROM t 2856UNION 2857SELECT c2 FROM t, cte 2858) SELECT COUNT(*) FROM cte; 2859COUNT(*) 28604 2861DROP TABLE t; 2862# 2863# mdev-12563: no recursive references on the top level of the CTE spec 2864# 2865CREATE TABLE t (i int); 2866INSERT INTO t VALUES (3), (1),(2); 2867SET standard_compliant_cte=0; 2868WITH RECURSIVE cte(f) AS ( 2869SELECT i FROM t 2870UNION 2871SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte ) 2872) SELECT * FROM cte; 2873f 28743 28751 28762 2877WITH RECURSIVE cte(f) AS ( 2878SELECT i FROM t 2879UNION 2880SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte WHERE i < 2 ) 2881UNION 2882SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte WHERE i > 2 ) 2883) SELECT * FROM cte; 2884f 28853 28861 28872 2888WITH RECURSIVE cte(f) AS ( 2889SELECT i FROM t 2890UNION 2891SELECT i FROM t 2892WHERE i NOT IN ( SELECT * FROM cte WHERE i < 2 2893UNION 2894SELECT * FROM cte WHERE i > 2) 2895) SELECT * FROM cte; 2896f 28973 28981 28992 2900WITH RECURSIVE cte(f) AS ( 2901SELECT i FROM t 2902UNION 2903SELECT i FROM t 2904WHERE i NOT IN ( SELECT * FROM t 2905WHERE i IN ( SELECT * FROM cte ) GROUP BY i ) 2906) SELECT * FROM cte; 2907f 29083 29091 29102 2911WITH RECURSIVE cte(f) AS ( 2912SELECT i FROM t 2913UNION 2914SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte ) 2915UNION 2916SELECT * FROM cte WHERE f > 2 2917) SELECT * FROM cte; 2918f 29193 29201 29212 2922set standard_compliant_cte=default; 2923DROP TABLE t; 2924# 2925# mdev-14184: recursive CTE embedded into CTE with multiple references 2926# 2927WITH 2928cte1 AS ( 2929SELECT n FROM ( 2930WITH RECURSIVE rec_cte(n) AS ( 2931SELECT 1 as n1 2932UNION ALL 2933SELECT n+1 as n2 FROM rec_cte WHERE n < 3 2934) SELECT n FROM rec_cte 2935) AS X 2936), 2937cte2 as ( 2938SELECT 2 FROM cte1 2939) 2940SELECT * 2941FROM cte1; 2942n 29431 29442 29453 2946# 2947# mdev-14629: a user-defined variable is defined by the recursive CTE 2948# 2949set @var= 2950( 2951with recursive cte_tab(a) as ( 2952select 1 2953union 2954select a+1 from cte_tab 2955where a<3) 2956select count(*) from cte_tab 2957); 2958select @var; 2959@var 29603 2961create table t1(a int, b int); 2962insert into t1 values (3,8),(1,5),(5,7),(7,4),(4,3); 2963set @var= 2964( 2965with recursive summ(a,s) as ( 2966select 1, 0 union 2967select t1.b, t1.b+summ.s from summ, t1 2968where summ.a=t1.a) 2969select s from summ 2970order by a desc 2971limit 1 2972); 2973select @var; 2974@var 297527 2976set @var= 2977( 2978with recursive 2979cte_1 as ( 2980select 1 2981union 2982select * from cte_2), 2983cte_2 as ( 2984select * from cte_1 2985union 2986select a from t1, cte_2 2987where t1.a=cte_2.a) 2988select * from cte_2 2989limit 1 2990); 2991ERROR HY000: Unacceptable mutual recursion with anchored table 'cte_1' 2992drop table t1; 2993# 2994# mdev-14777: crash caused by the same as in mdev-14755 2995# 2996CREATE TABLE t1 (i1 int NOT NULL, i2 int); 2997CREATE TABLE t2 (d1 int NOT NULL PRIMARY KEY); 2998CREATE TABLE t3 (i int ); 2999insert into t1 select seq,seq from seq_1_to_100000; 3000insert into t2 select seq from seq_1000_to_100000; 3001insert into t3 select seq from seq_1_to_1000; 3002SELECT * 3003FROM 3004( 3005SELECT * 3006FROM 3007( 3008WITH RECURSIVE rt AS 3009( 3010SELECT i2 P, i1 C FROM t1 WHERE i1 IN (SELECT d1 FROM t2) 3011UNION 3012SELECT t1.i2 P, rt.C C FROM t1, rt 3013) 3014SELECT C,P 3015FROM ( SELECT P,C FROM rt WHERE NOT EXISTS (SELECT 1 FROM t1) ) Y 3016) X 3017WHERE 1 = 1 3018) K, t3; 3019C P i 3020drop table t1,t2,t3; 3021# 3022# mdev-14879: subquery with recursive reference in WHERE of CTE 3023# 3024create table flights 3025(departure varchar(32), 3026arrival varchar(32), 3027carrier varchar(20), 3028flight_number char(7)); 3029insert into flights values 3030('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'), 3031('Seattle', 'Chicago', 'American', 'AA 2573'), 3032('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'), 3033('Chicago', 'New York', 'American', 'AA 375'), 3034('Chicago', 'Montreal', 'Air Canada', 'AC 3053'), 3035('Los Angeles', 'New York', 'Delta', 'DL 1197'), 3036('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'), 3037('New York', 'Paris', 'Air France', 'AF 23'), 3038('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'), 3039('Tokyo', 'Seattle', 'ANA', 'NH 178'), 3040('Los Angeles', 'Tokyo', 'ANA', 'NH 175'), 3041('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'), 3042('Montreal', 'Paris', 'Air Canada', 'AC 870'), 3043('Cairo', 'Paris', 'Air France', 'AF 503'), 3044('New York', 'Seattle', 'American', 'AA 45'), 3045('Paris', 'Chicago', 'Air France', 'AF 6734'); 3046with recursive destinations (city) as 3047( select a.arrival from flights a where a.departure='Cairo' 3048 union 3049select b.arrival from destinations r, flights b where r.city=b.departure) 3050select * from destinations; 3051city 3052Paris 3053Chicago 3054New York 3055Montreal 3056Seattle 3057Frankfurt 3058Los Angeles 3059Moscow 3060Tokyo 3061set standard_compliant_cte=0; 3062with recursive destinations (city, legs) as 3063( 3064select a.arrival, 1 from flights a where a.departure='Cairo' 3065 union 3066select b.arrival, r.legs + 1 from destinations r, flights b 3067where r.city=b.departure and b.arrival not in (select city from destinations) 3068) 3069select * from destinations; 3070city legs 3071Paris 1 3072Chicago 2 3073New York 3 3074Montreal 3 3075Seattle 4 3076Frankfurt 5 3077Los Angeles 5 3078Moscow 6 3079Tokyo 6 3080explain extended with recursive destinations (city, legs) as 3081( 3082select a.arrival, 1 from flights a where a.departure='Cairo' 3083 union 3084select b.arrival, r.legs + 1 from destinations r, flights b 3085where r.city=b.departure and b.arrival not in (select city from destinations) 3086) 3087select * from destinations; 3088id select_type table type possible_keys key key_len ref rows filtered Extra 30891 PRIMARY <derived2> ALL NULL NULL NULL NULL 16 100.00 30902 DERIVED a ALL NULL NULL NULL NULL 16 100.00 Using where 30913 RECURSIVE UNION b ALL NULL NULL NULL NULL 16 100.00 Using where 30923 RECURSIVE UNION <derived2> ref key0 key0 35 test.b.departure 2 100.00 30934 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 16 100.00 Using where 3094NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 3095Warnings: 3096Note 1003 with recursive destinations(`city`,`legs`) as (/* select#2 */ select `test`.`a`.`arrival` AS `city`,1 AS `legs` from `test`.`flights` `a` where `test`.`a`.`departure` = 'Cairo' union /* select#3 */ select `test`.`b`.`arrival` AS `arrival`,`r`.`legs` + 1 AS `r.legs + 1` from `destinations` `r` join `test`.`flights` `b` where `r`.`city` = `test`.`b`.`departure` and !(<in_optimizer>(`test`.`b`.`arrival`,<exists>(/* select#4 */ select `destinations`.`city` from `destinations` where trigcond(`test`.`b`.`arrival` = `destinations`.`city` or `destinations`.`city` is null) having trigcond(`destinations`.`city` is null)))))/* select#1 */ select `destinations`.`city` AS `city`,`destinations`.`legs` AS `legs` from `destinations` 3097set standard_compliant_cte=default; 3098drop table flights; 3099# 3100# MDEV-15162: Setting user variable in recursive CTE 3101# 3102SET @c=1; 3103WITH RECURSIVE cte AS 3104(SELECT 5 3105UNION 3106SELECT @c:=@c+1 FROM cte WHERE @c<3) 3107SELECT * FROM cte; 31085 31095 31102 31113 3112# 3113# MDEV-15575: using recursive cte with big_tables enabled 3114# 3115set tmp_memory_table_size=0; 3116with recursive qn as 3117(select 123 as a union all select 1+a from qn where a<130) 3118select * from qn; 3119a 3120123 3121124 3122125 3123126 3124127 3125128 3126129 3127130 3128set tmp_memory_table_size=default; 3129# 3130# MDEV-15571: using recursive cte with big_tables enabled 3131# 3132create table t1 (a bigint); 3133insert into t1 values(1); 3134set tmp_memory_table_size=0; 3135with recursive qn as 3136( 3137select a from t1 3138union all 3139select a*2000 from qn where a<10000000000000000000 3140) 3141select * from qn; 3142ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000' 3143set tmp_memory_table_size=default; 3144drop table t1; 3145# 3146# MDEV-15556: using recursive cte with big_tables enabled 3147# when recursive tables are accessed by key 3148# 3149set tmp_memory_table_size=0; 3150CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int); 3151INSERT INTO t1 VALUES 3152(1, "A", 2, 3), (2, "LA", 4, 5), (4, "LLA", 6, 7), 3153(6, "LLLA", NULL, NULL), (7, "RLLA", NULL, NULL), (5, "RLA", 8, 9), 3154(8, "LRLA", NULL, NULL), (9, "RRLA", NULL, NULL), (3, "RA", 10, 11), 3155(10, "LRA", 12, 13), (11, "RRA", 14, 15), (15, "RRRA", NULL, NULL), 3156(16, "B", 17, 18), (17, "LB", NULL, NULL), (18, "RB", NULL, NULL); 3157CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand(); 3158WITH RECURSIVE tree_of_a AS 3159(SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" 3160 UNION ALL 3161SELECT t2.*, concat(tree_of_a.path,",",t2.id) 3162FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.leftpar 3163UNION ALL 3164SELECT t2.*, concat(tree_of_a.path,",",t2.id) 3165FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.rightpar) 3166SELECT * FROM tree_of_a 3167ORDER BY path; 3168id name leftpar rightpar path 31691 A 2 3 1 31702 LA 4 5 1,2 31714 LLA 6 7 1,2,4 31726 LLLA NULL NULL 1,2,4,6 31737 RLLA NULL NULL 1,2,4,7 31745 RLA 8 9 1,2,5 31758 LRLA NULL NULL 1,2,5,8 31769 RRLA NULL NULL 1,2,5,9 31773 RA 10 11 1,3 317810 LRA 12 13 1,3,10 317911 RRA 14 15 1,3,11 318015 RRRA NULL NULL 1,3,11,15 3181EXPLAIN WITH RECURSIVE tree_of_a AS 3182(SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" 3183 UNION ALL 3184SELECT t2.*, concat(tree_of_a.path,",",t2.id) 3185FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.leftpar 3186UNION ALL 3187SELECT t2.*, concat(tree_of_a.path,",",t2.id) 3188FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.rightpar) 3189SELECT * FROM tree_of_a 3190ORDER BY path; 3191id select_type table type possible_keys key key_len ref rows Extra 31921 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 Using filesort 31932 DERIVED t2 ALL NULL NULL NULL NULL 15 Using where 31943 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 Using where 31953 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 31964 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 Using where 31974 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 3198NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL 3199DROP TABLE t1,t2; 3200set tmp_memory_table_size=default; 3201# 3202# MDEV-15840: recursive tables are accessed by key 3203# (the same problem as for MDEV-15556) 3204# 3205CREATE TABLE t1 (p1 text,k2 int, p2 text, k1 int); 3206INSERT INTO t1 select seq, seq, seq, seq from seq_1_to_1000; 3207CREATE PROCEDURE getNums() 3208BEGIN 3209WITH RECURSIVE cte as 3210( 3211SELECT * FROM t1 3212UNION 3213SELECT c.* FROM t1 c JOIN cte p ON c.p1 = p.p2 AND c.k2 = p.k1 3214) 3215SELECT * FROM cte LIMIT 10; 3216END | 3217call getNums(); 3218p1 k2 p2 k1 32191 1 1 1 32202 2 2 2 32213 3 3 3 32224 4 4 4 32235 5 5 5 32246 6 6 6 32257 7 7 7 32268 8 8 8 32279 9 9 9 322810 10 10 10 3229DROP PROCEDURE getNums; 3230DROP TABLE t1; 3231# 3232# MDEV-15894: aggregate/winfow functions in non-recorsive part 3233# 3234create table t1(b int); 3235insert into t1 values(10),(20),(10); 3236with recursive qn as 3237(select max(b) as a from t1 union 3238select a from qn) 3239select * from qn; 3240a 324120 3242with recursive qn as 3243(select rank() over (order by b) as a from t1 union 3244select a from qn) 3245select * from qn; 3246a 32471 32483 3249drop table t1; 3250# 3251# MDEV-16086: tmp table for CTE is created as ARIA tables 3252# 3253CREATE TABLE t1 ( 3254Id int(11) not null AUTO_INCREMENT, 3255Parent varchar(15) not null, 3256Child varchar(15) not null, 3257PRIMARY KEY (Id) 3258) ENGINE = MyISAM; 3259INSERT INTO t1 (Parent, Child) VALUES 3260('123', '456'),('456', '789'),('321', '654'),('654', '987'); 3261WITH RECURSIVE cte AS 3262( SELECT b.Parent, 3263b.Child, 3264CAST(CONCAT(b.Child,',') AS CHAR(513)) Path 3265FROM t1 b 3266LEFT OUTER JOIN t1 bc ON b.Child = bc.Parent 3267WHERE bc.Id IS NULL 3268UNION ALL SELECT c.Parent, 3269c.Child, 3270CONCAT(p.Path,c.Child,',') Path 3271FROM t1 c 3272INNER JOIN cte p ON c.Child = p.Parent) 3273SELECT * 3274FROM cte 3275ORDER BY Path; 3276Parent Child Path 3277456 789 789, 3278123 456 789,456, 3279654 987 987, 3280321 654 987,654, 3281DROP TABLE t1; 3282# 3283# MDEV-16212: recursive CTE with global ORDER BY 3284# 3285set statement max_recursive_iterations = 2 for 3286WITH RECURSIVE qn AS ( 3287SELECT 1 FROM dual UNION ALL 3288SELECT 1 FROM qn 3289ORDER BY (SELECT * FROM qn)) 3290SELECT count(*) FROM qn; 3291ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec' 3292# 3293# MDEV-15581: mix of ALL and DISTINCT UNION in recursive CTE 3294# 3295create table t1(a int); 3296insert into t1 values(1),(2); 3297insert into t1 values(1),(2); 3298set @c=0, @d=0; 3299WITH RECURSIVE qn AS 3300( 3301select 1,0 as col from t1 3302union distinct 3303select 1,0 from t1 3304union all 3305select 3, 0*(@c:=@c+1) from qn where @c<1 3306union all 3307select 3, 0*(@d:=@d+1) from qn where @d<1 3308) 3309select * from qn; 3310ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec' 3311drop table t1; 3312# 3313# MDEV-16629: function with recursive CTE using a base table 3314# 3315CREATE TABLE t1 (id int); 3316INSERT INTO t1 VALUES (0), (1),(2); 3317WITH recursive cte AS 3318(SELECT id FROM t1 UNION SELECT 3 FROM cte) 3319SELECT count(id) FROM cte; 3320count(id) 33214 3322CREATE OR REPLACE FUNCTION func() RETURNS int 3323RETURN 3324( 3325WITH recursive cte AS 3326(SELECT id FROM t1 UNION SELECT 3 FROM cte) 3327SELECT count(id) FROM cte 3328); 3329SELECT func(); 3330func() 33314 3332DROP FUNCTION func; 3333DROP TABLE t1; 3334# 3335# MDEV-16661: function with recursive CTE using no base tables 3336# (fixed by the patch for MDEV-16629) 3337# 3338CREATE OR REPLACE FUNCTION func() RETURNS int 3339RETURN 3340( 3341WITH RECURSIVE cte AS 3342(SELECT 1 as id UNION SELECT * FROM cte) 3343SELECT count(id) FROM cte 3344); 3345SELECT func(); 3346func() 33471 3348DROP FUNCTION func; 3349# 3350# MDEV-17024: two materialized CTEs using the same recursive CTE 3351# 3352create table t1 (id int); 3353insert into t1 values (1), (2), (3); 3354with recursive 3355rcte(a) as 3356(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3357cte1 as 3358(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 3359cte2 as 3360(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 3361select * from cte1, cte2; 3362c1 c2 33632 1 3364explain extended with recursive 3365rcte(a) as 3366(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3367cte1 as 3368(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 3369cte2 as 3370(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 3371select * from cte1, cte2; 3372id select_type table type possible_keys key key_len ref rows filtered Extra 33731 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 33741 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) 33752 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 33763 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 3377NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 33784 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 33794 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 33805 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 33815 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 3382Warnings: 3383Note 1003 with recursive rcte(`a`) as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (/* select#5 */ select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)/* select#1 */ select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` 3384prepare stmt from "with recursive 3385rcte(a) as 3386(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3387cte1 as 3388(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 3389cte2 as 3390(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 3391select * from cte1, cte2"; 3392execute stmt; 3393c1 c2 33942 1 3395execute stmt; 3396c1 c2 33972 1 3398create table t2 (c1 int, c2 int); 3399create procedure p() insert into t2 with recursive 3400rcte(a) as 3401(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3402cte1 as 3403(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 3404cte2 as 3405(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 3406select * from cte1, cte2; 3407call p(); 3408select * from t2; 3409c1 c2 34102 1 3411with recursive 3412rcte(a) as 3413(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3414cte1 as 3415(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 3416cte2 as 3417(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 3418select * from cte1; 3419c1 34202 3421with recursive 3422rcte(a) as 3423(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3424cte1 as 3425(select count(*) as c1 from t1), 3426cte2 as 3427(select count(*) as c2 from t2) 3428select * from cte1,cte2; 3429c1 c2 34303 1 3431with recursive 3432rcte(a) as 3433(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3434cte1 as 3435(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 3436cte2 as 3437(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 3438select * from cte1, cte2 where cte1.c1 = 3; 3439c1 c2 3440with recursive 3441rcte(a) as 3442(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3443cte1 as 3444(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 3445cte2 as 3446(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 3447select * from cte2, cte1; 3448c2 c1 34491 2 3450explain extended with recursive 3451rcte(a) as 3452(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3453cte1 as 3454(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 3455cte2 as 3456(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 3457select * from cte2, cte1; 3458id select_type table type possible_keys key key_len ref rows filtered Extra 34591 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 34601 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) 34612 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 34623 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 3463NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 34645 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 34655 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 34664 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 34674 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 3468Warnings: 3469Note 1003 with recursive rcte(`a`) as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (/* select#5 */ select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)/* select#1 */ select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1` 3470prepare stmt from "with recursive 3471rcte(a) as 3472(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3473cte1 as 3474(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 3475cte2 as 3476(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 3477select * from cte2, cte1"; 3478execute stmt; 3479c2 c1 34801 2 3481execute stmt; 3482c2 c1 34831 2 3484drop procedure p; 3485drop table t2; 3486create table t2 (c1 int, c2 int); 3487create procedure p() insert into t2 with recursive 3488rcte(a) as 3489(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 3490cte1 as 3491(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 3492cte2 as 3493(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 3494select * from cte2, cte1; 3495call p(); 3496select * from t2; 3497c1 c2 34981 2 3499drop procedure p; 3500drop table t1,t2; 3501# 3502# MDEV-17201: recursive part with LIMIT 3503# 3504CREATE TABLE purchases ( 3505id int unsigned NOT NULL AUTO_INCREMENT, 3506pdate date NOT NULL, 3507quantity int unsigned NOT NULL, 3508p_id int unsigned NOT NULL, 3509PRIMARY KEY (id) 3510); 3511INSERT INTO purchases(pdate, quantity, p_id) VALUES 3512('2014-11-01',5 ,1),('2014-11-03', 3 ,1), 3513('2014-11-01',2 ,2),('2014-11-03', 4 ,2); 3514CREATE TABLE expired ( 3515edate date NOT NULL, 3516quantity int unsigned NOT NULL, 3517p_id int unsigned NOT NULL, 3518PRIMARY KEY (edate,p_id) 3519); 3520INSERT INTO expired VALUES ('2014-11-12', 5 ,1),('2014-11-08', 1 ,2); 3521WITH RECURSIVE expired_map AS ( 3522SELECT edate AS expired_date, 3523CAST(NULL AS date) AS purchase_date, 35240 AS quantity, 3525e.p_id, 3526(SELECT MAX(id)+1 FROM purchases p 3527WHERE pdate <= edate AND p.p_id =p_id) AS purchase_processed, 3528quantity AS unresolved 3529FROM expired e 3530UNION 3531( SELECT expired_date, 3532pdate, 3533IF(p.quantity < m.unresolved, p.quantity, m.unresolved), 3534p.p_id, 3535p.id, 3536IF(p.quantity < m.unresolved, m.unresolved - p.quantity, 0) 3537FROM purchases p JOIN expired_map m ON p.p_id = m.p_id 3538WHERE p.id < m.purchase_processed AND m.unresolved > 0 3539ORDER BY p.id DESC 3540LIMIT 1 3541) 3542) 3543SELECT * FROM expired_map; 3544expired_date purchase_date quantity p_id purchase_processed unresolved 35452014-11-12 NULL 0 1 5 5 35462014-11-08 NULL 0 2 5 1 35472014-11-08 2014-11-03 1 2 4 0 3548DROP TABLE purchases, expired; 3549# 3550# MDEV-17635: Two recursive CTEs, the second using the first 3551# 3552WITH RECURSIVE 3553x AS (SELECT 0 as k UNION ALL SELECT k + 1 FROM x WHERE k < 1), 3554z AS 3555( SELECT k1 AS cx, k2 AS cy, k1, k2 3556FROM (SELECT k AS k1 FROM x) x1 JOIN (SELECT k AS k2 FROM x) y1 3557UNION 3558SELECT 1,1,1,1 FROM z) 3559SELECT * FROM z; 3560cx cy k1 k2 35610 0 0 0 35621 0 1 0 35630 1 0 1 35641 1 1 1 3565# https://wiki.postgresql.org/wiki/Mandelbrot_set: 3566WITH RECURSIVE x(i) AS ( 3567SELECT CAST(0 AS DECIMAL(13, 10)) 3568UNION ALL 3569SELECT i + 1 3570FROM x 3571WHERE i < 101 3572), 3573Z(Ix, Iy, Cx, Cy, X, Y, I) AS ( 3574SELECT Ix, Iy, X, Y, X, Y, 0 3575FROM (SELECT CAST(-2.2 + 0.031 * i AS DECIMAL(13, 10)) AS X, 3576i AS Ix FROM x) AS xgen 3577CROSS JOIN ( 3578SELECT CAST(-1.5 + 0.031 * i AS DECIMAL(13, 10)) AS Y, 3579i AS iY FROM x 3580) AS ygen 3581UNION ALL 3582SELECT Ix, Iy, Cx, Cy, 3583CAST(X * X - Y * Y + Cx AS DECIMAL(13, 10)) AS X, 3584CAST(Y * X * 2 + Cy AS DECIMAL(13, 10)), I + 1 3585FROM Z 3586WHERE X * X + Y * Y < 16.0 3587AND I < 27 3588), 3589Zt (Ix, Iy, I) AS ( 3590SELECT Ix, Iy, MAX(I) AS I 3591FROM Z 3592GROUP BY Iy, Ix 3593ORDER BY Iy, Ix 3594) 3595SELECT GROUP_CONCAT( 3596SUBSTRING( 3597' .,,,-----++++%%%%@@@@#### ', 3598GREATEST(I, 1), 35991 3600) ORDER BY Ix SEPARATOR '' 3601 ) AS 'Mandelbrot Set' 3602 FROM Zt 3603GROUP BY Iy 3604ORDER BY Iy; 3605Mandelbrot Set 3606 .................................................................................... 3607 ....................................................................................... 3608 ......................................................................................... 3609 ........................................................................................... 3610 ....................................................,,,,,,,,,................................. 3611 ................................................,,,,,,,,,,,,,,,,,,............................. 3612 ..............................................,,,,,,,,,,,,,,,,,,,,,,,,.......................... 3613 ............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,........................ 3614 ..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................... 3615 .........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................... 3616 ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................... 3617 .......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................. 3618 .......................................,,,,,,,,,,,,,,,,,,,,,,,,--,,,,,,,,,,,,,,,,,,,,................ 3619......................................,,,,,,,,,,,,,,,,,,,,,,,,,,-+--,,,,,,,,,,,,,,,,,,,............... 3620....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,.............. 3621...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,--- -----,,,,,,,,,,,,,,,,,............. 3622.................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++--++,,,,,,,,,,,,,,,,,,............ 3623................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%++---,,,,,,,,,,,,,,,,,............ 3624..............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,........... 3625.............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----- %%+----,,,,,,,,,,,,,,,,,,.......... 3626...........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---%-+% ----,,,,,,,,,,,,,,,,,,,......... 3627..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+ +## %+%---,,,,,,,,,,,,,,,,,,......... 3628........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----# # +---,,,,,,,,,,,,,,,,,,........ 3629.......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------% %-----,,,,,,,,,,,,,,,,,........ 3630.....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---------+ ------,,,,,,,,,,,,,,,,,....... 3631....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+@ +-----------,,,,,,,,,,,,....... 3632..................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----@-------++ ++-----------,,,,,,,,,,,,...... 3633.................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--+@% ---+ +@%%@ %%+@+@%------+-,,,,,,,,,,,...... 3634................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---- # ++% % @-----++--,,,,,,,,,,,..... 3635..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+ % %%++ %+%@-,,,,,,,,,,,..... 3636.............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+# #% ++-,,,,,,,,,,,,.... 3637............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+ @---,,,,,,,,,,,,.... 3638..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------++% ---,,,,,,,,,,,,.... 3639.........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+ + %+---,,,,,,,,,,,,,... 3640........,,,,,,,,,,,,,,,,,,,,,--------------------@ +----,,,,,,,,,,,,... 3641.......,,,,,,,,,,,,,,,,,,,,,,- +-----------------+ ----,,,,,,,,,,,,... 3642.......,,,,,,,,,,,,,,,,,,,,,--++------+---------+% +++--,,,,,,,,,,,,.. 3643......,,,,,,,,,,,,,,,,,,,,,,--%+-----++--------- #+-,,,,,,,,,,,,.. 3644.....,,,,,,,,,,,,,,,,,,,,,,----#%++--+@ -+-----+% --,,,,,,,,,,,,.. 3645.....,,,,,,,,,,,,,,,,,,,,,,-----+## ++@ + +----% +--,,,,,,,,,,,,,.. 3646....,,,,,,,,,,,,,,,,,,,,,,------+@ @ @@++++# +--,,,,,,,,,,,,,.. 3647....,,,,,,,,,,,,,,,,,,,,,-------% #++% -,,,,,,,,,,,,,.. 3648...,,,,,,,,,,,,,,,,,,,,,------++%# %%@ %-,,,,,,,,,,,,,,. 3649...,,,,,,,,,,,,,,,,,,,--------+ % +--,,,,,,,,,,,,,,. 3650...,,,,,,,,,,,,,,,,,,-----+--++@ # --,,,,,,,,,,,,,,. 3651..,,,,,,,,,,,,,,,,,-------%+++% @--,,,,,,,,,,,,,,,. 3652..,,,,,,,,,,,-------------+ @#@ ---,,,,,,,,,,,,,,,. 3653..,,,,,,,,,---@--------@-+% +---,,,,,,,,,,,,,,,. 3654..,,,,,------- +-++++-+%%% +----,,,,,,,,,,,,,,,. 3655..,,,,,,------%--------++% +----,,,,,,,,,,,,,,,. 3656..,,,,,,,,,,--+----------++# ---,,,,,,,,,,,,,,,. 3657..,,,,,,,,,,,,------------+@@@% +--,,,,,,,,,,,,,,,. 3658..,,,,,,,,,,,,,,,,,------- +++% %--,,,,,,,,,,,,,,,. 3659...,,,,,,,,,,,,,,,,,,---------+@ @ --,,,,,,,,,,,,,,. 3660...,,,,,,,,,,,,,,,,,,,,------- # %@ +--,,,,,,,,,,,,,,. 3661...,,,,,,,,,,,,,,,,,,,,,-------++@ %+ %-,,,,,,,,,,,,,,. 3662....,,,,,,,,,,,,,,,,,,,,,------- %++% %-,,,,,,,,,,,,,.. 3663....,,,,,,,,,,,,,,,,,,,,,,------+# %# #@ ++++ +--,,,,,,,,,,,,,.. 3664.....,,,,,,,,,,,,,,,,,,,,,,-----+ %%++% +@+----+ +--,,,,,,,,,,,,,.. 3665.....,,,,,,,,,,,,,,,,,,,,,,,---%+++--+#+--------% #--,,,,,,,,,,,,.. 3666......,,,,,,,,,,,,,,,,,,,,,,--++-----%%--------- @#--,,,,,,,,,,,,.. 3667.......,,,,,,,,,,,,,,,,,,,,,---------------------+@ +-++,,,,,,,,,,,,... 3668........,,,,,,,,,,,,,,,,,,,,,--------------------+ ----,,,,,,,,,,,,... 3669.........,,,,,,,,,,,,,,,,,,,,----,,,------------- #+----,,,,,,,,,,,,... 3670..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+ + +---,,,,,,,,,,,,,... 3671...........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+%# #---,,,,,,,,,,,,.... 3672............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+# @ @---,,,,,,,,,,,,.... 3673.............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+# + @--,,,,,,,,,,,,.... 3674..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+% %+@ %+-+ +++%-,,,,,,,,,,,..... 3675................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----% %@++ # % -----++-,,,,,,,,,,,,..... 3676.................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-- ++ ---+ + +%@ %++++++------%-,,,,,,,,,,,...... 3677...................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---- -------++ +------------,,,,,,,,,,,,...... 3678....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+% +--------,,,,,,,,,,,,,,,....... 3679......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+# -----,,,,,,,,,,,,,,,,,,....... 3680.......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+ #----,,,,,,,,,,,,,,,,,,........ 3681.........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+% %#---,,,,,,,,,,,,,,,,,,,........ 3682..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+%+%@ %+%%--,,,,,,,,,,,,,,,,,,......... 3683............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+-+% %----,,,,,,,,,,,,,,,,,,.......... 3684.............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%@+---,,,,,,,,,,,,,,,,,,,.......... 3685...............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,........... 3686................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%+ +--,,,,,,,,,,,,,,,,,............ 3687..................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++----,,,,,,,,,,,,,,,,,............. 3688...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,---@-----,,,,,,,,,,,,,,,,,............. 3689.....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,.............. 3690 .....................................,,,,,,,,,,,,,,,,,,,,,,,,,,--%,,,,,,,,,,,,,,,,,,,,............... 3691 .......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................. 3692 ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................. 3693 ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................... 3694 .........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................... 3695 ..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................... 3696 ............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,........................ 3697 .............................................,,,,,,,,,,,,,,,,,,,,,,,,.......................... 3698 ................................................,,,,,,,,,,,,,,,,,............................. 3699 .....................................................,,,,.................................... 3700 ........................................................................................... 3701 ......................................................................................... 3702 ...................................................................................... 3703 .................................................................................... 3704 ................................................................................. 3705 .............................................................................. 3706 ........................................................................... 3707 ........................................................................ 3708# 3709# MDEV-17871: EXPLAIN for query with not used recursive cte 3710# 3711create table t1 (a int); 3712insert into t1 values (2), (1), (4), (3); 3713explain extended 3714with recursive cte as 3715(select * from t1 where a=1 union select a+1 from cte where a<3) 3716select * from cte as t; 3717id select_type table type possible_keys key key_len ref rows filtered Extra 37181 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 37192 DERIVED t1 ALL NULL NULL NULL NULL 4 100.00 Using where 37203 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 100.00 Using where 3721NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 3722Warnings: 3723Note 1003 with recursive cte as (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union /* select#3 */ select `cte`.`a` + 1 AS `a+1` from `cte` where `cte`.`a` < 3)/* select#1 */ select `t`.`a` AS `a` from `cte` `t` 3724with recursive cte as 3725(select * from t1 where a=1 union select a+1 from cte where a<3) 3726select * from cte as t; 3727a 37281 37292 37303 3731explain extended 3732with recursive cte as 3733(select * from t1 where a=1 union select a+1 from cte where a<3) 3734select * from t1 as t; 3735id select_type table type possible_keys key key_len ref rows filtered Extra 37361 PRIMARY t ALL NULL NULL NULL NULL 4 100.00 3737Warnings: 3738Note 1003 with recursive cte as (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union /* select#3 */ select `cte`.`a` + 1 AS `a+1` from `cte` where `cte`.`a` < 3)/* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t1` `t` 3739with recursive cte as 3740(select * from t1 where a=1 union select a+1 from cte where a<3) 3741select * from t1 as t; 3742a 37432 37441 37454 37463 3747create table t2 ( i1 int, i2 int); 3748insert into t2 values (1,1),(2,2); 3749explain 3750with recursive cte as 3751( select * from t2 union select s1.* from t2 as s1, cte where s1.i1 = cte.i2 ) 3752select * from t2 as t; 3753id select_type table type possible_keys key key_len ref rows Extra 37541 PRIMARY t ALL NULL NULL NULL NULL 2 3755drop table t1,t2; 3756# 3757# MDEV-22042: ANALYZE of query using stored function and recursive CTE 3758# 3759create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam; 3760insert into t1 values (1,1),(2,2),(3,3); 3761create table t2 ( 3762a2 varchar(20) primary key, b1 varchar(20), key (b1) 3763) engine=myisam; 3764insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); 3765insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17); 3766create function f1(id varchar(20)) returns varchar(50) 3767begin 3768declare res varchar (50); 3769select a2 into res from t2 where a2=id and b1=1 limit 1; 3770return res; 3771end$$ 3772select fv 3773from (select t1.a1, f1(t1.a2) fv from t1) dt 3774where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' 3775 union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) 3776select a2 from cte); 3777fv 3778NULL 3779explain select fv 3780from (select t1.a1, f1(t1.a2) fv from t1) dt 3781where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' 3782 union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) 3783select a2 from cte); 3784id select_type table type possible_keys key key_len ref rows Extra 37851 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 37861 PRIMARY <derived3> ref key0 key0 23 test.t1.a1 1 FirstMatch(t1) 37873 DERIVED t2 const PRIMARY PRIMARY 22 const 1 Using index 37884 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 2 Using where 37894 RECURSIVE UNION tt2 ref b1 b1 23 cte.a2 2 3790NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL 3791analyze format=json select fv 3792from (select t1.a1, f1(t1.a2) fv from t1) dt 3793where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' 3794 union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) 3795select a2 from cte); 3796ANALYZE 3797{ 3798 "query_block": { 3799 "select_id": 1, 3800 "r_loops": 1, 3801 "r_total_time_ms": "REPLACED", 3802 "table": { 3803 "table_name": "t1", 3804 "access_type": "ALL", 3805 "r_loops": 1, 3806 "rows": 3, 3807 "r_rows": 3, 3808 "r_table_time_ms": "REPLACED", 3809 "r_other_time_ms": "REPLACED", 3810 "filtered": 100, 3811 "r_filtered": 100, 3812 "attached_condition": "t1.a1 is not null" 3813 }, 3814 "table": { 3815 "table_name": "<derived3>", 3816 "access_type": "ref", 3817 "possible_keys": ["key0"], 3818 "key": "key0", 3819 "key_length": "23", 3820 "used_key_parts": ["a2"], 3821 "ref": ["test.t1.a1"], 3822 "r_loops": 3, 3823 "rows": 1, 3824 "r_rows": 0.333333333, 3825 "r_table_time_ms": "REPLACED", 3826 "r_other_time_ms": "REPLACED", 3827 "filtered": 100, 3828 "r_filtered": 100, 3829 "first_match": "t1", 3830 "materialized": { 3831 "query_block": { 3832 "recursive_union": { 3833 "table_name": "<union3,4>", 3834 "access_type": "ALL", 3835 "r_loops": 0, 3836 "r_rows": null, 3837 "query_specifications": [ 3838 { 3839 "query_block": { 3840 "select_id": 3, 3841 "r_loops": 1, 3842 "r_total_time_ms": "REPLACED", 3843 "table": { 3844 "table_name": "t2", 3845 "access_type": "const", 3846 "possible_keys": ["PRIMARY"], 3847 "key": "PRIMARY", 3848 "key_length": "22", 3849 "used_key_parts": ["a2"], 3850 "ref": ["const"], 3851 "r_loops": 0, 3852 "rows": 1, 3853 "r_rows": null, 3854 "filtered": 100, 3855 "r_filtered": null, 3856 "using_index": true 3857 } 3858 } 3859 }, 3860 { 3861 "query_block": { 3862 "select_id": 4, 3863 "operation": "UNION", 3864 "r_loops": 1, 3865 "r_total_time_ms": "REPLACED", 3866 "table": { 3867 "table_name": "<derived3>", 3868 "access_type": "ALL", 3869 "r_loops": 1, 3870 "rows": 2, 3871 "r_rows": 1, 3872 "r_table_time_ms": "REPLACED", 3873 "r_other_time_ms": "REPLACED", 3874 "filtered": 100, 3875 "r_filtered": 100, 3876 "attached_condition": "cte.a2 is not null" 3877 }, 3878 "table": { 3879 "table_name": "tt2", 3880 "access_type": "ref", 3881 "possible_keys": ["b1"], 3882 "key": "b1", 3883 "key_length": "23", 3884 "used_key_parts": ["b1"], 3885 "ref": ["cte.a2"], 3886 "r_loops": 1, 3887 "rows": 2, 3888 "r_rows": 1, 3889 "r_table_time_ms": "REPLACED", 3890 "r_other_time_ms": "REPLACED", 3891 "filtered": 100, 3892 "r_filtered": 100 3893 } 3894 } 3895 } 3896 ] 3897 } 3898 } 3899 } 3900 } 3901 } 3902} 3903drop function f1; 3904drop table t1,t2; 3905# 3906# MDEV-22748: two materialized CTEs using the same recursive CTE 3907# (see also test case for MDEV-17024) 3908# 3909CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; 3910INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); 3911CREATE TABLE t2 (id int, tm date); 3912INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); 3913CREATE TABLE t3 (id int, tm date); 3914INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); 3915WITH RECURSIVE 3916cte AS 3917(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn 3918FROM t1 3919UNION ALL 3920SELECT YEAR(cte.st + INTERVAL 1 MONTH), 3921cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY 3922FROM cte JOIN t1 3923WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), 3924cte2 AS (SELECT YEAR, COUNT(*) 3925FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), 3926cte3 AS (SELECT YEAR, COUNT(*) 3927FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) 3928SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); 3929YEAR d1 d2 39302018 2018-01-01 2018-09-20 3931EXPLAIN EXTENDED WITH RECURSIVE 3932cte AS 3933(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn 3934FROM t1 3935UNION ALL 3936SELECT YEAR(cte.st + INTERVAL 1 MONTH), 3937cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY 3938FROM cte JOIN t1 3939WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), 3940cte2 AS (SELECT YEAR, COUNT(*) 3941FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), 3942cte3 AS (SELECT YEAR, COUNT(*) 3943FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) 3944SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); 3945id select_type table type possible_keys key key_len ref rows filtered Extra 39461 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 39471 PRIMARY <derived5> ref key0 key0 5 const 0 0.00 39481 PRIMARY <derived4> ref key0 key0 5 const 0 0.00 39492 DERIVED t1 system NULL NULL NULL NULL 1 100.00 39503 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00 39513 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 3952NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 39534 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 39544 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 39555 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 39565 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 3957Warnings: 3958Note 1003 with recursive cte as (/* select#2 */ select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all /* select#3 */ select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (/* select#4 */ select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (/* select#5 */ select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)/* select#1 */ select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where `cte3`.`YEAR` = 2018 and `cte2`.`YEAR` = 2018 3959PREPARE stmt FROM "WITH RECURSIVE 3960cte AS 3961(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn 3962FROM t1 3963UNION ALL 3964SELECT YEAR(cte.st + INTERVAL 1 MONTH), 3965cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY 3966FROM cte JOIN t1 3967WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), 3968cte2 AS (SELECT YEAR, COUNT(*) 3969FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), 3970cte3 AS (SELECT YEAR, COUNT(*) 3971FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) 3972SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)"; 3973EXECUTE stmt; 3974YEAR d1 d2 39752018 2018-01-01 2018-09-20 3976EXECUTE stmt; 3977YEAR d1 d2 39782018 2018-01-01 2018-09-20 3979CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); 3980CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE 3981cte AS 3982(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn 3983FROM t1 3984UNION ALL 3985SELECT YEAR(cte.st + INTERVAL 1 MONTH), 3986cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY 3987FROM cte JOIN t1 3988WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), 3989cte2 AS (SELECT YEAR, COUNT(*) 3990FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), 3991cte3 AS (SELECT YEAR, COUNT(*) 3992FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) 3993SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); 3994CALL p(); 3995SELECT * FROM t4; 3996YEAR d1 d2 39972018 2018-01-01 2018-09-20 3998DROP PROCEDURE p; 3999DROP TABLE t1,t2,t3,t4; 4000# 4001# MDEV-23619: recursive CTE used only in the second operand of UNION 4002# 4003create table t1 ( 4004a bigint(10) not null auto_increment, 4005b int(5) not null, 4006c bigint(10) default null, 4007primary key (a) 4008) engine myisam; 4009insert into t1 values 4010(1,3,12), (2,7,15), (3,1,3), (4,3,1); 4011explain with recursive r_cte as 4012( select * from t1 as s 4013union 4014select t1.* from t1, r_cte as r where t1.c = r.a ) 4015select 0 as b FROM dual union all select b FROM r_cte as t; 4016id select_type table type possible_keys key key_len ref rows Extra 40171 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 40182 DERIVED s ALL NULL NULL NULL NULL 4 40193 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where 40203 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2 4021NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 40224 UNION <derived2> ALL NULL NULL NULL NULL 4 4023with recursive r_cte as 4024( select * from t1 as s 4025union 4026select t1.* from t1, r_cte as r where t1.c = r.a ) 4027select 0 as b FROM dual union all select b FROM r_cte as t; 4028b 40290 40303 40317 40321 40333 4034analyze format=json with recursive r_cte as 4035( select * from t1 as s 4036union 4037select t1.* from t1, r_cte as r where t1.c = r.a ) 4038select 0 as b FROM dual union all select b FROM r_cte as t; 4039ANALYZE 4040{ 4041 "query_block": { 4042 "union_result": { 4043 "table_name": "<union1,4>", 4044 "access_type": "ALL", 4045 "r_loops": 0, 4046 "r_rows": null, 4047 "query_specifications": [ 4048 { 4049 "query_block": { 4050 "select_id": 1, 4051 "table": { 4052 "message": "No tables used" 4053 } 4054 } 4055 }, 4056 { 4057 "query_block": { 4058 "select_id": 4, 4059 "operation": "UNION", 4060 "r_loops": 1, 4061 "r_total_time_ms": "REPLACED", 4062 "table": { 4063 "table_name": "<derived2>", 4064 "access_type": "ALL", 4065 "r_loops": 1, 4066 "rows": 4, 4067 "r_rows": 4, 4068 "r_table_time_ms": "REPLACED", 4069 "r_other_time_ms": "REPLACED", 4070 "filtered": 100, 4071 "r_filtered": 100, 4072 "materialized": { 4073 "query_block": { 4074 "recursive_union": { 4075 "table_name": "<union2,3>", 4076 "access_type": "ALL", 4077 "r_loops": 0, 4078 "r_rows": null, 4079 "query_specifications": [ 4080 { 4081 "query_block": { 4082 "select_id": 2, 4083 "r_loops": 1, 4084 "r_total_time_ms": "REPLACED", 4085 "table": { 4086 "table_name": "s", 4087 "access_type": "ALL", 4088 "r_loops": 1, 4089 "rows": 4, 4090 "r_rows": 4, 4091 "r_table_time_ms": "REPLACED", 4092 "r_other_time_ms": "REPLACED", 4093 "filtered": 100, 4094 "r_filtered": 100 4095 } 4096 } 4097 }, 4098 { 4099 "query_block": { 4100 "select_id": 3, 4101 "operation": "UNION", 4102 "r_loops": 1, 4103 "r_total_time_ms": "REPLACED", 4104 "table": { 4105 "table_name": "t1", 4106 "access_type": "ALL", 4107 "r_loops": 1, 4108 "rows": 4, 4109 "r_rows": 4, 4110 "r_table_time_ms": "REPLACED", 4111 "r_other_time_ms": "REPLACED", 4112 "filtered": 100, 4113 "r_filtered": 100, 4114 "attached_condition": "t1.c is not null" 4115 }, 4116 "table": { 4117 "table_name": "<derived2>", 4118 "access_type": "ref", 4119 "possible_keys": ["key0"], 4120 "key": "key0", 4121 "key_length": "9", 4122 "used_key_parts": ["a"], 4123 "ref": ["test.t1.c"], 4124 "r_loops": 4, 4125 "rows": 2, 4126 "r_rows": 0.5, 4127 "r_table_time_ms": "REPLACED", 4128 "r_other_time_ms": "REPLACED", 4129 "filtered": 100, 4130 "r_filtered": 100 4131 } 4132 } 4133 } 4134 ] 4135 } 4136 } 4137 } 4138 } 4139 } 4140 } 4141 ] 4142 } 4143 } 4144} 4145prepare stmt from "with recursive r_cte as 4146( select * from t1 as s 4147union 4148select t1.* from t1, r_cte as r where t1.c = r.a ) 4149select 0 as b FROM dual union all select b FROM r_cte as t"; 4150execute stmt; 4151b 41520 41533 41547 41551 41563 4157execute stmt; 4158b 41590 41603 41617 41621 41633 4164deallocate prepare stmt; 4165#checking hanging cte that uses a recursive cte 4166explain with h_cte as 4167( with recursive r_cte as 4168( select * from t1 as s 4169union 4170select t1.* from t1, r_cte as r where t1.c = r.a ) 4171select 0 as b FROM dual union all select b FROM r_cte as t) 4172select * from t1 as tt; 4173id select_type table type possible_keys key key_len ref rows Extra 41741 PRIMARY tt ALL NULL NULL NULL NULL 4 4175with h_cte as 4176( with recursive r_cte as 4177( select * from t1 as s 4178union 4179select t1.* from t1, r_cte as r where t1.c = r.a ) 4180select 0 as b FROM dual union all select b FROM r_cte as t) 4181select * from t1 as tt; 4182a b c 41831 3 12 41842 7 15 41853 1 3 41864 3 1 4187analyze format=json with h_cte as 4188( with recursive r_cte as 4189( select * from t1 as s 4190union 4191select t1.* from t1, r_cte as r where t1.c = r.a ) 4192select 0 as b FROM dual union all select b FROM r_cte as t) 4193select * from t1 as tt; 4194ANALYZE 4195{ 4196 "query_block": { 4197 "select_id": 1, 4198 "r_loops": 1, 4199 "r_total_time_ms": "REPLACED", 4200 "table": { 4201 "table_name": "tt", 4202 "access_type": "ALL", 4203 "r_loops": 1, 4204 "rows": 4, 4205 "r_rows": 4, 4206 "r_table_time_ms": "REPLACED", 4207 "r_other_time_ms": "REPLACED", 4208 "filtered": 100, 4209 "r_filtered": 100 4210 } 4211 } 4212} 4213prepare stmt from "with h_cte as 4214( with recursive r_cte as 4215( select * from t1 as s 4216union 4217select t1.* from t1, r_cte as r where t1.c = r.a ) 4218select 0 as b FROM dual union all select b FROM r_cte as t) 4219select * from t1 as tt"; 4220execute stmt; 4221a b c 42221 3 12 42232 7 15 42243 1 3 42254 3 1 4226execute stmt; 4227a b c 42281 3 12 42292 7 15 42303 1 3 42314 3 1 4232deallocate prepare stmt; 4233drop table t1; 4234# 4235# MDEV-24019: query with recursive CTE when no default database is set 4236# 4237drop database test; 4238with recursive a as 4239(select 1 from dual union select * from a as r) 4240select * from a; 42411 42421 4243create database db1; 4244create table db1.t1 (a int); 4245insert into db1.t1 values (3), (7), (1); 4246with recursive cte as 4247(select * from db1.t1 union select * from (select * from cte) as t) 4248select * from cte; 4249a 42503 42517 42521 4253explain with recursive cte as 4254(select * from db1.t1 union select * from (select * from cte) as t) 4255select * from cte; 4256id select_type table type possible_keys key key_len ref rows Extra 42571 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 42582 DERIVED t1 ALL NULL NULL NULL NULL 3 42593 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 3 4260NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 4261prepare stmt from "with recursive cte as 4262(select * from db1.t1 union select * from (select * from cte) as t) 4263select * from cte"; 4264execute stmt; 4265a 42663 42677 42681 4269execute stmt; 4270a 42713 42727 42731 4274deallocate prepare stmt; 4275drop database db1; 4276create database test; 4277use test; 4278# 4279# MDEV-23406: query with mutually recursive CTEs when big_tables=1 4280# 4281set @save_big_tables=@@big_tables; 4282set big_tables=1; 4283Warnings: 4284Warning 1287 '@@big_tables' is deprecated and will be removed in a future release 4285create table folks(id int, name char(32), dob date, father int, mother int); 4286insert into folks values 4287(100, 'Me', '2000-01-01', 20, 30), 4288(20, 'Dad', '1970-02-02', 10, 9), 4289(30, 'Mom', '1975-03-03', 8, 7), 4290(10, 'Grandpa Bill', '1940-04-05', null, null), 4291(9, 'Grandma Ann', '1941-10-15', null, null), 4292(25, 'Uncle Jim', '1968-11-18', 8, 7), 4293(98, 'Sister Amy', '2001-06-20', 20, 30), 4294(7, 'Grandma Sally', '1943-08-23', null, 6), 4295(8, 'Grandpa Ben', '1940-10-21', null, null), 4296(6, 'Grandgrandma Martha', '1923-05-17', null, null), 4297(67, 'Cousin Eddie', '1992-02-28', 25, 27), 4298(27, 'Auntie Melinda', '1971-03-29', null, null); 4299with recursive 4300ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 4301w_id, w_name, w_dob, w_father, w_mother) 4302as 4303( 4304select h.*, w.* 4305from folks h, folks w, coupled_ancestors a 4306where a.father = h.id AND a.mother = w.id 4307union 4308select h.*, w.* 4309from folks v, folks h, folks w 4310where v.name = 'Me' and 4311(v.father = h.id AND v.mother= w.id) 4312), 4313coupled_ancestors (id, name, dob, father, mother) 4314as 4315( 4316select h_id, h_name, h_dob, h_father, h_mother 4317from ancestor_couples 4318union 4319select w_id, w_name, w_dob, w_father, w_mother 4320from ancestor_couples 4321) 4322select h_name, h_dob, w_name, w_dob 4323from ancestor_couples; 4324h_name h_dob w_name w_dob 4325Dad 1970-02-02 Mom 1975-03-03 4326Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 4327Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 4328explain with recursive 4329ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 4330w_id, w_name, w_dob, w_father, w_mother) 4331as 4332( 4333select h.*, w.* 4334from folks h, folks w, coupled_ancestors a 4335where a.father = h.id AND a.mother = w.id 4336union 4337select h.*, w.* 4338from folks v, folks h, folks w 4339where v.name = 'Me' and 4340(v.father = h.id AND v.mother= w.id) 4341), 4342coupled_ancestors (id, name, dob, father, mother) 4343as 4344( 4345select h_id, h_name, h_dob, h_father, h_mother 4346from ancestor_couples 4347union 4348select w_id, w_name, w_dob, w_father, w_mother 4349from ancestor_couples 4350) 4351select h_name, h_dob, w_name, w_dob 4352from ancestor_couples; 4353id select_type table type possible_keys key key_len ref rows Extra 43541 PRIMARY <derived3> ALL NULL NULL NULL NULL 1728 43554 DERIVED <derived3> ALL NULL NULL NULL NULL 1728 43565 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 1728 4357NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL 43583 DERIVED v ALL NULL NULL NULL NULL 12 Using where 43593 DERIVED h ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 43603 DERIVED w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) 43612 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 2 43622 RECURSIVE UNION h ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 43632 RECURSIVE UNION w ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) 4364NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL 4365prepare stmt from "with recursive 4366ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 4367w_id, w_name, w_dob, w_father, w_mother) 4368as 4369( 4370select h.*, w.* 4371from folks h, folks w, coupled_ancestors a 4372where a.father = h.id AND a.mother = w.id 4373union 4374select h.*, w.* 4375from folks v, folks h, folks w 4376where v.name = 'Me' and 4377(v.father = h.id AND v.mother= w.id) 4378), 4379coupled_ancestors (id, name, dob, father, mother) 4380as 4381( 4382select h_id, h_name, h_dob, h_father, h_mother 4383from ancestor_couples 4384union 4385select w_id, w_name, w_dob, w_father, w_mother 4386from ancestor_couples 4387) 4388select h_name, h_dob, w_name, w_dob 4389from ancestor_couples"; 4390execute stmt; 4391h_name h_dob w_name w_dob 4392Dad 1970-02-02 Mom 1975-03-03 4393Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 4394Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 4395execute stmt; 4396h_name h_dob w_name w_dob 4397Dad 1970-02-02 Mom 1975-03-03 4398Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 4399Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 4400deallocate prepare stmt; 4401with recursive 4402ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 4403w_id, w_name, w_dob, w_father, w_mother) 4404as 4405( 4406select h.*, w.* 4407from folks h, folks w, coupled_ancestors a 4408where a.father = h.id AND a.mother = w.id 4409), 4410coupled_ancestors (id, name, dob, father, mother) 4411as 4412( 4413select * 4414from folks 4415where name = 'Me' 4416 union all 4417select h_id, h_name, h_dob, h_father, h_mother 4418from ancestor_couples 4419union all 4420select w_id, w_name, w_dob, w_father, w_mother 4421from ancestor_couples 4422) 4423select h_name, h_dob, w_name, w_dob 4424from ancestor_couples; 4425h_name h_dob w_name w_dob 4426Dad 1970-02-02 Mom 1975-03-03 4427Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 4428Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 4429explain with recursive 4430ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 4431w_id, w_name, w_dob, w_father, w_mother) 4432as 4433( 4434select h.*, w.* 4435from folks h, folks w, coupled_ancestors a 4436where a.father = h.id AND a.mother = w.id 4437), 4438coupled_ancestors (id, name, dob, father, mother) 4439as 4440( 4441select * 4442from folks 4443where name = 'Me' 4444 union all 4445select h_id, h_name, h_dob, h_father, h_mother 4446from ancestor_couples 4447union all 4448select w_id, w_name, w_dob, w_father, w_mother 4449from ancestor_couples 4450) 4451select h_name, h_dob, w_name, w_dob 4452from ancestor_couples; 4453id select_type table type possible_keys key key_len ref rows Extra 44541 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 44553 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 44564 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 44575 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 4458NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL 44592 DERIVED h ALL NULL NULL NULL NULL 12 44602 DERIVED w ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join) 44612 DERIVED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (incremental, BNL join) 4462prepare stmt from "with recursive 4463ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 4464w_id, w_name, w_dob, w_father, w_mother) 4465as 4466( 4467select h.*, w.* 4468from folks h, folks w, coupled_ancestors a 4469where a.father = h.id AND a.mother = w.id 4470), 4471coupled_ancestors (id, name, dob, father, mother) 4472as 4473( 4474select * 4475from folks 4476where name = 'Me' 4477 union all 4478select h_id, h_name, h_dob, h_father, h_mother 4479from ancestor_couples 4480union all 4481select w_id, w_name, w_dob, w_father, w_mother 4482from ancestor_couples 4483) 4484select h_name, h_dob, w_name, w_dob 4485from ancestor_couples"; 4486execute stmt; 4487h_name h_dob w_name w_dob 4488Dad 1970-02-02 Mom 1975-03-03 4489Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 4490Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 4491execute stmt; 4492h_name h_dob w_name w_dob 4493Dad 1970-02-02 Mom 1975-03-03 4494Grandpa Bill 1940-04-05 Grandma Ann 1941-10-15 4495Grandpa Ben 1940-10-21 Grandma Sally 1943-08-23 4496deallocate prepare stmt; 4497drop table folks; 4498set big_tables=@save_big_tables; 4499Warnings: 4500Warning 1287 '@@big_tables' is deprecated and will be removed in a future release 4501# 4502# MDEV-26135: execution of PS for query with hanging recursive CTE 4503# 4504create table t1 (a int); 4505insert into t1 values (5), (7); 4506create table t2 (b int); 4507insert into t2 values (3), (7), (1); 4508with recursive r as (select a from t1 union select a+1 from r where a < 10) 4509select * from t2; 4510b 45113 45127 45131 4514prepare stmt from "with recursive r as (select a from t1 union select a+1 from r where a < 10) 4515select * from t2"; 4516execute stmt; 4517b 45183 45197 45201 4521execute stmt; 4522b 45233 45247 45251 4526deallocate prepare stmt; 4527drop table t1,t2; 4528# 4529# MDEV-26189: Unknown column reference within hanging recursive CTE 4530# 4531create table t1 (a int); 4532insert into t1 values (3), (7), (1); 4533with recursive 4534r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) 4535select * from t1 as t; 4536ERROR 42S22: Unknown column 'r.b' in 'where clause' 4537explain with recursive 4538r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) 4539select * from t1 as t; 4540ERROR 42S22: Unknown column 'r.b' in 'where clause' 4541create procedure sp1() with recursive 4542r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) 4543select * from t1 as t; 4544call sp1(); 4545ERROR 42S22: Unknown column 'r.b' in 'where clause' 4546call sp1(); 4547ERROR 42S22: Unknown column 'r.b' in 'where clause' 4548with recursive 4549r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) 4550select * from t1 as t; 4551ERROR 42S22: Unknown column 's1.b' in 'where clause' 4552explain with recursive 4553r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) 4554select * from t1 as t; 4555ERROR 42S22: Unknown column 's1.b' in 'where clause' 4556create procedure sp2() with recursive 4557r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) 4558select * from t1 as t; 4559call sp2(); 4560ERROR 42S22: Unknown column 's1.b' in 'where clause' 4561call sp2(); 4562ERROR 42S22: Unknown column 's1.b' in 'where clause' 4563drop procedure sp1; 4564drop procedure sp2; 4565drop table t1; 4566# 4567# MDEV-26202: Recursive CTE used indirectly twice 4568# (fixed by the patch forMDEV-26025) 4569# 4570with recursive 4571rcte as ( SELECT 1 AS a 4572UNION ALL 4573SELECT cast(a + 1 as unsigned int) FROM rcte WHERE a < 3), 4574cte1 AS (SELECT a FROM rcte), 4575cte2 AS (SELECT a FROM cte1), 4576cte3 AS ( SELECT a FROM cte2) 4577SELECT * FROM cte2, cte3; 4578a a 45791 1 45802 1 45813 1 45821 2 45832 2 45843 2 45851 3 45862 3 45873 3 4588# 4589# End of 10.2 tests 4590# 4591# 4592# MDEV-14217 [db crash] Recursive CTE when SELECT includes new field 4593# 4594CREATE TEMPORARY TABLE a_tbl ( 4595a VARCHAR(33) PRIMARY KEY, 4596b VARCHAR(33) 4597); 4598INSERT INTO a_tbl VALUES ('block0', 'block0'), ('block1', NULL); 4599WITH RECURSIVE Q0 AS ( 4600SELECT T0.a, T0.b, 5 4601FROM a_tbl T0 4602WHERE b IS NULL 4603UNION ALL 4604SELECT T1.a, T1.b 4605FROM Q0 4606JOIN a_tbl T1 4607ON T1.a=Q0.a 4608) SELECT distinct(Q0.a), Q0.b 4609FROM Q0; 4610ERROR 21000: The used SELECT statements have a different number of columns 4611DROP TABLE a_tbl; 4612WITH RECURSIVE x AS (SELECT 1,2 UNION ALL SELECT 1 FROM x) SELECT * FROM x; 4613ERROR 21000: The used SELECT statements have a different number of columns 4614# 4615# MDEV-15162: Setting user variable in recursive CTE 4616# 4617SET @c=1; 4618WITH RECURSIVE cte AS 4619(SELECT 5 4620UNION 4621SELECT @c:=@c+1 FROM cte WHERE @c<3) 4622SELECT * FROM cte; 46235 46245 46252 46263 4627# 4628# MDEV-14883: recursive references in operands of INTERSECT / EXCEPT 4629# 4630create table flights 4631(departure varchar(32), 4632arrival varchar(32), 4633carrier varchar(20), 4634flight_number char(7)); 4635insert into flights values 4636('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'), 4637('Seattle', 'Amsterdam', 'KLM', 'KL 6032'), 4638('Seattle', 'Chicago', 'American', 'AA 2573'), 4639('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'), 4640('Chicago', 'New York', 'American', 'AA 375'), 4641('Chicago', 'Montreal', 'Air Canada', 'AC 3053'), 4642('Los Angeles', 'New York', 'Delta', 'DL 1197'), 4643('New York', 'London', 'British Airways', 'BA 1511'), 4644('London', 'Moscow', 'British Airways', 'BA 233'), 4645('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'), 4646('Moscow', 'Dubai', 'Emirates', 'EK 2421'), 4647('Dubai', 'Tokyo', 'Emirates', 'EK 318'), 4648('Dubai', 'Bangkok', 'Emirates', 'EK 2142'), 4649('Beijing', 'Bangkok', 'Air China', 'CA 757'), 4650('Beijing', 'Tokyo', 'Air China', 'CA 6653'), 4651('Moscow', 'Bangkok', 'Aeroflot', 'SU 270'), 4652('New York', 'Reykjavik', 'Icelandair', 'FL 416'), 4653('New York', 'Paris', 'Air France', 'AF 23'), 4654('Amsterdam', 'Moscow', 'KLM', 'KL 903'), 4655('Frankfurt', 'Dubai', 'Lufthansa', 'LH 630'), 4656('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'), 4657('Reykjavik', 'London', 'British Airways', 'BA 2229'), 4658('Frankfurt', 'Beijing', 'Air China', 'CA 966'), 4659('Tokyo', 'Seattle', 'ANA', 'NH 178'), 4660('Los Angeles', 'Tokyo', 'ANA', 'NH 175'), 4661('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'), 4662('Montreal', 'Paris', 'Air Canada', 'AC 870'), 4663('London', 'Delhi', 'British Airways', 'BA 143'), 4664('Delhi', 'Bangkok', 'Air India', 'AI 306'), 4665('Delhi', 'Dubai', 'Air India', 'AI 995'), 4666('Dubai', 'Cairo', 'Emirates', 'EK 927'), 4667('Cairo', 'Paris', 'Air France', 'AF 503'), 4668('Amsterdam', 'New York', 'Delta', 'DL 47'), 4669('New York', 'Seattle', 'American', 'AA 45'), 4670('Paris', 'Chicago', 'Air France', 'AF 6734'); 4671create table distances 4672(city1 varchar(32), 4673city2 varchar(32), 4674dist int); 4675insert into distances values 4676('Seattle', 'Frankfurt', 5080), 4677('Seattle', 'Amsterdam', 4859), 4678('Seattle', 'Chicago', 1733), 4679('Seattle', 'Los Angeles', 960), 4680('Chicago', 'New York', 712), 4681('Chicago', 'Montreal', 746), 4682('Los Angeles', 'New York', 2446), 4683('New York', 'London', 3459), 4684('London', 'Moscow', 1554), 4685('Moscow', 'Tokyo', 4647), 4686('Moscow', 'Dubai', 2298), 4687('Dubai', 'Tokyo', 4929), 4688('Dubai', 'Bangkok', 3050), 4689('Beijing', 'Bangkok', 2046), 4690('Beijing', 'Tokyo', 1301), 4691('Moscow', 'Bangkok', 4390), 4692('New York', 'Reykjavik', 2613), 4693('New York', 'Paris', 3625), 4694('Amsterdam', 'Moscow', 1334), 4695('Frankfurt', 'Dubai', 3003), 4696('Frankfurt', 'Moscow', 1256), 4697('Reykjavik', 'London', 1173), 4698('Frankfurt', 'Beijing', 4836), 4699('Tokyo', 'Seattle', 4783), 4700('Los Angeles', 'Tokyo', 5479), 4701('Moscow', 'Los Angeles', 6071), 4702('Moscow', 'Reykjavik', 2052), 4703('Montreal', 'Paris', 3425), 4704('London', 'Delhi', 4159), 4705('London', 'Paris', 214), 4706('Delhi', 'Bangkok', 1810), 4707('Delhi', 'Dubai', 1369), 4708('Delhi', 'Beijing', 2350), 4709('Dubai', 'Cairo', 1501), 4710('Cairo', 'Paris', 1992), 4711('Amsterdam', 'New York', 3643), 4712('New York', 'Seattle', 2402), 4713('Paris', 'Chicago', 4136), 4714('Paris', 'Los Angeles', 5647); 4715with recursive destinations (city) as 4716( 4717select a.arrival from flights a where a.departure = 'Seattle' 4718 union 4719select b.arrival from destinations r, flights b where r.city = b.departure 4720) 4721select * from destinations; 4722city 4723Frankfurt 4724Amsterdam 4725Chicago 4726Los Angeles 4727New York 4728Montreal 4729Moscow 4730Dubai 4731Beijing 4732Tokyo 4733London 4734Bangkok 4735Reykjavik 4736Paris 4737Seattle 4738Cairo 4739Delhi 4740with recursive destinations (city) as 4741( 4742select a.arrival from flights a, distances d 4743where a.departure = 'Seattle' and 4744a.departure = d.city1 and a.arrival = d.city2 and 4745d.dist < 4000 4746union 4747select b.arrival from destinations r, flights b, distances d 4748where r.city = b.departure and 4749b.departure = d.city1 and b.arrival = d.city2 and 4750d.dist < 4000 4751) 4752select * from destinations; 4753city 4754Chicago 4755Los Angeles 4756New York 4757Montreal 4758London 4759Reykjavik 4760Paris 4761Seattle 4762Moscow 4763Dubai 4764Bangkok 4765Cairo 4766set standard_compliant_cte=0; 4767with recursive legs_to_destinations 4768(departure, arrival, dist, leg_no, acc_mileage) as 4769( 4770select a.departure, a.arrival, d.dist, 1, d.dist 4771from flights a, distances d 4772where a.departure = 'Seattle' and 4773a.departure = d.city1 and a.arrival = d.city2 and 4774d.dist < 4000 4775union all 4776select b.departure, b.arrival, d.dist, r.leg_no + 1, r.acc_mileage + d.dist 4777from legs_to_destinations r, flights b, distances d 4778where r.arrival = b.departure and 4779b.departure = d.city1 and b.arrival = d.city2 and 4780d.dist < 4000 and 4781b.arrival not in (select arrival from legs_to_destinations) 4782) 4783select * from legs_to_destinations; 4784departure arrival dist leg_no acc_mileage 4785Seattle Chicago 1733 1 1733 4786Seattle Los Angeles 960 1 960 4787Chicago New York 712 2 2445 4788Chicago Montreal 746 2 2479 4789Los Angeles New York 2446 2 3406 4790New York London 3459 3 6865 4791New York London 3459 3 5904 4792New York Reykjavik 2613 3 6019 4793New York Reykjavik 2613 3 5058 4794New York Paris 3625 3 7031 4795New York Paris 3625 3 6070 4796Montreal Paris 3425 3 5904 4797New York Seattle 2402 3 5808 4798New York Seattle 2402 3 4847 4799London Moscow 1554 4 7458 4800London Moscow 1554 4 8419 4801Moscow Dubai 2298 5 10717 4802Moscow Dubai 2298 5 9756 4803Dubai Bangkok 3050 6 12806 4804Dubai Bangkok 3050 6 13767 4805Dubai Cairo 1501 6 11257 4806Dubai Cairo 1501 6 12218 4807set standard_compliant_cte=default; 4808with recursive destinations (city) as 4809( 4810select a.arrival from flights a, distances d 4811where a.departure = 'Seattle' and 4812a.departure = d.city1 and a.arrival = d.city2 and 4813d.dist < 4000 4814union 4815select b.arrival from destinations r, flights b 4816where r.city = b.departure 4817intersect 4818select city2 from destinations s, distances d 4819where s.city = d.city1 and d.dist < 4000 4820) 4821select * from destinations; 4822city 4823Chicago 4824Los Angeles 4825New York 4826Montreal 4827London 4828Reykjavik 4829Paris 4830Seattle 4831Moscow 4832Dubai 4833Bangkok 4834Cairo 4835with recursive destinations (city) as 4836( 4837select a.arrival from flights a where a.departure = 'Seattle' 4838 union 4839select * from 4840( 4841select b.arrival from destinations r, flights b 4842where r.city = b.departure 4843except 4844select arrival from flights 4845where arrival in 4846('New York', 'London', 'Moscow', 'Dubai', 'Cairo', 'Tokyo') 4847) t 4848) 4849select * from destinations; 4850city 4851Frankfurt 4852Amsterdam 4853Chicago 4854Los Angeles 4855Montreal 4856Beijing 4857Bangkok 4858Paris 4859drop table flights, distances; 4860# 4861# MDEV-15159: Forced nullability of columns in recursive CTE 4862# 4863WITH RECURSIVE cte AS ( 4864SELECT 1 AS a UNION ALL 4865SELECT NULL FROM cte WHERE a IS NOT NULL) 4866SELECT * FROM cte; 4867a 48681 4869NULL 4870CREATE TABLE t1 (a int NOT NULL); 4871INSERT INTO t1 VALUES (0); 4872WITH RECURSIVE cte AS 4873(SELECT a FROM t1 where a=0 UNION SELECT NULL FROM cte) 4874SELECT * FROM cte; 4875a 48760 4877NULL 4878DROP TABLE t1; 4879# End of 10.3 tests 4880# 4881# MDEV-26108: Recursive CTE embedded into another CTE which is used twice 4882# 4883create table t1 (a int); 4884insert into t1 values (5), (7); 4885with cte_e as ( 4886with recursive cte_r as ( 4887select a from t1 union select a+1 as a from cte_r r where a < 10 4888) select * from cte_r 4889) select * from cte_e s1, cte_e s2 where s1.a=s2.a; 4890a a 48915 5 48927 7 48936 6 48948 8 48959 9 489610 10 4897drop table t1; 4898# End of 10.4 tests 4899