select 1 union ( select 2 union select 3);
1
1
2
3
explain extended
select 1 union ( select 2 union select 3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
4	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
NULL	UNION RESULT	<union1,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`2` AS `2` from (/* select#2 */ select 2 AS `2` union /* select#3 */ select 3 AS `3`) `__4`
select 1 union ( select 1 union select 1);
1
1
explain extended
select 1 union ( select 1 union select 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
4	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
NULL	UNION RESULT	<union1,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4`
select 1 union all ( select 1 union select 1);
1
1
1
explain extended
select 1 union all ( select 1 union select 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
4	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` union all /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4`
select 1 union ( select 1 union all select 1);
1
1
explain extended
select 1 union ( select 1 union all select 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
4	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union1,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4`
select 1 union select 1 union all select 1;
1
1
1
explain extended
select 1 union select 1 union all select 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` union /* select#2 */ select 1 AS `1` union all /* select#3 */ select 1 AS `1`
(select 1 as a) union (select 2) order by a;
a
1
2
explain extended
(select 1 as a) union (select 2) order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
Warnings:
Note	1003	(/* select#1 */ select 1 AS `a`) union (/* select#2 */ select 2 AS `2`) order by `a`
/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`;
a
1
2
explain extended
/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
Warnings:
Note	1003	/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`
select 1 union ( select 1 union (select 1 union (select 1 union select 1)));
1
1
explain extended all
select 1 union ( select 1 union (select 1 union (select 1 union select 1)));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
8	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
7	UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
6	UNION	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
5	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
NULL	UNION RESULT	<union4,5>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
NULL	UNION RESULT	<union3,6>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
NULL	UNION RESULT	<union2,7>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
NULL	UNION RESULT	<union1,8>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	/* select#1/0 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#8/0 */ select `__8`.`1` AS `1` from (/* select#2/1 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#7/1 */ select `__7`.`1` AS `1` from (/* select#3/2 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#6/2 */ select `__6`.`1` AS `1` from (/* select#4/3 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#5/3 */ select 1 AS `1`) `__6`) `__7`) `__8`
#
# MDEV-6341: INSERT ... SELECT UNION with parenthesis
#
create table t1 (a int, b int);
insert into t1 (select 1,1 union select 2,2);
select * from t1 order by 1;
a	b
1	1
2	2
delete from t1;
insert into t1 select 1,1 union select 2,2;
select * from t1 order by 1;
a	b
1	1
2	2
drop table t1;
CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a UNION SELECT 2;
select * from t1 order by 1;
a
1
2
drop table t1;
CREATE OR REPLACE TABLE t1 AS (SELECT 1 AS a UNION SELECT 2);
select * from t1 order by 1;
a
1
2
drop table t1;
CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a);
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select 1 AS `a`)	latin1	latin1_swedish_ci
drop view v1;
CREATE OR REPLACE VIEW v1 AS SELECT 1 AS a UNION SELECT 2;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select 2 AS `2`	latin1	latin1_swedish_ci
drop view v1;
CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a UNION SELECT 2);
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select 2 AS `2`	latin1	latin1_swedish_ci
drop view v1;
#
# MDEV-10028: Syntax error on ((SELECT ...) UNION (SELECT ...))
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
INSERT INTO t1 VALUES (30);
((SELECT a FROM t1) UNION (SELECT a FROM t1));
a
10
20
30
(SELECT * FROM t1 UNION SELECT * FROM t1);
a
10
20
30
((SELECT a FROM t1) LIMIT 1);
a
10
SELECT * FROM (SELECT 1 UNION (SELECT 2 UNION SELECT 3)) t1;
1
1
2
3
DROP TABLE t1;
#
# test of several levels of ORDER BY / LIMIT
#
create table t1 (a int, b int);
insert into t1 (a,b) values (1, 100), (2, 200), (3,30), (4,4);
select a,b from t1 order by 1 limit 3;
a	b
1	100
2	200
3	30
(select a,b from t1 order by 1 limit 3) order by 2 limit 2;
a	b
3	30
1	100
(select 10,1000 union select a,b from t1 order by 1 limit 3) order by 2 limit 2;
10	1000
3	30
1	100
((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1 limit 1;
a	b
1	100
((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1;
a	b
1	100
3	30
drop table t1;
#
# MDEV-16359: union with 3 selects in brackets
#
select 1 union select 1 union select 1;
1
1
(select 1 union select 1 union select 1);
1
1
((select 1) union (select 1) union (select 1));
1
1
#
# MDEV-16357: union in brackets with tail
#             union with tail in brackets
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES(1),(2),(3),(4);
CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (4),(5),(6),(7);
(SELECT a FROM t1  UNION SELECT a FROM t2) LIMIT 1;
a
1
(SELECT a FROM t1  UNION SELECT a FROM t2) ORDER BY a DESC;
a
7
6
5
4
3
2
1
(SELECT a FROM t1  UNION SELECT a FROM t2 LIMIT 1);
a
1
DROP TABLE t1,t2;
#
# MDEV-19324: ((SELECT ...) ORDER BY col ) LIMIT n
#
create table t1 (a int);
insert into t1 values (10),(20),(30);
select a from t1 order by a desc limit 1;
a
30
explain extended select a from t1 order by a desc limit 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 1
explain format=json select a from t1 order by a desc limit 1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "read_sorted_file": {
          "filesort": {
            "sort_key": "t1.a desc",
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "loops": 1,
              "rows": 3,
              "cost": "COST_REPLACED",
              "filtered": 100
            }
          }
        }
      }
    ]
  }
}
(select a from t1 order by a desc) limit 1;
a
30
explain extended (select a from t1 order by a desc) limit 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
Warnings:
Note	1003	(select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 1)
explain format=json (select a from t1 order by a desc) limit 1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "read_sorted_file": {
          "filesort": {
            "sort_key": "t1.a desc",
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "loops": 1,
              "rows": 3,
              "cost": "COST_REPLACED",
              "filtered": 100
            }
          }
        }
      }
    ]
  }
}
(select a from t1 where a=20 union select a from t1) order by a desc limit 1;
a
30
explain extended (select a from t1 where a=20 union select a from t1) order by a desc limit 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
2	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20 union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1
explain format=json (select a from t1 where a=20 union select a from t1) order by a desc limit 1;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "table_name": "<union1,2>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "query_block": {
            "select_id": 1,
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t1",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 3,
                  "cost": "COST_REPLACED",
                  "filtered": 100,
                  "attached_condition": "t1.a = 20"
                }
              }
            ]
          }
        },
        {
          "query_block": {
            "select_id": 2,
            "operation": "UNION",
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t1",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 3,
                  "cost": "COST_REPLACED",
                  "filtered": 100
                }
              }
            ]
          }
        }
      ]
    }
  }
}
((select a from t1 where a=20 union select a from t1) order by a desc) limit 1;
a
30
explain extended ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
2	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using filesort
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20 union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1
explain format=json ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "table_name": "<union1,2>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "query_block": {
            "select_id": 1,
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t1",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 3,
                  "cost": "COST_REPLACED",
                  "filtered": 100,
                  "attached_condition": "t1.a = 20"
                }
              }
            ]
          }
        },
        {
          "query_block": {
            "select_id": 2,
            "operation": "UNION",
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t1",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 3,
                  "cost": "COST_REPLACED",
                  "filtered": 100
                }
              }
            ]
          }
        }
      ]
    }
  }
}
drop table t1;
#
# MDEV-19363: ((SELECT ...) ORDER BY col ) LIMIT n UNION ...
#
create table t1 (pk int);
insert into t1 values (5),(4),(1),(2),(3);
((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4);
pk
1
2
5
explain extended ((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using filesort
2	UNION	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
Warnings:
Note	1003	(/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` order by `test`.`t1`.`pk` limit 2) union (/* select#2 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where `test`.`t1`.`pk` > 4)
explain format=json ((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4);
EXPLAIN
{
  "query_block": {
    "union_result": {
      "table_name": "<union1,2>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "query_block": {
            "select_id": 1,
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "read_sorted_file": {
                  "filesort": {
                    "sort_key": "t1.pk",
                    "table": {
                      "table_name": "t1",
                      "access_type": "ALL",
                      "loops": 1,
                      "rows": 5,
                      "cost": "COST_REPLACED",
                      "filtered": 100
                    }
                  }
                }
              }
            ]
          }
        },
        {
          "query_block": {
            "select_id": 2,
            "operation": "UNION",
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t1",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 5,
                  "cost": "COST_REPLACED",
                  "filtered": 100,
                  "attached_condition": "t1.pk > 4"
                }
              }
            ]
          }
        }
      ]
    }
  }
}
drop table t1;
#
# MDEV-18689: parenthesis around table names and derived tables
#
select * from ( mysql.db );
Host	Db	User	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Create_tmp_table_priv	Lock_tables_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Execute_priv	Event_priv	Trigger_priv	Delete_history_priv
create table t1 (a int);
insert into t1 values (7), (2), (7);
select * from (t1);
a
7
2
7
select * from ((t1));
a
7
2
7
select * from (t1 t) where t.a > 5;
a
7
7
select * from ((t1 t)) where t.a > 5;
a
7
7
select * from ((select a, sum(a) from t1 group by a) t);
a	sum(a)
2	2
7	14
select * from (((select a, sum(a) from t1 group by a) t));
a	sum(a)
2	2
7	14
update (t1 t) set t.a=t.a+1;
select * from t1;
a
8
3
8
drop table t1;
#
# MDEV-19956: query expressions in different contexts
#
create table t1 (a int);
insert into t1 values (3), (7), (1), (2), (4);
create table t2 (a int, b int);
insert into t2  values (3,30), (7,70), (1,10), (2,20), (4,40);
# 1. select
# 1.1. simple select
select * from t1;
a
3
7
1
2
4
(select * from t1);
a
3
7
1
2
4
((select * from t1));
a
3
7
1
2
4
# 1.2. select with tail
select * from t1 order by a;
a
1
2
3
4
7
select a from t1 order by a;
a
1
2
3
4
7
select a from t1 order by 1;
a
1
2
3
4
7
select * from t1 order by t1.a;
a
1
2
3
4
7
(select * from t1 order by t1.a);
a
1
2
3
4
7
((select * from t1 order by t1.a));
a
1
2
3
4
7
(select * from t1 order by t1.a limit 2);
a
1
2
(select a from t1 where a=1) order by 1 desc;
a
1
# 1.2. select with several tails
(select * from t2 order by a limit 2) order by b desc;
a	b
2	20
1	10
(select * from t2 order by t2.a limit 2) order by b desc;
a	b
2	20
1	10
((select * from t2 order by t2.a limit 2) order by b desc);
a	b
2	20
1	10
(((select * from t2 order by t2.a) limit 2) order by b desc);
a	b
2	20
1	10
# 2. union
# 2.1 simple union
select a from t1 union select a from t1;
a
3
7
1
2
4
select a from t1 union all select a from t1;
a
3
7
1
2
4
3
7
1
2
4
select a from t1 union select b from t2;
a
3
7
1
2
4
30
70
10
20
40
(select a from t1) union (select a from t1);
a
3
7
1
2
4
(select a from t1) union (select b from t2);
a
3
7
1
2
4
30
70
10
20
40
select a from t1 where a=1 union select a from t1 where a=3;
a
1
3
(select a from t1 where a=1) union select a from t1 where a=3;
a
1
3
((select a from t1 where a=1) union select a from t1 where a=3);
a
1
3
((select a from t1 where a<=3) union (select a from t1 where a=3));
a
3
1
2
select a from t1 where a=1 union (select a from t1 where a=3);
a
1
3
(select a from t1 where a=1 union (select a from t1 where a=3));
a
1
3
((select a from t1 where a=1 union (select a from t1 where a=3)));
a
1
3
select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=7;
a
1
3
7
( select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=7 );
a
1
3
7
(select a from t1 where a=1 order by a) union select a from t1 where a=3;
a
1
3
(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3;
a
7
1
2
4
3
((select a from t1 where a=1 order by a) union select a from t1 where a=3);
a
1
3
(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3;
a
7
1
2
4
3
( ( select a from t1 where a!=3 order by a desc limit 3)
union
select a from t1 where a=3 );
a
7
4
2
3
( select a from t1 where a <=3 except select a from t1 where a >=3 )
union
select a from t1 where a=7;
a
1
2
7
( ( select a from t1 where a <=3
except
select a from t1 where a >=3 )
union
select a from t1 where a=7 );
a
1
2
7
( select a from t1 where a <=3
except
( select a from t1 where a >=3
union
select a from t1 where a=7 ) );
a
1
2
( ( select a from t1 where a <=3 )
except
( select a from t1 where a >=3
union
select a from t1 where a=7 ) );
a
1
2
# 2.2. union with tail
select a from t1 where a=1 union select a from t1 where a=3 order by a desc;
a
3
1
(select a from t1 limit 2) union select a from t1 where a=3 order by a desc;
a
7
3
select a from t1 where a=4 union (select a from t1 where a <=4 limit 2)
order by a desc;
a
4
3
1
select a from t1 where a=4
union
(select a from t1 where a <=4 order by a limit 2)
order by a desc;
a
4
2
1
( select a from t1 where a=4
union
( select a from t1 where a <=4 order by a limit 2 ) )
order by a desc;
a
4
2
1
( select a from t1 where a <=3 except select a from t1 where a >=3 )
union
select a from t1 where a=7 order by a desc;
a
7
2
1
( select a from t1 where a!=3 order by a desc )
union
select a from t1 where a=3
order by a desc;
a
7
4
3
2
1
(select a from t1 where a=1)
union
(select a from t1 where a=3)
order by a desc;
a
3
1
( select a from t1 where a=1
union
select a from t1 where a=3 )
order by a desc;
a
3
1
( ( select a from t1 where a=1 )
union
( select a from t1 where a=3 ) )
order by a desc;
a
3
1
( select a from t1 where a=1
union
select a from t1 where a=3 )
order by 1 desc;
a
3
1
((select a from t1 where a=1 union select a from t1 where a=3)) order by 1 desc;
a
3
1
(((select a from t1 where a=1) union (select a from t1 where a=3)))
order by 1 desc;
a
3
1
( (select a from t1 where a=1 )
union
(select a from t1 where a=3) )
order by 1 desc;
a
3
1
# 2.3. complex union
select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=2
union
select a from t1 where a=4;
a
1
3
2
4
( select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=2 )
union
select a from t1 where a=4;
a
1
3
2
4
(select a from t1 where a=1 union select a from t1 where a=3)
union
(select a from t1 where a=2 union select a from t1 where a=4);
a
1
3
2
4
(select a from t1 where a=1 union (select a from t1 where a=3))
union
((select a from t1 where a=2) union select a from t1 where a=4);
a
1
3
2
4
( ( select a from t1 where a=1)
union
select a from t1 where a=3 )
union
select a from t1 where a=2
union
select a from t1 where a=4;
a
1
3
2
4
( ( ( select a from t1 where a=1)
union
select a from t1 where a=3 )
union
select a from t1 where a=2 )
union
select a from t1 where a=4;
a
1
3
2
4
select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=2
union
(select a from t1 where a=4);
a
1
3
2
4
select a from t1 where a=1
union
select a from t1 where a=3
union
( select a from t1 where a=2
union
( select a from t1 where a=4 ) );
a
1
3
2
4
select a from t1 where a=1
union
( select a from t1 where a=3
union
( select a from t1 where a=2
union
( select a from t1 where a=4 ) ) );
a
1
3
2
4
# 2.4. complex union with tail
( ( select a from t1 where a=1 union select a from t1 where a=3 )
order by a desc )
union
( ( select a from t1 where a=2 union select a from t1 where a=4 )
order by a desc );
a
3
1
4
2
( ( select a from t1 where a=1 union select a from t1 where a=3 )
order by a desc )
union
( ( select a from t1 where a=2 union select a from t1 where a=4 )
order by a desc )
order by a;
a
1
2
3
4
( select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=2  order by a desc limit 2 )
union
select a from t1 where a=4
order by a;
a
2
3
4
( select a from t1 where a=1
union
select a from t1 where a=3 order by a desc )
union
select a from t1 where a=2  order by a desc limit 2;
a
3
2
( ( select a from t1 where a >= 2
union
select a from t1 where a=1 order by a desc limit 2 )
union
select a from t1 where a=3  order by a limit 2 )
union
select a from t1 where a=1;
a
3
4
1
# 3. TVC
# 3.1. simple TVC
values (3), (7), (1);
3
3
7
1
(values (3), (7), (1));
3
3
7
1
((values (3), (7), (1)));
3
3
7
1
# 3.2. simple TVC with tail(s)
values (3), (7), (1) order by 1;
3
1
3
7
(values (3), (7), (1)) order by 1;
3
1
3
7
((values (3), (7), (1))) order by 1;
3
1
3
7
(((values (3), (7), (1))) order by 1);
3
1
3
7
(values (3), (7), (1) limit 2) order by 1 desc;
3
7
3
((values (3), (7), (1)) order by 1 desc) limit 2;
3
7
3
(((values (3), (7), (1)) order by 1 desc) limit 2);
3
7
3
# 3.3. union of TVCs
values (3), (7), (1) union values (3), (4), (2);
3
3
7
1
4
2
values (3), (7), (1) union all values (3), (4), (2);
3
3
7
1
3
4
2
values (3), (7), (1) union values (3), (4), (2);
3
3
7
1
4
2
values (3), (7), (1) except values (3), (4), (2);
3
7
1
(values (3), (7), (1)) union (values (3), (4), (2));
3
3
7
1
4
2
(values (3), (7), (1)) union (values (3), (4), (2)) union values (5), (7);
3
3
7
1
4
2
5
(values (3), (7), (1)) union (values (3), (4), (2)) union (values (5), (7));
3
3
7
1
4
2
5
(values (3), (7), (1) union values (3), (4), (2)) union values (5), (7);
3
3
7
1
4
2
5
values (3), (7), (1) union (values (3), (4), (2) union values (5), (7));
3
3
7
1
4
2
5
(values (3), (7), (1) union ((values (3), (4), (2) union values (5), (7))));
3
3
7
1
4
2
5
# 3.4. tailed union of TVCs
values (3), (7), (1) union values (3), (4), (2) order by 1;
3
1
2
3
4
7
(values (3), (7), (1) union values (3), (4), (2)) order by 1;
3
1
2
3
4
7
(values (3), (7), (1) union values (3), (4), (2)) order by 1;
3
1
2
3
4
7
values (3), (7), (1) union (values (3), (4), (2)) order by 1;
3
1
2
3
4
7
(values (3), (7), (1) union values (3), (4), (2)) order by 1;
3
1
2
3
4
7
((values (3), (7), (1)) union values (3), (4), (2)) order by 1;
3
1
2
3
4
7
# 3.5. union of tailed TVCs
(values (3), (7), (1) order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2);
3
1
3
4
((values (3), (7), (1) order by 1) limit 2)
union
((values (3), (4), (2) order by 1 desc) limit 2);
3
1
3
4
(((values (3), (7), (1)) order by 1) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2);
3
1
3
4
# 3.6. tailed union of tailed TVCs
(values (3), (7), (1) order by 1 limit 2)
union
values (3), (4), (2)
order by 1;
3
1
2
3
4
((values (3), (7), (1)) order by 1 limit 2)
union
((values (3), (4), (2) order by 1 desc) limit 2)
order by 1;
3
1
3
4
# 3.7 [tailed] union of [tailed] select and [tailed] TVC
(select a from t1 where a <=3 order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2);
a
1
2
4
3
((select a from t1 where a <=3) order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2);
a
1
2
4
3
(((select a from t1 where a <=3) order by a) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2);
a
1
2
4
3
( (((select a from t1 where a <=3) order by a) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2) );
a
1
2
4
3
(select a from t1 where a <=3 order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2)
order by a;
a
1
2
3
4
((select a from t1 where a <=3) order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2)
order by a;
a
1
2
3
4
(((select a from t1 where a <=3) order by a) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2)
order by a;
a
1
2
3
4
(((values (3), (4), (2)) order by 1 desc) limit 2);
3
4
3
( (((select a from t1 where a <=3) order by a) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2) )
order by a;
a
1
2
3
4
(values (3), (4), (2) order by 1 desc limit 2)
union
(select a from t1 where a <=3 order by 1 limit 2);
3
4
3
1
2
(values (3), (4), (2) order by 1 desc limit 2)
union
((select a from t1 where a <=3) order by 1 limit 2);
3
4
3
1
2
(((values (3), (4), (2)) order by 1 desc) limit 2)
union
(((select a from t1 where a <=3) order by 1) limit 2);
3
4
3
1
2
(((values (3), (4), (2)) order by 1 desc) limit 2)
union
(((select a from t1 where a <=3) order by a) limit 2)
order by 1;
3
1
2
3
4
( select a from t1 where a=1
union
values (3), (4), (2) order by 1 desc )
union
select a from t1 where a=2 order by a desc limit 3;
a
4
3
2
4. CTE
4.1. simple select with simple CTE
with t as (select * from t1 where a <=3)
select * from t;
a
3
1
2
with t as (select * from t1 where a <=3)
(select * from t);
a
3
1
2
with t as (select * from t1 where a <=3)
((select * from t));
a
3
1
2
with t as ((select * from t1 where a <=3))
select * from t;
a
3
1
2
with t as (((select * from t1 where a <=3)))
select * from t;
a
3
1
2
4.2. tailed select with simple CTE
with t as (select * from t1 where a <=3)
select * from t order by a;
a
1
2
3
with t as (select * from t1 where a <=3)
(select * from t) order by a;
a
1
2
3
with t as (select * from t1 where a <=3)
(select * from t) order by a desc limit 2;
a
3
2
4.3. [tailed] select with tailed CTE
with t as (select * from t1 where a >=2 order by a limit 2)
select * from t;
a
2
3
with t as (((select * from t1 where a >=2) order by a desc) limit 2)
select * from t;
a
7
4
with t as (select * from t1 where a >=2 order by a desc limit 2)
select * from t order by a;
a
4
7
4.4. [tailed] union with CTE
with t as (select * from t1 where a <=3)
select a from t1 where a=1 union select a from t where a=3;
a
1
3
with t as (select * from t1 where a <=3)
(select a from t) union (select b from t2);
a
3
1
2
30
70
10
20
40
with t as (select * from t1 where a <=3)
(select a from t) union (select b as a from t2) order by a desc;
a
70
40
30
20
10
3
2
1
4.5. [tailed] union with [tailed] union in CTE
with t as (select * from t1 where a < 3 union select * from t1 where a > 3)
select a from t1 where a=1 union select a from t where a=7;
a
1
7
with t as
( select * from t1 where a < 3
union
select * from t1 where a > 3
order by a desc limit 3 )
select a from t1 where a=4 union select a from t where a=7;
a
4
7
with t as
( select * from t1 where a < 3
union
select * from t1 where a > 3
order by a desc limit 3 )
select a from t1 where a=4 union select a from t where a=7 order by a desc;
a
7
4
with t as
( (select * from t1 where a < 3)
union
(select * from t1 where a > 3)
order by a desc limit 3 )
select a from t1 where a=4 union select a from t where a=7 order by a desc;
a
7
4
with t as
( (select * from t1 where a < 3)
union
(select * from t1 where a > 3)
order by a desc limit 3 )
(select a from t1 where a=4 union select a from t where a=7 order by a desc);
a
7
4
with t as
( (select * from t1 where a < 3)
union
(select * from t1 where a > 3)
order by a desc limit 3 )
((select a from t1 where a=4 union select a from t where a=7) order by a desc);
a
7
4
with t as
( select * from t1 where a < 3
union
values (4), (7)
order by a desc limit 3 )
select a from t1 where a=4 union select a from t where a=7 order by a desc;
a
7
4
4.6. [tailed] union with [tailed] union of TVC in CTE
with t(a) as
( values (2), (1)
union
(values (4), (7))
order by 1 desc limit 3 )
select a from t1 where a=4 union select a from t where a=7 order by a desc;
a
7
4
with t(a) as
( (values (2), (1))
union
(values (4), (7) order by 1 desc)
order by 1 desc limit 3 )
select a from t1 where a=1 union select a from t where a=7 order by a desc;
a
7
1
with t(a) as
( (values (2), (1))
union
(values (4), (7) order by 1 desc)
order by 1 limit 3 )
select a from t where a=1 union values (7) order by a desc;
a
7
1
with t(a) as
( (values (2), (1))
union
(values (4), (7) order by 1 desc ) )
select a from t where a=1 union select 7 order by a desc;
a
7
1
4.5. [tailed] union with two CTEs
with t as (select * from t1 where a < 3),
s as (select * from t1 where a > 3)
select a from t where a=1 union select a from s where a=7 order by a desc;
a
7
1
with t as (select * from t1 where a < 3),
s as (select * from t1 where a > 3)
(select a from t where a=1 union select a from s where a=7 order by a desc);
a
7
1
with t as (select * from t1 where a < 3),
s as (select * from t1 where a > 3)
(select a from t where a=1 union select a from s where a=7) order by a desc;
a
7
1
with t as (select * from t1 where a < 3),
s as (select * from t where a > 3)
select a from t where a=1 union select a from s where a=7 order by a desc;
a
1
# 5. single-row subquery in expression
# 5.1. [tailed] simple select in expression
select (a+1) + b as r from t2;
r
34
78
12
23
45
select ((a+1) + b) as r from t2;
r
34
78
12
23
45
select (b + (select 1)) as r from t2;
r
31
71
11
21
41
select (select a from t1 where a <=3 order by a desc limit 1) as r from t2;
r
3
3
3
3
3
select
(select a from t1 where a <=3 order by a desc limit 1) as r from t2;
r
3
3
3
3
3
select (select 100) as r from t2;
r
100
100
100
100
100
select ((select 100)) as r from t2;
r
100
100
100
100
100
select (select 100) + t2.b as r from t2;
r
130
170
110
120
140
select ((select 100) + t2.b) as r from t2;
r
130
170
110
120
140
# 5.2. [tailed] TVC in expression
select (values (200)) as r from t2;
r
200
200
200
200
200
select ((values (200))) as r from t2;
r
200
200
200
200
200
select (values (200)) + t2.b as r from t2;
r
230
270
210
220
240
select ((values (200)) + t2.b) as r from t2;
r
230
270
210
220
240
select (values (200), (300) order by 1 desc limit 1) as r from t2;
r
300
300
300
300
300
select ((values (200), (300)) order by 1 desc limit 1) as r from t2;
r
300
300
300
300
300
select (select * from t1 limit 1) as r from t2;
r
3
3
3
3
3
select (select * from t1 order by a limit 1) as r from t2;
r
1
1
1
1
1
select ((select * from t1 order by a limit 1)) as r from t2;
r
1
1
1
1
1
((select ((select * from t1 order by a limit 1)) as r from t2));
r
1
1
1
1
1
select (select * from t1 order by a limit 1) + t2.b as r from t2;
r
31
71
11
21
41
# 5.3. [tailed] union in expression
select
( select a from t1 where a<3  union select a from t1 where a>4
order by a desc limit 1 ) as r
from t1;
r
7
7
7
7
7
select
( (select a from t1 where a<3)  union (select a from t1 where a>4)
order by a desc limit 1 ) as r
from t1;
r
7
7
7
7
7
select
( select a from t1 where a<3  union select a from t1 where a>4
order by a desc limit 1 ) + t1.a as r
from t1;
r
10
14
8
9
11
select
t1.a +
( select a from t1 where a<3  union select a from t1 where a>4
order by a desc limit 1 ) as r
from t1;
r
10
14
8
9
11
select
( (select a from t1 where a<3 union select a from t1 where a>4
order by a desc limit 1 ) + t1.a) as r
from t1;
r
10
14
8
9
11
select
( ( (select a from t1 where a<3)  union (select a from t1 where a>4)
order by a desc limit 1 ) + t1.a ) as r
from t1;
r
10
14
8
9
11
# 5.4. [tailed] select with simple CTE in expression
select
( with t as (select * from t1 where a <=3)
select a from t limit 1) as r
from t2;
r
3
3
3
3
3
select
( with t as (select * from t1 where a <=3)
select a from t limit 1) + t2.b as r
from t2;
r
33
73
13
23
43
select
t2.b +( with t as (select * from t1 where a <=3)
select a from t limit 1) as r
from t2;
r
33
73
13
23
43
select
((( with t as (select * from t1 where a <=3)
select a from t limit 1) + t2.b)) as r
from t2;
r
33
73
13
23
43
select
( with t as (select * from t1 where a <=3)
select a from t limit 1) + 100 as r
from t2;
r
103
103
103
103
103
select
( with t as (select * from t1 where a <=3)
select a from t limit 1) + (select 100) as r
from t2;
r
103
103
103
103
103
select
( with t as (select * from t1 where a <=3)
select a from t limit 1) + t2.b + (select 100) as r
from t2;
r
133
173
113
123
143
select
( with t as (select * from t1 where a <=3)
select a from t limit 1 ) + (t2.b + (select 100)) as r
from t2;
r
133
173
113
123
143
select
( with t as (select * from t1 where a <=3)
select a from t limit 1 ) + t2.b + (values (100)) as r
from t2;
r
133
173
113
123
143
# 5.5. [tailed] union with simple CTE in expression
select
( with t as (select * from t1 where a <=3)
select a from t union select b from t2 order by a desc limit 1) as r
from t2;
r
70
70
70
70
70
select
( with t as (select * from t1 where a <=3)
(select a from t) union (select b from t2) order by a desc limit 1) as r
from t2;
r
70
70
70
70
70
select
( with t as (select * from t1 where a <=3)
(select a from t) union (select b from t2) order by a desc limit 1) as r
from t2;
r
70
70
70
70
70
select
( ( with t as (select * from t1 where a <=3)
(select a from t) union (select b from t2) order by a desc limit 1) +
t2.a ) as r
from t2;
r
73
77
71
72
74
# 5.6. [tailed] union with CTE with union in expression
select
( with t as
( select * from t1 where a < 3
union
select * from t1 where a > 3
order by a desc limit 3 )
select a from t1 where a=4 union select a from t where a=7 limit 1) as r
from t2;
r
4
4
4
4
4
select
( with t as
( select * from t1 where a < 3
union
select * from t1 where a > 3
order by a desc limit 3 )
select a from t1 where a=4 union select a from t where a=7 limit 1) +
t2. b as r
from t2;
r
34
74
14
24
44
# 5.7. [tailed] union of TVCs with CTE with union in expression
select
( with t(a) as
( (values (2), (1))
union
(values (4), (7) order by 1 limit 1)
order by 1 desc limit 3 ) select * from t limit 1 ) + t2.b as r
from t2;
r
34
74
14
24
44
select
( with t(a) as
( select 2 union select 1
union
(values (4), (7) order by 1 limit 1)
order by 1 limit 3 ) select * from t limit 1 ) + t2.b as r
from t2;
r
31
71
11
21
41
# 6. subquery
# 6.1. TVC in IN subquery
select a from t1 where a in (1,8,7);
a
7
1
select a from t1 where a in (values (1), (8), (7));
a
7
1
# 6.2. simple select in IN subquery
select a from t1 where a in (select a from t2 where a <= 3);
a
3
1
2
select a from t1 where a in ((select a from t2 where a <= 3));
a
3
1
2
# 6.3. union in IN subquery
select a from t1
where a in (select a from t1 where a<=2 union select a from t2 where b>40);
a
7
1
2
select a from t1
where a in (select a from t1 where a<=2 union (select a from t2 where b>40));
a
7
1
2
select a from t1
where a in ((select a from t1 where a<=2) union select a from t2 where b>40);
a
7
1
2
select a from t1
where a in ((select a from t1 where a<=2) union (select a from t2 where b>40));
a
7
1
2
# 6.4. select with CTE and union in IN subquery
with t as (select a from t1 where a<=2)
select a from t1
where a in ((select a from t) union (select a from t2 where b>40));
a
7
1
2
with t as ((select a from t1 where a<=2))
select a from t1
where a in ((select a from t) union (select a from t2 where b>40));
a
7
1
2
with t as ((select a from t1 where a<=2) order by a desc limit 1)
select a from t1
where a in ((select a from t) union (select a from t2 where b>40));
a
7
2
# 6.5. NOT IN subquery
select a from t1 where a not in (1,8,7);
a
3
2
4
select a from t1 where a not in (values (1), (8), (7));
a
3
2
4
select a from t1 where a not in (select a from t2 where a <= 3);
a
7
4
select a from t1 where a not in ((select a from t2 where a <= 3));
a
7
4
select a from t1
where a not in (select a from t1 where a<=2
union
select a from t2 where b>40);
a
3
4
select a from t1
where a not in (select a from t1 where a<=2
union
(select a from t2 where b>40));
a
3
4
select a from t1
where a not in ((select a from t1 where a<=2)
union
select a from t2 where b>40);
a
3
4
select a from t1
where a not in ((select a from t1 where a<=2)
union
(select a from t2 where b>40));
a
3
4
with t as ((select a from t1 where a<=2) order by a desc limit 1)
select a from t1
where a not in ((select a from t) union (select a from t2 where b>40));
a
3
1
4
# 6.6. IN subquery in expression
select 1 in (select a from t1) as r, b from t2 where b > 30;
r	b
1	70
1	40
select (1 in (select a from t1)) as r, b from t2 where b > 30;
r	b
1	70
1	40
select 1 in ((select a from t1)) as r, b from t2 where b > 30;
r	b
1	70
1	40
select ((1 in ((select a from t1)))) as r, b from t2 where b > 30;
r	b
1	70
1	40
select ((1 in ((select a from t1)))) as r, b from t2 where b > 30;
r	b
1	70
1	40
select b, if (a in (select a from t1 where a > 3),10,20) as r from t2;
b	r
30	20
70	10
10	20
20	20
40	10
select b, if (a in ((select a from t1 where a > 3)),10,20) as r from t2;
b	r
30	20
70	10
10	20
20	20
40	10
# 6.7. IN subquery in SF and SP
create function f1(x int) returns int
return (x in ((select a from t1 where a <= 4)));
select b, f1(a) from t2 where b > 20;
b	f1(a)
30	1
70	0
40	1
drop function f1;
create function f2(x int) returns int
if x in ((select a from t1 where a <= 4))
then return 100;
else return 200;
end if |
select b, f2(a) from t2 where b > 20;
b	f2(a)
30	100
70	200
40	100
drop function f2;
# 6.8. EXISTS subquery
select exists (select a from t1 where t1.a=t2.a) as r, b from t2 where b > 30;
r	b
1	70
1	40
select exists ((select a from t1 where t1.a=t2.a)) as r, b from t2 where b > 30;
r	b
1	70
1	40
with s as
( (select * from t1 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from t2 where exists ((select * from s where s.a=t2.a));
a	b
3	30
7	70
4	40
with t as ((select a from t1 where a<=2) order by a desc limit 1)
select a from t2
where not exists ((select a from t where t.a=t2.a)
except
(select a from t where a>40));
a
3
7
1
4
# 6.9. EXISTS subquery with SF and SP
create function f1(x int) returns int
return exists (((select * from t1 where x=a and a <= 4)));
select b, f1(a) from t2 where b > 20;
b	f1(a)
30	1
70	0
40	1
drop function f1;
create function f2(x int) returns int
if not exists (((select * from t1 where x=a and a <= 4)))
then return 100;
else return 200;
end if |
select b, f2(a) from t2 where b > 20;
b	f2(a)
30	200
70	100
40	200
drop function f2;
# 6.10. subquery with ANY
select a from t1 where a = any(select a from t2 where a <= 3);
a
3
1
2
select a from t1 where a = any((select a from t2 where a <= 3));
a
3
1
2
select a from t1
where a = any (select a from t1 where a<=2
union
select a from t2 where b>40);
a
7
1
2
select a from t1
where a = any(select a from t1 where a<=2
union
(select a from t2 where b>40));
a
7
1
2
select a from t1
where a = any((select a from t1 where a<=2)
union
select a from t2 where b>40);
a
7
1
2
select a from t1
where a = any((select a from t1 where a<=2)
union
(select a from t2 where b>40));
a
7
1
2
# 7. create table as
# 7.1. create table as simple select
create table t as select * from t1 where a <=3;
select * from t;
a
3
1
2
drop table t;
create table t select * from t1 where a <=3;
select * from t;
a
3
1
2
drop table t;
create table t as (select * from t1 where a <=3);
select * from t;
a
3
1
2
drop table t;
create table t (select * from t1 where a <=3);
select * from t;
a
3
1
2
drop table t;
create table t as ((select * from t1 where a <=3));
select * from t;
a
3
1
2
drop table t;
create table t ((select * from t1 where a <=3));
select * from t;
a
3
1
2
drop table t;
create table t(a decimal(10,2)) as select * from t1 where a <=3;
select * from t;
a
3.00
1.00
2.00
drop table t;
create table t(a decimal(10,2)) select * from t1 where a <=3;
select * from t;
a
3.00
1.00
2.00
drop table t;
create table t(a decimal(10,2)) as (select * from t1 where a <=3);
select * from t;
a
3.00
1.00
2.00
drop table t;
create table t(a decimal(10,2)) (select * from t1 where a <=3);
select * from t;
a
3.00
1.00
2.00
drop table t;
create table t(a decimal(10,2)) as ((select * from t1 where a <=3));
select * from t;
a
3.00
1.00
2.00
drop table t;
create table t(a decimal(10,2)) ((select * from t1 where a <=3));
select * from t;
a
3.00
1.00
2.00
drop table t;
create table t(a decimal(10,2), b int) as
((select a, a as b from t1 where a <=3));
select * from t;
a	b
3.00	3
1.00	1
2.00	2
drop table t;
create table t(a decimal(10,2), b int)
((select a, a as b from t1 where a <=3));
select * from t;
a	b
3.00	3
1.00	1
2.00	2
drop table t;
# 7.2. create table as tailed select
create table t as select * from t1 where a <=3 order by 1;
select * from t;
a
1
2
3
drop table t;
create table t select * from t1 where a <=3 order by 1;
select * from t;
a
1
2
3
drop table t;
create table t as select * from t1 where a <=3 order by 1 desc limit 2;
select * from t;
a
3
2
drop table t;
create table t select * from t1 where a <=3 order by 1 desc limit 2;
select * from t;
a
3
2
drop table t;
create table t as ((select * from t1 where a <=3) order by 1 desc) limit 2;
select * from t;
a
3
2
drop table t;
create table t ((select * from t1 where a <=3) order by 1 desc) limit 2;
select * from t;
a
3
2
drop table t;
# 7.3. create table as select wihout from clause
create table t as select 10;
select * from t;
10
10
drop table t;
create table t select 10;
select * from t;
10
10
drop table t;
# 7.4. create table as union of selects wihout from clause
create table t as select 10 union select 70;
select * from t;
10
10
70
drop table t;
create table t select 10 union select 70;
select * from t;
10
10
70
drop table t;
# 7.5. create table as TVC
create table t as values (7), (3), (8);
select * from t;
7
7
3
8
drop table t;
create table t values (7), (3), (8);
select * from t;
7
7
3
8
drop table t;
create table t as (values (7), (3), (8));
select * from t;
7
7
3
8
drop table t;
create table t (values (7), (3), (8));
select * from t;
7
7
3
8
drop table t;
create table t as ((values (7), (3), (8)));
select * from t;
7
7
3
8
drop table t;
create table t ((values (7), (3), (8)));
select * from t;
7
7
3
8
drop table t;
# 7.6. create table as select with CTE
create table t as
with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2)
select * from s;
select * from t;
a
3
2
drop table t;
create table t
with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2)
select * from s;
select * from t;
a
3
2
drop table t;
create table t as
with s as
( (select * from t1 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from s;
select * from t;
a
4
3
8
7
drop table t;
create table t
with s as
( (select * from t1 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from s;
select * from t;
a
4
3
8
7
drop table t;
create table t as
with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2)
select * from s;
select * from t;
a
3
2
drop table t;
# 7.7. create table as union with CTE
create table t as
with s as
( (select * from t1 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from s where a>=7 union select a from t2 where b<40;
select * from t;
a
8
7
3
1
2
drop table t;
create table t
with s as
( (select * from t1 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from s where a>=7 union select a from t2 where b<40;
select * from t;
a
8
7
3
1
2
drop table t;
create table t
with s as
( (select * from t1 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from s where a>=7 union select a from t2 where b<40;
select * from t;
a
8
7
3
1
2
drop table t;
create table t as
with s as
( ( (select * from t1 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) ) )
select * from s where a>=7 union select a from t2 where b<40;
select * from t;
a
8
7
3
1
2
drop table t;
create table t
with s as
( ( (select * from t1 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) ) )
select * from s where a>=7 union select a from t2 where b<40;
select * from t;
a
8
7
3
1
2
drop table t;
create table t as
with s as
( (select * from t1 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from s where a>=7 union select a from s where a<4;
select * from t;
a
8
7
3
drop table t;
create table t
with s as
( (select * from t1 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from s where a>=7 union select a from s where a<4;
select * from t;
a
8
7
3
drop table t;
create table t as
with s as
( select * from t1 where a <=4 or a=7 )
select * from s where a>=7 union select a from s where a<3;
select * from t;
a
7
1
2
drop table t;
create table t
with s as
(select * from t1 where a <=4 or a=7)
select * from s where a>=7 union select a from s where a<3;
select * from t;
a
7
1
2
drop table t;
create table t (a int)
with s as
( select * from t1 where a <=4 or a=7 )
select * from s where a>=7 union select a from s where a<3;
select * from t;
a
7
1
2
drop table t;
create table t (a int)
with s as
(select * from t1 where a <=4 or a=7)
select * from s where a>=7 union select a from s where a<3;
select * from t;
a
7
1
2
drop table t;
create table t
with s as
( select * from t1 where a <=4 or a=7 )
select * from s where a>=7 union select a from s where a<3
order by a desc limit 2;
select * from t;
a
7
2
drop table t;
create table t
( with s as
( select * from t1 where a <=4 or a=7 )
select * from s where a>=7 union select a from s where a<3
order by a desc limit 2 );
select * from t;
a
7
2
drop table t;
# 8. insert
create table t (c int, d int);
# 8.1. insert simple select
insert into t select * from t2 where a <=3;
select * from t;
c	d
3	30
1	10
2	20
delete from t;
insert into t(c) select t2.a from t2 where a <=3;
select * from t;
c	d
3	NULL
1	NULL
2	NULL
delete from t;
insert into t (select * from t2 where a <=3);
select * from t;
c	d
3	30
1	10
2	20
delete from t;
insert into t(c) (select t2.a from t2 where a <=3);
select * from t;
c	d
3	NULL
1	NULL
2	NULL
delete from t;
insert into t ((select * from t2 where a <=3));
select * from t;
c	d
3	30
1	10
2	20
delete from t;
insert into t(c) ((select t2.a from t2 where a <=3));
select * from t;
c	d
3	NULL
1	NULL
2	NULL
delete from t;
drop table t;
create table t(c decimal(10,2));
insert into t select * from t1 where a <=3;
select * from t;
c
3.00
1.00
2.00
delete from t;
insert into t(c) select * from t1 where a <=3;
select * from t;
c
3.00
1.00
2.00
delete from t;
insert into t (select * from t1 where a <=3);
select * from t;
c
3.00
1.00
2.00
delete from t;
insert into t(c) (select * from t1 where a <=3);
select * from t;
c
3.00
1.00
2.00
delete from t;
insert into t ((select * from t1 where a <=3));
select * from t;
c
3.00
1.00
2.00
delete from t;
insert into t(c) ((select * from t1 where a <=3));
select * from t;
c
3.00
1.00
2.00
delete from t;
drop table t;
create table t(a decimal(10,2), b int);
insert into t ((select * from t2 where a <=3));
select * from t;
a	b
3.00	30
1.00	10
2.00	20
delete from t;
insert into t(a) ((select a from t2 where a <=3));
select * from t;
a	b
3.00	NULL
1.00	NULL
2.00	NULL
delete from t;
drop table t;
create table t(c int, d int);
# 8.2. insert tailed select
insert into t select * from t2 where a <=3 order by 1;
select * from t;
c	d
1	10
2	20
3	30
delete from t;
insert into t(c) select a from t2 where a <=3 order by 1;
select * from t;
c	d
1	NULL
2	NULL
3	NULL
delete from t;
insert into t select * from t2 where a <=3 order by 1 desc limit 2;
select * from t;
c	d
3	30
2	20
delete from t;
insert into t(c) select a from t2 where a <=3 order by 1 desc limit 2;
select * from t;
c	d
3	NULL
2	NULL
delete from t;
insert into t ((select * from t2 where a <=3) order by 1 desc) limit 2;
select * from t;
c	d
3	30
2	20
delete from t;
insert into t(c) ((select a from t2 where a <=3) order by 1 desc) limit 2;
select * from t;
c	d
3	NULL
2	NULL
delete from t;
# 8.3. insert select without from clause
insert into t select 10, 20;
select * from t;
c	d
10	20
delete from t;
insert into t(c) select 10;
select * from t;
c	d
10	NULL
delete from t;
# 8.4. insert union of selects without from clause
insert into t select 10,20 union select 70,80;
select * from t;
c	d
10	20
70	80
delete from t;
insert into t(c) select 10 union select 70;
select * from t;
c	d
10	NULL
70	NULL
delete from t;
# 8.5. insert TVC
insert into t values (7,70), (3,30), (8,80);
select * from t;
c	d
7	70
3	30
8	80
delete from t;
insert into t(c) values (7), (3), (8);
select * from t;
c	d
7	NULL
3	NULL
8	NULL
delete from t;
insert into t (values (7,70), (3,30), (8,80));
select * from t;
c	d
7	70
3	30
8	80
delete from t;
insert into t(c) (values (7), (3), (8));
select * from t;
c	d
7	NULL
3	NULL
8	NULL
delete from t;
insert into t ((values (7,70), (3,30), (8,80)));
select * from t;
c	d
7	70
3	30
8	80
delete from t;
insert into t(c) ((values (7), (3), (8)));
select * from t;
c	d
7	NULL
3	NULL
8	NULL
delete from t;
# 8.7. insert simple select  with CTE
insert into t
with s(a,b) as (select * from t2 where a <=3 order by 1 desc limit 2)
select * from s;
select * from t;
c	d
3	30
2	20
delete from t;
insert into t(c)
with s(a) as (select a from t2 where a <=3 order by 1 desc limit 2)
select * from s;
select * from t;
c	d
3	NULL
2	NULL
delete from t;
insert into t
with s as
( (select * from t2 where a <=4 order by 1 desc limit 2)
union
values (3,30), (8,80), (7,70) )
select * from s;
select * from t;
c	d
4	40
3	30
8	80
7	70
delete from t;
insert into t(c)
with s as
( (select a from t2 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from s;
select * from t;
c	d
4	NULL
3	NULL
8	NULL
7	NULL
delete from t;
# 8.8. insert into union with CTE
insert into t(c)
with s as
( (select a from t2 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from s where a>=7 union select a from t2 where b<40;
select * from t;
c	d
8	NULL
7	NULL
3	NULL
1	NULL
2	NULL
delete from t;
insert into t
with s as
( (select * from t2 where a <=4 order by 1 desc limit 2)
union
values (3,30), (8,80), (7,70) )
select * from s where a>=7 union select * from s where a<4;
select * from t;
c	d
8	80
7	70
3	30
delete from t;
insert into t(c)
with s as
( (select a from t2 where a <=4 order by 1 desc limit 2)
union
values (3), (8), (7) )
select * from s where a>=7 union select * from s where a<4;
select * from t;
c	d
8	NULL
7	NULL
3	NULL
delete from t;
insert into t
with s as
( select * from t2 where a <=4 or a=7 )
select * from s where a>=7 union select * from s where a<3;
select * from t;
c	d
7	70
1	10
2	20
delete from t;
insert into t(c)
with s as
( select a from t2 where a <=4 or a=7 )
select * from s where a>=7 union select * from s where a<3;
select * from t;
c	d
7	NULL
1	NULL
2	NULL
delete from t;
drop table t;
# 9. derived table
# 9.1. derived table as [tailed] simple select
select * from (select * from t1) as dt;
a
3
7
1
2
4
select * from ((select * from t1)) as dt;
a
3
7
1
2
4
select * from (((select * from t1))) as dt;
a
3
7
1
2
4
select * from (select * from t1 order by a) as dt;
a
3
7
1
2
4
select * from (select a from t1 order by a) as dt;
a
3
7
1
2
4
select * from (select a from t1 order by 1) as dt;
a
3
7
1
2
4
select * from (select a from t1 order by t1.a) as dt;
a
3
7
1
2
4
select * from ((select * from t1 order by t1.a limit 2)) as dt;
a
1
2
select * from ((select * from t2 order by a limit 2) order by b desc) dt;
a	b
1	10
2	20
select * from ((select a from t1 where a=1) order by 1 desc) dt;
a
1
# 9.2. derived table as select with two tails
select * from
((select * from t2 order by t2.a limit 2) order by b desc) dt;
a	b
1	10
2	20
select * from
((select * from t2 order by t2.a limit 2) order by b desc) as dt;
a	b
1	10
2	20
select * from
(((select * from t2 order by t2.a limit 2) order by b desc )) as dt;
a	b
1	10
2	20
select * from
(((select * from t2 order by t2.a) limit 2) order by b desc) dt;
a	b
1	10
2	20
select * from
((select * from t2 order by a limit 2) order by b desc) dt;
a	b
1	10
2	20
select * from
((select a from t1 where a=1) order by 1 desc) as dt;
a
1
select * from
((select * from t2 order by t2.a limit 2) order by b desc) as dt;
a	b
1	10
2	20
# 9.3. derived table as union
select * from (select a from t1 union select a from t1) as dt;
a
3
7
1
2
4
select * from (select a from t1 union all select a from t1) as dt;
a
3
7
1
2
4
3
7
1
2
4
select * from (select a from t1 union select b from t2) as dt;
a
3
7
1
2
4
30
70
10
20
40
select * from
((select a from t1) union (select a from t1)) as dt;
a
3
7
1
2
4
select * from
((select a from t1) union (select b from t2)) as dt;
a
3
7
1
2
4
30
70
10
20
40
select * from
(select a from t1 where a=1 union select a from t1 where a=3) dt;
a
1
3
select * from
((select a from t1 where a=1) union select a from t1 where a=3) dt;
a
1
3
select * from
(((select a from t1 where a=1) union select a from t1 where a=3)) dt;
a
1
3
select * from
(((select a from t1 where a<=3) union (select a from t1 where a=3))) as dt;
a
3
1
2
select * from
(select a from t1 where a=1 union (select a from t1 where a=3)) as dt;
a
1
3
select * from
((select a from t1 where a=1 union (select a from t1 where a=3))) as dt;
a
1
3
select * from
(((select a from t1 where a=1 union (select a from t1 where a=3)))) as dt;
a
1
3
select * from
( select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=7 ) as dt;
a
1
3
7
select * from
( (select a from t1 where a=1 order by a)
union
select a from t1 where a=3 ) as dt;
a
1
3
select * from
( (select a from t1 where a!=3 order by a desc)
union
select a from t1 where a=3 ) as dt;
a
7
1
2
4
3
select * from
( ( select a from t1 where a <=3 except select a from t1 where a >=3 )
union
select a from t1 where a=7 ) as dt;
a
1
2
7
select * from
( ( ( select a from t1 where a <=3
except
select a from t1 where a >=3 )
union
select a from t1 where a=7 )  ) as dt;
a
1
2
7
select * from
( select a from t1 where a=1
union
select a from t1 where a=3
order by a desc) as dt;
a
3
1
select *from
( (select a from t1 limit 2)
union
select a from t1 where a=3
order by a desc) as dt;
a
7
3
select * from
( select a from t1 where a=4
union
(select a from t1 where a <=4 limit 2)
order by a desc ) as dt;
a
4
3
1
select * from
( ( select a from t1 where a=4
union
( select a from t1 where a <=4 order by a ) )
order by a desc limit 2 ) as dt;
a
4
3
select * from
( ( select a from t1 where a <=3 except select a from t1 where a >=3 )
union
select a from t1 where a=7 order by a desc ) as dt;
a
7
2
1
select * from
( ( select a from t1 where a!=3 order by a desc )
union
select a from t1 where a=3
order by a desc ) as dt;
a
7
4
3
2
1
select * from
( (select a from t1 where a=1)
union
(select a from t1 where a=3)
order by a desc ) as dt;
a
3
1
select * from
( ( select a from t1 where a=1
union
select a from t1 where a=3 )
order by a desc ) as dt;
a
3
1
select * from
( ( ( select a from t1 where a=1 )
union
( select a from t1 where a=3 ) )
order by a desc ) as dt;
a
3
1
select * from
( ( select a from t1 where a=1
union
select a from t1 where a=3 )
order by 1 desc ) as dt;
a
3
1
select * from
( ( (select a from t1 where a=1
union
select a from t1 where a=3) ) order by 1 desc ) as dt;
a
3
1
select * from
((((select a from t1 where a=1) union (select a from t1 where a=3)))
order by 1 desc ) as dt;
a
3
1
select * from
( ( (select a from t1 where a=1 )
union
(select a from t1 where a=3) )
order by 1 desc ) as dt;
a
3
1
select * from
( select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=2
union
select a from t1 where a=4 ) as dt;
a
1
3
2
4
select * from
( ( select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=2 )
union
select a from t1 where a=4 ) as dt;
a
1
3
2
4
select * from
( (select a from t1 where a=1 union select a from t1 where a=3)
union
(select a from t1 where a=2 union select a from t1 where a=4) ) as dt;
a
1
3
2
4
select * from
( (select a from t1 where a=1 union (select a from t1 where a=3))
union
((select a from t1 where a=2) union select a from t1 where a=4) ) as dt;
a
1
3
2
4
select * from
( ( ( select a from t1 where a=1)
union
select a from t1 where a=3 )
union
select a from t1 where a=2
union
select a from t1 where a=4 ) as dt;
a
1
3
2
4
select * from
( ( ( ( select a from t1 where a=1)
union
select a from t1 where a=3 )
union
select a from t1 where a=2 )
union
select a from t1 where a=4 ) as dt;
a
1
3
2
4
select * from
( select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=2
union
(select a from t1 where a=4) ) as dt;
a
1
3
2
4
select * from
( select a from t1 where a=1
union
select a from t1 where a=3
union
( select a from t1 where a=2
union
( select a from t1 where a=4 ) ) ) as dt;
a
1
3
2
4
select * from
( select a from t1 where a=1
union
( select a from t1 where a=3
union
( select a from t1 where a=2
union
( select a from t1 where a=4 ) ) ) ) as dt;
a
1
3
2
4
select * from
( ( ( select a from t1 where a=1 union select a from t1 where a=3 )
order by a desc limit 2 )
union
( ( select a from t1 where a=2 union select a from t1 where a=4 )
order by a desc limit 1 ) ) as dt;
a
3
1
4
select * from
( ( ( select a from t1 where a=1 union select a from t1 where a=3 )
order by a desc limit 2 )
union
( ( select a from t1 where a=2 union select a from t1 where a=4 )
order by a desc limit 2 )
order by a) as dt;
a
1
2
3
4
select * from
( ( select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=2  order by a desc limit 2 )
union
select a from t1 where a=4
order by a limit 3 ) as dt;
a
2
3
4
select * from
( ( select a from t1 where a=1
union
select a from t1 where a=3 order by a desc limit 2)
union
select a from t1 where a=2  order by a desc limit 2 ) as dt;
a
3
2
select * from
( ( ( select a from t1 where a >= 2
union
select a from t1 where a=1 order by a desc limit 2 )
union
select a from t1 where a=3  order by a limit 2 )
union
select a from t1 where a=1 ) as dt;
a
3
4
1
# 9.3. derived table as [tailed] TVC
select * from
( values (3), (7), (1) ) as dt;
3
3
7
1
select * from
( (values (3), (7), (1)) ) as dt;
3
3
7
1
select * from
(((values (3), (7), (1)))) as dt;
3
3
7
1
select * from
( values (3), (7), (1) order by 1 limit 2 ) as dt;
3
1
3
select * from
( (values (3), (7), (1)) order by 1 limit 2 ) as dt;
3
1
3
select * from
( ((values (3), (7), (1))) order by 1 limit 2 ) as dt;
3
1
3
select * from
( (((values (3), (7), (1))) order by 1 limit 2) ) as dt;
3
1
3
select * from
( ( (values (3), (7), (1) limit 2) order by 1 desc) ) as dt;
3
3
7
select * from
( ((values (3), (7), (1)) order by 1 desc) limit 2 ) as dt;
3
7
3
select * from
( (((values (3), (7), (1)) order by 1 desc) limit 2) ) as dt;
3
7
3
# 9.3. derived table as union of TVCs
select * from
( values (3), (7), (1) union values (3), (4), (2) ) dt;
3
3
7
1
4
2
select * from
( values (3), (7), (1) union all values (3), (4), (2) ) as dt;
3
3
7
1
3
4
2
select * from
( values (3), (7), (1) union values (3), (4), (2) ) as dt;
3
3
7
1
4
2
select * from
( values (3), (7), (1) except values (3), (4), (2) ) as dt;
3
7
1
select * from
( (values (3), (7), (1)) union (values (3), (4), (2)) ) as dt;
3
3
7
1
4
2
select * from
( (values (3), (7), (1))
union
(values (3), (4), (2))
union values (5), (7) ) dt;
3
3
7
1
4
2
5
select * from
( (values (3), (7), (1))
union
(values (3), (4), (2))
union
(values (5), (7)) ) as dt;
3
3
7
1
4
2
5
select * from
( (values (3), (7), (1)
union
values (3), (4), (2))
union
values (5), (7) ) as dt;
3
3
7
1
4
2
5
select * from
( values (3), (7), (1)
union (values (3), (4), (2)
union
values (5), (7)) ) as dt;
3
3
7
1
4
2
5
select * from
( (values (3), (7), (1)
union
((values (3), (4), (2)
union values (5), (7)))) ) dt;
3
3
7
1
4
2
5
select * from
( values (3), (7), (1)
union
values (3), (4), (2)
order by 1 ) as dt;
3
1
2
3
4
7
select * from
( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt;
3
1
2
3
4
7
select * from
( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt;
3
1
2
3
4
7
select * from
( values (3), (7), (1) union (values (3), (4), (2)) order by 1 ) as dt;
3
1
2
3
4
7
select * from
( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt;
3
1
2
3
4
7
select * from
( ((values (3), (7), (1)) union values (3), (4), (2)) order by 1 ) as dt;
3
1
2
3
4
7
select * from
( (values (3), (7), (1) order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2) ) as dt;
3
1
3
4
select * from
( ((values (3), (7), (1) order by 1) limit 2)
union
((values (3), (4), (2) order by 1 desc) limit 2) ) as dt;
3
1
3
4
select * from
( (((values (3), (7), (1)) order by 1) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt;
3
1
3
4
select * from
( (values (3), (7), (1) order by 1 limit 2)
union
values (3), (4), (2)
order by 1 limit 3 ) as dt;
3
1
2
3
select * from
( ((values (3), (7), (1)) order by 1 limit 2)
union
((values (3), (4), (2) order by 1 desc) limit 2)
order by 1 limit 3 ) as dt;
3
1
3
4
select * from
( (select a from t1 where a <=3 order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2) ) dt;
a
1
2
4
3
select * from
( ((select a from t1 where a <=3) order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2) ) as dt;
a
1
2
4
3
select * from
( (((select a from t1 where a <=3) order by a) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt;
a
1
2
4
3
select * from
( ( (((select a from t1 where a <=3) order by a) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2) ) ) dt;
a
1
2
4
3
select * from
( (select a from t1 where a <=3 order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2)
order by a ) as dt;
a
1
2
3
4
select * from
( ((select a from t1 where a <=3) order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2)
order by a ) as dt;
a
1
2
3
4
select * from
( (((select a from t1 where a <=3) order by a) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2)
order by a ) as dt;
a
1
2
3
4
select * from
( (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt;
3
4
3
select * from
( ( (((select a from t1 where a <=3) order by a) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2) )
order by a ) as dt;
a
1
2
3
4
select * from
( (values (3), (4), (2) order by 1 desc limit 2)
union
(select a from t1 where a <=3 order by 1 limit 2) ) as dt;
3
4
3
1
2
select * from
( (values (3), (4), (2) order by 1 desc limit 2)
union
((select a from t1 where a <=3) order by 1 limit 2) ) as dt;
3
4
3
1
2
select * from
( (((values (3), (4), (2)) order by 1 desc) limit 2)
union
(((select a from t1 where a <=3) order by 1) limit 2) ) as dt;
3
4
3
1
2
select * from
( (((values (3), (4), (2)) order by 1 desc) limit 2)
union
(((select a from t1 where a <=3) order by a) limit 2)
order by 1 ) as dt;
3
1
2
3
4
select * from
( ( select a from t1 where a=1
union
values (3), (4), (2) order by 1 desc )
union
select a from t1 where a=2 order by a desc limit 3 ) as dt;
a
4
3
2
# 9.4. derived table as [tailed] simple select with CTE
select * from
( with t as (select * from t1 where a <=3)
select * from t ) as dt;
a
3
1
2
select * from
( with t as (select * from t1 where a <=3)
(select * from t) ) as dt;
a
3
1
2
select * from
( with t as (select * from t1 where a <=3)
((select * from t)) ) as dt;
a
3
1
2
select * from
( with t as ((select * from t1 where a <=3))
select * from t ) as dt;
a
3
1
2
select * from
( with t as (((select * from t1 where a <=3)))
select * from t ) as dt;
a
3
1
2
select * from
( with t as (select * from t1 where a <=3)
select * from t order by a ) as dt;
a
3
1
2
select * from
( with t as (select * from t1 where a <=3)
(select * from t) order by a ) as dt;
a
3
1
2
select * from
( with t as (select * from t1 where a <=3)
(select * from t) order by a desc limit 2 ) as dt;
a
3
2
select * from
( with t as (select * from t1 where a >=2 order by a limit 2)
select * from t ) as dt;
a
2
3
select * from
( with t as (((select * from t1 where a >=2) order by a desc) limit 2)
select * from t ) as dt;
a
7
4
select * from
( with t as (select * from t1 where a >=2 order by a desc limit 2)
select * from t order by a ) as dt;
a
7
4
# 9.5. derived table as tailed union with CTE
select * from
( with t as (select * from t1 where a <=3)
select a from t1 where a=1 union select a from t where a=3 ) as dt;
a
1
3
select * from
( with t as (select * from t1 where a <=3)
(select a from t) union (select b from t2) ) as dt;
a
3
1
2
30
70
10
20
40
select * from
( with t as (select * from t1 where a <=3)
(select a from t) union (select b as a from t2) order by a desc ) as dt;
a
70
40
30
20
10
3
2
1
select * from
( with t as (select * from t1 where a < 3 union select * from t1 where a > 3)
select a from t1 where a=1 union select a from t where a=7 ) as dt;
a
1
7
select * from
( with t as
( select * from t1 where a < 3
union
select * from t1 where a > 3
order by a desc limit 3 )
select a from t1 where a=4 union select a from t where a=7 ) as dt;
a
4
7
select * from
( with t as
( select * from t1 where a < 3
union
select * from t1 where a > 3
order by a desc limit 3 )
select a from t1 where a=4
union
select a from t where a=7
order by a desc ) as dt;
a
7
4
select * from
( with t as
( (select * from t1 where a < 3)
union
(select * from t1 where a > 3)
order by a desc limit 3 )
select a from t1 where a=4
union select a from t where a=7
order by a desc ) dt;
a
7
4
select * from
( with t as
( (select * from t1 where a < 3)
union
(select * from t1 where a > 3)
order by a desc limit 3 )
(select a from t1 where a=4
union
select a from t where a=7
order by a desc) ) as dt;
a
7
4
select * from
(  with t as
( (select * from t1 where a < 3)
union
(select * from t1 where a > 3)
order by a desc limit 3 )
((select a from t1 where a=4
union
select a from t where a=7) order by a desc) ) as dt;
a
7
4
select * from
( with t as
( select * from t1 where a < 3
union
values (4), (7)
order by a desc limit 3 )
select a from t1 where a=4
union
select a from t where a=7
order by a desc ) dt;
a
7
4
select * from
( with t(a) as
( values (2), (1)
union
(values (4), (7))
order by 1 desc limit 3 )
select a from t1 where a=4
union select a from t where a=7
order by a desc ) as dt;
a
7
4
select * from
( with t(a) as
( (values (2), (1))
union
(values (4), (7) order by 1 desc)
order by 1 desc limit 3 )
select a from t1 where a=1
union
select a from t where a=7 order by a desc ) as dt;
a
7
1
select * from
( with t(a) as
( (values (2), (1))
union
(values (4), (7) order by 1 desc)
order by 1 limit 3 )
select a from t where a=1 union values (7) order by a desc ) as dt;
a
7
1
select * from
( with t(a) as
( (values (2), (1))
union
(values (4), (7) order by 1 desc ) )
select a from t where a=1 union select 7 order by a desc ) as dt;
a
7
1
select * from
( with t as (select * from t1 where a < 3),
s as (select * from t1 where a > 3)
select a from t where a=1
union select a from s where a=7
order by a desc ) dt;
a
7
1
select * from
( with t as (select * from t1 where a < 3),
s as (select * from t1 where a > 3)
(select a from t where a=1
union
select a from s where a=7 order by a desc) ) dt;
a
7
1
select * from
( with t as (select * from t1 where a < 3),
s as (select * from t1 where a > 3)
(select a from t where a=1
union
select a from s where a=7)
order by a desc ) dt;
a
7
1
10. view
10.1. view as simple select
create view v1 as
select * from t1;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1`	latin1	latin1_swedish_ci
select * from v1;
a
3
7
1
2
4
drop view v1;
create view v1 as
select 2*a as c from t1;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 2 * `t1`.`a` AS `c` from `t1`	latin1	latin1_swedish_ci
select * from v1;
c
6
14
2
4
8
drop view v1;
create view v1(c) as
select 2*a from t1;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 2 * `t1`.`a` AS `c` from `t1`	latin1	latin1_swedish_ci
select * from v1;
c
6
14
2
4
8
drop view v1;
create view v1 as
((select * from t1));
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1`)	latin1	latin1_swedish_ci
select * from v1;
a
3
7
1
2
4
drop view v1;
10.2. view as tailed simple select
create view v1 as
select * from t1 order by a;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` order by `t1`.`a`	latin1	latin1_swedish_ci
select * from v1;
a
1
2
3
4
7
drop view v1;
create view v1 as
(select * from t2 order by a limit 2) order by b desc;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__3`.`a` AS `a`,`__3`.`b` AS `b` from (select `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2` order by `t2`.`a` limit 2) `__3` order by `__3`.`b` desc	latin1	latin1_swedish_ci
select * from v1;
a	b
2	20
1	10
drop view v1;
10.3. view as union
create view v1 as
select a from t1 union select b from t2;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` union select `t2`.`b` AS `b` from `t2`	latin1	latin1_swedish_ci
select * from v1;
a
3
7
1
2
4
30
70
10
20
40
drop view v1;
create view v1 as
(select a from t1) union (select b from t2);
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1`) union (select `t2`.`b` AS `b` from `t2`)	latin1	latin1_swedish_ci
select * from v1;
a
3
7
1
2
4
30
70
10
20
40
drop view v1;
create view v1 as
(select a from t1 where a=1) union select a from t1 where a=3;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3	latin1	latin1_swedish_ci
select * from v1;
a
1
3
drop view v1;
create view v1 as
((select a from t1 where a<=3) union (select a from t1 where a=3));
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3)	latin1	latin1_swedish_ci
select * from v1;
a
3
1
2
drop view v1;
create view v1 as
select a from t1 where a=1
union
select a from t1 where a=3
union
select a from t1 where a=7;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 7	latin1	latin1_swedish_ci
select * from v1;
a
1
3
7
drop view v1;
create view v1 as
( ( select a from t1 where a!=3 order by a desc limit 3)
union
select a from t1 where a=3 );
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <> 3 order by `t1`.`a` desc limit 3) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3	latin1	latin1_swedish_ci
select * from v1;
a
7
4
2
3
drop view v1;
create view v1 as
( select a from t1 where a <=3 except select a from t1 where a >=3 )
union
select a from t1 where a=7;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__5`.`a` AS `a` from (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3 except select `t1`.`a` AS `a` from `t1` where `t1`.`a` >= 3) `__5` union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 7	latin1	latin1_swedish_ci
select * from v1;
a
1
2
7
drop view v1;
create view v1 as
(select a from t1 limit 2) union select a from t1 where a=3 order by a desc;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` limit 2) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 order by `a` desc	latin1	latin1_swedish_ci
select * from v1;
a
7
3
drop view v1;
create view v1 as
select a from t1 where a=1
union
( select a from t1 where a=3
union
( select a from t1 where a=2
union
( select a from t1 where a=4 ) ) );
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `__7`.`a` AS `a` from (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 union select `__6`.`a` AS `a` from (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 2 union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4)) `__6`) `__7`	latin1	latin1_swedish_ci
select * from v1;
a
1
3
2
4
drop view v1;
create view v1 as
( ( select a from t1 where a >= 2
union
select a from t1 where a=1 order by a desc limit 2 )
union
select a from t1 where a=3  order by a limit 2 )
union
select a from t1 where a=1;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__7`.`a` AS `a` from (select `__5`.`a` AS `a` from (select `t1`.`a` AS `a` from `t1` where `t1`.`a` >= 2 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 order by `a` desc limit 2) `__5` union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 order by `a` limit 2) `__7` union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1	latin1	latin1_swedish_ci
select * from v1;
a
3
4
1
drop view v1;
10.4. view as [tailed] TVC
create view v1 as
values (3), (7), (1);
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (3),(7),(1)	latin1	latin1_swedish_ci
select * from v1;
3
3
7
1
drop view v1;
create view v1 as
(((values (3), (7), (1))) order by 1);
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (3),(7),(1) order by 1)	latin1	latin1_swedish_ci
select * from v1;
3
1
3
7
drop view v1;
10.5. view as [tailed] union of TVCs
create view v1 as
values (3), (7), (1) union values (3), (4), (2);
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (3),(7),(1) union values (3),(4),(2)	latin1	latin1_swedish_ci
select * from v1;
3
3
7
1
4
2
drop view v1;
create view v1 as
(values (3), (7), (1) union values (3), (4), (2)) order by 1;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (3),(7),(1) union values (3),(4),(2) order by 1	latin1	latin1_swedish_ci
select * from v1;
3
1
2
3
4
7
drop view v1;
create view v1 as
(values (3), (7), (1) order by 1 limit 2)
union
(values (3), (4), (2) order by 1 desc limit 2);
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (3),(7),(1) order by 1 limit 2) union (values (3),(4),(2) order by 1 desc limit 2)	latin1	latin1_swedish_ci
select * from v1;
3
1
3
4
drop view v1;
create view v1 as
(values (3), (7), (1) order by 1 limit 2)
union
values (3), (4), (2)
order by 1;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (3),(7),(1) order by 1 limit 2) union values (3),(4),(2) order by 1	latin1	latin1_swedish_ci
select * from v1;
3
1
2
3
4
drop view v1;
10.6. view as [tailed] union of [tailed] select and tailed TVC
create view v1 as
( (((select a from t1 where a <=3) order by a) limit 2)
union
(((values (3), (4), (2)) order by 1 desc) limit 2) )
order by a;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3 order by `t1`.`a` limit 2) union (values (3),(4),(2) order by 1 desc limit 2) order by `a`	latin1	latin1_swedish_ci
select * from v1;
a
1
2
3
4
drop view v1;
create view v1 as
( select a from t1 where a=1
union
values (3), (4), (2) order by 1 desc )
union
select a from t1 where a=2 order by a desc limit 3;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__5`.`a` AS `a` from (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union values (3),(4),(2) order by 1 desc) `__5` union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 2 order by `a` desc limit 3	latin1	latin1_swedish_ci
select * from v1;
a
4
3
2
drop view v1;
10.7. view as select with CTE
create view v1 as
with t as (select * from t1 where a <=3)
select * from t;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3)select `t`.`a` AS `a` from `t`	latin1	latin1_swedish_ci
select * from v1;
a
3
1
2
drop view v1;
create view v1 as
with t as
( select * from t1 where a < 3
union
select * from t1 where a > 3
order by a desc limit 3 )
select a from t1 where a=4 union select a from t where a=7;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3 order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7	latin1	latin1_swedish_ci
select * from v1;
a
4
7
drop view v1;
10.8. view as union with CTE
create view v1 as
with t as
( (select * from t1 where a < 3)
union
(select * from t1 where a > 3)
order by a desc limit 3 )
(select a from t1 where a=4 union select a from t where a=7 order by a desc);
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3) order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc	latin1	latin1_swedish_ci
select * from v1;
a
7
4
drop view v1;
create view v1 as
with t as
( (select * from t1 where a < 3)
union
(select * from t1 where a > 3)
order by a desc limit 3 )
(select a from t where a=4 union select a from t where a=7 order by a desc);
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3) order by `a` desc limit 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc	latin1	latin1_swedish_ci
select * from v1;
a
7
4
drop view v1;
create view v1 as
with t(a) as (values (2), (1)) select a from t;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as (values (2),(1))select `t`.`a` AS `a` from `t`	latin1	latin1_swedish_ci
select * from v1;
a
2
1
drop view v1;
create view v1 as
with t(a) as
( values (2), (1)
union
(values (4), (7))
order by 1 desc limit 3 )
select a from t1 where a=4 union select a from t where a=7 order by a desc;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc	latin1	latin1_swedish_ci
select * from v1;
a
7
4
drop view v1;
create view v1 as
with t(a) as
( (values (2), (1))
union
(values (4), (7) order by 1 desc)
order by 1 desc limit 3 )
select a from t1 where a=1 union select a from t where a=7 order by a desc;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc	latin1	latin1_swedish_ci
select * from v1;
a
7
1
drop view v1;
create view v1 as
with t as (select * from t1 where a < 3),
s as (select * from t1 where a > 3)
select a from t where a=1 union select a from s where a=7 order by a desc;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3), s as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc	latin1	latin1_swedish_ci
select * from v1;
a
7
1
drop view v1;
create view v1 as
with t as (select * from t1 where a < 3),
s as (select * from t where a > 3)
select a from t where a=1 union select a from s where a=7 order by a desc;
show create view v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3), s as (select `t`.`a` AS `a` from `t` where `t`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc	latin1	latin1_swedish_ci
select * from v1;
a
1
drop view v1;
drop table t1,t2;
# End of 10.4 tests