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

     1  drop table if exists t1;
     2  drop table if exists t2;
     3  create table t1 (a smallint, b bigint, c int);
     4  insert into t1 values (1,2,3);
     5  insert into t1 values (1,2,3);
     6  insert into t1 values (3,4,5);
     7  insert into t1 values (3,4,5);
     8  insert into t1 values (3,4,5);
     9  insert into t1 values (4,5,6);
    10  insert into t1 values (4,5,6);
    11  insert into t1 values (1,1,2);
    12  insert into t1 values (1,2,1);
    13  insert into t1 values (3,4,5);
    14  create table t2 (a smallint, b bigint, c int);
    15  insert into t2 values (1,2,3);
    16  insert into t2 values (1,2,3);
    17  insert into t2 values (1,2,3);
    18  insert into t2 values (3,4,5);
    19  insert into t2 values (3,4,5);
    20  insert into t2 values (1,2,1);
    21  insert into t2 values (1,2,1);
    22  (select * from t1 intersect all select * from t2) order by 1,2,3;
    23  a    b    c
    24  1    2    3
    25  1    2    3
    26  1    2    1
    27  3    4    5
    28  3    4    5
    29  (select a,b from t1 intersect all select b,c from t2) order by 1,2;
    30  a    b
    31  4    5
    32  4    5
    33  drop table if exists t1;
    34  drop table if exists t2;
    35  create table t1 (
    36  id int not null,
    37  name varchar(20) default null,
    38  country varchar(20) default null,
    39  city varchar(20) default null,
    40  PRIMARY KEY (id)
    41  );
    42  insert into t1 values (1, 'Aakash', 'INDIA', 'Mumbai');
    43  insert into t1 values (2, 'George', 'USA', 'New York');
    44  insert into t1 values (3, 'David', 'INDIA', 'Bangalore');
    45  insert into t1 values (4, 'Leo', 'SPAIN', 'Madrid');
    46  insert into t1 values (5, 'Rahul', 'INDIA', 'Delhi');
    47  insert into t1 values (6, 'Brian', 'USA', 'Chicago');
    48  insert into t1 values (7, 'Justin', 'SPAIN', 'Barcelona');
    49  insert into t1 values (8, 'Judy', 'USA', null);
    50  insert into t1 values (9, 'Jessica', null, null);
    51  insert into t1 values (10, 'LiLei', 'CHINA', null);
    52  create table t2 (
    53  id int not null,
    54  country varchar(20) default null,
    55  city varchar(20) default null,
    56  PRIMARY KEY  (id)
    57  );
    58  insert into t2 values(101, 'INDIA', 'Mumbai');
    59  insert into t2 values(201, 'INDIA', 'Bangalore');
    60  insert into t2 values(301, 'USA', 'Chicago');
    61  insert into t2 values(401, 'USA', 'New York');
    62  insert into t2 values(501, 'SPAIN', 'Madrid');
    63  insert into t2 values(666, 'CHINA', null);
    64  insert into t2 values(404, 'USA', null);
    65  insert into t2 values(0, null, null);
    66  (select country from t1 intersect all select country from t2) order by 1;
    67  country
    68  USA
    69  USA
    70  USA
    71  null
    72  CHINA
    73  INDIA
    74  INDIA
    75  SPAIN
    76  (select city from t1 intersect all select city from t2) order by 1;
    77  city
    78  New York
    79  null
    80  null
    81  null
    82  Chicago
    83  Mumbai
    84  Bangalore
    85  Madrid
    86  (select city from t1 intersect all select city from t2 where city is not null) order by 1;
    87  city
    88  New York
    89  Chicago
    90  Mumbai
    91  Bangalore
    92  Madrid
    93  (select country,city from t1 intersect all select country,city from t2) order by 1,2;
    94  country    city
    95  INDIA    Mumbai
    96  USA    New York
    97  SPAIN    Madrid
    98  USA    null
    99  null    null
   100  INDIA    Bangalore
   101  USA    Chicago
   102  CHINA    null
   103  (select country,city from t1 where country='JAPAN' intersect all select country,city from t2) order by 1,2;
   104  country    city
   105  (select country,city from t1 intersect all select country,city from t2 where country='JAPAN') order by 1,2;
   106  country    city
   107  t1 intersect all select * from t2 ;
   108  SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 2 near "t1 intersect all select * from t2 ;";