oceanbase

1
drop table if exists t1,t2,t3,t4;
2
create table t1(c1 int primary key, c2 int);
3
create table t2(c1 int primary key, c2 int);
4
create table t3(c1 int, c2 int primary key);
5
create table t4(c1 int primary key, c2 int);
6
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
7
insert into t2 values(0,0),(2,2),(4,4),(6,6);
8
insert into t3 values(1,1),(3,3),(5,5),(7,7);
9
insert into t4 values(1,0),(2,0),(3,1),(4,1);
10
select * from t1 except select * from t1;
11
c1	c2
12
select * from t1 except all select * from t1;
13
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all select * from t1' at line 1
14
select * from t1 except select * from t2;
15
c1	c2
16
1	1
17
3	3
18
5	5
19
select * from t1 except all select * from t2;
20
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all select * from t2' at line 1
21
select * from t1 except select * from t3;
22
c1	c2
23
2	2
24
4	4
25
select * from t1 except all select * from t3;
26
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all select * from t3' at line 1
27
select * from t1 except select 1,1 from t1;
28
c1	c2
29
2	2
30
3	3
31
4	4
32
5	5
33
select * from t1 except all select 1,1 from t1;
34
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all select 1,1 from t1' at line 1
35
select * from t2 except select * from t3;
36
c1	c2
37
0	0
38
2	2
39
4	4
40
6	6
41
select * from t2 except all select * from t3;
42
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all select * from t3' at line 1
43
(select * from t2) except (select * from t2 where false);
44
c1	c2
45
0	0
46
2	2
47
4	4
48
6	6
49
(select * from t2) except all (select * from t2 where false);
50
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all (select * from t2 where false)' at line 1
51
(select * from t2 where false) except (select * from t2);
52
c1	c2
53
(select * from t2 where false) except (select * from t2 where false);
54
c1	c2
55
select c2  from t4 except select 1 from t4;
56
c2
57
0
58
select c2  from t4 except all select 1 from t4;
59
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all select 1 from t4' at line 1
60
select c2  from t4 except select 0 from t4;
61
c2
62
1
63
select c2  from t4 except select c2  from t4;
64
c2
65
select c2  from t4 except select distinct c2  from t4;
66
c2
67
select distinct c2  from t4 except select distinct c2  from t4;
68
c2
69
select distinct c2  from t4 except all select distinct c2  from t4;
70
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all select distinct c2  from t4' at line 1
71
select distinct c2  from t4 except select c2  from t4;
72
c2
73
select distinct c2  from t4 except all select c2  from t4;
74
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all select c2  from t4' at line 1
75
select 0 from t4 except select c2 from t4;
76
0
77
select 1 from t4 except select 0 from t4;
78
1
79
1
80
select 1 from t4 except all select 0 from t4;
81
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all select 0 from t4' at line 1
82
(select * from t4 except (select * from t4 where c1=1) )except (select * from t4 where c1=2);
83
c1	c2
84
3	1
85
4	1
86
select * from t4 except (select * from t4 where c1=1) except (select * from t4 where c1=3);
87
c1	c2
88
2	0
89
4	1
90
select * from t4 except select 1,0 from t4 except select 3,1 from t4;
91
c1	c2
92
2	0
93
4	1
94
(select * from t4 where false) except (select * from t4 where false) except select * from t4;
95
c1	c2
96
select * from t4 except ((select * from t4 where c1=1) except (select * from t4 where c1=2));
97
c1	c2
98
2	0
99
3	1
100
4	1
101
select * from t4 except ((select * from t4 where c1=1) union (select * from t4 where c1=2));
102
c1	c2
103
3	1
104
4	1
105
drop table if exists t5,t6;
106
create table t5(c1 int primary key, c2 int);
107
create table t6(c1 int primary key, c2 int);
108
insert into t5 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,2),(7,3),(8,6);
109
insert into t6 values(0,0),(2,2),(4,4),(6,6);
110
select c2 from t5 except select c2 from t6;
111
c2
112
1
113
3
114
5
115
select c2 from t5 except  all select c2 from t6;
116
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'all select c2 from t6' at line 1
117
drop table t1,t2,t3,t4,t5,t6;
118