1--source include/default_optimizer_switch.inc 2 3create table t1 (a int, b varchar(32)); 4insert into t1 values 5(4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd'); 6insert into t1 values 7(3,'eee'), (7,'bb'), (1,'fff'), (4,'ggg'); 8 9--ERROR ER_UNACCEPTABLE_MUTUAL_RECURSION 10with recursive 11t as 12( 13 select * from t1 where t1.b >= 'c' 14 union 15 select * from r 16), 17r as 18( 19 select * from t 20 union 21 select t1.* from t1,r where r.a+1 = t1.a 22) 23select * from r; 24 25 26--ERROR ER_UNACCEPTABLE_MUTUAL_RECURSION 27with recursive 28a1(a,b) as 29(select * from t1 where t1.a>3 30union 31select * from b1 where b1.a >3 32union 33select * from c1 where c1.a>3), 34b1(a,b) as 35(select * from a1 where a1.b > 'ccc' 36union 37select * from c1 where c1.b > 'ddd'), 38c1(a,b) as 39(select * from a1 where a1.a<6 and a1.b< 'zz' 40union 41select * from b1 where b1.b > 'auu') 42select * from c1; 43 44drop table t1; 45 46 47--echo # WITH RECURSIVE vs just WITH 48 49create table t1 (a int); 50insert into t1 values 51 (0), (1), (2), (3), (4); 52create table t2 (a int); 53insert into t2 values 54 (1), (2), (3), (4), (5); 55 56 57--echo # just WITH : s refers to t defined after s 58--ERROR ER_NO_SUCH_TABLE 59with 60 s(a) as (select t.a + 10 from t), 61 t(a) as (select t1.a from t1) 62select * from s; 63 64--echo # WITH RECURSIVE: s refers to t defined after s 65with recursive 66 s(a) as (select t.a + 10 from t), 67 t(a) as (select t1.a from t1) 68select * from s; 69 70--echo # just WITH : defined t1 is non-recursive and uses base tables t1,t2 71with 72t1 as 73( 74select a from t2 where t2.a=3 75union 76select t2.a from t1,t2 where t1.a+1=t2.a 77) 78select * from t1; 79 80explain 81with 82t1 as 83( 84select a from t2 where t2.a=3 85union 86select t2.a from t1,t2 where t1.a+1=t2.a 87) 88select * from t1; 89 90 91--echo #WITH RECURSIVE : defined t1 is recursive and uses only base table t2 92with recursive 93t1 as 94( 95select a from t2 where t2.a=3 96union 97select t2.a from t1,t2 where t1.a+1=t2.a 98) 99select * from t1; 100 101explain 102with recursive 103t1 as 104( 105select a from t2 where t2.a=3 106union 107select t2.a from t1,t2 where t1.a+1=t2.a 108) 109select * from t1; 110 111--echo # just WITH : types of t1 columns are determined by all parts of union 112 113create view v1 as 114with 115t1 as 116( 117select a from t2 where t2.a=3 118union 119select t2.a+1 from t1,t2 where t1.a=t2.a 120) 121select * from t1; 122 123show columns from v1; 124 125 126--echo # WITH RECURSIVE : types of t1 columns are determined by anchor parts 127 128create view v2 as 129with recursive 130t1 as 131( 132select a from t2 where t2.a=3 133union 134select t2.a+1 from t1,t2 where t1.a=t2.a 135) 136select * from t1; 137 138show columns from v2; 139 140drop view v1,v2; 141 142drop table t1,t2; 143 144 145create table folks(id int, name char(32), dob date, father int, mother int); 146 147insert into folks values 148(100, 'Me', '2000-01-01', 20, 30), 149(20, 'Dad', '1970-02-02', 10, 9), 150(30, 'Mom', '1975-03-03', 8, 7), 151(10, 'Grandpa Bill', '1940-04-05', null, null), 152(9, 'Grandma Ann', '1941-10-15', null, null), 153(25, 'Uncle Jim', '1968-11-18', 8, 7), 154(98, 'Sister Amy', '2001-06-20', 20, 30), 155(7, 'Grandma Sally', '1943-08-23', null, 6), 156(8, 'Grandpa Ben', '1940-10-21', null, null), 157(6, 'Grandgrandma Martha', '1923-05-17', null, null), 158(67, 'Cousin Eddie', '1992-02-28', 25, 27), 159(27, 'Auntie Melinda', '1971-03-29', null, null); 160 161--echo # simple recursion with one anchor and one recursive select 162--echo # the anchor is the first select in the specification 163with recursive 164ancestors 165as 166( 167 select * 168 from folks 169 where name = 'Me' and dob = '2000-01-01' 170 union 171 select p.id, p.name, p.dob, p.father, p.mother 172 from folks as p, ancestors AS a 173 where p.id = a.father or p.id = a.mother 174) 175select * from ancestors; 176 177--echo # simple recursion with one anchor and one recursive select 178--echo # the anchor is the last select in the specification 179with recursive 180ancestors 181as 182( 183 select p.* 184 from folks as p, ancestors AS a 185 where p.id = a.father or p.id = a.mother 186 union 187 select * 188 from folks 189 where name = 'Me' and dob = '2000-01-01' 190) 191select * from ancestors; 192 193--echo # simple recursion with one anchor and one recursive select 194--echo # the anchor is the first select in the specification 195with recursive 196ancestors 197as 198( 199 select * 200 from folks 201 where name = 'Cousin Eddie' 202 union 203 select p.* 204 from folks as p, ancestors as a 205 where p.id = a.father or p.id = a.mother 206) 207select * from ancestors; 208 209--echo # simple recursion with or in anchor and or in recursive part 210with recursive 211ancestors 212as 213( 214 select * 215 from folks 216 where name = 'Me' or name='Sister Amy' 217 union 218 select p.* 219 from folks as p, ancestors as a 220 where p.id = a.father or p.id = a.mother 221) 222select * from ancestors; 223 224--echo # two recursive definition, one uses another 225with recursive 226prev_gen 227as 228( 229 select folks.* 230 from folks, prev_gen 231 where folks.id=prev_gen.father or folks.id=prev_gen.mother 232 union 233 select * 234 from folks 235 where name='Me' 236), 237ancestors 238as 239( 240 select * 241 from folks 242 where name='Me' 243 union 244 select * 245 from ancestors 246 union 247 select * 248 from prev_gen 249) 250select ancestors.name, ancestors.dob from ancestors; 251 252--echo # recursive definition with two attached non-recursive 253with recursive 254ancestors(id,name,dob) 255as 256( 257 with 258 father(child_id,id,name,dob) 259 as 260 ( 261 select folks.id, f.id, f.name, f.dob 262 from folks, folks f 263 where folks.father=f.id 264 265 ), 266 mother(child_id,id,name,dob) 267 as 268 ( 269 select folks.id, m.id, m.name, m.dob 270 from folks, folks m 271 where folks.mother=m.id 272 273 ) 274 select folks.id, folks.name, folks.dob 275 from folks 276 where name='Me' 277 union 278 select f.id, f.name, f.dob 279 from ancestors a, father f 280 where f.child_id=a.id 281 union 282 select m.id, m.name, m.dob 283 from ancestors a, mother m 284 where m.child_id=a.id 285 286) 287select ancestors.name, ancestors.dob from ancestors; 288 289--echo # simple recursion with one anchor and one recursive select 290--echo # the anchor is the first select in the specification 291with recursive 292descendants 293as 294( 295 select * 296 from folks 297 where name = 'Grandpa Bill' 298 union 299 select folks.* 300 from folks, descendants as d 301 where d.id=folks.father or d.id=folks.mother 302) 303select * from descendants; 304 305--echo # simple recursion with one anchor and one recursive select 306--echo # the anchor is the first select in the specification 307with recursive 308descendants 309as 310( 311 select * 312 from folks 313 where name = 'Grandma Sally' 314 union 315 select folks.* 316 from folks, descendants as d 317 where d.id=folks.father or d.id=folks.mother 318) 319select * from descendants; 320 321 322--echo # simple recursive table used three times in the main query 323with recursive 324ancestors 325as 326( 327 select * 328 from folks 329 where name = 'Me' and dob = '2000-01-01' 330 union 331 select p.* 332 from folks as p, ancestors AS a 333 where p.id = a.father OR p.id = a.mother 334) 335select * 336 from ancestors t1, ancestors t2 337 where exists (select * from ancestors a 338 where a.father=t1.id AND a.mother=t2.id); 339 340 341--echo # simple recursive table used three times in the main query 342with 343ancestor_couples(husband, h_dob, wife, w_dob) 344as 345( 346with recursive 347ancestors 348as 349( 350 select * 351 from folks 352 where name = 'Me' 353 union 354 select p.* 355 from folks as p, ancestors AS a 356 where p.id = a.father OR p.id = a.mother 357) 358select t1.name, t1.dob, t2.name, t2.dob 359 from ancestors t1, ancestors t2 360 where exists (select * from ancestors a 361 where a.father=t1.id AND a.mother=t2.id) 362) 363select * from ancestor_couples; 364 365 366--echo # simple recursion with two selects in recursive part 367with recursive 368ancestors 369as 370( 371 select * 372 from folks 373 where name = 'Me' 374 union 375 select p.* 376 from folks as p, ancestors as fa 377 where p.id = fa.father 378 union 379 select p.* 380 from folks as p, ancestors as ma 381 where p.id = ma.mother 382) 383select * from ancestors; 384 385 386--echo # mutual recursion with renaming 387with recursive 388ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 389 w_id, w_name, w_dob, w_father, w_mother) 390as 391( 392 select h.*, w.* 393 from folks h, folks w, coupled_ancestors a 394 where a.father = h.id AND a.mother = w.id 395 union 396 select h.*, w.* 397 from folks v, folks h, folks w 398 where v.name = 'Me' and 399 (v.father = h.id AND v.mother= w.id) 400), 401coupled_ancestors (id, name, dob, father, mother) 402as 403( 404 select h_id, h_name, h_dob, h_father, h_mother 405 from ancestor_couples 406 union 407 select w_id, w_name, w_dob, w_father, w_mother 408 from ancestor_couples 409) 410select h_name, h_dob, w_name, w_dob 411 from ancestor_couples; 412 413 414--echo # mutual recursion with union all 415with recursive 416ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 417 w_id, w_name, w_dob, w_father, w_mother) 418as 419( 420 select h.*, w.* 421 from folks h, folks w, coupled_ancestors a 422 where a.father = h.id AND a.mother = w.id 423 union 424 select h.*, w.* 425 from folks v, folks h, folks w 426 where v.name = 'Me' and 427 (v.father = h.id AND v.mother= w.id) 428), 429coupled_ancestors (id, name, dob, father, mother) 430as 431( 432 select h_id, h_name, h_dob, h_father, h_mother 433 from ancestor_couples 434 union all 435 select w_id, w_name, w_dob, w_father, w_mother 436 from ancestor_couples 437) 438select h_name, h_dob, w_name, w_dob 439 from ancestor_couples; 440 441 442--echo # mutual recursion with renaming 443with recursive 444ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 445 w_id, w_name, w_dob, w_father, w_mother) 446as 447( 448 select h.*, w.* 449 from folks h, folks w, coupled_ancestors a 450 where a.father = h.id AND a.mother = w.id 451 union 452 select h.*, w.* 453 from folks v, folks h, folks w 454 where v.name = 'Me' and 455 (v.father = h.id AND v.mother= w.id) 456), 457coupled_ancestors (id, name, dob, father, mother) 458as 459( 460 select h_id, h_name, h_dob, h_father, h_mother 461 from ancestor_couples 462 union 463 select w_id, w_name, w_dob, w_father, w_mother 464 from ancestor_couples 465) 466select h_name, h_dob, w_name, w_dob 467 from ancestor_couples; 468 469 470--echo # mutual recursion with union all 471with recursive 472ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 473 w_id, w_name, w_dob, w_father, w_mother) 474as 475( 476 select h.*, w.* 477 from folks h, folks w, coupled_ancestors a 478 where a.father = h.id AND a.mother = w.id 479), 480coupled_ancestors (id, name, dob, father, mother) 481as 482( 483 select * 484 from folks 485 where name = 'Me' 486 union all 487 select h_id, h_name, h_dob, h_father, h_mother 488 from ancestor_couples 489 union all 490 select w_id, w_name, w_dob, w_father, w_mother 491 from ancestor_couples 492) 493select h_name, h_dob, w_name, w_dob 494 from ancestor_couples; 495 496--echo # mutual recursion with one select in the first definition 497with recursive 498ancestor_couple_ids(h_id, w_id) 499as 500( 501 select a.father, a.mother 502 from coupled_ancestors a 503 where a.father is not null and a.mother is not null 504), 505coupled_ancestors (id, name, dob, father, mother) 506as 507( 508 select * 509 from folks 510 where name = 'Me' 511 union all 512 select p.* 513 from folks p, ancestor_couple_ids fa 514 where p.id = fa.h_id 515 union all 516 select p.* 517 from folks p, ancestor_couple_ids ma 518 where p.id = ma.w_id 519) 520select * 521 from ancestor_couple_ids; 522 523 524--echo # join of a mutually recursive table with base tables 525with recursive 526ancestor_couple_ids(h_id, w_id) 527as 528( 529 select a.father, a.mother 530 from coupled_ancestors a 531 where a.father is not null and a.mother is not null 532), 533coupled_ancestors (id, name, dob, father, mother) 534as 535( 536 select * 537 from folks 538 where name = 'Me' 539 union all 540 select p.* 541 from folks p, ancestor_couple_ids fa 542 where p.id = fa.h_id 543 union all 544 select p.* 545 from folks p, ancestor_couple_ids ma 546 where p.id = ma.w_id 547) 548select h.name, h.dob, w.name, w.dob 549 from ancestor_couple_ids c, folks h, folks w 550 where c.h_id = h.id and c.w_id= w.id; 551 552 553--echo # join of two mutually recursive tables 554with recursive 555ancestor_couple_ids(h_id, w_id) 556as 557( 558 select a.father, a.mother 559 from coupled_ancestors a 560 where a.father is not null and a.mother is not null 561), 562coupled_ancestors (id, name, dob, father, mother) 563as 564( 565 select * 566 from folks 567 where name = 'Me' 568 union all 569 select p.* 570 from folks p, ancestor_couple_ids fa 571 where p.id = fa.h_id 572 union all 573 select p.* 574 from folks p, ancestor_couple_ids ma 575 where p.id = ma.w_id 576) 577select h.name, h.dob, w.name, w.dob 578 from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w 579 where c.h_id = h.id and c.w_id= w.id; 580 581explain extended 582with recursive 583ancestor_couple_ids(h_id, w_id) 584as 585( 586 select a.father, a.mother 587 from coupled_ancestors a 588 where a.father is not null and a.mother is not null 589), 590coupled_ancestors (id, name, dob, father, mother) 591as 592( 593 select * 594 from folks 595 where name = 'Me' 596 union all 597 select p.* 598 from folks p, ancestor_couple_ids fa 599 where p.id = fa.h_id 600 union all 601 select p.* 602 from folks p, ancestor_couple_ids ma 603 where p.id = ma.w_id 604) 605select h.name, h.dob, w.name, w.dob 606 from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w 607 where c.h_id = h.id and c.w_id= w.id; 608 609 610--echo # simple mutual recursion 611with recursive 612ancestor_couple_ids(h_id, w_id) 613as 614( 615 select a.father, a.mother 616 from coupled_ancestors a 617), 618coupled_ancestors (id, name, dob, father, mother) 619as 620( 621 select * 622 from folks 623 where name = 'Me' 624 union all 625 select p.* 626 from folks p, ancestor_couple_ids fa 627 where p.id = fa.h_id 628 union all 629 select p.* 630 from folks p, ancestor_couple_ids ma 631 where p.id = ma.w_id 632) 633select * 634 from ancestor_couple_ids; 635 636 637--echo # join of two mutually recursive tables 638with recursive 639ancestor_couple_ids(h_id, w_id) 640as 641( 642 select a.father, a.mother 643 from coupled_ancestors a 644), 645coupled_ancestors (id, name, dob, father, mother) 646as 647( 648 select * 649 from folks 650 where name = 'Me' 651 union all 652 select p.* 653 from folks p, ancestor_couple_ids fa 654 where p.id = fa.h_id 655 union all 656 select p.* 657 from folks p, ancestor_couple_ids ma 658 where p.id = ma.w_id 659) 660select h.name, h.dob, w.name, w.dob 661 from ancestor_couple_ids c, coupled_ancestors h, coupled_ancestors w 662 where c.h_id = h.id and c.w_id= w.id; 663 664 665--echo # execution of prepared query using a recursive table 666prepare stmt1 from " 667with recursive 668ancestors 669as 670( 671 select * 672 from folks 673 where name = 'Me' and dob = '2000-01-01' 674 union 675 select p.id, p.name, p.dob, p.father, p.mother 676 from folks as p, ancestors AS a 677 where p.id = a.father or p.id = a.mother 678) 679select * from ancestors; 680"; 681 682execute stmt1; 683execute stmt1; 684 685deallocate prepare stmt1; 686 687 688--echo # view using a recursive table 689create view v1 as 690with recursive 691ancestors 692as 693( 694 select * 695 from folks 696 where name = 'Me' and dob = '2000-01-01' 697 union 698 select p.id, p.name, p.dob, p.father, p.mother 699 from folks as p, ancestors AS a 700 where p.id = a.father or p.id = a.mother 701) 702select * from ancestors; 703 704show create view v1; 705 706select * from v1; 707 708create view v2 as 709with recursive 710ancestors 711as 712( 713 select * 714 from folks 715 where name = 'Me' 716 union 717 select p.* 718 from folks as p, ancestors as fa 719 where p.id = fa.father 720 union 721 select p.* 722 from folks as p, ancestors as ma 723 where p.id = ma.mother 724) 725select * from ancestors; 726 727show create view v2; 728 729select * from v2; 730 731drop view v1,v2; 732 733 734explain extended 735with recursive 736ancestors 737as 738( 739 select * 740 from folks 741 where name = 'Me' and dob = '2000-01-01' 742 union 743 select p.id, p.name, p.dob, p.father, p.mother 744 from folks as p, ancestors AS a 745 where p.id = a.father or p.id = a.mother 746) 747select * from ancestors; 748 749 750--echo # recursive spec with two anchor selects and two recursive ones 751with recursive 752ancestor_ids (id) 753as 754( 755 select father from folks where name = 'Me' 756 union 757 select mother from folks where name = 'Me' 758 union 759 select father from folks, ancestor_ids a where folks.id = a.id 760 union 761 select mother from folks, ancestor_ids a where folks.id = a.id 762), 763ancestors 764as 765( 766 select p.* from folks as p, ancestor_ids as a 767 where p.id = a.id 768) 769select * from ancestors; 770 771 772--echo # recursive spec using union all 773with recursive 774ancestors 775as 776( 777 select * 778 from folks 779 where name = 'Me' 780 union all 781 select p.* 782 from folks as p, ancestors as fa 783 where p.id = fa.father 784 union all 785 select p.* 786 from folks as p, ancestors as ma 787 where p.id = ma.mother 788) 789select * from ancestors; 790 791 792--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE 793with recursive 794ancestor_ids (id, generation) 795as 796( 797 select father, 1 from folks where name = 'Me' and father is not null 798 union all 799 select mother, 1 from folks where name = 'Me' and mother is not null 800 union all 801 select father, fa.generation+1 from folks, ancestor_ids fa 802 where folks.id = fa.id and (father not in (select id from ancestor_ids)) 803 union all 804 select mother, ma.generation+1 from folks, ancestor_ids ma 805 where folks.id = ma.id and (mother not in (select id from ancestor_ids)) 806) 807select generation, name from ancestor_ids a, folks 808 where a.id = folks.id; 809 810set standard_compliant_cte=0; 811 812--ERROR ER_WITH_COL_WRONG_LIST 813with recursive 814ancestor_ids (id, generation) 815as 816( 817 select father from folks where name = 'Me' and father is not null 818 union all 819 select mother from folks where name = 'Me' and mother is not null 820 union all 821 select father, fa.generation+1 from folks, ancestor_ids fa 822 where folks.id = fa.id and (father not in (select id from ancestor_ids)) 823 union all 824 select mother, ma.generation+1 from folks, ancestor_ids ma 825 where folks.id = ma.id and (mother not in (select id from ancestor_ids)) 826) 827select generation, name from ancestor_ids a, folks 828 where a.id = folks.id; 829 830with recursive 831ancestor_ids (id, generation) 832as 833( 834 select father, 1 from folks where name = 'Me' and father is not null 835 union all 836 select mother, 1 from folks where name = 'Me' and mother is not null 837 union all 838 select father, fa.generation+1 from folks, ancestor_ids fa 839 where folks.id = fa.id and father is not null and 840 (father not in (select id from ancestor_ids)) 841 union all 842 select mother, ma.generation+1 from folks, ancestor_ids ma 843 where folks.id = ma.id and mother is not null and 844 (mother not in (select id from ancestor_ids)) 845) 846select generation, name from ancestor_ids a, folks 847 where a.id = folks.id; 848 849set standard_compliant_cte=1; 850 851--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE 852with recursive 853coupled_ancestor_ids (id) 854as 855( 856 select father from folks where name = 'Me' and father is not null 857 union 858 select mother from folks where name = 'Me' and mother is not null 859 union 860 select n.father 861 from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n 862 where folks.father = fa.id and folks.mother = ma.id and 863 (fa.id = n.id or ma.id = n.id) and 864 n.father is not null and n.mother is not null 865 union 866 select n.mother 867 from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n 868 where folks.father = fa.id and folks.mother = ma.id and 869 (fa.id = n.id or ma.id = n.id) and 870 n.father is not null and n.mother is not null 871) 872select p.* from coupled_ancestor_ids a, folks p 873 where a.id = p.id; 874 875set statement standard_compliant_cte=0 for 876with recursive 877coupled_ancestor_ids (id) 878as 879( 880 select father from folks where name = 'Me' and father is not null 881 union 882 select mother from folks where name = 'Me' and mother is not null 883 union 884 select n.father 885 from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n 886 where folks.father = fa.id and folks.mother = ma.id and 887 (fa.id = n.id or ma.id = n.id) and 888 n.father is not null and n.mother is not null 889 union 890 select n.mother 891 from folks, coupled_ancestor_ids fa, coupled_ancestor_ids ma, folks n 892 where folks.father = fa.id and folks.mother = ma.id and 893 (fa.id = n.id or ma.id = n.id) and 894 n.father is not null and n.mother is not null 895) 896select p.* from coupled_ancestor_ids a, folks p 897 where a.id = p.id; 898 899--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE 900with recursive 901ancestor_ids (id) 902as 903( 904 select father from folks where name = 'Me' 905 union 906 select mother from folks where name = 'Me' 907 union 908 select father from folks left join ancestor_ids a on folks.id = a.id 909 union 910 select mother from folks left join ancestor_ids a on folks.id = a.id 911), 912ancestors 913as 914( 915 select p.* from folks as p, ancestor_ids as a 916 where p.id = a.id 917) 918select * from ancestors; 919 920set statement standard_compliant_cte=0 for 921with recursive 922ancestor_ids (id) 923as 924( 925 select father from folks where name = 'Me' 926 union 927 select mother from folks where name = 'Me' 928 union 929 select father from folks left join ancestor_ids a on folks.id = a.id 930 union 931 select mother from folks left join ancestor_ids a on folks.id = a.id 932), 933ancestors 934as 935( 936 select p.* from folks as p, ancestor_ids as a 937 where p.id = a.id 938) 939select * from ancestors; 940 941with recursive 942ancestor_ids (id, generation) 943as 944( 945 select father, 1 from folks where name = 'Me' 946 union 947 select mother, 1 from folks where name = 'Me' 948 union 949 select father, a.generation+1 from folks, ancestor_ids a 950 where folks.id = a.id 951 union 952 select mother, a.generation+1 from folks, ancestor_ids a 953 where folks.id = a.id 954), 955ancestors 956as 957( 958 select generation, name from folks as p, ancestor_ids as a 959 where p.id = a.id 960) 961select * from ancestors; 962 963--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE 964with recursive 965ancestor_ids (id, generation) 966as 967( 968 select father, 1 from folks where name = 'Me' 969 union 970 select mother, 1 from folks where name = 'Me' 971 union 972 select max(father), max(a.generation)+1 from folks, ancestor_ids a 973 where folks.id = a.id 974 group by a.generation 975 union 976 select max(mother), max(a.generation)+1 from folks, ancestor_ids a 977 where folks.id = a.id 978 group by a.generation 979), 980ancestors 981as 982( 983 select generation, name from folks as p, ancestor_ids as a 984 where p.id = a.id 985) 986select * from ancestors; 987 988set statement standard_compliant_cte=0 for 989with recursive 990ancestor_ids (id, generation) 991as 992( 993 select father, 1 from folks where name = 'Me' 994 union 995 select mother, 1 from folks where name = 'Me' 996 union 997 select max(father), a.generation+1 from folks, ancestor_ids a 998 where folks.id = a.id 999 group by a.generation 1000 union 1001 select max(mother), a.generation+1 from folks, ancestor_ids a 1002 where folks.id = a.id 1003 group by a.generation 1004), 1005ancestors 1006as 1007( 1008 select generation, name from folks as p, ancestor_ids as a 1009 where p.id = a.id 1010) 1011select * from ancestors; 1012 1013set statement max_recursive_iterations=1 for 1014with recursive 1015ancestor_ids (id, generation) 1016as 1017( 1018 select father, 1 from folks where name = 'Me' 1019 union 1020 select mother, 1 from folks where name = 'Me' 1021 union 1022 select father, a.generation+1 from folks, ancestor_ids a 1023 where folks.id = a.id 1024 union 1025 select mother, a.generation+1 from folks, ancestor_ids a 1026 where folks.id = a.id 1027), 1028ancestors 1029as 1030( 1031 select generation, name from folks as p, ancestor_ids as a 1032 where p.id = a.id 1033) 1034select * from ancestors; 1035 1036--echo # query with recursive tables using key access 1037 1038alter table folks add primary key (id); 1039 1040explain 1041with recursive 1042ancestors 1043as 1044( 1045 select * 1046 from folks 1047 where name = 'Me' 1048 union 1049 select p.* 1050 from folks as p, ancestors as fa 1051 where p.id = fa.father 1052 union 1053 select p.* 1054 from folks as p, ancestors as ma 1055 where p.id = ma.mother 1056) 1057select * from ancestors; 1058 1059 1060with recursive 1061ancestors 1062as 1063( 1064 select * 1065 from folks 1066 where name = 'Me' 1067 union 1068 select p.* 1069 from folks as p, ancestors as fa 1070 where p.id = fa.father 1071 union 1072 select p.* 1073 from folks as p, ancestors as ma 1074 where p.id = ma.mother 1075) 1076select * from ancestors; 1077 1078 1079--echo # 1080--echo # EXPLAIN FORMAT=JSON on a query where one recursive CTE uses another: 1081--echo # 1082explain 1083with recursive 1084prev_gen 1085as 1086( 1087 select folks.* 1088 from folks, prev_gen 1089 where folks.id=prev_gen.father or folks.id=prev_gen.mother 1090 union 1091 select * 1092 from folks 1093 where name='Me' 1094), 1095ancestors 1096as 1097( 1098 select * 1099 from folks 1100 where name='Me' 1101 union 1102 select * 1103 from ancestors 1104 union 1105 select * 1106 from prev_gen 1107) 1108select ancestors.name, ancestors.dob from ancestors; 1109 1110explain FORMAT=JSON 1111with recursive 1112prev_gen 1113as 1114( 1115 select folks.* 1116 from folks, prev_gen 1117 where folks.id=prev_gen.father or folks.id=prev_gen.mother 1118 union 1119 select * 1120 from folks 1121 where name='Me' 1122), 1123ancestors 1124as 1125( 1126 select * 1127 from folks 1128 where name='Me2' 1129 union 1130 select * 1131 from ancestors where id < 234 1132 union 1133 select * 1134 from prev_gen where id < 345 1135) 1136select ancestors.name, ancestors.dob from ancestors; 1137 1138--echo # 1139explain format=json 1140with recursive 1141ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 1142 w_id, w_name, w_dob, w_father, w_mother) 1143as 1144( 1145 select h.*, w.* 1146 from folks h, folks w, coupled_ancestors a 1147 where a.father = h.id AND a.mother = w.id 1148 union 1149 select h.*, w.* 1150 from folks v, folks h, folks w 1151 where v.name = 'Me' and 1152 (v.father = h.id AND v.mother= w.id) 1153), 1154coupled_ancestors (id, name, dob, father, mother) 1155as 1156( 1157 select h_id, h_name, h_dob, h_father, h_mother 1158 from ancestor_couples 1159 union all 1160 select w_id, w_name, w_dob, w_father, w_mother 1161 from ancestor_couples 1162) 1163select h_name, h_dob, w_name, w_dob 1164 from ancestor_couples; 1165 1166 1167create table my_ancestors 1168with recursive 1169ancestor_ids (id) 1170as 1171( 1172 select father from folks where name = 'Me' 1173 union 1174 select mother from folks where name = 'Me' 1175 union 1176 select father from folks, ancestor_ids a where folks.id = a.id 1177 union 1178 select mother from folks, ancestor_ids a where folks.id = a.id 1179) 1180select p.* from folks as p, ancestor_ids as a where p.id = a.id; 1181 1182select * from my_ancestors; 1183 1184delete from my_ancestors; 1185 1186insert into my_ancestors 1187with recursive 1188ancestor_ids (id) 1189as 1190( 1191 select father from folks where name = 'Me' 1192 union 1193 select mother from folks where name = 'Me' 1194 union 1195 select father from folks, ancestor_ids a where folks.id = a.id 1196 union 1197 select mother from folks, ancestor_ids a where folks.id = a.id 1198) 1199select p.* from folks as p, ancestor_ids as a where p.id = a.id; 1200 1201select * from my_ancestors; 1202 1203drop table my_ancestors; 1204 1205# 1206# MDEV-17967 Add a solution of the 8 queens problem to the regression test for CTE 1207# 1208# adapted to MariaDB from https://rosettacode.org/wiki/N-queens_problem#SQL 1209# 1210let $N=4; # 8 takes too long for a test 1211eval WITH RECURSIVE 1212 positions(i) AS ( 1213 VALUES(0) 1214 UNION SELECT ALL 1215 i+1 FROM positions WHERE i < $N*$N-1 1216 ), 1217 solutions(board, n_queens) AS ( 1218 SELECT REPEAT('-', $N*$N), 0 1219 FROM positions 1220 UNION 1221 SELECT 1222 concat(substr(board, 1, i),'*',substr(board, i+2)),n_queens + 1 AS n_queens 1223 FROM positions AS ps, solutions 1224 WHERE n_queens < $N 1225 AND substr(board,1,i) != '*' 1226 AND NOT EXISTS ( 1227 SELECT 1 FROM positions WHERE 1228 substr(board,i+1,1) = '*' AND 1229 ( 1230 i % $N = ps.i % $N OR 1231 i div $N = ps.i div $N OR 1232 i div $N + (i % $N) = ps.i div $N + (ps.i % $N) OR 1233 i div $N - (i % $N) = ps.i div $N - (ps.i % $N) 1234 ) 1235 ) 1236 ) 1237SELECT regexp_replace(board,concat('(',REPEAT('.', $N),')'),'\\\\1\\n') n_queens FROM solutions WHERE n_queens = $N; 1238 1239--echo # 1240--echo # MDEV-10883: execution of prepared statement from SELECT 1241--echo # with recursive CTE that renames columns 1242--echo # 1243 1244prepare stmt from" 1245with recursive 1246ancestor_ids (id) 1247as 1248( 1249 select father from folks where name = 'Me' 1250 union 1251 select mother from folks where name = 'Me' 1252 union 1253 select father from folks, ancestor_ids a where folks.id = a.id 1254 union 1255 select mother from folks, ancestor_ids a where folks.id = a.id 1256) 1257select p.* from folks as p, ancestor_ids as a where p.id = a.id; 1258"; 1259execute stmt; 1260deallocate prepare stmt; 1261 1262--echo # 1263--echo # MDEV-10881: execution of prepared statement from 1264--echo # CREATE ... SELECT, INSERT ... SELECT 1265--echo # 1266 1267prepare stmt from" 1268create table my_ancestors 1269with recursive 1270ancestor_ids (id) 1271as 1272( 1273 select father from folks where name = 'Me' 1274 union 1275 select mother from folks where name = 'Me' 1276 union 1277 select father from folks, ancestor_ids a where folks.id = a.id 1278 union 1279 select mother from folks, ancestor_ids a where folks.id = a.id 1280) 1281select p.* from folks as p, ancestor_ids as a where p.id = a.id; 1282"; 1283execute stmt; 1284deallocate prepare stmt; 1285select * from my_ancestors; 1286 1287delete from my_ancestors; 1288 1289prepare stmt from" 1290insert into my_ancestors 1291with recursive 1292ancestor_ids (id) 1293as 1294( 1295 select father from folks where name = 'Me' 1296 union 1297 select mother from folks where name = 'Me' 1298 union 1299 select father from folks, ancestor_ids a where folks.id = a.id 1300 union 1301 select mother from folks, ancestor_ids a where folks.id = a.id 1302) 1303select p.* from folks as p, ancestor_ids as a where p.id = a.id; 1304"; 1305execute stmt; 1306deallocate prepare stmt; 1307select * from my_ancestors; 1308 1309drop table my_ancestors; 1310 1311--echo # 1312--echo # MDEV-10933: WITH clause together with SELECT in parenthesis 1313--echo # CREATE SELECT 1314--echo # 1315 1316create table my_ancestors 1317( 1318with recursive 1319ancestor_ids (id) 1320as 1321( 1322 select father from folks where name = 'Me' 1323 union 1324 select mother from folks where name = 'Me' 1325 union 1326 select father from folks, ancestor_ids a where folks.id = a.id 1327 union 1328 select mother from folks, ancestor_ids a where folks.id = a.id 1329) 1330select p.* from folks as p, ancestor_ids as a where p.id = a.id 1331); 1332select * from my_ancestors; 1333drop table my_ancestors; 1334 1335drop table folks; 1336 1337--echo # 1338--echo # MDEV-10372: [bb-10.2-mdev9864 tree] EXPLAIN with recursive CTE enters endless recursion 1339--echo # 1340create table t1(a int); 1341insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1342 1343explain format=json 1344with recursive t as (select a from t1 union select a+10 from t where a < 1000) 1345select * from t; 1346 1347drop table t1; 1348 1349 1350--echo # 1351--echo # MDEV-10737: recursive union with several anchors at the end 1352--echo # 1353 1354WITH RECURSIVE cte(n) AS 1355 ( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 ) 1356SELECT * FROM cte; 1357 1358--echo # 1359--echo # MDEV-10736: recursive definition with anchor over a table with blob 1360--echo # 1361 1362CREATE TABLE t1 (f VARCHAR(1024)); 1363WITH RECURSIVE cte(f) AS 1364 (SELECT t1.f FROM t1 UNION ALL SELECT cte.f FROM cte) 1365SELECT * FROM cte as t; 1366DROP TABLE t1; 1367 1368--echo # 1369--echo # MDEV-10899: mergeable derived in the spec of recursive CTE 1370--echo # 1371 1372create table t1 (a int); 1373insert into t1 values 1374 (0), (1), (2), (3), (4); 1375create table t2 (a int); 1376insert into t2 values 1377 (1), (2), (3), (4), (5); 1378 1379with recursive 1380t1 as 1381( 1382select x.a from (select a from t2 where t2.a=3) x 1383union 1384select t2.a from t1,t2 where t1.a+1=t2.a 1385) 1386select * from t1; 1387 1388explain 1389with recursive 1390t1 as 1391( 1392select x.a from (select a from t2 where t2.a=3) x 1393union 1394select t2.a from t1,t2 where t1.a+1=t2.a 1395) 1396select * from t1; 1397 1398drop table t1,t2; 1399 1400--echo # 1401--echo # MDEV-11278: non-mergeable view in the spec of recursive CTE 1402--echo # 1403 1404create table t1 (a int); 1405insert into t1 values 1406 (0), (1), (2), (3), (4); 1407create table t2 (a int); 1408insert into t2 values 1409 (1), (2), (3), (4), (5); 1410 1411create view v1 as 1412 select a from t2 where a < 3 1413 union 1414 select a from t2 where a > 4; 1415 1416with recursive 1417t1 as 1418( 1419select a from v1 where a=1 1420union 1421select v1.a from t1,v1 where t1.a+1=v1.a 1422) 1423select * from t1; 1424 1425drop view v1; 1426drop table t1,t2; 1427 1428 1429--echo # 1430--echo # MDEV-11259: recursive CTE with concatenation operation 1431--echo # 1432 1433DROP TABLE IF EXISTS edges; 1434CREATE TABLE edges( 1435 a int(10) unsigned NOT NULL, 1436 b int(10) unsigned NOT NULL, 1437 PRIMARY KEY (a,b), 1438 KEY b(b) 1439); 1440 1441INSERT INTO edges 1442 VALUES (1,3),(2,1),(2,4),(3,4),(3,5),(3,6),(4,7),(5,1),(5,6),(6,1); 1443 1444DROP TABLE IF EXISTS edges2; 1445CREATE VIEW edges2 (a, b) AS 1446 SELECT a, b FROM edges UNION ALL SELECT b, a FROM edges; 1447 1448--sorted_result 1449WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS 1450( SELECT a, b, 1 AS distance, 1451 concat(a, '.', b, '.') AS path_string 1452 FROM edges 1453 1454 UNION ALL 1455 1456 SELECT tc.a, e.b, tc.distance + 1, 1457 concat(tc.path_string, e.b, '.') AS path_string 1458 FROM edges AS e 1459 JOIN transitive_closure AS tc 1460 ON e.a = tc.b 1461 WHERE tc.path_string NOT LIKE concat('%', e.b, '.%') 1462) 1463SELECT * FROM transitive_closure 1464ORDER BY a, b, distance; 1465 1466--sorted_result 1467WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS 1468( SELECT a, b, 1 AS distance, 1469 concat(a, '.', b, '.') AS path_string 1470 FROM edges 1471 WHERE a = 1 -- source 1472 1473 UNION ALL 1474 1475 SELECT tc.a, e.b, tc.distance + 1, 1476 concat(tc.path_string, e.b, '.') AS path_string 1477 FROM edges AS e 1478 JOIN transitive_closure AS tc ON e.a = tc.b 1479 WHERE tc.path_string NOT LIKE concat('%', e.b, '.%') 1480) 1481 SELECT * FROM transitive_closure 1482 WHERE b = 6 -- destination 1483ORDER BY a, b, distance; 1484 1485--sorted_result 1486WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS 1487( SELECT a, b, 1 AS distance, 1488 concat(a, '.', b, '.') AS path_string 1489 FROM edges2 1490 1491 UNION ALL 1492 1493 SELECT tc.a, e.b, tc.distance + 1, 1494 concat(tc.path_string, e.b, '.') AS path_string 1495 FROM edges2 AS e 1496 JOIN transitive_closure AS tc ON e.a = tc.b 1497 WHERE tc.path_string NOT LIKE concat('%', e.b, '.%') 1498) 1499SELECT * FROM transitive_closure 1500ORDER BY a, b, distance; 1501 1502--sorted_result 1503WITH RECURSIVE transitive_closure(a, b, distance, path_string) 1504AS 1505( SELECT a, b, 1 AS distance, 1506 concat(a, '.', b, '.') AS path_string 1507 FROM edges2 1508 1509 UNION ALL 1510 1511 SELECT tc.a, e.b, tc.distance + 1, 1512 concat(tc.path_string, e.b, '.') AS path_string 1513 FROM edges2 AS e 1514 JOIN transitive_closure AS tc ON e.a = tc.b 1515 WHERE tc.path_string NOT LIKE concat('%', e.b, '.%') 1516) 1517SELECT a, b, min(distance) AS dist FROM transitive_closure 1518GROUP BY a, b 1519ORDER BY a, dist, b; 1520 1521DROP VIEW edges2; 1522DROP TABLE edges; 1523 1524 1525--echo # 1526--echo # MDEV-11674: recursive CTE table that cannot be stored 1527--echo # in a heap table 1528--echo # 1529 1530create table t1 (id int, test_data varchar(36)); 1531 1532insert into t1(id, test_data) 1533select id, test_data 1534 from ( 1535 with recursive data_generator(id, test_data) as ( 1536 select 1 as id, uuid() as test_data 1537 union all 1538 select id + 1, uuid() from data_generator where id < 150000 1539 ) 1540 select * from data_generator 1541 ) as a; 1542 1543drop table t1; 1544 1545--echo # 1546--echo # MDEV-10773: ANALYZE for query with recursive CTE 1547--echo # 1548 1549--source include/analyze-format.inc 1550analyze format=json 1551with recursive src(counter) as 1552(select 1 1553 union 1554 select counter+1 from src where counter<10 1555) select * from src; 1556 1557--echo # 1558--echo # mdev-12360: recursive reference in left operand of LEFT JOIN 1559--echo # 1560 1561create table folks(id int, name char(32), dob date, father int, mother int); 1562 1563insert into folks values 1564(100, 'Me', '2000-01-01', 20, 30), 1565(20, 'Dad', '1970-02-02', 10, 9), 1566(30, 'Mom', '1975-03-03', 8, 7), 1567(10, 'Grandpa Bill', '1940-04-05', null, null), 1568(9, 'Grandma Ann', '1941-10-15', null, null), 1569(25, 'Uncle Jim', '1968-11-18', 8, 7), 1570(98, 'Sister Amy', '2001-06-20', 20, 30), 1571(7, 'Grandma Sally', '1943-08-23', null, 6), 1572(8, 'Grandpa Ben', '1940-10-21', null, null), 1573(6, 'Grandgrandma Martha', '1923-05-17', null, null), 1574(67, 'Cousin Eddie', '1992-02-28', 25, 27), 1575(27, 'Auntie Melinda', '1971-03-29', null, null); 1576 1577with recursive 1578ancestor_ids (id) 1579as 1580( 1581 select father from folks where name = 'Me' 1582 union 1583 select mother from folks where name = 'Me' 1584 union 1585 select father from ancestor_ids as a left join folks on folks.id = a.id 1586 union 1587 select mother from ancestor_ids as a left join folks on folks.id = a.id 1588), 1589ancestors 1590as 1591( 1592 select p.* from folks as p, ancestor_ids as a 1593 where p.id = a.id 1594) 1595select * from ancestors; 1596 1597drop table folks; 1598 1599--echo # 1600--echo # mdev-12368: crash with mutually recursive CTE 1601--echo # that arenot Standard compliant 1602--echo # 1603 1604create table value_nodes (v char(4)); 1605create table module_nodes(m char(4)); 1606create table module_arguments(m char(4), v char(4)); 1607create table module_results(m char(4), v char(4)); 1608 1609--ERROR ER_NOT_STANDARD_COMPLIANT_RECURSIVE 1610with recursive 1611reached_values as 1612( 1613 select v from value_nodes where v in ('v3','v7','v9') 1614 union 1615 select module_results.v from module_results, applied_modules 1616 where module_results.m = applied_modules.m 1617), 1618applied_modules as 1619( 1620 select module_nodes.m 1621 from 1622 module_nodes 1623 left join 1624 ( 1625 module_arguments 1626 left join 1627 reached_values 1628 on module_arguments.v = reached_values.v 1629 ) 1630 on reached_values.v is null and 1631 module_nodes.m = module_arguments.m 1632 where module_arguments.m is null 1633) 1634select * from reached_values; 1635 1636drop table value_nodes, module_nodes, module_arguments, module_results; 1637 1638--echo # 1639--echo # mdev-12375: query using one of two mutually recursive CTEs 1640--echo # whose non-recursive part returns an empty set 1641--echo # 1642 1643create table value_nodes (v char(4)); 1644insert into value_nodes values 1645 ('v1'), ('v2'), ('v3'), ('v4'), ('v5'), ('v6'), ('v7'), ('v8'), ('v9'), 1646 ('v10'), ('v11'), ('v12'), ('v13'), ('v14'), ('v15'), ('v16'); 1647create table module_nodes(m char(4)); 1648insert into module_nodes values 1649 ('m1'), ('m2'), ('m3'), ('m4'), ('m5'), ('m6'), ('m7'); 1650create table module_arguments(m char(4), v char(4)); 1651insert into module_arguments values 1652 ('m1','v3'), ('m1','v9'), 1653 ('m2','v4'), ('m2','v3'), ('m2','v7'), 1654 ('m3','v6'), 1655 ('m4','v4'), ('m4','v1'), 1656 ('m5','v10'), ('m5','v8'), ('m5','v3'), 1657 ('m6','v8'), ('m6','v1'), 1658 ('m7','v11'), ('m7','v12'); 1659create table module_results(m char(4), v char(4)); 1660insert into module_results values 1661 ('m1','v4'), 1662 ('m2','v1'), ('m2','v6'), 1663 ('m3','v10'), 1664 ('m4','v8'), 1665 ('m5','v11'), ('m5','v9'), 1666 ('m6','v12'), ('m6','v4'), 1667 ('m7','v2'); 1668 1669set statement max_recursive_iterations=2, standard_compliant_cte=0 for 1670with recursive 1671reached_values as 1672( 1673 select v from value_nodes where v in ('v3','v7','v9') 1674 union 1675 select module_results.v from module_results, applied_modules 1676 where module_results.m = applied_modules.m 1677), 1678applied_modules as 1679( 1680 select * from module_nodes where 1=0 1681 union 1682 select module_nodes.m 1683 from 1684 module_nodes 1685 left join 1686 ( 1687 module_arguments 1688 left join 1689 reached_values 1690 on module_arguments.v = reached_values.v 1691 ) 1692 on reached_values.v is null and 1693 module_nodes.m = module_arguments.m 1694 where module_arguments.m is null 1695) 1696select * from applied_modules; 1697 1698drop table value_nodes, module_nodes, module_arguments, module_results; 1699 1700--echo # 1701--echo # mdev-12519: recursive references in subqueries 1702--echo # 1703 1704create table t1 (lp char(4) not null, rp char(4) not null); 1705insert into t1 values 1706 ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5'), 1707 ('p2','p7'), ('p7','p8'), ('p8','p3'), ('p8','p4'); 1708 1709set standard_compliant_cte=0; 1710 1711with recursive 1712reachables(p) as 1713( 1714 select lp from t1 where lp = 'p1' 1715 union 1716 select t1.rp from reachables, t1 1717 where t1.lp = reachables.p 1718) 1719select * from reachables; 1720 1721with recursive 1722reachables(p) as 1723( 1724 select lp from t1 where lp = 'p1' 1725 union 1726 select t1.rp from reachables, t1 1727 where 'p3' not in (select * from reachables) and 1728 t1.lp = reachables.p 1729) 1730select * from reachables; 1731 1732with recursive 1733reachables(p) as 1734( 1735 select lp from t1 where lp = 'p1' 1736 union 1737 select t1.rp from reachables, t1 1738 where 'p3' not in (select p from reachables where p <= 'p5' 1739 union 1740 select p from reachables where p > 'p5') and 1741 t1.lp = reachables.p 1742) 1743select * from reachables; 1744 1745prepare stmt from " 1746with recursive 1747reachables(p) as 1748( 1749 select lp from t1 where lp = 'p1' 1750 union 1751 select t1.rp from reachables, t1 1752 where 'p3' not in (select p from reachables where p <= 'p5' 1753 union 1754 select p from reachables where p > 'p5') and 1755 t1.lp = reachables.p 1756) 1757select * from reachables; 1758"; 1759 1760execute stmt; 1761execute stmt; 1762 1763deallocate prepare stmt; 1764 1765drop table t1; 1766 1767create table objects(v char(4) not null); 1768insert into objects values 1769 ('v1'), ('v2'), ('v3'), ('v4'), ('v5'), 1770 ('v6'), ('v7'), ('v8'), ('v9'), ('v10'); 1771 1772create table modules(m char(4) not null); 1773insert into modules values 1774 ('m1'), ('m2'), ('m3'), ('m4'); 1775 1776create table module_arguments(m char(4) not null, v char(4) not null); 1777insert into module_arguments values 1778 ('m1','v3'), ('m1','v9'), 1779 ('m2','v4'), ('m2','v7'), 1780 ('m3','v6'), ('m4','v2'); 1781 1782create table module_results(m char(4) not null, v char(4) not null); 1783insert into module_results values 1784 ('m1','v4'), 1785 ('m2','v1'), ('m2','v6'), 1786 ('m3','v10'), ('m4','v7'); 1787 1788set standard_compliant_cte=0; 1789 1790with recursive 1791reached_objects as 1792( 1793 select v, 'init' as m from objects where v in ('v3','v7','v9') 1794 union 1795 select module_results.v, module_results.m from module_results, applied_modules 1796 where module_results.m = applied_modules.m 1797), 1798applied_modules as 1799( 1800 select * from modules where 1=0 1801 union 1802 select modules.m 1803 from 1804 modules 1805 where 1806 not exists (select * from module_arguments 1807 where module_arguments.m = modules.m and 1808 module_arguments.v not in 1809 (select v from reached_objects)) 1810) 1811select * from reached_objects; 1812 1813with recursive 1814reached_objects as 1815( 1816 select v, 'init' as m from objects where v in ('v3','v7','v9') 1817 union 1818 select module_results.v, module_results.m from module_results, applied_modules 1819 where module_results.m = applied_modules.m 1820), 1821applied_modules as 1822( 1823 select * from modules where 1=0 1824 union 1825 select modules.m 1826 from 1827 modules 1828 where 1829 'v6' not in (select v from reached_objects) and 1830 not exists (select * from module_arguments 1831 where module_arguments.m = modules.m and 1832 module_arguments.v not in 1833 (select v from reached_objects)) 1834) 1835select * from reached_objects; 1836 1837prepare stmt from " 1838with recursive 1839reached_objects as 1840( 1841 select v, 'init' as m from objects where v in ('v3','v7','v9') 1842 union 1843 select module_results.v, module_results.m from module_results, applied_modules 1844 where module_results.m = applied_modules.m 1845), 1846applied_modules as 1847( 1848 select * from modules where 1=0 1849 union 1850 select modules.m 1851 from 1852 modules 1853 where 1854 'v6' not in (select v from reached_objects) and 1855 not exists (select * from module_arguments 1856 where module_arguments.m = modules.m and 1857 module_arguments.v not in 1858 (select v from reached_objects)) 1859) 1860select * from reached_objects; 1861"; 1862 1863execute stmt; 1864execute stmt; 1865 1866deallocate prepare stmt; 1867 1868drop table objects, modules, module_arguments, module_results; 1869 1870set standard_compliant_cte=default; 1871select @@standard_compliant_cte; 1872 1873--echo # 1874--echo # mdev-12554: impossible where in recursive select 1875--echo # 1876 1877CREATE TABLE t1 (i int); 1878INSERT INTO t1 VALUES (1),(2); 1879 1880WITH RECURSIVE 1881cte(f) AS ( SELECT i FROM t1 UNION SELECT f FROM t1, cte WHERE 1=0 ) 1882SELECT * FROM cte; 1883 1884DROP TABLE t1; 1885 1886--echo # 1887--echo # mdev-12556: recursive execution uses Aria temporary tables 1888--echo # 1889 1890CREATE TABLE t (c1 varchar(255), c2 tinytext); 1891INSERT INTO t VALUES ('a','a'),('b','b'),('c','c'),('d','d'); 1892 1893let $q1= 1894WITH RECURSIVE cte(f) AS ( 1895 SELECT c1 FROM t 1896 UNION 1897 SELECT c1 FROM t, cte 1898) SELECT COUNT(*) FROM cte; 1899 1900let $q2= 1901WITH RECURSIVE cte(f) AS ( 1902 SELECT c2 FROM t 1903 UNION 1904 SELECT c2 FROM t, cte 1905) SELECT COUNT(*) FROM cte; 1906 1907eval ANALYZE $q1; 1908eval $q1; 1909 1910eval ANALYZE $q2; 1911eval $q2; 1912 1913DROP TABLE t; 1914 1915--echo # 1916--echo # mdev-12563: no recursive references on the top level of the CTE spec 1917--echo # 1918 1919CREATE TABLE t (i int); 1920INSERT INTO t VALUES (3), (1),(2); 1921 1922SET standard_compliant_cte=0; 1923 1924WITH RECURSIVE cte(f) AS ( 1925 SELECT i FROM t 1926 UNION 1927 SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte ) 1928) SELECT * FROM cte; 1929 1930WITH RECURSIVE cte(f) AS ( 1931 SELECT i FROM t 1932 UNION 1933 SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte WHERE i < 2 ) 1934 UNION 1935 SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte WHERE i > 2 ) 1936) SELECT * FROM cte; 1937 1938WITH RECURSIVE cte(f) AS ( 1939 SELECT i FROM t 1940 UNION 1941 SELECT i FROM t 1942 WHERE i NOT IN ( SELECT * FROM cte WHERE i < 2 1943 UNION 1944 SELECT * FROM cte WHERE i > 2) 1945) SELECT * FROM cte; 1946 1947WITH RECURSIVE cte(f) AS ( 1948 SELECT i FROM t 1949 UNION 1950 SELECT i FROM t 1951 WHERE i NOT IN ( SELECT * FROM t 1952 WHERE i IN ( SELECT * FROM cte ) GROUP BY i ) 1953) SELECT * FROM cte; 1954 1955WITH RECURSIVE cte(f) AS ( 1956 SELECT i FROM t 1957 UNION 1958 SELECT i FROM t WHERE i NOT IN ( SELECT * FROM cte ) 1959 UNION 1960 SELECT * FROM cte WHERE f > 2 1961) SELECT * FROM cte; 1962 1963set standard_compliant_cte=default; 1964 1965DROP TABLE t; 1966 1967--echo # 1968--echo # mdev-14184: recursive CTE embedded into CTE with multiple references 1969--echo # 1970 1971WITH 1972cte1 AS ( 1973 SELECT n FROM ( 1974 WITH RECURSIVE rec_cte(n) AS ( 1975 SELECT 1 as n1 1976 UNION ALL 1977 SELECT n+1 as n2 FROM rec_cte WHERE n < 3 1978 ) SELECT n FROM rec_cte 1979 ) AS X 1980), 1981cte2 as ( 1982 SELECT 2 FROM cte1 1983) 1984SELECT * 1985FROM cte1; 1986 1987--echo # 1988--echo # mdev-14629: a user-defined variable is defined by the recursive CTE 1989--echo # 1990 1991set @var= 1992( 1993 with recursive cte_tab(a) as ( 1994 select 1 1995 union 1996 select a+1 from cte_tab 1997 where a<3) 1998 select count(*) from cte_tab 1999); 2000 2001select @var; 2002 2003create table t1(a int, b int); 2004insert into t1 values (3,8),(1,5),(5,7),(7,4),(4,3); 2005 2006set @var= 2007( 2008 with recursive summ(a,s) as ( 2009 select 1, 0 union 2010 select t1.b, t1.b+summ.s from summ, t1 2011 where summ.a=t1.a) 2012 select s from summ 2013 order by a desc 2014 limit 1 2015); 2016 2017select @var; 2018 2019--ERROR ER_UNACCEPTABLE_MUTUAL_RECURSION 2020set @var= 2021( 2022 with recursive 2023 cte_1 as ( 2024 select 1 2025 union 2026 select * from cte_2), 2027 cte_2 as ( 2028 select * from cte_1 2029 union 2030 select a from t1, cte_2 2031 where t1.a=cte_2.a) 2032 select * from cte_2 2033 limit 1 2034); 2035 2036drop table t1; 2037 2038--echo # 2039--echo # mdev-14777: crash caused by the same as in mdev-14755 2040--echo # 2041 2042--source include/have_sequence.inc 2043 2044CREATE TABLE t1 (i1 int NOT NULL, i2 int); 2045CREATE TABLE t2 (d1 int NOT NULL PRIMARY KEY); 2046CREATE TABLE t3 (i int ); 2047 2048insert into t1 select seq,seq from seq_1_to_100000; 2049insert into t2 select seq from seq_1000_to_100000; 2050insert into t3 select seq from seq_1_to_1000; 2051 2052SELECT * 2053FROM 2054( 2055 SELECT * 2056 FROM 2057 ( 2058 WITH RECURSIVE rt AS 2059 ( 2060 SELECT i2 P, i1 C FROM t1 WHERE i1 IN (SELECT d1 FROM t2) 2061 UNION 2062 SELECT t1.i2 P, rt.C C FROM t1, rt 2063 ) 2064 SELECT C,P 2065 FROM ( SELECT P,C FROM rt WHERE NOT EXISTS (SELECT 1 FROM t1) ) Y 2066 ) X 2067 WHERE 1 = 1 2068) K, t3; 2069 2070drop table t1,t2,t3; 2071 2072--echo # 2073--echo # mdev-14879: subquery with recursive reference in WHERE of CTE 2074--echo # 2075 2076create table flights 2077(departure varchar(32), 2078 arrival varchar(32), 2079 carrier varchar(20), 2080 flight_number char(7)); 2081 2082insert into flights values 2083('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'), 2084('Seattle', 'Chicago', 'American', 'AA 2573'), 2085('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'), 2086('Chicago', 'New York', 'American', 'AA 375'), 2087('Chicago', 'Montreal', 'Air Canada', 'AC 3053'), 2088('Los Angeles', 'New York', 'Delta', 'DL 1197'), 2089('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'), 2090('New York', 'Paris', 'Air France', 'AF 23'), 2091('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'), 2092('Tokyo', 'Seattle', 'ANA', 'NH 178'), 2093('Los Angeles', 'Tokyo', 'ANA', 'NH 175'), 2094('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'), 2095('Montreal', 'Paris', 'Air Canada', 'AC 870'), 2096('Cairo', 'Paris', 'Air France', 'AF 503'), 2097('New York', 'Seattle', 'American', 'AA 45'), 2098('Paris', 'Chicago', 'Air France', 'AF 6734'); 2099 2100with recursive destinations (city) as 2101( select a.arrival from flights a where a.departure='Cairo' 2102 union 2103 select b.arrival from destinations r, flights b where r.city=b.departure) 2104select * from destinations; 2105 2106set standard_compliant_cte=0; 2107 2108let $q= 2109with recursive destinations (city, legs) as 2110( 2111 select a.arrival, 1 from flights a where a.departure='Cairo' 2112 union 2113 select b.arrival, r.legs + 1 from destinations r, flights b 2114 where r.city=b.departure and b.arrival not in (select city from destinations) 2115) 2116select * from destinations; 2117 2118eval $q; 2119eval explain extended $q; 2120 2121set standard_compliant_cte=default; 2122 2123drop table flights; 2124 2125--echo # 2126--echo # MDEV-15162: Setting user variable in recursive CTE 2127--echo # 2128 2129SET @c=1; 2130 2131WITH RECURSIVE cte AS 2132 (SELECT 5 2133 UNION 2134 SELECT @c:=@c+1 FROM cte WHERE @c<3) 2135SELECT * FROM cte; 2136 2137--echo # 2138--echo # MDEV-15575: using recursive cte with big_tables enabled 2139--echo # 2140 2141set tmp_memory_table_size=0; # force on-disk tmp table 2142 2143with recursive qn as 2144(select 123 as a union all select 1+a from qn where a<130) 2145select * from qn; 2146 2147set tmp_memory_table_size=default; 2148 2149--echo # 2150--echo # MDEV-15571: using recursive cte with big_tables enabled 2151--echo # 2152 2153create table t1 (a bigint); 2154insert into t1 values(1); 2155 2156set tmp_memory_table_size=0; # force on-disk tmp table 2157 2158--error ER_DATA_OUT_OF_RANGE 2159with recursive qn as 2160( 2161 select a from t1 2162 union all 2163 select a*2000 from qn where a<10000000000000000000 2164) 2165select * from qn; 2166 2167set tmp_memory_table_size=default; 2168 2169drop table t1; 2170 2171--echo # 2172--echo # MDEV-15556: using recursive cte with big_tables enabled 2173--echo # when recursive tables are accessed by key 2174--echo # 2175 2176set tmp_memory_table_size=0; # force on-disk tmp table 2177 2178CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int); 2179INSERT INTO t1 VALUES 2180 (1, "A", 2, 3), (2, "LA", 4, 5), (4, "LLA", 6, 7), 2181 (6, "LLLA", NULL, NULL), (7, "RLLA", NULL, NULL), (5, "RLA", 8, 9), 2182 (8, "LRLA", NULL, NULL), (9, "RRLA", NULL, NULL), (3, "RA", 10, 11), 2183 (10, "LRA", 12, 13), (11, "RRA", 14, 15), (15, "RRRA", NULL, NULL), 2184 (16, "B", 17, 18), (17, "LB", NULL, NULL), (18, "RB", NULL, NULL); 2185 2186CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand(); 2187 2188let $q= 2189WITH RECURSIVE tree_of_a AS 2190 (SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" 2191 UNION ALL 2192 SELECT t2.*, concat(tree_of_a.path,",",t2.id) 2193 FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.leftpar 2194 UNION ALL 2195 SELECT t2.*, concat(tree_of_a.path,",",t2.id) 2196 FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.rightpar) 2197SELECT * FROM tree_of_a 2198ORDER BY path; 2199 2200eval $q; 2201eval EXPLAIN $q; 2202 2203DROP TABLE t1,t2; 2204 2205set tmp_memory_table_size=default; 2206 2207--echo # 2208--echo # MDEV-15840: recursive tables are accessed by key 2209--echo # (the same problem as for MDEV-15556) 2210--echo # 2211 2212--source include/have_sequence.inc 2213 2214CREATE TABLE t1 (p1 text,k2 int, p2 text, k1 int); 2215INSERT INTO t1 select seq, seq, seq, seq from seq_1_to_1000; 2216 2217DELIMITER |; 2218CREATE PROCEDURE getNums() 2219BEGIN 2220WITH RECURSIVE cte as 2221( 2222 SELECT * FROM t1 2223 UNION 2224 SELECT c.* FROM t1 c JOIN cte p ON c.p1 = p.p2 AND c.k2 = p.k1 2225) 2226SELECT * FROM cte LIMIT 10; 2227END | 2228 2229DELIMITER ;| 2230call getNums(); 2231 2232DROP PROCEDURE getNums; 2233DROP TABLE t1; 2234 2235--echo # 2236--echo # MDEV-15894: aggregate/winfow functions in non-recorsive part 2237--echo # 2238 2239create table t1(b int); 2240insert into t1 values(10),(20),(10); 2241 2242with recursive qn as 2243 (select max(b) as a from t1 union 2244 select a from qn) 2245select * from qn; 2246 2247with recursive qn as 2248 (select rank() over (order by b) as a from t1 union 2249 select a from qn) 2250select * from qn; 2251 2252drop table t1; 2253 2254--echo # 2255--echo # MDEV-16086: tmp table for CTE is created as ARIA tables 2256--echo # 2257 2258CREATE TABLE t1 ( 2259 Id int(11) not null AUTO_INCREMENT, 2260 Parent varchar(15) not null, 2261 Child varchar(15) not null, 2262 PRIMARY KEY (Id) 2263) ENGINE = MyISAM; 2264 2265INSERT INTO t1 (Parent, Child) VALUES 2266 ('123', '456'),('456', '789'),('321', '654'),('654', '987'); 2267 2268WITH RECURSIVE cte AS 2269 ( SELECT b.Parent, 2270 b.Child, 2271 CAST(CONCAT(b.Child,',') AS CHAR(513)) Path 2272 FROM t1 b 2273 LEFT OUTER JOIN t1 bc ON b.Child = bc.Parent 2274 WHERE bc.Id IS NULL 2275 UNION ALL SELECT c.Parent, 2276 c.Child, 2277 CONCAT(p.Path,c.Child,',') Path 2278 FROM t1 c 2279 INNER JOIN cte p ON c.Child = p.Parent) 2280SELECT * 2281FROM cte 2282ORDER BY Path; 2283 2284DROP TABLE t1; 2285 2286--echo # 2287--echo # MDEV-16212: recursive CTE with global ORDER BY 2288--echo # 2289 2290--error ER_NOT_SUPPORTED_YET 2291set statement max_recursive_iterations = 2 for 2292WITH RECURSIVE qn AS ( 2293SELECT 1 FROM dual UNION ALL 2294SELECT 1 FROM qn 2295ORDER BY (SELECT * FROM qn)) 2296SELECT count(*) FROM qn; 2297 2298--echo # 2299--echo # MDEV-15581: mix of ALL and DISTINCT UNION in recursive CTE 2300--echo # 2301 2302create table t1(a int); 2303insert into t1 values(1),(2); 2304insert into t1 values(1),(2); 2305 2306set @c=0, @d=0; 2307--error ER_NOT_SUPPORTED_YET 2308WITH RECURSIVE qn AS 2309( 2310select 1,0 as col from t1 2311union distinct 2312select 1,0 from t1 2313union all 2314select 3, 0*(@c:=@c+1) from qn where @c<1 2315union all 2316select 3, 0*(@d:=@d+1) from qn where @d<1 2317) 2318select * from qn; 2319 2320drop table t1; 2321 2322--echo # 2323--echo # MDEV-16629: function with recursive CTE using a base table 2324--echo # 2325 2326CREATE TABLE t1 (id int); 2327INSERT INTO t1 VALUES (0), (1),(2); 2328 2329WITH recursive cte AS 2330(SELECT id FROM t1 UNION SELECT 3 FROM cte) 2331SELECT count(id) FROM cte; 2332 2333CREATE OR REPLACE FUNCTION func() RETURNS int 2334RETURN 2335( 2336 WITH recursive cte AS 2337 (SELECT id FROM t1 UNION SELECT 3 FROM cte) 2338 SELECT count(id) FROM cte 2339); 2340 2341SELECT func(); 2342 2343DROP FUNCTION func; 2344DROP TABLE t1; 2345 2346--echo # 2347--echo # MDEV-16661: function with recursive CTE using no base tables 2348--echo # (fixed by the patch for MDEV-16629) 2349--echo # 2350 2351CREATE OR REPLACE FUNCTION func() RETURNS int 2352RETURN 2353( 2354 WITH RECURSIVE cte AS 2355 (SELECT 1 as id UNION SELECT * FROM cte) 2356 SELECT count(id) FROM cte 2357); 2358 2359SELECT func(); 2360 2361DROP FUNCTION func; 2362 2363--echo # 2364--echo # MDEV-17024: two materialized CTEs using the same recursive CTE 2365--echo # 2366 2367create table t1 (id int); 2368insert into t1 values (1), (2), (3); 2369 2370let $q= 2371with recursive 2372rcte(a) as 2373(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 2374cte1 as 2375(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 2376cte2 as 2377(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 2378select * from cte1, cte2; 2379 2380eval $q; 2381eval explain extended $q; 2382eval prepare stmt from "$q"; 2383execute stmt; 2384execute stmt; 2385 2386create table t2 (c1 int, c2 int); 2387eval create procedure p() insert into t2 $q; 2388call p(); 2389select * from t2; 2390 2391let $q1= 2392with recursive 2393rcte(a) as 2394(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 2395cte1 as 2396(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 2397cte2 as 2398(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 2399select * from cte1; 2400 2401eval $q1; 2402 2403let $q2= 2404with recursive 2405rcte(a) as 2406(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 2407cte1 as 2408(select count(*) as c1 from t1), 2409cte2 as 2410(select count(*) as c2 from t2) 2411select * from cte1,cte2; 2412 2413eval $q2; 2414 2415let $q3= 2416with recursive 2417rcte(a) as 2418(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 2419cte1 as 2420(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 2421cte2 as 2422(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 2423select * from cte1, cte2 where cte1.c1 = 3; 2424 2425eval $q3; 2426 2427let $q4= 2428with recursive 2429rcte(a) as 2430(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), 2431cte1 as 2432(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), 2433cte2 as 2434(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) 2435select * from cte2, cte1; 2436 2437eval $q4; 2438eval explain extended $q4; 2439eval prepare stmt from "$q4"; 2440execute stmt; 2441execute stmt; 2442 2443drop procedure p; 2444drop table t2; 2445 2446create table t2 (c1 int, c2 int); 2447eval create procedure p() insert into t2 $q4; 2448call p(); 2449select * from t2; 2450 2451drop procedure p; 2452drop table t1,t2; 2453 2454--echo # 2455--echo # MDEV-17201: recursive part with LIMIT 2456--echo # 2457 2458CREATE TABLE purchases ( 2459 id int unsigned NOT NULL AUTO_INCREMENT, 2460 pdate date NOT NULL, 2461 quantity int unsigned NOT NULL, 2462 p_id int unsigned NOT NULL, 2463 PRIMARY KEY (id) 2464); 2465INSERT INTO purchases(pdate, quantity, p_id) VALUES 2466 ('2014-11-01',5 ,1),('2014-11-03', 3 ,1), 2467 ('2014-11-01',2 ,2),('2014-11-03', 4 ,2); 2468 2469CREATE TABLE expired ( 2470 edate date NOT NULL, 2471 quantity int unsigned NOT NULL, 2472 p_id int unsigned NOT NULL, 2473 PRIMARY KEY (edate,p_id) 2474); 2475 2476INSERT INTO expired VALUES ('2014-11-12', 5 ,1),('2014-11-08', 1 ,2); 2477 2478WITH RECURSIVE expired_map AS ( 2479 SELECT edate AS expired_date, 2480 CAST(NULL AS date) AS purchase_date, 2481 0 AS quantity, 2482 e.p_id, 2483 (SELECT MAX(id)+1 FROM purchases p 2484 WHERE pdate <= edate AND p.p_id =p_id) AS purchase_processed, 2485 quantity AS unresolved 2486 FROM expired e 2487 UNION 2488 ( SELECT expired_date, 2489 pdate, 2490 IF(p.quantity < m.unresolved, p.quantity, m.unresolved), 2491 p.p_id, 2492 p.id, 2493 IF(p.quantity < m.unresolved, m.unresolved - p.quantity, 0) 2494 FROM purchases p JOIN expired_map m ON p.p_id = m.p_id 2495 WHERE p.id < m.purchase_processed AND m.unresolved > 0 2496 ORDER BY p.id DESC 2497 LIMIT 1 2498 ) 2499) 2500SELECT * FROM expired_map; 2501 2502DROP TABLE purchases, expired; 2503 2504--echo # 2505--echo # MDEV-17635: Two recursive CTEs, the second using the first 2506--echo # 2507 2508WITH RECURSIVE 2509x AS (SELECT 0 as k UNION ALL SELECT k + 1 FROM x WHERE k < 1), 2510z AS 2511 ( SELECT k1 AS cx, k2 AS cy, k1, k2 2512 FROM (SELECT k AS k1 FROM x) x1 JOIN (SELECT k AS k2 FROM x) y1 2513 UNION 2514 SELECT 1,1,1,1 FROM z) 2515SELECT * FROM z; 2516 2517--echo # https://wiki.postgresql.org/wiki/Mandelbrot_set: 2518 2519WITH RECURSIVE x(i) AS ( 2520 SELECT CAST(0 AS DECIMAL(13, 10)) 2521 UNION ALL 2522 SELECT i + 1 2523 FROM x 2524 WHERE i < 101 2525), 2526Z(Ix, Iy, Cx, Cy, X, Y, I) AS ( 2527 SELECT Ix, Iy, X, Y, X, Y, 0 2528 FROM (SELECT CAST(-2.2 + 0.031 * i AS DECIMAL(13, 10)) AS X, 2529 i AS Ix FROM x) AS xgen 2530 CROSS JOIN ( 2531 SELECT CAST(-1.5 + 0.031 * i AS DECIMAL(13, 10)) AS Y, 2532 i AS iY FROM x 2533 ) AS ygen 2534 UNION ALL 2535 SELECT Ix, Iy, Cx, Cy, 2536 CAST(X * X - Y * Y + Cx AS DECIMAL(13, 10)) AS X, 2537 CAST(Y * X * 2 + Cy AS DECIMAL(13, 10)), I + 1 2538 FROM Z 2539 WHERE X * X + Y * Y < 16.0 2540 AND I < 27 2541), 2542Zt (Ix, Iy, I) AS ( 2543 SELECT Ix, Iy, MAX(I) AS I 2544 FROM Z 2545 GROUP BY Iy, Ix 2546 ORDER BY Iy, Ix 2547) 2548SELECT GROUP_CONCAT( 2549 SUBSTRING( 2550 ' .,,,-----++++%%%%@@@@#### ', 2551 GREATEST(I, 1), 2552 1 2553 ) ORDER BY Ix SEPARATOR '' 2554 ) AS 'Mandelbrot Set' 2555 FROM Zt 2556GROUP BY Iy 2557ORDER BY Iy; 2558 2559--echo # 2560--echo # MDEV-17871: EXPLAIN for query with not used recursive cte 2561--echo # 2562 2563create table t1 (a int); 2564insert into t1 values (2), (1), (4), (3); 2565 2566let $rec_cte = 2567with recursive cte as 2568 (select * from t1 where a=1 union select a+1 from cte where a<3); 2569 2570eval 2571explain extended 2572$rec_cte 2573select * from cte as t; 2574 2575eval 2576$rec_cte 2577select * from cte as t; 2578 2579eval 2580explain extended 2581$rec_cte 2582select * from t1 as t; 2583 2584eval 2585$rec_cte 2586select * from t1 as t; 2587 2588create table t2 ( i1 int, i2 int); 2589insert into t2 values (1,1),(2,2); 2590 2591explain 2592with recursive cte as 2593 ( select * from t2 union select s1.* from t2 as s1, cte where s1.i1 = cte.i2 ) 2594select * from t2 as t; 2595 2596drop table t1,t2; 2597 2598 2599--echo # 2600--echo # MDEV-22042: ANALYZE of query using stored function and recursive CTE 2601--echo # 2602 2603create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam; 2604insert into t1 values (1,1),(2,2),(3,3); 2605 2606create table t2 ( 2607a2 varchar(20) primary key, b1 varchar(20), key (b1) 2608) engine=myisam; 2609insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); 2610insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17); 2611 2612delimiter $$; 2613create function f1(id varchar(20)) returns varchar(50) 2614begin 2615 declare res varchar (50); 2616 select a2 into res from t2 where a2=id and b1=1 limit 1; 2617 return res; 2618end$$ 2619delimiter ;$$ 2620 2621let q= 2622select fv 2623from (select t1.a1, f1(t1.a2) fv from t1) dt 2624where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' 2625 union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) 2626select a2 from cte); 2627 2628eval $q; 2629eval explain $q; 2630--source include/analyze-format.inc 2631eval analyze format=json $q; 2632 2633drop function f1; 2634drop table t1,t2; 2635 2636--echo # 2637--echo # MDEV-22748: two materialized CTEs using the same recursive CTE 2638--echo # (see also test case for MDEV-17024) 2639--echo # 2640 2641CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ; 2642INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20'); 2643CREATE TABLE t2 (id int, tm date); 2644INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30'); 2645CREATE TABLE t3 (id int, tm date); 2646INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30'); 2647 2648let $q= 2649WITH RECURSIVE 2650cte AS 2651 (SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn 2652 FROM t1 2653 UNION ALL 2654 SELECT YEAR(cte.st + INTERVAL 1 MONTH), 2655 cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY 2656 FROM cte JOIN t1 2657 WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ), 2658cte2 AS (SELECT YEAR, COUNT(*) 2659 FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), 2660cte3 AS (SELECT YEAR, COUNT(*) 2661 FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) 2662SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR); 2663 2664eval $q; 2665eval EXPLAIN EXTENDED $q; 2666eval PREPARE stmt FROM "$q"; 2667EXECUTE stmt; 2668EXECUTE stmt; 2669 2670CREATE TABLE t4 (YEAR int(4), d1 date , d2 date); 2671eval CREATE PROCEDURE p() INSERT INTO t4 $q; 2672CALL p(); 2673SELECT * FROM t4; 2674 2675DROP PROCEDURE p; 2676DROP TABLE t1,t2,t3,t4; 2677 2678--echo # 2679--echo # MDEV-23619: recursive CTE used only in the second operand of UNION 2680--echo # 2681 2682create table t1 ( 2683 a bigint(10) not null auto_increment, 2684 b int(5) not null, 2685 c bigint(10) default null, 2686 primary key (a) 2687) engine myisam; 2688insert into t1 values 2689 (1,3,12), (2,7,15), (3,1,3), (4,3,1); 2690 2691let $q= 2692with recursive r_cte as 2693( select * from t1 as s 2694 union 2695 select t1.* from t1, r_cte as r where t1.c = r.a ) 2696select 0 as b FROM dual union all select b FROM r_cte as t; 2697 2698eval explain $q; 2699eval $q; 2700--source include/analyze-format.inc 2701eval analyze format=json $q; 2702eval prepare stmt from "$q"; 2703execute stmt; 2704execute stmt; 2705deallocate prepare stmt; 2706 2707--echo #checking hanging cte that uses a recursive cte 2708let $q1= 2709with h_cte as 2710( with recursive r_cte as 2711 ( select * from t1 as s 2712 union 2713 select t1.* from t1, r_cte as r where t1.c = r.a ) 2714 select 0 as b FROM dual union all select b FROM r_cte as t) 2715select * from t1 as tt; 2716 2717eval explain $q1; 2718eval $q1; 2719--source include/analyze-format.inc 2720eval analyze format=json $q1; 2721eval prepare stmt from "$q1"; 2722execute stmt; 2723execute stmt; 2724deallocate prepare stmt; 2725 2726drop table t1; 2727 2728--echo # 2729--echo # MDEV-24019: query with recursive CTE when no default database is set 2730--echo # 2731 2732drop database test; 2733 2734let $q= 2735with recursive a as 2736 (select 1 from dual union select * from a as r) 2737select * from a; 2738 2739eval $q; 2740 2741create database db1; 2742create table db1.t1 (a int); 2743insert into db1.t1 values (3), (7), (1); 2744 2745let $q= 2746with recursive cte as 2747 (select * from db1.t1 union select * from (select * from cte) as t) 2748select * from cte; 2749 2750eval $q; 2751eval explain $q; 2752 2753eval prepare stmt from "$q"; 2754execute stmt; 2755execute stmt; 2756deallocate prepare stmt; 2757 2758drop database db1; 2759 2760create database test; 2761use test; 2762 2763--echo # 2764--echo # MDEV-23406: query with mutually recursive CTEs when big_tables=1 2765--echo # 2766 2767set @save_big_tables=@@big_tables; 2768set big_tables=1; 2769 2770create table folks(id int, name char(32), dob date, father int, mother int); 2771 2772insert into folks values 2773(100, 'Me', '2000-01-01', 20, 30), 2774(20, 'Dad', '1970-02-02', 10, 9), 2775(30, 'Mom', '1975-03-03', 8, 7), 2776(10, 'Grandpa Bill', '1940-04-05', null, null), 2777(9, 'Grandma Ann', '1941-10-15', null, null), 2778(25, 'Uncle Jim', '1968-11-18', 8, 7), 2779(98, 'Sister Amy', '2001-06-20', 20, 30), 2780(7, 'Grandma Sally', '1943-08-23', null, 6), 2781(8, 'Grandpa Ben', '1940-10-21', null, null), 2782(6, 'Grandgrandma Martha', '1923-05-17', null, null), 2783(67, 'Cousin Eddie', '1992-02-28', 25, 27), 2784(27, 'Auntie Melinda', '1971-03-29', null, null); 2785 2786let q= 2787with recursive 2788ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 2789 w_id, w_name, w_dob, w_father, w_mother) 2790as 2791( 2792 select h.*, w.* 2793 from folks h, folks w, coupled_ancestors a 2794 where a.father = h.id AND a.mother = w.id 2795 union 2796 select h.*, w.* 2797 from folks v, folks h, folks w 2798 where v.name = 'Me' and 2799 (v.father = h.id AND v.mother= w.id) 2800), 2801coupled_ancestors (id, name, dob, father, mother) 2802as 2803( 2804 select h_id, h_name, h_dob, h_father, h_mother 2805 from ancestor_couples 2806 union 2807 select w_id, w_name, w_dob, w_father, w_mother 2808 from ancestor_couples 2809) 2810select h_name, h_dob, w_name, w_dob 2811 from ancestor_couples; 2812 2813eval $q; 2814eval explain $q; 2815eval prepare stmt from "$q"; 2816execute stmt; 2817execute stmt; 2818deallocate prepare stmt; 2819 2820let $q= 2821with recursive 2822ancestor_couples(h_id, h_name, h_dob, h_father, h_mother, 2823 w_id, w_name, w_dob, w_father, w_mother) 2824as 2825( 2826 select h.*, w.* 2827 from folks h, folks w, coupled_ancestors a 2828 where a.father = h.id AND a.mother = w.id 2829), 2830coupled_ancestors (id, name, dob, father, mother) 2831as 2832( 2833 select * 2834 from folks 2835 where name = 'Me' 2836 union all 2837 select h_id, h_name, h_dob, h_father, h_mother 2838 from ancestor_couples 2839 union all 2840 select w_id, w_name, w_dob, w_father, w_mother 2841 from ancestor_couples 2842) 2843select h_name, h_dob, w_name, w_dob 2844 from ancestor_couples; 2845 2846eval $q; 2847eval explain $q; 2848eval prepare stmt from "$q"; 2849execute stmt; 2850execute stmt; 2851deallocate prepare stmt; 2852 2853drop table folks; 2854 2855set big_tables=@save_big_tables; 2856 2857--echo # 2858--echo # MDEV-26135: execution of PS for query with hanging recursive CTE 2859--echo # 2860 2861create table t1 (a int); 2862insert into t1 values (5), (7); 2863create table t2 (b int); 2864insert into t2 values (3), (7), (1); 2865 2866let $q= 2867with recursive r as (select a from t1 union select a+1 from r where a < 10) 2868select * from t2; 2869 2870eval $q; 2871eval prepare stmt from "$q"; 2872execute stmt; 2873execute stmt; 2874deallocate prepare stmt; 2875 2876drop table t1,t2; 2877 2878--echo # 2879--echo # MDEV-26189: Unknown column reference within hanging recursive CTE 2880--echo # 2881 2882create table t1 (a int); 2883insert into t1 values (3), (7), (1); 2884 2885let $q1= 2886with recursive 2887 r as (select * from t1 union select s1.* from t1 as s1, r where s1.a = r.b) 2888select * from t1 as t; 2889 2890--ERROR ER_BAD_FIELD_ERROR 2891eval $q1; 2892--ERROR ER_BAD_FIELD_ERROR 2893eval explain $q1; 2894 2895eval create procedure sp1() $q1; 2896--ERROR ER_BAD_FIELD_ERROR 2897call sp1(); 2898--ERROR ER_BAD_FIELD_ERROR 2899call sp1(); 2900 2901let $q2= 2902with recursive 2903 r as (select * from t1 union select s1.* from t1 as s1, r where s1.b = r.a) 2904select * from t1 as t; 2905 2906--ERROR ER_BAD_FIELD_ERROR 2907eval $q2; 2908--ERROR ER_BAD_FIELD_ERROR 2909eval explain $q2; 2910 2911eval create procedure sp2() $q2; 2912--ERROR ER_BAD_FIELD_ERROR 2913call sp2(); 2914--ERROR ER_BAD_FIELD_ERROR 2915call sp2(); 2916 2917drop procedure sp1; 2918drop procedure sp2; 2919 2920drop table t1; 2921 2922--echo # 2923--echo # MDEV-26202: Recursive CTE used indirectly twice 2924--echo # (fixed by the patch forMDEV-26025) 2925--echo # 2926 2927with recursive 2928 rcte as ( SELECT 1 AS a 2929 UNION ALL 2930 SELECT cast(a + 1 as unsigned int) FROM rcte WHERE a < 3), 2931 cte1 AS (SELECT a FROM rcte), 2932 cte2 AS (SELECT a FROM cte1), 2933 cte3 AS ( SELECT a FROM cte2) 2934SELECT * FROM cte2, cte3; 2935 2936--echo # 2937--echo # End of 10.2 tests 2938--echo # 2939 2940--echo # 2941--echo # MDEV-14217 [db crash] Recursive CTE when SELECT includes new field 2942--echo # 2943 2944CREATE TEMPORARY TABLE a_tbl ( 2945 a VARCHAR(33) PRIMARY KEY, 2946 b VARCHAR(33) 2947); 2948 2949INSERT INTO a_tbl VALUES ('block0', 'block0'), ('block1', NULL); 2950 2951--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT 2952WITH RECURSIVE Q0 AS ( 2953 SELECT T0.a, T0.b, 5 2954 FROM a_tbl T0 2955 WHERE b IS NULL 2956 UNION ALL 2957 SELECT T1.a, T1.b 2958 FROM Q0 2959 JOIN a_tbl T1 2960 ON T1.a=Q0.a 2961) SELECT distinct(Q0.a), Q0.b 2962 FROM Q0; 2963DROP TABLE a_tbl; 2964 2965--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT 2966WITH RECURSIVE x AS (SELECT 1,2 UNION ALL SELECT 1 FROM x) SELECT * FROM x; 2967 2968--echo # 2969--echo # MDEV-15162: Setting user variable in recursive CTE 2970--echo # 2971 2972SET @c=1; 2973 2974WITH RECURSIVE cte AS 2975 (SELECT 5 2976 UNION 2977 SELECT @c:=@c+1 FROM cte WHERE @c<3) 2978SELECT * FROM cte; 2979 2980--echo # 2981--echo # MDEV-14883: recursive references in operands of INTERSECT / EXCEPT 2982--echo # 2983 2984create table flights 2985(departure varchar(32), 2986 arrival varchar(32), 2987 carrier varchar(20), 2988 flight_number char(7)); 2989 2990insert into flights values 2991('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'), 2992('Seattle', 'Amsterdam', 'KLM', 'KL 6032'), 2993('Seattle', 'Chicago', 'American', 'AA 2573'), 2994('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'), 2995('Chicago', 'New York', 'American', 'AA 375'), 2996('Chicago', 'Montreal', 'Air Canada', 'AC 3053'), 2997('Los Angeles', 'New York', 'Delta', 'DL 1197'), 2998('New York', 'London', 'British Airways', 'BA 1511'), 2999('London', 'Moscow', 'British Airways', 'BA 233'), 3000('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'), 3001('Moscow', 'Dubai', 'Emirates', 'EK 2421'), 3002('Dubai', 'Tokyo', 'Emirates', 'EK 318'), 3003('Dubai', 'Bangkok', 'Emirates', 'EK 2142'), 3004('Beijing', 'Bangkok', 'Air China', 'CA 757'), 3005('Beijing', 'Tokyo', 'Air China', 'CA 6653'), 3006('Moscow', 'Bangkok', 'Aeroflot', 'SU 270'), 3007('New York', 'Reykjavik', 'Icelandair', 'FL 416'), 3008('New York', 'Paris', 'Air France', 'AF 23'), 3009('Amsterdam', 'Moscow', 'KLM', 'KL 903'), 3010('Frankfurt', 'Dubai', 'Lufthansa', 'LH 630'), 3011('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'), 3012('Reykjavik', 'London', 'British Airways', 'BA 2229'), 3013('Frankfurt', 'Beijing', 'Air China', 'CA 966'), 3014('Tokyo', 'Seattle', 'ANA', 'NH 178'), 3015('Los Angeles', 'Tokyo', 'ANA', 'NH 175'), 3016('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'), 3017('Montreal', 'Paris', 'Air Canada', 'AC 870'), 3018('London', 'Delhi', 'British Airways', 'BA 143'), 3019('Delhi', 'Bangkok', 'Air India', 'AI 306'), 3020('Delhi', 'Dubai', 'Air India', 'AI 995'), 3021('Dubai', 'Cairo', 'Emirates', 'EK 927'), 3022('Cairo', 'Paris', 'Air France', 'AF 503'), 3023('Amsterdam', 'New York', 'Delta', 'DL 47'), 3024('New York', 'Seattle', 'American', 'AA 45'), 3025('Paris', 'Chicago', 'Air France', 'AF 6734'); 3026 3027create table distances 3028(city1 varchar(32), 3029 city2 varchar(32), 3030 dist int); 3031 3032insert into distances values 3033('Seattle', 'Frankfurt', 5080), 3034('Seattle', 'Amsterdam', 4859), 3035('Seattle', 'Chicago', 1733), 3036('Seattle', 'Los Angeles', 960), 3037('Chicago', 'New York', 712), 3038('Chicago', 'Montreal', 746), 3039('Los Angeles', 'New York', 2446), 3040('New York', 'London', 3459), 3041('London', 'Moscow', 1554), 3042('Moscow', 'Tokyo', 4647), 3043('Moscow', 'Dubai', 2298), 3044('Dubai', 'Tokyo', 4929), 3045('Dubai', 'Bangkok', 3050), 3046('Beijing', 'Bangkok', 2046), 3047('Beijing', 'Tokyo', 1301), 3048('Moscow', 'Bangkok', 4390), 3049('New York', 'Reykjavik', 2613), 3050('New York', 'Paris', 3625), 3051('Amsterdam', 'Moscow', 1334), 3052('Frankfurt', 'Dubai', 3003), 3053('Frankfurt', 'Moscow', 1256), 3054('Reykjavik', 'London', 1173), 3055('Frankfurt', 'Beijing', 4836), 3056('Tokyo', 'Seattle', 4783), 3057('Los Angeles', 'Tokyo', 5479), 3058('Moscow', 'Los Angeles', 6071), 3059('Moscow', 'Reykjavik', 2052), 3060('Montreal', 'Paris', 3425), 3061('London', 'Delhi', 4159), 3062('London', 'Paris', 214), 3063('Delhi', 'Bangkok', 1810), 3064('Delhi', 'Dubai', 1369), 3065('Delhi', 'Beijing', 2350), 3066('Dubai', 'Cairo', 1501), 3067('Cairo', 'Paris', 1992), 3068('Amsterdam', 'New York', 3643), 3069('New York', 'Seattle', 2402), 3070('Paris', 'Chicago', 4136), 3071('Paris', 'Los Angeles', 5647); 3072 3073with recursive destinations (city) as 3074( 3075 select a.arrival from flights a where a.departure = 'Seattle' 3076 union 3077 select b.arrival from destinations r, flights b where r.city = b.departure 3078) 3079select * from destinations; 3080 3081with recursive destinations (city) as 3082( 3083 select a.arrival from flights a, distances d 3084 where a.departure = 'Seattle' and 3085 a.departure = d.city1 and a.arrival = d.city2 and 3086 d.dist < 4000 3087 union 3088 select b.arrival from destinations r, flights b, distances d 3089 where r.city = b.departure and 3090 b.departure = d.city1 and b.arrival = d.city2 and 3091 d.dist < 4000 3092) 3093select * from destinations; 3094 3095set standard_compliant_cte=0; 3096 3097with recursive legs_to_destinations 3098 (departure, arrival, dist, leg_no, acc_mileage) as 3099( 3100 select a.departure, a.arrival, d.dist, 1, d.dist 3101 from flights a, distances d 3102 where a.departure = 'Seattle' and 3103 a.departure = d.city1 and a.arrival = d.city2 and 3104 d.dist < 4000 3105 union all 3106 select b.departure, b.arrival, d.dist, r.leg_no + 1, r.acc_mileage + d.dist 3107 from legs_to_destinations r, flights b, distances d 3108 where r.arrival = b.departure and 3109 b.departure = d.city1 and b.arrival = d.city2 and 3110 d.dist < 4000 and 3111 b.arrival not in (select arrival from legs_to_destinations) 3112) 3113select * from legs_to_destinations; 3114 3115set standard_compliant_cte=default; 3116 3117with recursive destinations (city) as 3118( 3119 select a.arrival from flights a, distances d 3120 where a.departure = 'Seattle' and 3121 a.departure = d.city1 and a.arrival = d.city2 and 3122 d.dist < 4000 3123 union 3124 select b.arrival from destinations r, flights b 3125 where r.city = b.departure 3126 intersect 3127 select city2 from destinations s, distances d 3128 where s.city = d.city1 and d.dist < 4000 3129) 3130select * from destinations; 3131 3132with recursive destinations (city) as 3133( 3134 select a.arrival from flights a where a.departure = 'Seattle' 3135 union 3136 select * from 3137 ( 3138 select b.arrival from destinations r, flights b 3139 where r.city = b.departure 3140 except 3141 select arrival from flights 3142 where arrival in 3143 ('New York', 'London', 'Moscow', 'Dubai', 'Cairo', 'Tokyo') 3144 ) t 3145) 3146select * from destinations; 3147 3148drop table flights, distances; 3149 3150--echo # 3151--echo # MDEV-15159: Forced nullability of columns in recursive CTE 3152--echo # 3153 3154WITH RECURSIVE cte AS ( 3155 SELECT 1 AS a UNION ALL 3156 SELECT NULL FROM cte WHERE a IS NOT NULL) 3157SELECT * FROM cte; 3158 3159CREATE TABLE t1 (a int NOT NULL); 3160INSERT INTO t1 VALUES (0); 3161 3162WITH RECURSIVE cte AS 3163 (SELECT a FROM t1 where a=0 UNION SELECT NULL FROM cte) 3164SELECT * FROM cte; 3165 3166DROP TABLE t1; 3167 3168--echo # End of 10.3 tests 3169 3170--echo # 3171--echo # MDEV-26108: Recursive CTE embedded into another CTE which is used twice 3172--echo # 3173 3174create table t1 (a int); 3175insert into t1 values (5), (7); 3176 3177with cte_e as ( 3178 with recursive cte_r as ( 3179 select a from t1 union select a+1 as a from cte_r r where a < 10 3180 ) select * from cte_r 3181) select * from cte_e s1, cte_e s2 where s1.a=s2.a; 3182 3183drop table t1; 3184 3185--echo # End of 10.4 tests 3186