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

     1  drop table if exists t1;
     2  create table t1(
     3  a int,
     4  b varchar(100)
     5  );
     6  insert into t1 values(30, 'cccc');
     7  insert into t1 values(20, 'bbbb');
     8  insert into t1 values(10, 'aaaa');
     9  insert into t1 values ();
    10  select * from t1;
    11  a    b
    12  30    cccc
    13  20    bbbb
    14  10    aaaa
    15  null    null
    16  drop table if exists t2;
    17  create table t2(
    18  col1 date,
    19  col2 datetime,
    20  col3 timestamp
    21  );
    22  insert into t2 values ();
    23  insert into t2 values('2022-01-01', '2022-01-01', '2022-01-01');
    24  insert into t2 values('2022-01-01', '2022-01-01 00:00:00', '2022-01-01 00:00:00.000000');
    25  insert into t2 values('2022-01-01', '2022-01-01 00:00:00.000000', '2022-01-01 23:59:59.999999');
    26  select * from t2;
    27  col1    col2    col3
    28  null    null    null
    29  2022-01-01    2022-01-01 00:00:00    2022-01-01 00:00:00
    30  2022-01-01    2022-01-01 00:00:00    2022-01-01 00:00:00
    31  2022-01-01    2022-01-01 00:00:00    2022-01-02 00:00:00
    32  (select a from t1 union all select col1 from t2) order by a;
    33  a
    34  null
    35  null
    36  10
    37  20
    38  2022-01-01
    39  2022-01-01
    40  2022-01-01
    41  30
    42  (select a from t1 union all select col1 from t2) order by col1;
    43  invalid input: column col1 does not exist
    44  select a from t1 union all select col1 from t2;
    45  a
    46  30
    47  20
    48  10
    49  null
    50  null
    51  2022-01-01
    52  2022-01-01
    53  2022-01-01
    54  (select a from t1 order by a) union all select col1 from t2;
    55  a
    56  30
    57  20
    58  10
    59  null
    60  null
    61  2022-01-01
    62  2022-01-01
    63  2022-01-01
    64  (select a from t1) union all (select col1 from t2 order by col1);
    65  a
    66  30
    67  20
    68  10
    69  null
    70  null
    71  2022-01-01
    72  2022-01-01
    73  2022-01-01
    74  (select a from t1 order by a) union all (select col1 from t2 order by col1);
    75  a
    76  30
    77  20
    78  10
    79  null
    80  null
    81  2022-01-01
    82  2022-01-01
    83  2022-01-01
    84  (select a from t1 union all select col1 from t2) order by col1;
    85  invalid input: column col1 does not exist
    86  (select a from t1 union select col2 from t2) order by a;
    87  a
    88  null
    89  10
    90  20
    91  2022-01-01 00:00:00
    92  30
    93  (select a from t1 union select col2 from t2) order by col2;
    94  invalid input: column col2 does not exist
    95  select a from t1 union select col2 from t2;
    96  a
    97  30
    98  20
    99  10
   100  null
   101  2022-01-01 00:00:00
   102  (select a from t1 order by a) union select col2 from t2;
   103  a
   104  30
   105  20
   106  10
   107  null
   108  2022-01-01 00:00:00
   109  (select a from t1) union (select col2 from t2 order by col2);
   110  a
   111  30
   112  20
   113  10
   114  null
   115  2022-01-01 00:00:00
   116  (select a from t1 order by a) union (select col2 from t2 order by col2);
   117  a
   118  30
   119  20
   120  10
   121  null
   122  2022-01-01 00:00:00
   123  (select a from t1 union select col2 from t2) order by col2;
   124  invalid input: column col2 does not exist
   125  drop table t1;
   126  drop table t2;
   127  drop table if exists t3;
   128  create table t3(
   129  a tinyint
   130  );
   131  insert into t3 values (20),(10),(30),(-10);
   132  drop table if exists t4;
   133  create table t4(
   134  col1 smallint,
   135  col2 smallint unsigned,
   136  col3 float,
   137  col4 bool
   138  );
   139  insert into t4 values(100, 65535, 127.0, 1);
   140  insert into t4 values(300, 0, 1.0, 0);
   141  insert into t4 values(500, 100, 0.0, 0);
   142  insert into t4 values(200, 35, 127.0, 1);
   143  insert into t4 values(200, 35, 127.44, 1);
   144  select a from t3 union select col1 from t4;
   145  a
   146  20
   147  10
   148  30
   149  -10
   150  100
   151  300
   152  500
   153  200
   154  (select a from t3) union (select col2 from t4 order by col2);
   155  a
   156  20
   157  10
   158  30
   159  -10
   160  65535
   161  0
   162  100
   163  35
   164  select a from t3 union select col2 from t4;
   165  a
   166  20
   167  10
   168  30
   169  -10
   170  65535
   171  0
   172  100
   173  35
   174  select a from t3 union select col3 from t4;
   175  a
   176  20.0
   177  10.0
   178  30.0
   179  -10.0
   180  127.0
   181  1.0
   182  0.0
   183  127.44
   184  select a from t3 union select col4 from t4;
   185  a
   186  true
   187  false
   188  drop table t3;
   189  drop table t4;
   190  drop table if exists t5;
   191  create table t5(
   192  a int,
   193  b text
   194  );
   195  insert into t5 values (12, 'aa');
   196  insert into t5 values (20, 'bb');
   197  insert into t5 values (18, 'aa');
   198  insert into t5 values (15, 'bb');
   199  drop table if exists t6;
   200  create table t6 (
   201  col1 varchar(100),
   202  col2 text
   203  );
   204  insert into t6 values ('aa', '11');
   205  insert into t6 values ('bb', '22');
   206  insert into t6 values ('cc', '33');
   207  insert into t6 values ('dd', '44');
   208  select a from t5 union select col1 from t6;
   209  invalid argument cast to int, bad value aa
   210  select a from t5 union select col2 from t6;
   211  a
   212  12
   213  20
   214  18
   215  15
   216  11
   217  22
   218  33
   219  44
   220  select b from t5 union select col1 from t6;
   221  b
   222  aa
   223  bb
   224  cc
   225  dd
   226  select b from t5 union select col2 from t6;
   227  b
   228  aa
   229  bb
   230  11
   231  22
   232  33
   233  44
   234  drop table t5;
   235  drop table t6;
   236  drop table if exists t7;
   237  CREATE TABLE t7 (
   238  a int not null,
   239  b char (10) not null
   240  );
   241  insert into t7 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
   242  select * from t7 union select * from t7 limit 2;
   243  a    b
   244  1    a
   245  2    b
   246  select * from (select * from t7 union select * from t7) a;
   247  a    b
   248  1    a
   249  2    b
   250  3    c
   251  select * from (select * from t7 union all select * from t7) a;
   252  a    b
   253  1    a
   254  2    b
   255  3    c
   256  3    c
   257  1    a
   258  2    b
   259  3    c
   260  3    c
   261  select * from (select * from t7 union all select * from t7 limit 2) a;
   262  a    b
   263  1    a
   264  2    b
   265  select * from (select * from t7 union select * from t7 limit 2) a;
   266  a    b
   267  1    a
   268  2    b
   269  select * from (select * from t7 union select * from t7 where a > 1) a;
   270  a    b
   271  1    a
   272  2    b
   273  3    c
   274  select * from (select * from t7 union all select * from t7 where a > 1) a;
   275  a    b
   276  1    a
   277  2    b
   278  3    c
   279  3    c
   280  2    b
   281  3    c
   282  3    c
   283  select * from (select * from t7 union select * from t7 where a < 1) a;
   284  a    b
   285  1    a
   286  2    b
   287  3    c
   288  select * from (select * from t7 union all select * from t7 where a < 1) a;
   289  a    b
   290  1    a
   291  2    b
   292  3    c
   293  3    c
   294  select * from (select * from t7 where a > 1 union select * from t7 where a < 1) a;
   295  a    b
   296  2    b
   297  3    c
   298  select * from (select * from t7 where a > 1 union all select * from t7 where a < 1) a;
   299  a    b
   300  2    b
   301  3    c
   302  3    c
   303  select * from (select * from t7 where a >=1 union select * from t7 where a <= 1) a;
   304  a    b
   305  1    a
   306  2    b
   307  3    c
   308  select * from (select * from t7 where a >=1 union all select * from t7 where a <= 1) a;
   309  a    b
   310  1    a
   311  2    b
   312  3    c
   313  3    c
   314  1    a
   315  select * from (select * from t7 where a between 1 and 3 union select * from t7 where a <= 1) a;
   316  a    b
   317  1    a
   318  2    b
   319  3    c
   320  select * from (select * from t7 where a between 1 and 3 union all select * from t7 where a <= 1) a;
   321  a    b
   322  1    a
   323  2    b
   324  3    c
   325  3    c
   326  1    a
   327  select * from (select * from t7 where a between 1 and 3 union all select * from t7 where a between 3 and 1) a;
   328  a    b
   329  1    a
   330  2    b
   331  3    c
   332  3    c
   333  select * from (select * from t7 where a between 1 and 3 union all select * from t7 where a between 3 and 1) a;
   334  a    b
   335  1    a
   336  2    b
   337  3    c
   338  3    c
   339  drop table t7;
   340  create table t8(a int);
   341  create table t9(a int);
   342  create table t10(a int);
   343  insert into t8 values(1),(1);
   344  insert into t9 values(2),(2);
   345  insert into t10 values(3),(3);
   346  select * from t8 union distinct select * from t9 union all select * from t10;
   347  a
   348  1
   349  2
   350  3
   351  3
   352  select * from t8 union distinct select * from t9 union distinct select * from t10;
   353  a
   354  1
   355  2
   356  3
   357  select * from (select * from t8 union distinct select * from t9 union all select * from t10) X;
   358  a
   359  1
   360  2
   361  3
   362  3
   363  select * from t8 union select * from t9 intersect select * from t10;
   364  a
   365  1
   366  select * from t8 union select * from t9 minus select * from t10;
   367  a
   368  1
   369  2
   370  (select * from t8 union select * from t9) intersect select * from t10;
   371  a
   372  (select * from t8 union select * from t9) minus select * from t10;
   373  a
   374  1
   375  2
   376  drop table t8;
   377  drop table t9;
   378  drop table t10;
   379  SELECT 'case+union+test' UNION
   380  SELECT CASE '1' WHEN '2' THEN 'BUG' ELSE 'nobug' END;
   381  case+union+test
   382  case+union+test
   383  nobug
   384  select 'case+union+tet' union
   385  SELECT CASE '1' WHEN '1' THEN 'BUG' ELSE 'nobug' END;
   386  case+union+tet
   387  case+union+tet
   388  BUG
   389  SELECT 1, 2 UNION SELECT 'a', 'b';
   390  invalid argument cast to int, bad value a
   391  select 'a' union select concat('a', -4);
   392  a
   393  a
   394  a-4
   395  select 'a' union select concat('a', -4.5);
   396  a
   397  a
   398  a-4.5
   399  select 'a' union select concat('a', -(4 + 1));
   400  a
   401  a
   402  a-5
   403  select 'a' union select concat('a', 4 - 5);
   404  a
   405  a
   406  a-1
   407  select 'a' union select concat('a', -'3');
   408  a
   409  a
   410  a-3
   411  select 'a' union select concat('a', -concat('3',4));
   412  a
   413  a
   414  a-34
   415  select 'a' union select concat('a', -0);
   416  a
   417  a
   418  a0
   419  select 'a' union select concat('a', -0.0);
   420  a
   421  a
   422  a0.0
   423  select 'a' union select concat('a', -0.0000);
   424  a
   425  a
   426  a0.0000
   427  select concat((select x from (select 'a' as x) as t1 ),
   428  (select y from (select 'b' as y) as t2 )) from (select 1 union select 2 )
   429  as t3;
   430  concat((select x from (select 'a' as x) as t1 ), (select y from (select 'b' as y) as t2 ))
   431  ab
   432  ab
   433  drop table if exists t11;
   434  create table t11(f1 varchar(6));
   435  insert into t11 values ("123456");
   436  select concat(f1, 2) a from t11 union select 'x' a from t11;
   437  a
   438  1234562
   439  x
   440  drop table t11;
   441  drop table if exists t12;
   442  create table t12 (EVENT_ID int auto_increment primary key,  LOCATION char(20));
   443  insert into t12 values (NULL,"Mic-4"),(NULL,"Mic-5"),(NULL,"Mic-6");
   444  SELECT LOCATION FROM t12 WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION FROM t12 WHERE EVENT_ID=3;
   445  LOCATION
   446  Mic-5
   447  Mic-6
   448  SELECT LOCATION FROM t12 WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION FROM t12 WHERE EVENT_ID=3;
   449  LOCATION
   450  Mic-5
   451  Mic-6
   452  SELECT LOCATION FROM t12 WHERE EVENT_ID=2 UNION ALL  SELECT LOCATION FROM t12 WHERE EVENT_ID=3;
   453  LOCATION
   454  Mic-5
   455  Mic-6
   456  drop table t12;
   457  drop table if exists t13;
   458  create table t13 (a int primary key);
   459  insert into t13 values (1);
   460  select * from t13 where 3 in (select (1+1) union select 1);
   461  a
   462  select * from t13 where 3 in (select (1+2) union select 1);
   463  a
   464  1
   465  prepare st_18492 from 'select * from t13 where 3 in (select (1+1) union select 1)';
   466  execute st_18492;
   467  a
   468  prepare st_18493 from 'select * from t13 where 3 in (select (2+1) union select 1)';
   469  execute st_18493;
   470  a
   471  1
   472  deallocate prepare st_18492;
   473  deallocate prepare st_18493;
   474  drop table t13;
   475  select cast(a as DECIMAL(3,2))
   476  from (select 11.1234 as a
   477  UNION select 11.1234
   478  UNION select 11.1234
   479  ) t;
   480  invalid input: 11.1234 beyond the range, can't be converted to Decimal64(3,2).
   481  drop table if exists t14;
   482  CREATE TABLE t14 (
   483  `pseudo` char(35) NOT NULL default '',
   484  `pseudo1` char(35) NOT NULL default '',
   485  `same` tinyint(1) unsigned NOT NULL default '1',
   486  PRIMARY KEY  (`pseudo1`),
   487  KEY `pseudo` (`pseudo`)
   488  );
   489  INSERT INTO t14 (pseudo,pseudo1,same) VALUES
   490  ('joce', 'testtt', 1),('joce', 'tsestset', 1),('dekad', 'joce', 1);
   491  SELECT pseudo FROM t14 WHERE pseudo1='joce' UNION SELECT pseudo FROM t14 WHERE pseudo='joce';
   492  pseudo
   493  dekad
   494  joce
   495  SELECT pseudo1 FROM t14 WHERE pseudo1='joce' UNION SELECT pseudo1 FROM t14 WHERE pseudo='joce';
   496  pseudo1
   497  joce
   498  testtt
   499  tsestset
   500  SELECT * FROM t14 WHERE pseudo1='joce' UNION SELECT * FROM t14 WHERE pseudo='joce' order by pseudo desc,pseudo1 desc;
   501  pseudo    pseudo1    same
   502  joce    tsestset    1
   503  joce    testtt    1
   504  dekad    joce    1
   505  SELECT pseudo1 FROM t14 WHERE pseudo='joce' UNION SELECT pseudo FROM t14 WHERE pseudo1='joce';
   506  pseudo1
   507  testtt
   508  tsestset
   509  dekad
   510  SELECT pseudo1 FROM t14 WHERE pseudo='joce' UNION ALL SELECT pseudo FROM t14 WHERE pseudo1='joce';
   511  pseudo1
   512  testtt
   513  tsestset
   514  dekad
   515  drop table t14;
   516  drop table if exists t15;
   517  CREATE TABLE t15 (
   518  id int(3) unsigned default '0'
   519  );
   520  INSERT INTO t15 (id) VALUES("1");
   521  drop table if exists t16;
   522  CREATE TABLE t16 (
   523  id int(3) unsigned default '0',
   524  id_master int(5) default '0',
   525  text15 varchar(5) default NULL,
   526  text16 varchar(5) default NULL
   527  );
   528  INSERT INTO t16 (id, id_master, text15, text16) VALUES("1", "1", "foo1", "bar1");
   529  INSERT INTO t16 (id, id_master, text15, text16) VALUES("2", "1", "foo2", "bar2");
   530  INSERT INTO t16 (id, id_master, text15, text16) VALUES("3", "1", NULL, "bar3");
   531  INSERT INTO t16 (id, id_master, text15, text16) VALUES("4", "1", "foo4", "bar4");
   532  SELECT 1 AS id_master, 1 AS id, NULL AS text15, 'ABCDE' AS text16
   533  UNION
   534  SELECT id_master, t16.id, text15, text16 FROM t15 LEFT JOIN t16 ON t15.id = t16.id_master;
   535  id_master    id    text15    text16
   536  1    1    null    ABCDE
   537  1    4    foo4    bar4
   538  1    3    null    bar3
   539  1    2    foo2    bar2
   540  1    1    foo1    bar1
   541  SELECT 1 AS id_master, 1 AS id, NULL AS text15, 'ABCDE' AS text16
   542  UNION ALL
   543  SELECT id_master, t16.id, text15, text16 FROM t15 LEFT JOIN t16 ON t15.id = t16.id_master;
   544  id_master    id    text15    text16
   545  1    1    null    ABCDE
   546  1    4    foo4    bar4
   547  1    3    null    bar3
   548  1    2    foo2    bar2
   549  1    1    foo1    bar1
   550  SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text15, 'ABCDE' AS text16
   551  UNION
   552  SELECT id_master, t16.id, text15, text16 FROM t15 LEFT JOIN t16 ON t15.id = t16.id_master;
   553  id_master    id    text15    text16
   554  1    1    ABCDE    ABCDE
   555  1    4    foo4    bar4
   556  1    3    null    bar3
   557  1    2    foo2    bar2
   558  1    1    foo1    bar1
   559  SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text15, 'ABCDE' AS text16
   560  UNION
   561  SELECT id_master, t16.id, text15, text16 FROM t15 right join  t16 ON t15.id = t16.id_master;
   562  id_master    id    text15    text16
   563  1    1    ABCDE    ABCDE
   564  1    1    foo1    bar1
   565  1    2    foo2    bar2
   566  1    3    null    bar3
   567  1    4    foo4    bar4
   568  SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text15, 'ABCDE' AS text16
   569  UNION
   570  SELECT id_master, t16.id, text15, text16 FROM t15 JOIN t16 ON t15.id = t16.id_master;
   571  id_master    id    text15    text16
   572  1    1    ABCDE    ABCDE
   573  1    1    foo1    bar1
   574  1    2    foo2    bar2
   575  1    3    null    bar3
   576  1    4    foo4    bar4
   577  SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text15, 'ABCDE' AS text16
   578  UNION ALL
   579  SELECT id_master, t16.id, text15, text16 FROM t15 LEFT JOIN t16 ON t15.id = t16.id_master;
   580  id_master    id    text15    text16
   581  1    1    ABCDE    ABCDE
   582  1    4    foo4    bar4
   583  1    3    null    bar3
   584  1    2    foo2    bar2
   585  1    1    foo1    bar1
   586  SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text15, 'ABCDE' AS text16
   587  UNION ALL
   588  SELECT id_master, t16.id, text15, text16 FROM t15 right JOIN t16 ON t15.id = t16.id_master;
   589  id_master    id    text15    text16
   590  1    1    ABCDE    ABCDE
   591  1    1    foo1    bar1
   592  1    2    foo2    bar2
   593  1    3    null    bar3
   594  1    4    foo4    bar4
   595  SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text15, 'ABCDE' AS text16
   596  UNION ALL
   597  SELECT id_master, t16.id, text15, text16 FROM t15  JOIN t16 ON t15.id = t16.id_master;
   598  id_master    id    text15    text16
   599  1    1    ABCDE    ABCDE
   600  1    1    foo1    bar1
   601  1    2    foo2    bar2
   602  1    3    null    bar3
   603  1    4    foo4    bar4
   604  drop table t15;
   605  drop table t16;
   606  drop table if exists t17;
   607  create table t17 (
   608  RID int(11) not null default '0',
   609  IID int(11) not null default '0',
   610  nada varchar(50)  not null,
   611  NAME varchar(50) not null,
   612  PHONE varchar(50) not null);
   613  insert into t17 ( RID,IID,nada,NAME,PHONE) values
   614  (1, 1, 'main', 'a', '111'),
   615  (2, 1, 'main', 'b', '222'),
   616  (3, 1, 'main', 'c', '333'),
   617  (4, 1, 'main', 'd', '444'),
   618  (5, 1, 'main', 'e', '555'),
   619  (6, 2, 'main', 'c', '333'),
   620  (7, 2, 'main', 'd', '454'),
   621  (8, 2, 'main', 'e', '555'),
   622  (9, 2, 'main', 'f', '666'),
   623  (10, 2, 'main', 'g', '777');
   624  select A.NAME, A.PHONE, B.NAME, B.PHONE from t17 A
   625  left join t17 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null)
   626  union
   627  select A.NAME, A.PHONE, B.NAME, B.PHONE from t17 B left join t17 A on B.NAME = A.NAME and A.IID = 1
   628  where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
   629  NAME    PHONE    NAME    PHONE
   630  a    111    null    null
   631  b    222    null    null
   632  d    444    d    454
   633  null    null    f    666
   634  null    null    g    777
   635  select A.NAME, A.PHONE, B.NAME, B.PHONE from t17 A
   636  left join t17 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null)
   637  union all
   638  select A.NAME, A.PHONE, B.NAME, B.PHONE from t17 B left join t17 A on B.NAME = A.NAME and A.IID = 1
   639  where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);
   640  NAME    PHONE    NAME    PHONE
   641  a    111    null    null
   642  b    222    null    null
   643  d    444    d    454
   644  d    444    d    454
   645  null    null    f    666
   646  null    null    g    777
   647  drop table t17;
   648  select * from (select 'tb1' as name, 1 as count union all select 'tb3' as name, 3 as count union all select 'tb2' as name, 2 as count) order by count;
   649  name    count
   650  tb1    1
   651  tb2    2
   652  tb3    3