mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
2506 lines
60 KiB
Text
2506 lines
60 KiB
Text
select 1 union ( select 2 union select 3);
|
|
explain extended
|
|
select 1 union ( select 2 union select 3);
|
|
select 1 union ( select 1 union select 1);
|
|
explain extended
|
|
select 1 union ( select 1 union select 1);
|
|
select 1 union all ( select 1 union select 1);
|
|
explain extended
|
|
select 1 union all ( select 1 union select 1);
|
|
select 1 union ( select 1 union all select 1);
|
|
explain extended
|
|
select 1 union ( select 1 union all select 1);
|
|
select 1 union select 1 union all select 1;
|
|
explain extended
|
|
select 1 union select 1 union all select 1;
|
|
|
|
(select 1 as a) union (select 2) order by a;
|
|
explain extended
|
|
(select 1 as a) union (select 2) order by a;
|
|
/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`;
|
|
explain extended
|
|
/* 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)));
|
|
explain extended all
|
|
select 1 union ( select 1 union (select 1 union (select 1 union select 1)));
|
|
|
|
--echo #
|
|
--echo # MDEV-6341: INSERT ... SELECT UNION with parenthesis
|
|
--echo #
|
|
create table t1 (a int, b int);
|
|
insert into t1 (select 1,1 union select 2,2);
|
|
select * from t1 order by 1;
|
|
delete from t1;
|
|
insert into t1 select 1,1 union select 2,2;
|
|
select * from t1 order by 1;
|
|
drop table t1;
|
|
CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a UNION SELECT 2;
|
|
select * from t1 order by 1;
|
|
drop table t1;
|
|
CREATE OR REPLACE TABLE t1 AS (SELECT 1 AS a UNION SELECT 2);
|
|
select * from t1 order by 1;
|
|
drop table t1;
|
|
CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a);
|
|
show create view v1;
|
|
drop view v1;
|
|
CREATE OR REPLACE VIEW v1 AS SELECT 1 AS a UNION SELECT 2;
|
|
show create view v1;
|
|
drop view v1;
|
|
CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a UNION SELECT 2);
|
|
show create view v1;
|
|
drop view v1;
|
|
|
|
|
|
--echo #
|
|
--echo # MDEV-10028: Syntax error on ((SELECT ...) UNION (SELECT ...))
|
|
--echo #
|
|
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));
|
|
(SELECT * FROM t1 UNION SELECT * FROM t1);
|
|
((SELECT a FROM t1) LIMIT 1);
|
|
SELECT * FROM (SELECT 1 UNION (SELECT 2 UNION SELECT 3)) t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # test of several levels of ORDER BY / LIMIT
|
|
--echo #
|
|
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;
|
|
(select a,b from t1 order by 1 limit 3) order by 2 limit 2;
|
|
(select 10,1000 union select a,b from t1 order by 1 limit 3) order by 2 limit 2;
|
|
((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1 limit 1;
|
|
((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-16359: union with 3 selects in brackets
|
|
--echo #
|
|
|
|
select 1 union select 1 union select 1;
|
|
(select 1 union select 1 union select 1);
|
|
((select 1) union (select 1) union (select 1));
|
|
|
|
--echo #
|
|
--echo # MDEV-16357: union in brackets with tail
|
|
--echo # union with tail in brackets
|
|
--echo #
|
|
|
|
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;
|
|
(SELECT a FROM t1 UNION SELECT a FROM t2) ORDER BY a DESC;
|
|
|
|
(SELECT a FROM t1 UNION SELECT a FROM t2 LIMIT 1);
|
|
|
|
DROP TABLE t1,t2;
|
|
|
|
--echo #
|
|
--echo # MDEV-19324: ((SELECT ...) ORDER BY col ) LIMIT n
|
|
--echo #
|
|
|
|
create table t1 (a int);
|
|
insert into t1 values (10),(20),(30);
|
|
|
|
let $q1=
|
|
select a from t1 order by a desc limit 1;
|
|
eval $q1;
|
|
eval explain extended $q1;
|
|
--source include/explain-no-costs.inc
|
|
eval explain format=json $q1;
|
|
|
|
let $q2=
|
|
(select a from t1 order by a desc) limit 1;
|
|
eval $q2;
|
|
eval explain extended $q2;
|
|
--source include/explain-no-costs.inc
|
|
eval explain format=json $q2;
|
|
|
|
let $q1=
|
|
(select a from t1 where a=20 union select a from t1) order by a desc limit 1;
|
|
eval $q1;
|
|
eval explain extended $q1;
|
|
--source include/explain-no-costs.inc
|
|
eval explain format=json $q1;
|
|
|
|
let $q2=
|
|
((select a from t1 where a=20 union select a from t1) order by a desc) limit 1;
|
|
eval $q2;
|
|
eval explain extended $q2;
|
|
--source include/explain-no-costs.inc
|
|
eval explain format=json $q2;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-19363: ((SELECT ...) ORDER BY col ) LIMIT n UNION ...
|
|
--echo #
|
|
|
|
create table t1 (pk int);
|
|
insert into t1 values (5),(4),(1),(2),(3);
|
|
|
|
let $q=
|
|
((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4);
|
|
eval $q;
|
|
eval explain extended $q;
|
|
--source include/explain-no-costs.inc
|
|
eval explain format=json $q;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-18689: parenthesis around table names and derived tables
|
|
--echo #
|
|
|
|
select * from ( mysql.db );
|
|
|
|
create table t1 (a int);
|
|
insert into t1 values (7), (2), (7);
|
|
|
|
select * from (t1);
|
|
select * from ((t1));
|
|
select * from (t1 t) where t.a > 5;
|
|
select * from ((t1 t)) where t.a > 5;
|
|
|
|
select * from ((select a, sum(a) from t1 group by a) t);
|
|
select * from (((select a, sum(a) from t1 group by a) t));
|
|
|
|
update (t1 t) set t.a=t.a+1;
|
|
select * from t1;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-19956: query expressions in different contexts
|
|
--echo #
|
|
|
|
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);
|
|
|
|
|
|
--echo # 1. select
|
|
|
|
--echo # 1.1. simple select
|
|
|
|
select * from t1;
|
|
(select * from t1);
|
|
((select * from t1));
|
|
--echo # 1.2. select with tail
|
|
select * from t1 order by a;
|
|
select a from t1 order by a;
|
|
select a from t1 order by 1;
|
|
select * from t1 order by t1.a;
|
|
(select * from t1 order by t1.a);
|
|
((select * from t1 order by t1.a));
|
|
(select * from t1 order by t1.a limit 2);
|
|
(select a from t1 where a=1) order by 1 desc;
|
|
|
|
--echo # 1.2. select with several tails
|
|
|
|
(select * from t2 order by a limit 2) order by b desc;
|
|
(select * from t2 order by t2.a limit 2) order by b desc;
|
|
((select * from t2 order by t2.a limit 2) order by b desc);
|
|
(((select * from t2 order by t2.a) limit 2) order by b desc);
|
|
|
|
|
|
--echo # 2. union
|
|
|
|
--echo # 2.1 simple union
|
|
|
|
select a from t1 union select a from t1;
|
|
select a from t1 union all select a from t1;
|
|
select a from t1 union select b from t2;
|
|
(select a from t1) union (select a from t1);
|
|
(select a from t1) union (select b from t2);
|
|
select a from t1 where a=1 union select a from t1 where a=3;
|
|
(select a from t1 where a=1) union select a from t1 where a=3;
|
|
((select a from t1 where a=1) union select a from t1 where a=3);
|
|
((select a from t1 where a<=3) union (select a from t1 where a=3));
|
|
select a from t1 where a=1 union (select a from t1 where a=3);
|
|
(select a from t1 where a=1 union (select a from t1 where a=3));
|
|
((select a from t1 where a=1 union (select a from t1 where a=3)));
|
|
|
|
select a from t1 where a=1
|
|
union
|
|
select a from t1 where a=3
|
|
union
|
|
select a from t1 where a=7;
|
|
|
|
( select a from t1 where a=1
|
|
union
|
|
select a from t1 where a=3
|
|
union
|
|
select a from t1 where a=7 );
|
|
|
|
(select a from t1 where a=1 order by a) union select a from t1 where a=3;
|
|
(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3;
|
|
((select a from t1 where a=1 order by a) union select a from t1 where a=3);
|
|
(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3;
|
|
|
|
( ( select a from t1 where a!=3 order by a desc limit 3)
|
|
union
|
|
select a from t1 where a=3 );
|
|
|
|
( select a from t1 where a <=3 except select a from t1 where a >=3 )
|
|
union
|
|
select a from t1 where a=7;
|
|
|
|
( ( select a from t1 where a <=3
|
|
except
|
|
select a from t1 where a >=3 )
|
|
union
|
|
select a from t1 where a=7 );
|
|
|
|
( select a from t1 where a <=3
|
|
except
|
|
( select a from t1 where a >=3
|
|
union
|
|
select a from t1 where a=7 ) );
|
|
|
|
( ( select a from t1 where a <=3 )
|
|
except
|
|
( select a from t1 where a >=3
|
|
union
|
|
select a from t1 where a=7 ) );
|
|
|
|
--echo # 2.2. union with tail
|
|
|
|
select a from t1 where a=1 union select a from t1 where a=3 order by a desc;
|
|
(select a from t1 limit 2) union select a from t1 where a=3 order by a desc;
|
|
|
|
select a from t1 where a=4 union (select a from t1 where a <=4 limit 2)
|
|
order by a desc;
|
|
|
|
select a from t1 where a=4
|
|
union
|
|
(select a from t1 where a <=4 order by a limit 2)
|
|
order by a desc;
|
|
|
|
( select a from t1 where a=4
|
|
union
|
|
( select a from t1 where a <=4 order by a limit 2 ) )
|
|
order by a desc;
|
|
|
|
( 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;
|
|
|
|
( select a from t1 where a!=3 order by a desc )
|
|
union
|
|
select a from t1 where a=3
|
|
order by a desc;
|
|
|
|
(select a from t1 where a=1)
|
|
union
|
|
(select a from t1 where a=3)
|
|
order by a desc;
|
|
|
|
( select a from t1 where a=1
|
|
union
|
|
select a from t1 where a=3 )
|
|
order by a desc;
|
|
|
|
( ( select a from t1 where a=1 )
|
|
union
|
|
( select a from t1 where a=3 ) )
|
|
order by a desc;
|
|
|
|
( select a from t1 where a=1
|
|
union
|
|
select a from t1 where a=3 )
|
|
order by 1 desc;
|
|
|
|
((select a from t1 where a=1 union select a from t1 where a=3)) order by 1 desc;
|
|
(((select a from t1 where a=1) union (select a from t1 where a=3)))
|
|
order by 1 desc;
|
|
|
|
( (select a from t1 where a=1 )
|
|
union
|
|
(select a from t1 where a=3) )
|
|
order by 1 desc;
|
|
|
|
--echo # 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;
|
|
|
|
( 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;
|
|
|
|
(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);
|
|
(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);
|
|
|
|
( ( 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;
|
|
( ( ( 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;
|
|
|
|
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);
|
|
|
|
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 ) );
|
|
|
|
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 ) ) );
|
|
|
|
--echo # 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 );
|
|
|
|
( ( 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;
|
|
|
|
( 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;
|
|
|
|
( 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;
|
|
|
|
( ( 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;
|
|
|
|
|
|
--echo # 3. TVC
|
|
|
|
--echo # 3.1. simple TVC
|
|
|
|
values (3), (7), (1);
|
|
(values (3), (7), (1));
|
|
((values (3), (7), (1)));
|
|
|
|
--echo # 3.2. simple TVC with tail(s)
|
|
|
|
values (3), (7), (1) order by 1;
|
|
(values (3), (7), (1)) order by 1;
|
|
((values (3), (7), (1))) order by 1;
|
|
(((values (3), (7), (1))) order by 1);
|
|
(values (3), (7), (1) limit 2) order by 1 desc;
|
|
((values (3), (7), (1)) order by 1 desc) limit 2;
|
|
(((values (3), (7), (1)) order by 1 desc) limit 2);
|
|
|
|
--echo # 3.3. union of TVCs
|
|
|
|
values (3), (7), (1) union values (3), (4), (2);
|
|
values (3), (7), (1) union all values (3), (4), (2);
|
|
values (3), (7), (1) union values (3), (4), (2);
|
|
values (3), (7), (1) except values (3), (4), (2);
|
|
(values (3), (7), (1)) union (values (3), (4), (2));
|
|
(values (3), (7), (1)) union (values (3), (4), (2)) union values (5), (7);
|
|
(values (3), (7), (1)) union (values (3), (4), (2)) union (values (5), (7));
|
|
(values (3), (7), (1) union values (3), (4), (2)) union values (5), (7);
|
|
values (3), (7), (1) union (values (3), (4), (2) union values (5), (7));
|
|
(values (3), (7), (1) union ((values (3), (4), (2) union values (5), (7))));
|
|
|
|
--echo # 3.4. tailed union of TVCs
|
|
|
|
values (3), (7), (1) union values (3), (4), (2) order by 1;
|
|
(values (3), (7), (1) union values (3), (4), (2)) order by 1;
|
|
(values (3), (7), (1) union values (3), (4), (2)) order by 1;
|
|
values (3), (7), (1) union (values (3), (4), (2)) order by 1;
|
|
(values (3), (7), (1) union values (3), (4), (2)) order by 1;
|
|
((values (3), (7), (1)) union values (3), (4), (2)) order by 1;
|
|
|
|
--echo # 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);
|
|
|
|
((values (3), (7), (1) order by 1) limit 2)
|
|
union
|
|
((values (3), (4), (2) order by 1 desc) limit 2);
|
|
|
|
(((values (3), (7), (1)) order by 1) limit 2)
|
|
union
|
|
(((values (3), (4), (2)) order by 1 desc) limit 2);
|
|
|
|
--echo # 3.6. tailed union of tailed TVCs
|
|
|
|
(values (3), (7), (1) order by 1 limit 2)
|
|
union
|
|
values (3), (4), (2)
|
|
order by 1;
|
|
|
|
((values (3), (7), (1)) order by 1 limit 2)
|
|
union
|
|
((values (3), (4), (2) order by 1 desc) limit 2)
|
|
order by 1;
|
|
|
|
--echo # 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);
|
|
|
|
((select a from t1 where a <=3) order by 1 limit 2)
|
|
union
|
|
(values (3), (4), (2) order by 1 desc limit 2);
|
|
|
|
(((select a from t1 where a <=3) order by a) limit 2)
|
|
union
|
|
(((values (3), (4), (2)) order by 1 desc) limit 2);
|
|
|
|
( (((select a from t1 where a <=3) order by a) limit 2)
|
|
union
|
|
(((values (3), (4), (2)) order by 1 desc) limit 2) );
|
|
|
|
(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;
|
|
|
|
((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;
|
|
|
|
(((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;
|
|
|
|
(((values (3), (4), (2)) order by 1 desc) limit 2);
|
|
( (((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;
|
|
|
|
(values (3), (4), (2) order by 1 desc limit 2)
|
|
union
|
|
(select a from t1 where a <=3 order by 1 limit 2);
|
|
|
|
(values (3), (4), (2) order by 1 desc limit 2)
|
|
union
|
|
((select a from t1 where a <=3) order by 1 limit 2);
|
|
|
|
(((values (3), (4), (2)) order by 1 desc) limit 2)
|
|
union
|
|
(((select a from t1 where a <=3) order by 1) limit 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;
|
|
|
|
( 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;
|
|
|
|
|
|
--echo 4. CTE
|
|
|
|
--echo 4.1. simple select with simple CTE
|
|
|
|
with t as (select * from t1 where a <=3)
|
|
select * from t;
|
|
|
|
with t as (select * from t1 where a <=3)
|
|
(select * from t);
|
|
|
|
with t as (select * from t1 where a <=3)
|
|
((select * from t));
|
|
|
|
with t as ((select * from t1 where a <=3))
|
|
select * from t;
|
|
with t as (((select * from t1 where a <=3)))
|
|
select * from t;
|
|
|
|
--echo 4.2. tailed select with simple CTE
|
|
|
|
with t as (select * from t1 where a <=3)
|
|
select * from t order by a;
|
|
|
|
with t as (select * from t1 where a <=3)
|
|
(select * from t) order by a;
|
|
|
|
with t as (select * from t1 where a <=3)
|
|
(select * from t) order by a desc limit 2;
|
|
|
|
--echo 4.3. [tailed] select with tailed CTE
|
|
|
|
with t as (select * from t1 where a >=2 order by a limit 2)
|
|
select * from t;
|
|
|
|
with t as (((select * from t1 where a >=2) order by a desc) limit 2)
|
|
select * from t;
|
|
|
|
with t as (select * from t1 where a >=2 order by a desc limit 2)
|
|
select * from t order by a;
|
|
|
|
--echo 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;
|
|
|
|
with t as (select * from t1 where a <=3)
|
|
(select a from t) union (select b from t2);
|
|
|
|
with t as (select * from t1 where a <=3)
|
|
(select a from t) union (select b as a from t2) order by a desc;
|
|
|
|
--echo 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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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);
|
|
|
|
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);
|
|
|
|
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;
|
|
|
|
|
|
--echo 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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
--echo 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;
|
|
|
|
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);
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
|
|
--echo # 5. single-row subquery in expression
|
|
|
|
--echo # 5.1. [tailed] simple select in expression
|
|
|
|
select (a+1) + b as r from t2;
|
|
select ((a+1) + b) as r from t2;
|
|
select (b + (select 1)) as r from t2;
|
|
select (select a from t1 where a <=3 order by a desc limit 1) as r from t2;
|
|
|
|
select
|
|
(select a from t1 where a <=3 order by a desc limit 1) as r from t2;
|
|
|
|
select (select 100) as r from t2;
|
|
select ((select 100)) as r from t2;
|
|
select (select 100) + t2.b as r from t2;
|
|
select ((select 100) + t2.b) as r from t2;
|
|
|
|
--echo # 5.2. [tailed] TVC in expression
|
|
|
|
select (values (200)) as r from t2;
|
|
select ((values (200))) as r from t2;
|
|
select (values (200)) + t2.b as r from t2;
|
|
select ((values (200)) + t2.b) as r from t2;
|
|
select (values (200), (300) order by 1 desc limit 1) as r from t2;
|
|
select ((values (200), (300)) order by 1 desc limit 1) as r from t2;
|
|
select (select * from t1 limit 1) as r from t2;
|
|
select (select * from t1 order by a limit 1) as r from t2;
|
|
select ((select * from t1 order by a limit 1)) as r from t2;
|
|
((select ((select * from t1 order by a limit 1)) as r from t2));
|
|
select (select * from t1 order by a limit 1) + t2.b as r from t2;
|
|
|
|
--echo # 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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
--echo # 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;
|
|
|
|
select
|
|
( with t as (select * from t1 where a <=3)
|
|
select a from t limit 1) + t2.b as r
|
|
from t2;
|
|
|
|
select
|
|
t2.b +( with t as (select * from t1 where a <=3)
|
|
select a from t limit 1) as r
|
|
from t2;
|
|
|
|
select
|
|
((( with t as (select * from t1 where a <=3)
|
|
select a from t limit 1) + t2.b)) as r
|
|
from t2;
|
|
|
|
select
|
|
( with t as (select * from t1 where a <=3)
|
|
select a from t limit 1) + 100 as r
|
|
from t2;
|
|
|
|
select
|
|
( with t as (select * from t1 where a <=3)
|
|
select a from t limit 1) + (select 100) as r
|
|
from t2;
|
|
|
|
select
|
|
( with t as (select * from t1 where a <=3)
|
|
select a from t limit 1) + t2.b + (select 100) as r
|
|
from t2;
|
|
|
|
select
|
|
( with t as (select * from t1 where a <=3)
|
|
select a from t limit 1 ) + (t2.b + (select 100)) as r
|
|
from t2;
|
|
|
|
select
|
|
( with t as (select * from t1 where a <=3)
|
|
select a from t limit 1 ) + t2.b + (values (100)) as r
|
|
from t2;
|
|
|
|
--echo # 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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
--echo # 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;
|
|
|
|
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;
|
|
|
|
--echo # 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;
|
|
|
|
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;
|
|
|
|
|
|
--echo # 6. subquery
|
|
|
|
--echo # 6.1. TVC in IN subquery
|
|
|
|
select a from t1 where a in (1,8,7);
|
|
select a from t1 where a in (values (1), (8), (7));
|
|
|
|
--echo # 6.2. simple select in IN subquery
|
|
|
|
select a from t1 where a in (select a from t2 where a <= 3);
|
|
select a from t1 where a in ((select a from t2 where a <= 3));
|
|
|
|
--echo # 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);
|
|
|
|
select a from t1
|
|
where a in (select a from t1 where a<=2 union (select a from t2 where b>40));
|
|
|
|
select a from t1
|
|
where a in ((select a from t1 where a<=2) union select a from t2 where b>40);
|
|
|
|
select a from t1
|
|
where a in ((select a from t1 where a<=2) union (select a from t2 where b>40));
|
|
|
|
--echo # 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));
|
|
|
|
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));
|
|
|
|
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));
|
|
|
|
|
|
--echo # 6.5. NOT IN subquery
|
|
|
|
select a from t1 where a not in (1,8,7);
|
|
select a from t1 where a not in (values (1), (8), (7));
|
|
select a from t1 where a not in (select a from t2 where a <= 3);
|
|
select a from t1 where a not in ((select a from t2 where a <= 3));
|
|
|
|
select a from t1
|
|
where a not in (select a from t1 where a<=2
|
|
union
|
|
select a from t2 where b>40);
|
|
|
|
select a from t1
|
|
where a not in (select a from t1 where a<=2
|
|
union
|
|
(select a from t2 where b>40));
|
|
|
|
select a from t1
|
|
where a not in ((select a from t1 where a<=2)
|
|
union
|
|
select a from t2 where b>40);
|
|
|
|
select a from t1
|
|
where a not in ((select a from t1 where a<=2)
|
|
union
|
|
(select a from t2 where b>40));
|
|
|
|
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));
|
|
|
|
--echo # 6.6. IN subquery in expression
|
|
|
|
select 1 in (select a from t1) as r, b from t2 where b > 30;
|
|
select (1 in (select a from t1)) as r, b from t2 where b > 30;
|
|
select 1 in ((select a from t1)) as r, b from t2 where b > 30;
|
|
select ((1 in ((select a from t1)))) as r, b from t2 where b > 30;
|
|
select ((1 in ((select a from t1)))) as r, b from t2 where b > 30;
|
|
select b, if (a in (select a from t1 where a > 3),10,20) as r from t2;
|
|
select b, if (a in ((select a from t1 where a > 3)),10,20) as r from t2;
|
|
|
|
--echo # 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;
|
|
drop function f1;
|
|
delimiter |;
|
|
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 |
|
|
delimiter ;|
|
|
select b, f2(a) from t2 where b > 20;
|
|
drop function f2;
|
|
|
|
--echo # 6.8. EXISTS subquery
|
|
|
|
select exists (select a from t1 where t1.a=t2.a) as r, b from t2 where b > 30;
|
|
select exists ((select a from t1 where t1.a=t2.a)) as r, b from t2 where b > 30;
|
|
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));
|
|
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));
|
|
|
|
--echo # 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;
|
|
drop function f1;
|
|
|
|
delimiter |;
|
|
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 |
|
|
delimiter ;|
|
|
select b, f2(a) from t2 where b > 20;
|
|
drop function f2;
|
|
|
|
--echo # 6.10. subquery with ANY
|
|
|
|
select a from t1 where a = any(select a from t2 where a <= 3);
|
|
select a from t1 where a = any((select a from t2 where a <= 3));
|
|
|
|
select a from t1
|
|
where a = any (select a from t1 where a<=2
|
|
union
|
|
select a from t2 where b>40);
|
|
|
|
select a from t1
|
|
where a = any(select a from t1 where a<=2
|
|
union
|
|
(select a from t2 where b>40));
|
|
|
|
select a from t1
|
|
where a = any((select a from t1 where a<=2)
|
|
union
|
|
select a from t2 where b>40);
|
|
|
|
select a from t1
|
|
where a = any((select a from t1 where a<=2)
|
|
union
|
|
(select a from t2 where b>40));
|
|
|
|
|
|
--echo # 7. create table as
|
|
|
|
--echo # 7.1. create table as simple select
|
|
|
|
create table t as select * from t1 where a <=3;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t select * from t1 where a <=3;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t as (select * from t1 where a <=3);
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t (select * from t1 where a <=3);
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t as ((select * from t1 where a <=3));
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t ((select * from t1 where a <=3));
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t(a decimal(10,2)) as select * from t1 where a <=3;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t(a decimal(10,2)) select * from t1 where a <=3;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t(a decimal(10,2)) as (select * from t1 where a <=3);
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t(a decimal(10,2)) (select * from t1 where a <=3);
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t(a decimal(10,2)) as ((select * from t1 where a <=3));
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t(a decimal(10,2)) ((select * from t1 where a <=3));
|
|
select * from t;
|
|
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;
|
|
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;
|
|
drop table t;
|
|
|
|
--echo # 7.2. create table as tailed select
|
|
|
|
create table t as select * from t1 where a <=3 order by 1;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t select * from t1 where a <=3 order by 1;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t as select * from t1 where a <=3 order by 1 desc limit 2;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t select * from t1 where a <=3 order by 1 desc limit 2;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t as ((select * from t1 where a <=3) order by 1 desc) limit 2;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t ((select * from t1 where a <=3) order by 1 desc) limit 2;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
--echo # 7.3. create table as select wihout from clause
|
|
|
|
create table t as select 10;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t select 10;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
--echo # 7.4. create table as union of selects wihout from clause
|
|
|
|
create table t as select 10 union select 70;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t select 10 union select 70;
|
|
select * from t;
|
|
drop table t;
|
|
|
|
--echo # 7.5. create table as TVC
|
|
|
|
create table t as values (7), (3), (8);
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t values (7), (3), (8);
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t as (values (7), (3), (8));
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t (values (7), (3), (8));
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t as ((values (7), (3), (8)));
|
|
select * from t;
|
|
drop table t;
|
|
|
|
create table t ((values (7), (3), (8)));
|
|
select * from t;
|
|
drop table t;
|
|
|
|
--echo # 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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
drop table t;
|
|
|
|
--echo # 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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
drop table t;
|
|
|
|
|
|
--echo # 8. insert
|
|
|
|
create table t (c int, d int);
|
|
|
|
--echo # 8.1. insert simple select
|
|
|
|
insert into t select * from t2 where a <=3;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) select t2.a from t2 where a <=3;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t (select * from t2 where a <=3);
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) (select t2.a from t2 where a <=3);
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t ((select * from t2 where a <=3));
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) ((select t2.a from t2 where a <=3));
|
|
select * from t;
|
|
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;
|
|
delete from t;
|
|
|
|
insert into t(c) select * from t1 where a <=3;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t (select * from t1 where a <=3);
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) (select * from t1 where a <=3);
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t ((select * from t1 where a <=3));
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) ((select * from t1 where a <=3));
|
|
select * from t;
|
|
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;
|
|
delete from t;
|
|
|
|
insert into t(a) ((select a from t2 where a <=3));
|
|
select * from t;
|
|
delete from t;
|
|
|
|
drop table t;
|
|
create table t(c int, d int);
|
|
|
|
--echo # 8.2. insert tailed select
|
|
|
|
insert into t select * from t2 where a <=3 order by 1;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) select a from t2 where a <=3 order by 1;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t select * from t2 where a <=3 order by 1 desc limit 2;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) select a from t2 where a <=3 order by 1 desc limit 2;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t ((select * from t2 where a <=3) order by 1 desc) limit 2;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) ((select a from t2 where a <=3) order by 1 desc) limit 2;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
--echo # 8.3. insert select without from clause
|
|
|
|
insert into t select 10, 20;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) select 10;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
--echo # 8.4. insert union of selects without from clause
|
|
|
|
insert into t select 10,20 union select 70,80;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) select 10 union select 70;
|
|
select * from t;
|
|
delete from t;
|
|
|
|
--echo # 8.5. insert TVC
|
|
|
|
insert into t values (7,70), (3,30), (8,80);
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) values (7), (3), (8);
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t (values (7,70), (3,30), (8,80));
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) (values (7), (3), (8));
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t ((values (7,70), (3,30), (8,80)));
|
|
select * from t;
|
|
delete from t;
|
|
|
|
insert into t(c) ((values (7), (3), (8)));
|
|
select * from t;
|
|
delete from t;
|
|
|
|
--echo # 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;
|
|
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;
|
|
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;
|
|
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;
|
|
delete from t;
|
|
|
|
--echo # 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;
|
|
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;
|
|
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;
|
|
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;
|
|
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;
|
|
delete from t;
|
|
|
|
drop table t;
|
|
|
|
|
|
--echo # 9. derived table
|
|
|
|
--echo # 9.1. derived table as [tailed] simple select
|
|
|
|
select * from (select * from t1) as dt;
|
|
select * from ((select * from t1)) as dt;
|
|
select * from (((select * from t1))) as dt;
|
|
select * from (select * from t1 order by a) as dt;
|
|
select * from (select a from t1 order by a) as dt;
|
|
select * from (select a from t1 order by 1) as dt;
|
|
select * from (select a from t1 order by t1.a) as dt;
|
|
select * from ((select * from t1 order by t1.a limit 2)) as dt;
|
|
select * from ((select * from t2 order by a limit 2) order by b desc) dt;
|
|
select * from ((select a from t1 where a=1) order by 1 desc) dt;
|
|
|
|
--echo # 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;
|
|
|
|
select * from
|
|
((select * from t2 order by t2.a limit 2) order by b desc) as dt;
|
|
|
|
select * from
|
|
(((select * from t2 order by t2.a limit 2) order by b desc )) as dt;
|
|
|
|
select * from
|
|
(((select * from t2 order by t2.a) limit 2) order by b desc) dt;
|
|
|
|
select * from
|
|
((select * from t2 order by a limit 2) order by b desc) dt;
|
|
|
|
select * from
|
|
((select a from t1 where a=1) order by 1 desc) as dt;
|
|
|
|
select * from
|
|
((select * from t2 order by t2.a limit 2) order by b desc) as dt;
|
|
|
|
|
|
--echo # 9.3. derived table as union
|
|
|
|
select * from (select a from t1 union select a from t1) as dt;
|
|
select * from (select a from t1 union all select a from t1) as dt;
|
|
select * from (select a from t1 union select b from t2) as dt;
|
|
|
|
select * from
|
|
((select a from t1) union (select a from t1)) as dt;
|
|
|
|
select * from
|
|
((select a from t1) union (select b from t2)) as dt;
|
|
|
|
select * from
|
|
(select a from t1 where a=1 union select a from t1 where a=3) dt;
|
|
|
|
select * from
|
|
((select a from t1 where a=1) union select a from t1 where a=3) dt;
|
|
|
|
select * from
|
|
(((select a from t1 where a=1) union select a from t1 where a=3)) dt;
|
|
|
|
select * from
|
|
(((select a from t1 where a<=3) union (select a from t1 where a=3))) as dt;
|
|
|
|
select * from
|
|
(select a from t1 where a=1 union (select a from t1 where a=3)) as dt;
|
|
|
|
select * from
|
|
((select a from t1 where a=1 union (select a from t1 where a=3))) as dt;
|
|
|
|
select * from
|
|
(((select a from t1 where a=1 union (select a from t1 where a=3)))) as dt;
|
|
|
|
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;
|
|
|
|
select * from
|
|
( (select a from t1 where a=1 order by a)
|
|
union
|
|
select a from t1 where a=3 ) as dt;
|
|
|
|
select * from
|
|
( (select a from t1 where a!=3 order by a desc)
|
|
union
|
|
select a from t1 where a=3 ) as dt;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
select * from
|
|
( select a from t1 where a=1
|
|
union
|
|
select a from t1 where a=3
|
|
order by a desc) as dt;
|
|
|
|
select *from
|
|
( (select a from t1 limit 2)
|
|
union
|
|
select a from t1 where a=3
|
|
order by a desc) as dt;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
select * from
|
|
( (select a from t1 where a=1)
|
|
union
|
|
(select a from t1 where a=3)
|
|
order by a desc ) as dt;
|
|
|
|
select * from
|
|
( ( select a from t1 where a=1
|
|
union
|
|
select a from t1 where a=3 )
|
|
order by a desc ) as dt;
|
|
|
|
select * from
|
|
( ( ( select a from t1 where a=1 )
|
|
union
|
|
( select a from t1 where a=3 ) )
|
|
order by a desc ) as dt;
|
|
|
|
select * from
|
|
( ( select a from t1 where a=1
|
|
union
|
|
select a from t1 where a=3 )
|
|
order by 1 desc ) as dt;
|
|
|
|
select * from
|
|
( ( (select a from t1 where a=1
|
|
union
|
|
select a from t1 where a=3) ) order by 1 desc ) as dt;
|
|
|
|
select * from
|
|
((((select a from t1 where a=1) union (select a from t1 where a=3)))
|
|
order by 1 desc ) as dt;
|
|
|
|
select * from
|
|
( ( (select a from t1 where a=1 )
|
|
union
|
|
(select a from t1 where a=3) )
|
|
order by 1 desc ) as dt;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
--echo # 9.3. derived table as [tailed] TVC
|
|
|
|
select * from
|
|
( values (3), (7), (1) ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1)) ) as dt;
|
|
|
|
select * from
|
|
(((values (3), (7), (1)))) as dt;
|
|
|
|
select * from
|
|
( values (3), (7), (1) order by 1 limit 2 ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1)) order by 1 limit 2 ) as dt;
|
|
|
|
select * from
|
|
( ((values (3), (7), (1))) order by 1 limit 2 ) as dt;
|
|
|
|
select * from
|
|
( (((values (3), (7), (1))) order by 1 limit 2) ) as dt;
|
|
|
|
select * from
|
|
( ( (values (3), (7), (1) limit 2) order by 1 desc) ) as dt;
|
|
|
|
select * from
|
|
( ((values (3), (7), (1)) order by 1 desc) limit 2 ) as dt;
|
|
|
|
select * from
|
|
( (((values (3), (7), (1)) order by 1 desc) limit 2) ) as dt;
|
|
|
|
--echo # 9.3. derived table as union of TVCs
|
|
|
|
select * from
|
|
( values (3), (7), (1) union values (3), (4), (2) ) dt;
|
|
|
|
select * from
|
|
( values (3), (7), (1) union all values (3), (4), (2) ) as dt;
|
|
|
|
select * from
|
|
( values (3), (7), (1) union values (3), (4), (2) ) as dt;
|
|
|
|
select * from
|
|
( values (3), (7), (1) except values (3), (4), (2) ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1)) union (values (3), (4), (2)) ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1))
|
|
union
|
|
(values (3), (4), (2))
|
|
union values (5), (7) ) dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1))
|
|
union
|
|
(values (3), (4), (2))
|
|
union
|
|
(values (5), (7)) ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1)
|
|
union
|
|
values (3), (4), (2))
|
|
union
|
|
values (5), (7) ) as dt;
|
|
|
|
select * from
|
|
( values (3), (7), (1)
|
|
union (values (3), (4), (2)
|
|
union
|
|
values (5), (7)) ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1)
|
|
union
|
|
((values (3), (4), (2)
|
|
union values (5), (7)))) ) dt;
|
|
|
|
select * from
|
|
( values (3), (7), (1)
|
|
union
|
|
values (3), (4), (2)
|
|
order by 1 ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt;
|
|
|
|
select * from
|
|
( values (3), (7), (1) union (values (3), (4), (2)) order by 1 ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt;
|
|
|
|
select * from
|
|
( ((values (3), (7), (1)) union values (3), (4), (2)) order by 1 ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1) order by 1 limit 2)
|
|
union
|
|
(values (3), (4), (2) order by 1 desc limit 2) ) as dt;
|
|
|
|
select * from
|
|
( ((values (3), (7), (1) order by 1) limit 2)
|
|
union
|
|
((values (3), (4), (2) order by 1 desc) limit 2) ) as dt;
|
|
|
|
select * from
|
|
( (((values (3), (7), (1)) order by 1) limit 2)
|
|
union
|
|
(((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt;
|
|
|
|
select * from
|
|
( (values (3), (7), (1) order by 1 limit 2)
|
|
union
|
|
values (3), (4), (2)
|
|
order by 1 limit 3 ) as dt;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
select * from
|
|
( (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
|
|
--echo # 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;
|
|
|
|
select * from
|
|
( with t as (select * from t1 where a <=3)
|
|
(select * from t) ) as dt;
|
|
|
|
select * from
|
|
( with t as (select * from t1 where a <=3)
|
|
((select * from t)) ) as dt;
|
|
|
|
select * from
|
|
( with t as ((select * from t1 where a <=3))
|
|
select * from t ) as dt;
|
|
|
|
select * from
|
|
( with t as (((select * from t1 where a <=3)))
|
|
select * from t ) as dt;
|
|
|
|
select * from
|
|
( with t as (select * from t1 where a <=3)
|
|
select * from t order by a ) as dt;
|
|
|
|
select * from
|
|
( with t as (select * from t1 where a <=3)
|
|
(select * from t) order by a ) as dt;
|
|
|
|
select * from
|
|
( with t as (select * from t1 where a <=3)
|
|
(select * from t) order by a desc limit 2 ) as dt;
|
|
|
|
select * from
|
|
( with t as (select * from t1 where a >=2 order by a limit 2)
|
|
select * from t ) as dt;
|
|
|
|
select * from
|
|
( with t as (((select * from t1 where a >=2) order by a desc) limit 2)
|
|
select * from t ) as dt;
|
|
|
|
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;
|
|
|
|
--echo # 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;
|
|
|
|
select * from
|
|
( with t as (select * from t1 where a <=3)
|
|
(select a from t) union (select b from t2) ) as dt;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
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;
|
|
|
|
|
|
--echo 10. view
|
|
|
|
--echo 10.1. view as simple select
|
|
|
|
create view v1 as
|
|
select * from t1;
|
|
show create view v1;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
create view v1 as
|
|
select 2*a as c from t1;
|
|
show create view v1;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
create view v1(c) as
|
|
select 2*a from t1;
|
|
show create view v1;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
create view v1 as
|
|
((select * from t1));
|
|
show create view v1;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
--echo 10.2. view as tailed simple select
|
|
|
|
#enable after fix MDEV-29554
|
|
--disable_view_protocol
|
|
create view v1 as
|
|
select * from t1 order by a;
|
|
show create view v1;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
create view v1 as
|
|
(select * from t2 order by a limit 2) order by b desc;
|
|
show create view v1;
|
|
select * from v1;
|
|
drop view v1;
|
|
--enable_view_protocol
|
|
|
|
--echo 10.3. view as union
|
|
|
|
create view v1 as
|
|
select a from t1 union select b from t2;
|
|
show create view v1;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
create view v1 as
|
|
(select a from t1) union (select b from t2);
|
|
show create view v1;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
--echo 10.4. view as [tailed] TVC
|
|
|
|
create view v1 as
|
|
values (3), (7), (1);
|
|
show create view v1;
|
|
select * from v1;
|
|
drop view v1;
|
|
create view v1 as
|
|
(((values (3), (7), (1))) order by 1);
|
|
show create view v1;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
--echo 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;
|
|
select * from v1;
|
|
drop view v1;
|
|
create view v1 as
|
|
(values (3), (7), (1) union values (3), (4), (2)) order by 1;
|
|
show create view v1;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
--echo 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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
--echo 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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
--echo 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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
create view v1 as
|
|
with t(a) as (values (2), (1)) select a from t;
|
|
show create view v1;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
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;
|
|
select * from v1;
|
|
drop view v1;
|
|
|
|
drop table t1,t2;
|
|
|
|
--echo # End of 10.4 tests
|
|
|