github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/operator/bag_operator.test (about)

     1  -- @suite
     2  -- @setup
     3  
     4  drop table if exists t1;
     5  drop table if exists t2;
     6  create table t1 (a smallint, b bigint, c int);
     7  insert into t1 values (1,2,3);
     8  insert into t1 values (1,2,3);
     9  insert into t1 values (3,4,5);
    10  insert into t1 values (3,4,5);
    11  insert into t1 values (3,4,5);
    12  insert into t1 values (4,5,6);
    13  insert into t1 values (4,5,6);
    14  insert into t1 values (1,1,2);
    15  insert into t1 values (1,2,1);
    16  insert into t1 values (3,4,5);
    17  create table t2 (a smallint, b bigint, c int);
    18  insert into t2 values (1,2,3);
    19  insert into t2 values (1,2,3);
    20  insert into t2 values (1,2,3);
    21  insert into t2 values (3,4,5);
    22  insert into t2 values (3,4,5);
    23  insert into t2 values (1,2,1);
    24  insert into t2 values (1,2,1);
    25  
    26  
    27  -- @case
    28  -- @desc:test for bag operators
    29  -- @label:bvt
    30  
    31  (select * from t1 intersect all select * from t2) order by 1,2,3;
    32  (select a,b from t1 intersect all select b,c from t2) order by 1,2;
    33  
    34  
    35  drop table if exists t1;
    36  drop table if exists t2;
    37  create table t1 (
    38  id int not null,
    39  name varchar(20) default null,
    40  country varchar(20) default null,
    41  city varchar(20) default null,
    42  PRIMARY KEY (id)
    43  );
    44  insert into t1 values (1, 'Aakash', 'INDIA', 'Mumbai');
    45  insert into t1 values (2, 'George', 'USA', 'New York');
    46  insert into t1 values (3, 'David', 'INDIA', 'Bangalore');
    47  insert into t1 values (4, 'Leo', 'SPAIN', 'Madrid');
    48  insert into t1 values (5, 'Rahul', 'INDIA', 'Delhi');
    49  insert into t1 values (6, 'Brian', 'USA', 'Chicago');
    50  insert into t1 values (7, 'Justin', 'SPAIN', 'Barcelona');
    51  insert into t1 values (8, 'Judy', 'USA', null);
    52  insert into t1 values (9, 'Jessica', null, null);
    53  insert into t1 values (10, 'LiLei', 'CHINA', null);
    54  create table t2 (
    55  id int not null,
    56  country varchar(20) default null,
    57  city varchar(20) default null,
    58  PRIMARY KEY  (id)
    59  );
    60  insert into t2 values(101, 'INDIA', 'Mumbai');
    61  insert into t2 values(201, 'INDIA', 'Bangalore');
    62  insert into t2 values(301, 'USA', 'Chicago');
    63  insert into t2 values(401, 'USA', 'New York');
    64  insert into t2 values(501, 'SPAIN', 'Madrid');
    65  insert into t2 values(666, 'CHINA', null);
    66  insert into t2 values(404, 'USA', null);
    67  insert into t2 values(0, null, null);
    68  
    69  (select country from t1 intersect all select country from t2) order by 1;
    70  (select city from t1 intersect all select city from t2) order by 1;
    71  (select city from t1 intersect all select city from t2 where city is not null) order by 1;
    72  (select country,city from t1 intersect all select country,city from t2) order by 1,2;
    73  (select country,city from t1 where country='JAPAN' intersect all select country,city from t2) order by 1,2;
    74  (select country,city from t1 intersect all select country,city from t2 where country='JAPAN') order by 1,2;
    75  t1 intersect all select * from t2 ;