github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/union/union.result (about)

     1  drop table if exists t1;
     2  drop table if exists t2;
     3  create table t1(a int, b int);
     4  insert into t1 values (1, 10),(2, 20);
     5  create table t2(aa int, bb varchar(20));
     6  insert into t2 values (11, "aa"),(22, "bb");
     7  (select a from t1) order by b desc;
     8  a
     9  2
    10  1
    11  (((select a from t1) order by b desc));
    12  a
    13  2
    14  1
    15  (((select a from t1))) order by b desc;
    16  a
    17  2
    18  1
    19  (((select a from t1 order by b desc) limit 1));
    20  a
    21  2
    22  (((select a from t1 order by b desc))) limit 1;
    23  a
    24  2
    25  (select a from t1 union select aa from t2) order by a desc;
    26  a
    27  22
    28  11
    29  2
    30  1
    31  (select a from t1 order by a) order by a;
    32  SQL syntax error: multiple ORDER BY clauses not allowed
    33  (((select a from t1 order by a))) order by a;
    34  SQL syntax error: multiple ORDER BY clauses not allowed
    35  (((select a from t1) order by a)) order by a;
    36  SQL syntax error: multiple ORDER BY clauses not allowed
    37  (select a from t1 limit 1) limit 1;
    38  SQL syntax error: multiple LIMIT clauses not allowed
    39  (((select a from t1 limit 1))) limit 1;
    40  SQL syntax error: multiple LIMIT clauses not allowed
    41  (((select a from t1) limit 1)) limit 1;
    42  SQL syntax error: multiple LIMIT clauses not allowed
    43  (select a from t1 union select aa from t2) order by aa;
    44  invalid input: column aa does not exist
    45  select a from t1 union select a from t1;
    46  a
    47  1
    48  2
    49  drop table if exists t3;
    50  create table t3(
    51  a tinyint
    52  );
    53  insert into t3 values (20),(10),(30),(-10);
    54  drop table if exists t4;
    55  create table t4(
    56  col1 smallint,
    57  col2 smallint unsigned,
    58  col3 float,
    59  col4 bool
    60  );
    61  insert into t4 values(100, 65535, 127.0, 1);
    62  insert into t4 values(300, 0, 1.0, 0);
    63  insert into t4 values(500, 100, 0.0, 0);
    64  insert into t4 values(200, 35, 127.0, 1);
    65  insert into t4 values(200, 35, 127.44, 1);
    66  select a from t3 union select col3 from t4 order by a;
    67  a
    68  -10
    69  0
    70  1
    71  10
    72  20
    73  30
    74  127
    75  127.44
    76  drop table if exists t7;
    77  CREATE TABLE t7 (
    78  a int not null,
    79  b char (10) not null
    80  );
    81  insert into t7 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
    82  select * from (select * from t7 union all select * from t7 limit 2) a;
    83  a	b
    84  1	a
    85  2	b
    86  drop table if exists t2;
    87  CREATE TABLE t2 ( id int(3) unsigned default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL);
    88  INSERT INTO t2 (id, id_master, text1, text2) VALUES("1", "1", "foo1", "bar1");
    89  INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1", "foo2", "bar2");
    90  INSERT INTO t2 (id, id_master, text1, text2) VALUES("3", "1", NULL, "bar3");
    91  INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1", "foo4", "bar4");
    92  SELECT 1 AS id_master, 1 AS id, NULL AS text1, 'ABCDE' AS text2 UNION SELECT id_master, id, text1, text2 FROM t2 order by id;
    93  id_master	id	text	text2
    94  1	1	null	ABCDE
    95  1	1	foo1	bar1
    96  1	2	foo2	bar2
    97  1	3	null	bar3
    98  1	4	foo4	bar4
    99  DROP TABLE IF EXISTS t1;
   100  DROP TABLE IF EXISTS t2;
   101  CREATE TABLE t1(id INT NOT NULL, name CHAR(20) NOT NULL, sex CHAR(4) NOT NULL, PRIMARY KEY (id));
   102  CREATE TABLE t2(id VARCHAR(10) NOT NULL, name VARCHAR(20) NOT NULL, nation VARCHAR(20) NOT NULL, PRIMARY KEY(id));
   103  INSERT INTO t1() VALUES(1,'ronaldo','F'), (2,'kante','M'), (3,'noyer','F'),(4,'modrici','M');
   104  INSERT INTO t2() VALUES(1,'ronaldo','American'), (2,'kante','Franch'), (3,'noyer','Germany'),(4,'modrici','UK');
   105  (SELECT * FROM t1 WHERE id BETWEEN 1 AND 2 ORDER BY name) UNION ALL (SELECT * FROM t2 WHERE nation BETWEEN 'A' AND 'F' ORDER BY id DESC);
   106  id	name	sex
   107  2	kante	M
   108  1	ronaldo	F
   109  1	ronaldo	American
   110  select * from (SELECT * FROM t1 UNION ALL SELECT * FROM t2) a where id > 1 and sex > 'F' order by id;
   111  id	name	sex
   112  2	kante	M
   113  2	kante	Franch
   114  3	noyer	Germany
   115  4	modrici	M
   116  4	modrici	UK