1create table t1 (a int, b int) engine=MyISAM; 2create table t2 (c int, d int) engine=MyISAM; 3create table t3 (e int, f int) engine=MyISAM; 4create table t4 (g int, h int) engine=MyISAM; 5insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3); 6insert into t2 values (2,2),(3,3),(5,5),(2,2),(2,2),(3,3); 7insert into t3 values (4,4),(2,2),(2,2),(1,1),(3,3); 8insert into t4 values (2,2),(4,4),(1,1); 9create view v0(g, h) as select a,c from t1,t2; 10# test optimization 11select * from t1 12INTERSECT ALL 13select * from t2 14INTERSECT ALL 15select * from t3; 16a b 172 2 182 2 193 3 20EXPLAIN EXTENDED select * from t1 21INTERSECT ALL 22select * from t2 23INTERSECT ALL 24select * from t3; 25id select_type table type possible_keys key key_len ref rows filtered Extra 261 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 272 INTERSECT t2 ALL NULL NULL NULL NULL 6 100.00 283 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 29NULL INTERSECT RESULT <intersect1,2,3> ALL NULL NULL NULL NULL NULL NULL 30Warnings: 31Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect all /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect all /* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` 32select * from t1 33INTERSECT ALL 34select * from t2 35INTERSECT ALL 36select * from t3 37INTERSECT 38select * from t1; 39a b 402 2 413 3 42EXPLAIN EXTENDED select * from t1 43INTERSECT ALL 44select * from t2 45INTERSECT ALL 46select * from t3 47INTERSECT 48select * from t1; 49id select_type table type possible_keys key key_len ref rows filtered Extra 501 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 512 INTERSECT t2 ALL NULL NULL NULL NULL 6 100.00 523 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 534 INTERSECT t1 ALL NULL NULL NULL NULL 5 100.00 54NULL INTERSECT RESULT <intersect1,2,3,4> ALL NULL NULL NULL NULL NULL NULL 55Warnings: 56Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect /* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` intersect /* select#4 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` 57select * from t1 58INTERSECT ALL 59select * from t2 60INTERSECT ALL 61select * from t3 62EXCEPT ALL 63select * from t4; 64a b 652 2 663 3 67EXPLAIN EXTENDED select * from t1 68INTERSECT ALL 69select * from t2 70INTERSECT ALL 71select * from t3 72EXCEPT ALL 73select * from t4; 74id select_type table type possible_keys key key_len ref rows filtered Extra 751 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 762 INTERSECT t2 ALL NULL NULL NULL NULL 6 100.00 773 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 784 EXCEPT t4 ALL NULL NULL NULL NULL 3 100.00 79NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL 80Warnings: 81Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect all /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect all /* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` except all /* select#4 */ select `test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t4` 82select * from t1 83INTERSECT 84select * from t2 85EXCEPT ALL 86select * from t4; 87a b 883 3 89EXPLAIN EXTENDED select * from t1 90INTERSECT 91select * from t2 92EXCEPT ALL 93select * from t4; 94id select_type table type possible_keys key key_len ref rows filtered Extra 951 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 962 INTERSECT t2 ALL NULL NULL NULL NULL 6 100.00 973 EXCEPT t4 ALL NULL NULL NULL NULL 3 100.00 98NULL UNIT RESULT <unit1,2,3> ALL NULL NULL NULL NULL NULL NULL 99Warnings: 100Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` except /* select#3 */ select `test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t4` 101insert into t4 values (1,1),(9,9); 102select * from t1 103UNION ALL 104select * from t2 105UNION ALL 106select * from t3 107EXCEPT 108select * from t4; 109a b 1103 3 1115 5 112EXPLAIN EXTENDED select * from t1 113UNION ALL 114select * from t2 115UNION ALL 116select * from t3 117EXCEPT 118select * from t4; 119id select_type table type possible_keys key key_len ref rows filtered Extra 1201 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 1212 UNION t2 ALL NULL NULL NULL NULL 6 100.00 1223 UNION t3 ALL NULL NULL NULL NULL 5 100.00 1234 EXCEPT t4 ALL NULL NULL NULL NULL 5 100.00 124NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL 125Warnings: 126Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` union /* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` except /* select#4 */ select `test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t4` 127delete from t4; 128insert into t4 values (3,3),(3,3); 129select * from t1 130INTERSECT ALL 131select * from t2 132UNION ALL 133select * from t3 134EXCEPT ALL 135select * from t1 136UNION 137select * from t4 138EXCEPT 139select * from t3 140UNION ALL 141select * from t1; 142a b 1432 2 1442 2 1451 1 1463 3 1473 3 148EXPLAIN EXTENDED select * from t1 149INTERSECT ALL 150select * from t2 151UNION ALL 152select * from t3 153EXCEPT ALL 154select * from t1 155UNION 156select * from t4 157EXCEPT 158select * from t3 159UNION ALL 160select * from t1; 161id select_type table type possible_keys key key_len ref rows filtered Extra 1621 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 1632 INTERSECT t2 ALL NULL NULL NULL NULL 6 100.00 1643 UNION t3 ALL NULL NULL NULL NULL 5 100.00 1654 EXCEPT t1 ALL NULL NULL NULL NULL 5 100.00 1665 UNION t4 ALL NULL NULL NULL NULL 2 100.00 1676 EXCEPT t3 ALL NULL NULL NULL NULL 5 100.00 1687 UNION t1 ALL NULL NULL NULL NULL 5 100.00 169NULL UNIT RESULT <unit1,2,3,4,5,6,7> ALL NULL NULL NULL NULL NULL NULL 170Warnings: 171Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` intersect all /* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` union all /* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` except all /* select#4 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union /* select#5 */ select `test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t4` except /* select#6 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` union all /* select#7 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` 172drop table t4; 173# test optimization with brackets 174( 175(select 1 except select 5 union all select 6) 176union 177(select 2 intersect all select 3 intersect all select 4) 178except 179(select 7 intersect all select 8) 180) 181union all 182(select 9 union all select 10) 183except all 184select 11; 1851 1861 1876 1889 18910 190EXPLAIN EXTENDED ( 191(select 1 except select 5 union all select 6) 192union 193(select 2 intersect all select 3 intersect all select 4) 194except 195(select 7 intersect all select 8) 196) 197union all 198(select 9 union all select 10) 199except all 200select 11; 201id select_type table type possible_keys key key_len ref rows filtered Extra 2021 PRIMARY <derived8> ALL NULL NULL NULL NULL 4 100.00 2038 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 2042 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2053 EXCEPT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2064 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 207NULL UNIT RESULT <unit2,3,4> ALL NULL NULL NULL NULL NULL NULL 2089 UNION <derived5> ALL NULL NULL NULL NULL 2 100.00 2095 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2106 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2117 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 212NULL INTERSECT RESULT <intersect5,6,7> ALL NULL NULL NULL NULL NULL NULL 21312 EXCEPT <derived10> ALL NULL NULL NULL NULL 2 100.00 21410 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 21511 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 216NULL INTERSECT RESULT <intersect10,11> ALL NULL NULL NULL NULL NULL NULL 217NULL UNIT RESULT <unit8,9,12> ALL NULL NULL NULL NULL NULL NULL 21815 UNION <derived13> ALL NULL NULL NULL NULL 2 100.00 21913 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22014 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22116 EXCEPT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 222NULL UNIT RESULT <unit1,15,16> ALL NULL NULL NULL NULL NULL NULL 223Warnings: 224Note 1003 /* select#1 */ select `__14`.`1` AS `1` from (/* select#8 */ select `__7`.`1` AS `1` from (/* select#2 */ select 1 AS `1` except /* select#3 */ select 5 AS `5` union /* select#4 */ select 6 AS `6`) `__7` union /* select#9 */ select `__8`.`2` AS `2` from (/* select#5 */ select 2 AS `2` intersect /* select#6 */ select 3 AS `3` intersect /* select#7 */ select 4 AS `4`) `__8` except /* select#12 */ select `__11`.`7` AS `7` from (/* select#10 */ select 7 AS `7` intersect /* select#11 */ select 8 AS `8`) `__11`) `__14` union all /* select#15 */ select `__15`.`9` AS `9` from (/* select#13 */ select 9 AS `9` union all /* select#14 */ select 10 AS `10`) `__15` except all /* select#16 */ select 11 AS `11` 225(select 1 union all select 2) 226union 227(select 3 union all select 4); 2281 2291 2302 2313 2324 233EXPLAIN EXTENDED (select 1 union all select 2) 234union 235(select 3 union all select 4); 236id select_type table type possible_keys key key_len ref rows filtered Extra 2371 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 2382 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2393 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2406 UNION <derived4> ALL NULL NULL NULL NULL 2 100.00 2414 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2425 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 243NULL UNION RESULT <union1,6> ALL NULL NULL NULL NULL NULL NULL 244Warnings: 245Note 1003 /* select#1 */ select `__5`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 2 AS `2`) `__5` union /* select#6 */ select `__6`.`3` AS `3` from (/* select#4 */ select 3 AS `3` union /* select#5 */ select 4 AS `4`) `__6` 246(select 1 intersect all select 2) 247except 248select 3; 2491 250EXPLAIN EXTENDED (select 1 intersect all select 2) 251except 252select 3; 253id select_type table type possible_keys key key_len ref rows filtered Extra 2541 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 2552 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2563 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 257NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL NULL 2584 EXCEPT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 259NULL EXCEPT RESULT <except1,4> ALL NULL NULL NULL NULL NULL NULL 260Warnings: 261Note 1003 /* select#1 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` intersect /* select#3 */ select 2 AS `2`) `__4` except /* select#4 */ select 3 AS `3` 262(select 1 intersect all select 2 intersect all select 3) 263intersect 264(select 4 intersect all select 5); 2651 266EXPLAIN EXTENDED (select 1 intersect all select 2 intersect all select 3) 267intersect 268(select 4 intersect all select 5); 269id select_type table type possible_keys key key_len ref rows filtered Extra 2701 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 2712 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2723 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2734 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 274NULL INTERSECT RESULT <intersect2,3,4> ALL NULL NULL NULL NULL NULL NULL 2757 INTERSECT <derived5> ALL NULL NULL NULL NULL 2 100.00 2765 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2776 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 278NULL INTERSECT RESULT <intersect5,6> ALL NULL NULL NULL NULL NULL NULL 279NULL INTERSECT RESULT <intersect1,7> ALL NULL NULL NULL NULL NULL NULL 280Warnings: 281Note 1003 /* select#1 */ select `__6`.`1` AS `1` from (/* select#2 */ select 1 AS `1` intersect /* select#3 */ select 2 AS `2` intersect /* select#4 */ select 3 AS `3`) `__6` intersect /* select#7 */ select `__7`.`4` AS `4` from (/* select#5 */ select 4 AS `4` intersect /* select#6 */ select 5 AS `5`) `__7` 282# test set operations with table value constructor 283(values (1,1),(1,1),(1,1),(2,2),(2,2),(3,3),(9,9)) 284INTERSECT ALL 285(values (1,1),(2,2),(2,2),(3,3),(3,3),(3,3),(8,8)) 286EXCEPT ALL 287(values (7,7),(1,1)); 2881 1 2892 2 2902 2 2913 3 292delete from t1; 293insert into t1 values(1,1),(1,1),(2,2),(4,4),(9,9); 294select * from t1 295UNION ALL 296(values (11,12),(3,3),(2,2),(3,3),(4,4),(8,8)) 297INTERSECT 298(values (13,14),(7,7),(2,2),(3,3),(1,1)) 299INTERSECT ALL 300(values (15,16),(2,2),(1,1)) 301EXCEPT 302(values (17,18),(1,1)); 303a b 3042 2 3054 4 3069 9 307# test set operations with derived table 308select * from ( 309select * from t1 310UNION ALL 311select * from t2 312)dt1 313INTERSECT ALL 314select * from ( 315select * from t2 316EXCEPT ALL 317select * from t3 318)dt2; 319a b 3202 2 3213 3 3225 5 323select * from ( 324select * from t1 325UNION ALL 326select * from t3 327)dt1 328EXCEPT ALL 329select * from ( 330select * from t2 331INTERSECT ALL 332select * from t2 333)dt2; 334a b 3351 1 3361 1 3374 4 3389 9 3391 1 3404 4 341SELECT * from( 342select * from ( 343select * from t1 344UNION ALL 345select * from t2 346)dt1 347INTERSECT ALL 348select * from ( 349select * from t2 350EXCEPT ALL 351select * from t3 352)dt2 353)dt3; 354a b 3552 2 3563 3 3575 5 358# integration test 359select * from t1 360UNION ALL 361select * from t2 362INTERSECT ALL 363(values (1,1), (2,2), (2,2), (5,5), (2,2)) 364INTERSECT ALL 365select * from (select * from t1 union all select * from t1) sq 366EXCEPT ALL 367select * from t3 368UNION ALL 369select * from t2 370UNION 371select * from t3 372EXCEPT 373select a,c from t1,t2 374UNION ALL 375select * from v0 where g < 4 376UNION ALL 377select * from t3; 378a b 3791 1 3801 2 3813 3 3829 9 3835 5 3844 4 3851 2 3862 2 3871 3 3881 3 3892 3 3901 5 3911 5 3922 5 3931 2 3941 2 3952 2 3961 2 3971 2 3982 2 3991 3 4001 3 4012 3 4024 4 4032 2 4042 2 4051 1 4063 3 407select * from t1 408UNION ALL 409select * from t2 410INTERSECT ALL 411(values (1,1), (2,2), (2,2), (5,5), (2,2)) 412INTERSECT ALL 413select * from (select * from t1 union all select * from t1) sq 414EXCEPT ALL 415select * from t3 416UNION ALL 417select * from t2 418UNION 419select * from t3 420EXCEPT 421select a,c from t1,t2 422UNION ALL 423select * from v0 where g < 4 424UNION ALL 425select * from t3 426ORDER BY a; 427a b 4281 1 4291 1 4301 2 4311 2 4321 2 4331 2 4341 2 4351 2 4361 3 4371 3 4381 3 4391 3 4401 5 4411 5 4422 2 4432 2 4442 2 4452 2 4462 2 4472 3 4482 3 4492 5 4503 3 4513 3 4524 4 4534 4 4545 5 4559 9 456select * from ( 457select * from t1 458UNION ALL 459select * from t2 460INTERSECT ALL 461(values (1,1), (2,2), (2,2), (5,5), (2,2) ) 462INTERSECT ALL 463select * from (select * from t1 union all select * from t1) sq 464EXCEPT ALL 465select * from t3 466UNION ALL 467select * from t2 468UNION 469select * from t3 470EXCEPT 471select a,c from t1,t2 472UNION ALL 473select * from v0 where g < 4 474UNION ALL 475select * from t3 476) dt; 477a b 4781 1 4791 2 4803 3 4819 9 4825 5 4834 4 4841 2 4852 2 4861 3 4871 3 4882 3 4891 5 4901 5 4912 5 4921 2 4931 2 4942 2 4951 2 4961 2 4972 2 4981 3 4991 3 5002 3 5014 4 5022 2 5032 2 5041 1 5053 3 506EXPLAIN 507select * from t1 508UNION ALL 509select * from t2 510INTERSECT ALL 511(values (1,1), (2,2), (2,2), (5,5), (2,2) ) 512INTERSECT ALL 513select * from (select * from t1 union all select * from t1) sq 514EXCEPT ALL 515select * from t3 516UNION ALL 517select * from t2 518UNION 519select * from t3 520EXCEPT 521select a,c from t1,t2 522UNION ALL 523select * from v0 where g < 4 524UNION ALL 525select * from t3; 526id select_type table type possible_keys key key_len ref rows Extra 5271 PRIMARY t1 ALL NULL NULL NULL NULL 5 5288 UNION <derived2> ALL NULL NULL NULL NULL 5 5292 DERIVED t2 ALL NULL NULL NULL NULL 6 5303 INTERSECT NULL NULL NULL NULL NULL NULL NULL No tables used 5314 INTERSECT <derived5> ALL NULL NULL NULL NULL 10 5325 DERIVED t1 ALL NULL NULL NULL NULL 5 5336 UNION t1 ALL NULL NULL NULL NULL 5 534NULL INTERSECT RESULT <intersect2,3,4> ALL NULL NULL NULL NULL NULL 5357 EXCEPT t3 ALL NULL NULL NULL NULL 5 5369 UNION t2 ALL NULL NULL NULL NULL 6 53710 UNION t3 ALL NULL NULL NULL NULL 5 53811 EXCEPT t1 ALL NULL NULL NULL NULL 5 53911 EXCEPT t2 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) 54012 UNION t1 ALL NULL NULL NULL NULL 5 Using where 54112 UNION t2 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) 54213 UNION t3 ALL NULL NULL NULL NULL 5 543NULL UNIT RESULT <unit1,8,7,9,10,11,12,13> ALL NULL NULL NULL NULL NULL 544EXPLAIN format=json 545select * from t1 546UNION ALL 547select * from t2 548INTERSECT ALL 549(values (1,1), (2,2), (2,2), (5,5), (2,2) ) 550INTERSECT ALL 551select * from (select * from t1 union all select * from t1) sq 552EXCEPT ALL 553select * from t3 554UNION ALL 555select * from t2 556UNION 557select * from t3 558EXCEPT 559select a,c from t1,t2 560UNION ALL 561select * from v0 where g < 4 562UNION ALL 563select * from t3; 564EXPLAIN 565{ 566 "query_block": { 567 "union_result": { 568 "table_name": "<unit1,8,7,9,10,11,12,13>", 569 "access_type": "ALL", 570 "query_specifications": [ 571 { 572 "query_block": { 573 "select_id": 1, 574 "table": { 575 "table_name": "t1", 576 "access_type": "ALL", 577 "rows": 5, 578 "filtered": 100 579 } 580 } 581 }, 582 { 583 "query_block": { 584 "select_id": 8, 585 "operation": "UNION", 586 "table": { 587 "table_name": "<derived2>", 588 "access_type": "ALL", 589 "rows": 5, 590 "filtered": 100, 591 "materialized": { 592 "query_block": { 593 "union_result": { 594 "table_name": "<intersect2,3,4>", 595 "access_type": "ALL", 596 "query_specifications": [ 597 { 598 "query_block": { 599 "select_id": 2, 600 "table": { 601 "table_name": "t2", 602 "access_type": "ALL", 603 "rows": 6, 604 "filtered": 100 605 } 606 } 607 }, 608 { 609 "query_block": { 610 "select_id": 3, 611 "operation": "INTERSECT", 612 "table": { 613 "message": "No tables used" 614 } 615 } 616 }, 617 { 618 "query_block": { 619 "select_id": 4, 620 "operation": "INTERSECT", 621 "table": { 622 "table_name": "<derived5>", 623 "access_type": "ALL", 624 "rows": 10, 625 "filtered": 100, 626 "materialized": { 627 "query_block": { 628 "union_result": { 629 "table_name": "<union5,6>", 630 "access_type": "ALL", 631 "query_specifications": [ 632 { 633 "query_block": { 634 "select_id": 5, 635 "table": { 636 "table_name": "t1", 637 "access_type": "ALL", 638 "rows": 5, 639 "filtered": 100 640 } 641 } 642 }, 643 { 644 "query_block": { 645 "select_id": 6, 646 "operation": "UNION", 647 "table": { 648 "table_name": "t1", 649 "access_type": "ALL", 650 "rows": 5, 651 "filtered": 100 652 } 653 } 654 } 655 ] 656 } 657 } 658 } 659 } 660 } 661 } 662 ] 663 } 664 } 665 } 666 } 667 } 668 }, 669 { 670 "query_block": { 671 "select_id": 7, 672 "operation": "EXCEPT", 673 "table": { 674 "table_name": "t3", 675 "access_type": "ALL", 676 "rows": 5, 677 "filtered": 100 678 } 679 } 680 }, 681 { 682 "query_block": { 683 "select_id": 9, 684 "operation": "UNION", 685 "table": { 686 "table_name": "t2", 687 "access_type": "ALL", 688 "rows": 6, 689 "filtered": 100 690 } 691 } 692 }, 693 { 694 "query_block": { 695 "select_id": 10, 696 "operation": "UNION", 697 "table": { 698 "table_name": "t3", 699 "access_type": "ALL", 700 "rows": 5, 701 "filtered": 100 702 } 703 } 704 }, 705 { 706 "query_block": { 707 "select_id": 11, 708 "operation": "EXCEPT", 709 "table": { 710 "table_name": "t1", 711 "access_type": "ALL", 712 "rows": 5, 713 "filtered": 100 714 }, 715 "block-nl-join": { 716 "table": { 717 "table_name": "t2", 718 "access_type": "ALL", 719 "rows": 6, 720 "filtered": 100 721 }, 722 "buffer_type": "flat", 723 "buffer_size": "65", 724 "join_type": "BNL" 725 } 726 } 727 }, 728 { 729 "query_block": { 730 "select_id": 12, 731 "operation": "UNION", 732 "table": { 733 "table_name": "t1", 734 "access_type": "ALL", 735 "rows": 5, 736 "filtered": 100, 737 "attached_condition": "t1.a < 4" 738 }, 739 "block-nl-join": { 740 "table": { 741 "table_name": "t2", 742 "access_type": "ALL", 743 "rows": 6, 744 "filtered": 100 745 }, 746 "buffer_type": "flat", 747 "buffer_size": "65", 748 "join_type": "BNL" 749 } 750 } 751 }, 752 { 753 "query_block": { 754 "select_id": 13, 755 "operation": "UNION", 756 "table": { 757 "table_name": "t3", 758 "access_type": "ALL", 759 "rows": 5, 760 "filtered": 100 761 } 762 } 763 } 764 ] 765 } 766 } 767} 768EXPLAIN EXTENDED 769select * from t1 770UNION ALL 771select * from t2 772INTERSECT ALL 773(values (1,1), (2,2), (2,2), (5,5), (2,2) ) 774INTERSECT ALL 775select * from (select * from t1 union all select * from t1) sq 776EXCEPT ALL 777select * from t3 778UNION ALL 779select * from t2 780UNION 781select * from t3 782EXCEPT 783select a,c from t1,t2 784UNION ALL 785select * from v0 where g < 4 786UNION ALL 787select * from t3; 788id select_type table type possible_keys key key_len ref rows filtered Extra 7891 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 7908 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 7912 DERIVED t2 ALL NULL NULL NULL NULL 6 100.00 7923 INTERSECT NULL NULL NULL NULL NULL NULL NULL NULL No tables used 7934 INTERSECT <derived5> ALL NULL NULL NULL NULL 10 100.00 7945 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 7956 UNION t1 ALL NULL NULL NULL NULL 5 100.00 796NULL INTERSECT RESULT <intersect2,3,4> ALL NULL NULL NULL NULL NULL NULL 7977 EXCEPT t3 ALL NULL NULL NULL NULL 5 100.00 7989 UNION t2 ALL NULL NULL NULL NULL 6 100.00 79910 UNION t3 ALL NULL NULL NULL NULL 5 100.00 80011 EXCEPT t1 ALL NULL NULL NULL NULL 5 100.00 80111 EXCEPT t2 ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) 80212 UNION t1 ALL NULL NULL NULL NULL 5 100.00 Using where 80312 UNION t2 ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) 80413 UNION t3 ALL NULL NULL NULL NULL 5 100.00 805NULL UNIT RESULT <unit1,8,7,9,10,11,12,13> ALL NULL NULL NULL NULL NULL NULL 806Warnings: 807Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union all /* select#8 */ select `__8`.`c` AS `c`,`__8`.`d` AS `d` from (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect all (values (1,1),(2,2),(2,2),(5,5),(2,2)) intersect all /* select#4 */ select `sq`.`a` AS `a`,`sq`.`b` AS `b` from (/* select#5 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union all /* select#6 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) `sq`) `__8` except all /* select#7 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` union /* select#9 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` union /* select#10 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` except /* select#11 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` union all /* select#12 */ select `test`.`t1`.`a` AS `g`,`test`.`t2`.`c` AS `h` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` < 4 union all /* select#13 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` 808PREPARE stmt from" 809 select * from t1 810 UNION ALL 811 select * from t2 812 INTERSECT ALL 813 (values (1,1), (2,2), (2,2), (5,5), (2,2) ) 814 INTERSECT ALL 815 select * from (select * from t1 union all select * from t1) sq 816 EXCEPT ALL 817 select * from t3 818 UNION ALL 819 select * from t2 820 UNION 821 select * from t3 822 EXCEPT 823 select a,c from t1,t2 824 UNION ALL 825 select * from v0 where g < 4 826 UNION ALL 827 select * from t3 828"; 829EXECUTE stmt; 830a b 8311 1 8321 2 8333 3 8349 9 8355 5 8364 4 8371 2 8382 2 8391 3 8401 3 8412 3 8421 5 8431 5 8442 5 8451 2 8461 2 8472 2 8481 2 8491 2 8502 2 8511 3 8521 3 8532 3 8544 4 8552 2 8562 2 8571 1 8583 3 859EXECUTE stmt; 860a b 8611 1 8621 2 8633 3 8649 9 8655 5 8664 4 8671 2 8682 2 8691 3 8701 3 8712 3 8721 5 8731 5 8742 5 8751 2 8761 2 8772 2 8781 2 8791 2 8802 2 8811 3 8821 3 8832 3 8844 4 8852 2 8862 2 8871 1 8883 3 889deallocate prepare stmt; 890create view v1(i1, i2) as 891select * from t1 892UNION ALL 893select * from t2 894INTERSECT ALL 895(values (1,1), (2,2), (2,2), (5,5), (2,2) ) 896INTERSECT ALL 897select * from (select * from t1 union all select * from t1) sq 898EXCEPT ALL 899select * from t3 900UNION ALL 901select * from t2 902UNION 903select * from t3 904EXCEPT 905select a,c from t1,t2 906UNION ALL 907select * from v0 where g < 4 908UNION ALL 909select * from t3; 910show create view v1; 911View Create View character_set_client collation_connection 912v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`a` AS `i1`,`test`.`t1`.`b` AS `i2` from `test`.`t1` union all select `__9`.`c` AS `c`,`__9`.`d` AS `d` from (select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` intersect all (values (1,1),(2,2),(2,2),(5,5),(2,2)) intersect all select `sq`.`a` AS `a`,`sq`.`b` AS `b` from (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union all select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) `sq`) `__9` except all select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` union all select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` union select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` except select `test`.`t1`.`a` AS `a`,`test`.`t2`.`c` AS `c` from (`test`.`t1` join `test`.`t2`) union all select `v0`.`g` AS `g`,`v0`.`h` AS `h` from `test`.`v0` where `v0`.`g` < 4 union all select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3` latin1 latin1_swedish_ci 913select * from v1 limit 14; 914i1 i2 9151 1 9161 2 9173 3 9189 9 9195 5 9204 4 9211 2 9222 2 9231 3 9241 3 9252 3 9261 5 9271 5 9282 5 929select * from v1 order by i1 limit 14; 930i1 i2 9311 1 9321 1 9331 2 9341 2 9351 2 9361 2 9371 2 9381 2 9391 3 9401 3 9411 3 9421 3 9431 5 9441 5 945drop table t1,t2,t3; 946drop view v0,v1; 947# compare result 948create table t1 (a int, b int); 949create table t2 (c int, d int); 950create table t3 (e int, f int); 951create table t4 (g int, h int); 952insert into t1 values (1,1),(1,1),(2,2); 953insert into t2 values (1,1),(1,1),(2,2),(3,3); 954insert into t3 values (1,1); 955insert into t4 values (4,4); 956select * from t1 intersect all select * from t2 except select * from t3 union select * from t4; 957a b 9584 4 9592 2 960select * from t1 intersect all select * from t2 except ALL select * from t3 union select * from t4; 961a b 9621 1 9632 2 9644 4 965select * from t1 intersect DISTINCT select * from t2 except select * from t3 union select * from t4; 966a b 9674 4 9682 2 969select * from t1 intersect DISTINCT select * from t2 except ALL select * from t3 union select * from t4; 970a b 9714 4 9722 2 973delete from t1; 974delete from t2; 975delete from t3; 976delete from t4; 977insert into t1 values (1,1),(1,1),(1,1),(2,2),(2,2),(4,4),(5,5); 978insert into t2 values (1,1),(1,1),(1,1),(2,2),(2,2),(3,3); 979insert into t3 values (1,1),(2,2),(2,2); 980select * from t1 intersect all select * from t2 intersect all select * from t3; 981a b 9821 1 9832 2 9842 2 985select * from t1 intersect all select * from t2 intersect select * from t3; 986a b 9871 1 9882 2 989select * from t1 intersect all select * from t1 intersect all select * from t2 intersect select * from t3; 990a b 9911 1 9922 2 993delete from t1; 994delete from t2; 995delete from t3; 996insert into t1 values (1,1),(1,1),(2,2); 997insert into t2 values (1,1),(1,1),(2,2),(3,3); 998insert into t3 values (1,1),(5,5); 999insert into t4 values (4,4),(4,4),(4,4); 1000select * from t1 intersect all select * from t2 union all select * from t3 union select * from t4; 1001a b 10021 1 10032 2 10045 5 10054 4 1006select * from t1 intersect DISTINCT select * from t2 union DISTINCT select * from t3 union select * from t4; 1007a b 10081 1 10092 2 10105 5 10114 4 1012select * from t1 intersect all select * from t2 intersect all select * from t3 union select * from t4; 1013a b 10141 1 10154 4 1016select * from t1 intersect all select * from t2 intersect DISTINCT select * from t3 union select * from t4; 1017a b 10181 1 10194 4 1020select * from t1 intersect DISTINCT select * from t2 intersect DISTINCT select * from t3 union select * from t4; 1021a b 10221 1 10234 4 1024select * from t1 intersect all select * from t2 EXCEPT select * from t3 union select * from t4; 1025a b 10264 4 10272 2 1028select * from t1 intersect DISTINCT select * from t2 EXCEPT select * from t3 union select * from t4; 1029a b 10304 4 10312 2 1032select * from t1 intersect all select * from t2 EXCEPT ALL select * from t3 union select * from t4; 1033a b 10341 1 10352 2 10364 4 1037select * from t1 EXCEPT select * from t2 union all select * from t3 union select * from t4; 1038a b 10395 5 10401 1 10414 4 1042select * from t1 EXCEPT select * from t2 union DISTINCT select * from t3 union select * from t4; 1043a b 10445 5 10451 1 10464 4 1047delete from t1; 1048delete from t2; 1049delete from t3; 1050delete from t4; 1051insert into t1 values (1,1),(2,2); 1052insert into t2 values (1,1),(2,2); 1053insert into t3 values (1,1),(3,3); 1054select * from t1 union all select * from t2 except all select * from t3; 1055a b 10561 1 10572 2 10582 2 1059select * from t1 union all select * from t2 except DISTINCT select * from t3; 1060a b 10612 2 1062select * from t1 union DISTINCT select * from t2 except all select * from t3; 1063a b 10642 2 1065select * from t1 union DISTINCT select * from t2 except DISTINCT select * from t3; 1066a b 10672 2 1068drop table t1; 1069drop table t2; 1070drop table t3; 1071drop table t4; 1072select 1 intersect all select 2 intersect all select 3 intersect select 4 union select 5; 10731 10745 1075select 1 intersect all select 2 intersect all select 3 union select 4 except select 5; 10761 10774 1078select 1 union select 2 except all select 3 union select 4; 10791 10801 10812 10824 1083select 1 union all select 2 union all select 3 union select 4; 10841 10851 10862 10873 10884 1089# test with limited resource 1090set @@max_heap_table_size= 1024; 1091Warnings: 1092Warning 1292 Truncated incorrect max_heap_table_size value: '1024' 1093set @@tmp_table_size= 1024; 1094create table t1 (a int, b int); 1095insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); 1096insert into t1 select * from t1; 1097insert into t1 select * from t1; 1098insert into t1 select * from t1; 1099insert into t1 select a+100, b+100 from t1; 1100create table t2 (a int, b int); 1101insert into t2 values (10,10),(11,11),(12,12),(13,13),(14,14),(5,5),(6,6),(7,7),(8,8),(9,9); 1102insert into t2 select * from t2; 1103insert into t2 select * from t2; 1104insert into t2 select * from t2; 1105insert into t2 select a+100, b+100 from t2; 1106select count(*) from 1107( 1108select * from t1 1109INTERSECT ALL 1110select * from t2 1111) c; 1112count(*) 111380 1114select count(*) from 1115( 1116select * from t1 1117EXCEPT ALL 1118select * from t2 1119) c; 1120count(*) 112180 1122select count(*) from 1123( 1124select * from t1 1125INTERSECT ALL 1126select * from t2 1127UNION ALL 1128select * from t1 1129EXCEPT ALL 1130select * from t2 1131) c; 1132count(*) 1133160 1134delete from t1; 1135delete from t2; 1136insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0); 1137insert into t1 select a+10, b+10 from t1; 1138insert into t1 select a+20, b+20 from t1; 1139insert into t1 select a+40, b+40 from t1; 1140insert into t1 select a+80, b+80 from t1; 1141insert into t2 values (1110,1110),(1111,1111),(1112,1112),(1113,1113),(1114,1114),(1105,1105),(1106,1106),(1107,1107),(1108,1108),(1109,1109); 1142insert into t2 select a+10, b+10 from t2; 1143insert into t2 select a+20, b+20 from t2; 1144insert into t2 select a+40, b+40 from t2; 1145insert into t2 select a+80, b+80 from t2; 1146select count(*) from 1147( 1148select * from t1 1149UNION ALL 1150select * from t2 1151EXCEPT ALL 1152values (1,1) 1153) c; 1154count(*) 1155319 1156drop table t1; 1157drop table t2; 1158# 1159# MDEV-24242: set expression with empty intermediate result 1160# when tmp_memory_table_size is set to 0 1161# 1162create table t1 (a int, b int) engine=MyISAM; 1163insert into t1 values (1,1), (2,2); 1164create table t2 (a int, b int) engine=MyISAM; 1165insert into t2 values (11,11), (12,12), (13,13); 1166select * from t1 1167except all 1168select * from t1 1169except 1170select * from t1 1171union all 1172select * from t2; 1173a b 117412 12 117511 11 117613 13 1177set tmp_memory_table_size=0; 1178select * from t1 1179except all 1180select * from t1 1181except 1182select * from t1 1183union all 1184select * from t2; 1185a b 118612 12 118711 11 118813 13 1189set tmp_memory_table_size=default; 1190drop table t1,t2; 1191# End of 10.4 tests 1192