github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/select/minus.sql (about)

     1  
     2  -- test varchar column minus
     3  drop table if exists t1;
     4  create table t1(
     5  a varchar(100),
     6  b varchar(100)
     7  );
     8  
     9  insert into t1 values('dddd', 'cccc');
    10  insert into t1 values('aaaa', 'bbbb');
    11  insert into t1 values('eeee', 'aaaa');
    12  insert into t1 values ();
    13  select * from t1;
    14  
    15  (select a from t1) minus (select b from t1);
    16  (select a from t1) minus (select a from t1 limit 1);
    17  (select a from t1) minus (select a from t1 limit 2);
    18  (select a from t1) minus (select a from t1 limit 3);
    19  (select a from t1) minus (select a from t1 limit 4);
    20  
    21  (select b from t1) minus (select a from t1);
    22  (select b from t1) minus (select b from t1 limit 1);
    23  (select b from t1) minus (select b from t1 limit 2);
    24  (select b from t1) minus (select b from t1 limit 3);
    25  (select b from t1) minus (select b from t1 limit 4);
    26  
    27  (select a from t1) minus (select b from t1) minus (select b from t1);
    28  (select a from t1) minus (select b from t1) minus (select b from t1) minus (select a from t1);
    29  
    30  ((select a from t1) union (select b from t1)) minus (select a from t1);
    31  
    32  drop table t1;
    33  
    34  -- test date type and time type minus
    35  drop table if exists t2;
    36  create table t2(
    37  col1 date,
    38  col2 datetime,
    39  col3 timestamp
    40  );
    41  
    42  insert into t2 values ();
    43  insert into t2 values('2022-01-01', '2022-01-01', '2022-01-01');
    44  insert into t2 values('2022-01-01', '2022-01-01 00:00:00', '2022-01-01 00:00:00.000000');
    45  insert into t2 values('2022-01-01', '2022-01-01 00:00:00.000000', '2022-01-01 23:59:59.999999');
    46  select * from t2;
    47  
    48  -- test date type and time type
    49  (select col1 from t2) minus (select col1 from t2 limit 1);
    50  (select col1 from t2) minus (select col2 from t2);
    51  (select col1 from t2) minus (select col3 from t2);
    52  
    53  (select col1 from t2) minus (select col1 from t2 limit 0);
    54  (select col1 from t2) minus (select col2 from t2 limit 0);
    55  (select col1 from t2) minus (select col3 from t2 limit 0);
    56  
    57  (select col2 from t2) minus (select col1 from t2 limit 0);
    58  (select col2 from t2) minus (select col2 from t2 limit 0);
    59  (select col2 from t2) minus (select col3 from t2 limit 0);
    60  
    61  (select col3 from t2) minus (select col1 from t2 limit 0);
    62  (select col3 from t2) minus (select col2 from t2 limit 0);
    63  (select col3 from t2) minus (select col3 from t2 limit 0);
    64  
    65  drop table t2;
    66  
    67  
    68  -- test union and minus
    69  drop table if exists t3;
    70  create table t3(
    71  a int
    72  );
    73  
    74  insert into t3 values (20),(10),(30),(-10);
    75  
    76  drop table if exists t4;
    77  create table t4(
    78  col1 float,
    79  col2 decimal(5,2)
    80  );
    81  
    82  insert into t4 values(100.01,100.01);
    83  insert into t4 values(1.10,1.10);
    84  insert into t4 values(0.0,0.0);
    85  insert into t4 values(127.0,127.0);
    86  insert into t4 values(127.44,127.44);
    87  
    88  
    89  (select a from t3) union (select col1 from t4) minus (select col1 from t4);
    90  (select a from t3) union (select col2 from t4) minus (select col2 from t4);
    91  (select a from t3) union (select col1 from t4) minus (select a from t3);
    92  (select a from t3) union (select col2 from t4) minus (select a from t3);
    93  
    94  (select col1 from t4) minus (select col1 from t4);
    95  (select col1 from t4) minus (select col2 from t4);
    96  (select col2 from t4) minus (select col2 from t4);
    97  (select col2 from t4) minus (select col1 from t4);
    98  
    99  drop table t3;
   100  drop table t4;
   101  
   102  -- test int type and text type union varchar type and text type
   103  drop table if exists t5;
   104  create table t5(
   105  a int,
   106  b text
   107  );
   108  
   109  insert into t5 values (11, 'aa');
   110  insert into t5 values (33, 'bb');
   111  insert into t5 values (44, 'aa');
   112  insert into t5 values (55, 'cc');
   113  insert into t5 values (55, 'dd');
   114  
   115  drop table if exists t6;
   116  create table t6 (
   117  col1 varchar(100),
   118  col2 text,
   119  col3 char(100)
   120  );
   121  
   122  insert into t6 values ('aa', '11', 'aa');
   123  insert into t6 values ('bb', '22', '11');
   124  insert into t6 values ('cc', '33', 'bb');
   125  insert into t6 values ('dd', '44', '22');
   126  
   127  (select a from t5) minus (select col2 from t6);
   128  (select col2 from t6) minus (select a from t5);
   129  
   130  (select b from t5) minus (select col1 from t6);
   131  (select b from t5) minus (select col2 from t6);
   132  (select b from t5) minus (select col3 from t6);
   133  
   134  (select col1 from t6) minus (select b from t5);
   135  (select col2 from t6) minus (select b from t5);
   136  (select col3 from t6) minus (select b from t5);
   137  
   138  drop table t5;
   139  drop table t6;
   140  
   141  -- test subquery minus
   142  drop table if exists t7;
   143  CREATE TABLE t7 (
   144  a int not null,
   145  b char (10) not null
   146  );
   147  
   148  insert into t7 values(1,'3'),(2,'4'),(3,'5'),(3,'1');
   149  
   150  select * from (select a from t7 minus select a from t7) a;
   151  
   152  select * from (select a from t7 minus select b from t7) a;
   153  select * from (select b from t7 minus select a from t7) a;
   154  
   155  select * from (select a from t7 minus (select b from t7 limit 2)) a;
   156  select * from (select b from t7 minus (select a from t7 limit 2)) a;
   157  
   158  select * from (select a from t7 minus select b from t7 limit 1) a;
   159  select * from (select b from t7 minus select a from t7 limit 1) a;
   160  
   161  select * from (select a from t7 minus select b from t7 where a > 2) a;
   162  select * from (select b from t7 minus select a from t7 where a > 4) a;
   163  
   164  drop table t7;
   165  
   166  -- test minus prepare
   167  drop table if exists t8;
   168  create table t8 (
   169  a int primary key,
   170  b int
   171  );
   172  
   173  insert into t8 values (1,5),(2,4),(3,3);
   174  set @a=1;
   175  
   176  prepare s1 from '(select a from t8 where a>?) minus (select b from t8 where b>?)';
   177  prepare s2 from '(select a from t8 where a>?)';
   178  
   179  execute s1 using @a;
   180  execute s1 using @a, @a;
   181  execute s2 using @a;
   182  execute s2 using @a, @a;
   183  
   184  deallocate prepare s1;
   185  deallocate prepare s2;
   186  
   187  drop table t8;
   188  
   189  -- test minus join, left join, right join
   190  drop table if exists t9;
   191  create table t9(
   192  a int,
   193  b varchar
   194  );
   195  
   196  insert into t9 values (1, 'a'), (2, 'b'), (3,'c'), (4, 'd');
   197  
   198  drop table if exists t10;
   199  create table t10(
   200  c int,
   201  d varchar
   202  );
   203  
   204  insert into t10 values (1, 'a'), (10, 'b'), (2,'b'), (2, 'e');
   205  
   206  (select a from t9) minus (select tab1.a from t9 as tab1 join t10 as tab2 on tab1.a=tab2.c);
   207  (select a from t9) minus (select tab1.a from t9 as tab1 left join t10 as tab2 on tab1.a=tab2.c);
   208  (select a from t9) minus (select tab1.a from t9 as tab1 right join t10 as tab2 on tab1.a=tab2.c);
   209  
   210  (select b from t9) minus (select tab1.b from t9 as tab1 join t10 as tab2 on tab1.b=tab2.d);
   211  (select b from t9) minus (select tab1.b from t9 as tab1 left join t10 as tab2 on tab1.b=tab2.d);
   212  (select b from t9) minus (select tab1.b from t9 as tab1 right join t10 as tab2 on tab1.b=tab2.d);
   213  
   214  (select c from t10) minus (select tab1.a from t9 as tab1 join t10 as tab2 on tab1.a=tab2.c);
   215  (select c from t10) minus (select tab1.a from t9 as tab1 left join t10 as tab2 on tab1.a=tab2.c);
   216  (select c from t10) minus (select tab1.a from t9 as tab1 right join t10 as tab2 on tab1.a=tab2.c);
   217  
   218  (select d from t10) minus (select tab1.b from t9 as tab1 join t10 as tab2 on tab1.b=tab2.d);
   219  (select d from t10) minus (select tab1.b from t9 as tab1 left join t10 as tab2 on tab1.b=tab2.d);
   220  (select d from t10) minus (select tab1.b from t9 as tab1 right join t10 as tab2 on tab1.b=tab2.d);
   221  
   222  drop table t9;
   223  drop table t10;
   224  
   225  
   226  drop table if exists t11;
   227  create table t11 (
   228  RID int(11) not null default '0',
   229  IID int(11) not null default '0',
   230  nada varchar(50)  not null,
   231  NAME varchar(50) not null,
   232  PHONE varchar(50) not null);
   233  
   234  insert into t11 ( RID,IID,nada,NAME,PHONE) values
   235  (1, 1, 'main', 'a', '111'),
   236  (2, 1, 'main', 'b', '222'),
   237  (3, 1, 'main', 'c', '333'),
   238  (4, 1, 'main', 'd', '444'),
   239  (5, 1, 'main', 'e', '555'),
   240  (6, 2, 'main', 'c', '333'),
   241  (7, 2, 'main', 'd', '454'),
   242  (8, 2, 'main', 'e', '555'),
   243  (9, 2, 'main', 'f', '666'),
   244  (10, 2, 'main', 'g', '777');
   245  
   246  select A.NAME, A.PHONE, B.NAME, B.PHONE from t11 A
   247  left join t11 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null)
   248  minus
   249  select A.NAME, A.PHONE, B.NAME, B.PHONE from t11 B left join t11 A on B.NAME = A.NAME and A.IID = 1
   250  where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
   251  
   252  select A.NAME, A.PHONE, B.NAME, B.PHONE from t11 B left join t11 A on B.NAME = A.NAME and A.IID = 1
   253  where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null)
   254  minus
   255  select A.NAME, A.PHONE, B.NAME, B.PHONE from t11 A
   256  left join t11 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null);
   257  
   258  set @val1=1;
   259  set @val2=2;
   260  
   261  prepare s1 from
   262  'select A.NAME, A.PHONE, B.NAME, B.PHONE from t11 A
   263  left join t11 B on A.NAME = B.NAME and B.IID = ? where A.IID = ? and (A.PHONE <> B.PHONE or B.NAME is null)
   264  minus
   265  select A.NAME, A.PHONE, B.NAME, B.PHONE from t11 B left join t11 A on B.NAME = A.NAME and A.IID = ?
   266  where B.IID = ? and (A.PHONE <> B.PHONE or A.NAME is null)';
   267  
   268  execute s1 using @val2, @val1, @val1, @val2;
   269  
   270  deallocate prepare s1;
   271  
   272  drop table t11;
   273  
   274  -- test primary key minus
   275  drop table if exists t12;
   276  
   277  create table t12(
   278  a int primary key,
   279  b int auto_increment
   280  );
   281  
   282  insert into t12(a) values (1);
   283  insert into t12(a) values (2);
   284  insert into t12(a) values (3);
   285  insert into t12(a) values (10);
   286  insert into t12(a) values (20);
   287  
   288  (select a from t12 ) minus (select b from t12);
   289  (select a from t12 ) minus (select a from t12);
   290  
   291  (select b from t12 ) minus (select a from t12);
   292  (select b from t12 ) minus (select b from t12);
   293  
   294  drop table t12;
   295  
   296  drop table if exists t2;
   297  create table t2(
   298  col1 date,
   299  col2 datetime,
   300  col3 timestamp
   301  );
   302  
   303  insert into t2 values ();
   304  insert into t2 values('2022-01-01', '2022-01-01', '2022-01-01');
   305  insert into t2 values('2022-01-01', '2022-01-01 00:00:00.99999', '2022-01-01 00:00:00.000000');
   306  insert into t2 values('2022-01-01', '2022-01-01 00:00:00.999999', '2022-01-01 23:59:59.999999');
   307  select * from t2;
   308  
   309  (select col1 from t2) minus (select col2 from t2 limit 0);
   310  (select col1 from t2) minus (select col3 from t2 limit 0);
   311  
   312  (select col2 from t2) minus (select col1 from t2 limit 0);
   313  (select col2 from t2) minus (select col3 from t2 limit 0);
   314  
   315  (select col3 from t2) minus (select col1 from t2 limit 0);
   316  (select col3 from t2) minus (select col2 from t2 limit 0);
   317  
   318  drop table t2;