oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: yuchen.wyc
5
# owner group: SQL1
6
# Test of functions except
7

8
--disable_warnings
9
drop table if exists t1,t2,t3,t4;
10
--enable_warnings
11

12

13
create table t1(c1 int primary key, c2 int);
14
create table t2(c1 int primary key, c2 int);
15
create table t3(c1 int, c2 int primary key);
16
create table t4(c1 int primary key, c2 int);
17

18
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
19
insert into t2 values(0,0),(2,2),(4,4),(6,6);
20
insert into t3 values(1,1),(3,3),(5,5),(7,7);
21
insert into t4 values(1,0),(2,0),(3,1),(4,1);
22

23
#all same
24
select * from t1 except select * from t1;
25
--error 1064
26
select * from t1 except all select * from t1;
27
#partial same 
28
select * from t1 except select * from t2;
29
--error 1064
30
select * from t1 except all select * from t2;
31

32
#different schema
33
select * from t1 except select * from t3;
34
--error 1064
35
select * from t1 except all select * from t3;
36

37
#constant
38
select * from t1 except select 1,1 from t1;
39
--error 1064
40
select * from t1 except all select 1,1 from t1;
41

42
#has no same
43
select * from t2 except select * from t3;
44
--error 1064
45
select * from t2 except all select * from t3;
46

47
#empty input
48
(select * from t2) except (select * from t2 where false);
49
--error 1064
50
(select * from t2) except all (select * from t2 where false);
51
(select * from t2 where false) except (select * from t2);
52
(select * from t2 where false) except (select * from t2 where false);
53

54
#distinct
55
select c2  from t4 except select 1 from t4;
56
--error 1064
57
select c2  from t4 except all select 1 from t4;
58
select c2  from t4 except select 0 from t4;
59
select c2  from t4 except select c2  from t4;
60
select c2  from t4 except select distinct c2  from t4;
61
select distinct c2  from t4 except select distinct c2  from t4;
62
--error 1064
63
select distinct c2  from t4 except all select distinct c2  from t4;
64
select distinct c2  from t4 except select c2  from t4;
65
--error 1064
66
select distinct c2  from t4 except all select c2  from t4;
67

68
select 0 from t4 except select c2 from t4;
69
select 1 from t4 except select 0 from t4;
70
--error 1064
71
select 1 from t4 except all select 0 from t4;
72

73

74
# nest
75
(select * from t4 except (select * from t4 where c1=1) )except (select * from t4 where c1=2);
76
select * from t4 except (select * from t4 where c1=1) except (select * from t4 where c1=3);
77
select * from t4 except select 1,0 from t4 except select 3,1 from t4;
78
(select * from t4 where false) except (select * from t4 where false) except select * from t4;
79

80
select * from t4 except ((select * from t4 where c1=1) except (select * from t4 where c1=2));
81
select * from t4 except ((select * from t4 where c1=1) union (select * from t4 where c1=2));
82
--disable_warnings
83
drop table if exists t5,t6;
84
--enable_warnings
85
create table t5(c1 int primary key, c2 int);
86
create table t6(c1 int primary key, c2 int);
87

88
insert into t5 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,2),(7,3),(8,6);
89
insert into t6 values(0,0),(2,2),(4,4),(6,6);
90
select c2 from t5 except select c2 from t6;
91
--error 1064
92
select c2 from t5 except  all select c2 from t6;
93

94

95
drop table t1,t2,t3,t4,t5,t6;
96

97

98