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

     1  drop table if exists t1;
     2  create table t1(
     3  a varchar(100),
     4  b varchar(100)
     5  );
     6  insert into t1 values('dddd', 'cccc');
     7  insert into t1 values('aaaa', 'bbbb');
     8  insert into t1 values('eeee', 'aaaa');
     9  insert into t1 values ();
    10  select * from t1;
    11  a    b
    12  dddd    cccc
    13  aaaa    bbbb
    14  eeee    aaaa
    15  null    null
    16  (select a from t1) minus (select b from t1);
    17  a
    18  dddd
    19  eeee
    20  (select a from t1) minus (select a from t1 limit 1);
    21  a
    22  aaaa
    23  null
    24  eeee
    25  (select a from t1) minus (select a from t1 limit 2);
    26  a
    27  null
    28  eeee
    29  (select a from t1) minus (select a from t1 limit 3);
    30  a
    31  null
    32  (select a from t1) minus (select a from t1 limit 4);
    33  a
    34  (select b from t1) minus (select a from t1);
    35  b
    36  bbbb
    37  cccc
    38  (select b from t1) minus (select b from t1 limit 1);
    39  b
    40  aaaa
    41  bbbb
    42  null
    43  (select b from t1) minus (select b from t1 limit 2);
    44  b
    45  aaaa
    46  null
    47  (select b from t1) minus (select b from t1 limit 3);
    48  b
    49  null
    50  (select b from t1) minus (select b from t1 limit 4);
    51  b
    52  (select a from t1) minus (select b from t1) minus (select b from t1);
    53  a
    54  dddd
    55  eeee
    56  (select a from t1) minus (select b from t1) minus (select b from t1) minus (select a from t1);
    57  a
    58  ((select a from t1) union (select b from t1)) minus (select a from t1);
    59  a
    60  bbbb
    61  cccc
    62  drop table t1;
    63  drop table if exists t2;
    64  create table t2(
    65  col1 date,
    66  col2 datetime,
    67  col3 timestamp
    68  );
    69  insert into t2 values ();
    70  insert into t2 values('2022-01-01', '2022-01-01', '2022-01-01');
    71  insert into t2 values('2022-01-01', '2022-01-01 00:00:00', '2022-01-01 00:00:00.000000');
    72  insert into t2 values('2022-01-01', '2022-01-01 00:00:00.000000', '2022-01-01 23:59:59.999999');
    73  select * from t2;
    74  col1    col2    col3
    75  null    null    null
    76  2022-01-01    2022-01-01 00:00:00    2022-01-01 00:00:00
    77  2022-01-01    2022-01-01 00:00:00    2022-01-01 00:00:00
    78  2022-01-01    2022-01-01 00:00:00    2022-01-02 00:00:00
    79  (select col1 from t2) minus (select col1 from t2 limit 1);
    80  col1
    81  2022-01-01
    82  (select col1 from t2) minus (select col2 from t2);
    83  col1
    84  (select col1 from t2) minus (select col3 from t2);
    85  col1
    86  (select col1 from t2) minus (select col1 from t2 limit 0);
    87  col1
    88  null
    89  2022-01-01
    90  (select col1 from t2) minus (select col2 from t2 limit 0);
    91  col1
    92  null
    93  2022-01-01 00:00:00
    94  (select col1 from t2) minus (select col3 from t2 limit 0);
    95  col1
    96  null
    97  2022-01-01 00:00:00
    98  (select col2 from t2) minus (select col1 from t2 limit 0);
    99  col2
   100  null
   101  2022-01-01 00:00:00
   102  (select col2 from t2) minus (select col2 from t2 limit 0);
   103  col2
   104  null
   105  2022-01-01 00:00:00
   106  (select col2 from t2) minus (select col3 from t2 limit 0);
   107  col2
   108  null
   109  2022-01-01 00:00:00
   110  (select col3 from t2) minus (select col1 from t2 limit 0);
   111  col3
   112  2022-01-02 00:00:00
   113  null
   114  2022-01-01 00:00:00
   115  (select col3 from t2) minus (select col2 from t2 limit 0);
   116  col3
   117  null
   118  2022-01-01 00:00:00
   119  2022-01-02 00:00:00
   120  (select col3 from t2) minus (select col3 from t2 limit 0);
   121  col3
   122  2022-01-02 00:00:00
   123  null
   124  2022-01-01 00:00:00
   125  drop table t2;
   126  drop table if exists t3;
   127  create table t3(
   128  a int
   129  );
   130  insert into t3 values (20),(10),(30),(-10);
   131  drop table if exists t4;
   132  create table t4(
   133  col1 float,
   134  col2 decimal(5,2)
   135  );
   136  insert into t4 values(100.01,100.01);
   137  insert into t4 values(1.10,1.10);
   138  insert into t4 values(0.0,0.0);
   139  insert into t4 values(127.0,127.0);
   140  insert into t4 values(127.44,127.44);
   141  (select a from t3) union (select col1 from t4) minus (select col1 from t4);
   142  a
   143  30.0
   144  10.0
   145  -10.0
   146  20.0
   147  (select a from t3) union (select col2 from t4) minus (select col2 from t4);
   148  a
   149  30.0
   150  10.0
   151  -10.0
   152  20.0
   153  (select a from t3) union (select col1 from t4) minus (select a from t3);
   154  a
   155  127.0
   156  100.01000213623047
   157  1.100000023841858
   158  127.44000244140625
   159  0.0
   160  (select a from t3) union (select col2 from t4) minus (select a from t3);
   161  a
   162  127.44
   163  127.0
   164  100.01
   165  1.1
   166  0.0
   167  (select col1 from t4) minus (select col1 from t4);
   168  col1
   169  (select col1 from t4) minus (select col2 from t4);
   170  col1
   171  100.01000213623047
   172  1.100000023841858
   173  127.44000244140625
   174  (select col2 from t4) minus (select col2 from t4);
   175  col2
   176  (select col2 from t4) minus (select col1 from t4);
   177  col2
   178  127.44
   179  100.01
   180  1.1
   181  drop table t3;
   182  drop table t4;
   183  drop table if exists t5;
   184  create table t5(
   185  a int,
   186  b text
   187  );
   188  insert into t5 values (11, 'aa');
   189  insert into t5 values (33, 'bb');
   190  insert into t5 values (44, 'aa');
   191  insert into t5 values (55, 'cc');
   192  insert into t5 values (55, 'dd');
   193  drop table if exists t6;
   194  create table t6 (
   195  col1 varchar(100),
   196  col2 text,
   197  col3 char(100)
   198  );
   199  insert into t6 values ('aa', '11', 'aa');
   200  insert into t6 values ('bb', '22', '11');
   201  insert into t6 values ('cc', '33', 'bb');
   202  insert into t6 values ('dd', '44', '22');
   203  (select a from t5) minus (select col2 from t6);
   204  a
   205  55
   206  (select col2 from t6) minus (select a from t5);
   207  col2
   208  22
   209  (select b from t5) minus (select col1 from t6);
   210  b
   211  (select b from t5) minus (select col2 from t6);
   212  b
   213  dd
   214  cc
   215  aa
   216  bb
   217  (select b from t5) minus (select col3 from t6);
   218  b
   219  dd
   220  cc
   221  (select col1 from t6) minus (select b from t5);
   222  col1
   223  (select col2 from t6) minus (select b from t5);
   224  col2
   225  44
   226  22
   227  11
   228  33
   229  (select col3 from t6) minus (select b from t5);
   230  col3
   231  22
   232  11
   233  drop table t5;
   234  drop table t6;
   235  drop table if exists t7;
   236  CREATE TABLE t7 (
   237  a int not null,
   238  b char (10) not null
   239  );
   240  insert into t7 values(1,'3'),(2,'4'),(3,'5'),(3,'1');
   241  select * from (select a from t7 minus select a from t7) a;
   242  a
   243  select * from (select a from t7 minus select b from t7) a;
   244  a
   245  2
   246  select * from (select b from t7 minus select a from t7) a;
   247  b
   248  4
   249  5
   250  select * from (select a from t7 minus (select b from t7 limit 2)) a;
   251  a
   252  1
   253  2
   254  select * from (select b from t7 minus (select a from t7 limit 2)) a;
   255  b
   256  3
   257  4
   258  5
   259  select * from (select a from t7 minus select b from t7 limit 1) a;
   260  a
   261  2
   262  select * from (select b from t7 minus select a from t7 limit 1) a;
   263  b
   264  4
   265  select * from (select a from t7 minus select b from t7 where a > 2) a;
   266  a
   267  3
   268  2
   269  select * from (select b from t7 minus select a from t7 where a > 4) a;
   270  b
   271  1
   272  3
   273  4
   274  5
   275  drop table t7;
   276  drop table if exists t8;
   277  create table t8 (
   278  a int primary key,
   279  b int
   280  );
   281  insert into t8 values (1,5),(2,4),(3,3);
   282  set @a=1;
   283  prepare s1 from '(select a from t8 where a>?) minus (select b from t8 where b>?)';
   284  prepare s2 from '(select a from t8 where a>?)';
   285  execute s1 using @a;
   286  invalid input: Incorrect arguments to EXECUTE
   287  execute s1 using @a, @a;
   288  a
   289  2
   290  execute s2 using @a;
   291  a
   292  2
   293  3
   294  execute s2 using @a, @a;
   295  invalid input: Incorrect arguments to EXECUTE
   296  deallocate prepare s1;
   297  deallocate prepare s2;
   298  drop table t8;
   299  drop table if exists t9;
   300  create table t9(
   301  a int,
   302  b varchar
   303  );
   304  insert into t9 values (1, 'a'), (2, 'b'), (3,'c'), (4, 'd');
   305  drop table if exists t10;
   306  create table t10(
   307  c int,
   308  d varchar
   309  );
   310  insert into t10 values (1, 'a'), (10, 'b'), (2,'b'), (2, 'e');
   311  (select a from t9) minus (select tab1.a from t9 as tab1 join t10 as tab2 on tab1.a=tab2.c);
   312  a
   313  3
   314  4
   315  (select a from t9) minus (select tab1.a from t9 as tab1 left join t10 as tab2 on tab1.a=tab2.c);
   316  a
   317  (select a from t9) minus (select tab1.a from t9 as tab1 right join t10 as tab2 on tab1.a=tab2.c);
   318  a
   319  3
   320  4
   321  (select b from t9) minus (select tab1.b from t9 as tab1 join t10 as tab2 on tab1.b=tab2.d);
   322  b
   323  c
   324  d
   325  (select b from t9) minus (select tab1.b from t9 as tab1 left join t10 as tab2 on tab1.b=tab2.d);
   326  b
   327  (select b from t9) minus (select tab1.b from t9 as tab1 right join t10 as tab2 on tab1.b=tab2.d);
   328  b
   329  c
   330  d
   331  (select c from t10) minus (select tab1.a from t9 as tab1 join t10 as tab2 on tab1.a=tab2.c);
   332  c
   333  10
   334  (select c from t10) minus (select tab1.a from t9 as tab1 left join t10 as tab2 on tab1.a=tab2.c);
   335  c
   336  10
   337  (select c from t10) minus (select tab1.a from t9 as tab1 right join t10 as tab2 on tab1.a=tab2.c);
   338  c
   339  10
   340  (select d from t10) minus (select tab1.b from t9 as tab1 join t10 as tab2 on tab1.b=tab2.d);
   341  d
   342  e
   343  (select d from t10) minus (select tab1.b from t9 as tab1 left join t10 as tab2 on tab1.b=tab2.d);
   344  d
   345  e
   346  (select d from t10) minus (select tab1.b from t9 as tab1 right join t10 as tab2 on tab1.b=tab2.d);
   347  d
   348  e
   349  drop table t9;
   350  drop table t10;
   351  drop table if exists t11;
   352  create table t11 (
   353  RID int(11) not null default '0',
   354  IID int(11) not null default '0',
   355  nada varchar(50)  not null,
   356  NAME varchar(50) not null,
   357  PHONE varchar(50) not null);
   358  insert into t11 ( RID,IID,nada,NAME,PHONE) values
   359  (1, 1, 'main', 'a', '111'),
   360  (2, 1, 'main', 'b', '222'),
   361  (3, 1, 'main', 'c', '333'),
   362  (4, 1, 'main', 'd', '444'),
   363  (5, 1, 'main', 'e', '555'),
   364  (6, 2, 'main', 'c', '333'),
   365  (7, 2, 'main', 'd', '454'),
   366  (8, 2, 'main', 'e', '555'),
   367  (9, 2, 'main', 'f', '666'),
   368  (10, 2, 'main', 'g', '777');
   369  select A.NAME, A.PHONE, B.NAME, B.PHONE from t11 A
   370  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)
   371  minus
   372  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
   373  where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
   374  name    phone    name    phone
   375  a    111    null    null
   376  b    222    null    null
   377  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
   378  where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null)
   379  minus
   380  select A.NAME, A.PHONE, B.NAME, B.PHONE from t11 A
   381  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);
   382  name    phone    name    phone
   383  null    null    g    777
   384  null    null    f    666
   385  set @val1=1;
   386  set @val2=2;
   387  prepare s1 from
   388  'select A.NAME, A.PHONE, B.NAME, B.PHONE from t11 A
   389  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)
   390  minus
   391  select A.NAME, A.PHONE, B.NAME, B.PHONE from t11 B left join t11 A on B.NAME = A.NAME and A.IID = ?
   392  where B.IID = ? and (A.PHONE <> B.PHONE or A.NAME is null)';
   393  execute s1 using @val2, @val1, @val1, @val2;
   394  name    phone    name    phone
   395  a    111    null    null
   396  b    222    null    null
   397  deallocate prepare s1;
   398  drop table t11;
   399  drop table if exists t12;
   400  create table t12(
   401  a int primary key,
   402  b int auto_increment
   403  );
   404  insert into t12(a) values (1);
   405  insert into t12(a) values (2);
   406  insert into t12(a) values (3);
   407  insert into t12(a) values (10);
   408  insert into t12(a) values (20);
   409  (select a from t12 ) minus (select b from t12);
   410  a
   411  20
   412  10
   413  (select a from t12 ) minus (select a from t12);
   414  a
   415  (select b from t12 ) minus (select a from t12);
   416  b
   417  4
   418  5
   419  (select b from t12 ) minus (select b from t12);
   420  b
   421  drop table t12;
   422  drop table if exists t2;
   423  create table t2(
   424  col1 date,
   425  col2 datetime,
   426  col3 timestamp
   427  );
   428  
   429  insert into t2 values ();
   430  insert into t2 values('2022-01-01', '2022-01-01', '2022-01-01');
   431  insert into t2 values('2022-01-01', '2022-01-01 00:00:00.99999', '2022-01-01 00:00:00.000000');
   432  insert into t2 values('2022-01-01', '2022-01-01 00:00:00.999999', '2022-01-01 23:59:59.999999');
   433  select * from t2;
   434  col1    col2    col3
   435  null    null    null
   436  2022-01-01    2022-01-01 00:00:00    2022-01-01 00:00:00
   437  2022-01-01    2022-01-01 00:00:01    2022-01-01 00:00:00
   438  2022-01-01    2022-01-01 00:00:01    2022-01-02 00:00:00
   439  (select col1 from t2) minus (select col2 from t2 limit 0);
   440  col1
   441  null
   442  2022-01-01 00:00:00
   443  (select col1 from t2) minus (select col3 from t2 limit 0);
   444  col1
   445  null
   446  2022-01-01 00:00:00
   447  (select col2 from t2) minus (select col1 from t2 limit 0);
   448  col2
   449  2022-01-01 00:00:01
   450  null
   451  2022-01-01 00:00:00
   452  (select col2 from t2) minus (select col3 from t2 limit 0);
   453  col2
   454  2022-01-01 00:00:01
   455  null
   456  2022-01-01 00:00:00
   457  (select col3 from t2) minus (select col1 from t2 limit 0);
   458  col3
   459  2022-01-02 00:00:00
   460  null
   461  2022-01-01 00:00:00
   462  (select col3 from t2) minus (select col2 from t2 limit 0);
   463  col3
   464  null
   465  2022-01-01 00:00:00
   466  2022-01-02 00:00:00
   467  drop table t2;