/
envoy.istio
/
oceanbase
GigaCode
beta
ОбзорЦентр заботыВойти

oceanbase

Форк
0
Ветки: 12Коммиты: 12104Теги: 40
oceanbase
/tools
/deploy
/mysql_test
/r
/mysql
/
intersect.result 
247 строк · 15.6 Кб
1
drop table if exists t1,t2,t3,t4,t8;
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 where false ) intersect (select * from t2 where false);
11
c1	c2
12
(select * from t1 where false ) intersect all (select * from t2 where false);
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 t2 where false)' at line 1
14
(select * from t1 where false ) intersect select * from t2;
15
c1	c2
16
(select * from t1 where false ) intersect all select * from t2;
17
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
18
select * from t1 intersect (select * from t2 where false);
19
c1	c2
20
select * from t1 intersect all (select * from t2 where false);
21
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
22
(select * from t1 where false) intersect (select * from t2 where false) intersect (select * from t3 where false);
23
c1	c2
24
(select * from t1 where false) intersect all (select * from t2 where false) intersect (select * from t3 where false);
25
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) intersect (select * from t3 where false)' at line 1
26
select c1 from t1 intersect select c1 from t1;
27
c1
28
1
29
2
30
3
31
4
32
5
33
select c1 from t1 intersect all select c1 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 c1 from t1' at line 1
35
select c1 from t1 intersect select c1 from t4;
36
c1
37
1
38
2
39
3
40
4
41
select c1 from t1 intersect all select c1 from t4;
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 c1 from t4' at line 1
43
select c1 from t4 intersect select c1 from t1;
44
c1
45
1
46
2
47
3
48
4
49
select c1 from t4 intersect all select c1 from t1;
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 c1 from t1' at line 1
51
select c1 from t2 intersect select c1 from t3;
52
c1
53
select c1 from t2 intersect all select c1 from t3;
54
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 c1 from t3' at line 1
55
select c1 from t1 intersect select c1 from t2;
56
c1
57
2
58
4
59
select c1 from t1 intersect all select c1 from t2;
60
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 c1 from t2' at line 1
61
select c2 from t4 intersect select c2 from t1;
62
c2
63
1
64
select c2 from t4 intersect all select c2 from t1;
65
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 t1' at line 1
66
select c2 from t1 intersect select c2 from t4;
67
c2
68
1
69
select c2 from t1 intersect all select 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 c2 from t4' at line 1
71
select * from t1 intersect (select * from t2 union select * from t3) ;
72
c1	c2
73
1	1
74
2	2
75
3	3
76
4	4
77
5	5
78
select * from t1 intersect all  (select * from t2 union select * from t3) ;
79
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 union select * from t3)' at line 1
80
(select * from t1 intersect select * from t2) union (select * from t1 intersect select * from t3);
81
c1	c2
82
1	1
83
2	2
84
3	3
85
4	4
86
5	5
87
select * from t1 intersect all (select * from t2 union select * from t3) ;
88
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 union select * from t3)' at line 1
89
select c2 from t1 intersect select c2 from t3 intersect select c2 from t4;
90
c2
91
1
92
select c2 from t1 intersect all select c2 from t3 intersect select c2 from t4;
93
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 t3 intersect select c2 from t4' at line 1
94
select c2 from t1 intersect (select c2 from t3 intersect select c2 from t4);
95
c2
96
1
97
select c2 from t1 intersect all (select c2 from t3 intersect select c2 from t4);
98
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 t3 intersect select c2 from t4)' at line 1
99
drop table if exists t5,t6;
100
create table t5(c1 int primary key, c2 int);
101
create table t6(c1 int primary key, c2 int);
102
insert into t5 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,2),(7,3),(8,6),(9,6),(10,4),(11,2);
103
insert into t6 values(0,0),(2,2),(4,4),(6,6),(1,2),(3,4),(5,6);
104
select c2 from t5 intersect select c2 from t6;
105
c2
106
2
107
4
108
6
109
select c2 from t5 intersect  all select c2 from t6;
110
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
111
select c2 from t5 intersect  select c1,c2 from t6;
112
ERROR 21000: The used SELECT statements have a different number of columns
113
create table t8 (c1 BINARY(99) ,c2 INTEGER(127) ,c3 VARBINARY(71) ,c4 BINARY(65) ,c5 BIGINT(127) ,c6 INTEGER(127) ,c7 INT(127) ,c8 INTEGER(127) ,c9 NUMERIC(64,25) ,c10 VARBINARY(55) ,c11 BIGINT(127) ,c12 VARCHAR(86) ,c13 INTEGER(127) ,c14 INT(127) ,c15 INTEGER(127) ,c16 DECIMAL(64,15) ,c17 INTEGER(127) ,c18 VARBINARY(76) ,c19 INTEGER(127) , primary key(c7,c6), index i_OkcN(c19) global, index i_ahayugXY(c10,c18) global, index i_iA(c16,c2) global)  DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin PARTITION BY KEY(c7) PARTITIONS 20;
114
insert into t8 values('cdxLVdvvJjcoHFBECqXKkRGoMXYgNsTpJRrqYaluQdfEeB00000',0,'RetSgyIigYXIPgBXBsmZHQZQPLWcGoELCf00000','wD00000',0,0,0,0,0.0000000000000000000000000,'ieLhJAFmNCq00000',0,'DcKPncNvJjyYcNJKFmVUVhqvgUlLfINbycifYNHjzqALoLfn00000',0,0,0,0.000000000000000,0,'MojimRsQZuotWXMuvSQVDSdCmOaloSXNLRTxMYYXaxGbtuk00000',0);
115
explain (select /*+USE_HASH_AGGREGATION*/ c15 from (select c15 from t8) except (select c15 from t8) order by 1 limit 1000) union all (select  t8_alias1.c10 from t8 t8_alias1 limit 1);
116
Query Plan
117
================================================================================
118
|ID|OPERATOR                       |NAME                 |EST.ROWS|EST.TIME(us)|
119
--------------------------------------------------------------------------------
120
|0 |UNION ALL                      |                     |2       |88          |
121
|1 |├─TOP-N SORT                   |                     |1       |86          |
122
|2 |│ └─HASH EXCEPT DISTINCT       |                     |1       |86          |
123
|3 |│   ├─PX COORDINATOR           |                     |1       |43          |
124
|4 |│   │ └─EXCHANGE OUT DISTR     |:EX10000             |1       |43          |
125
|5 |│   │   └─PX PARTITION ITERATOR|                     |1       |43          |
126
|6 |│   │     └─TABLE FULL SCAN    |t8                   |1       |43          |
127
|7 |│   └─PX COORDINATOR           |                     |1       |43          |
128
|8 |│     └─EXCHANGE OUT DISTR     |:EX20000             |1       |43          |
129
|9 |│       └─PX PARTITION ITERATOR|                     |1       |43          |
130
|10|│         └─TABLE FULL SCAN    |t8                   |1       |43          |
131
|11|└─DISTRIBUTED TABLE FULL SCAN  |t8_alias1(i_ahayugXY)|1       |3           |
132
================================================================================
133
Outputs & filters:
134
-------------------------------------
135
  0 - output([UNION([1])]), filter(nil), rowset=16
136
  1 - output([cast(EXCEPT([1]), VARCHAR(127))]), filter(nil), rowset=16
137
      sort_keys([EXCEPT([1]), ASC]), topn(1000)
138
  2 - output([EXCEPT([1])], [cast(EXCEPT([1]), VARCHAR(127))]), filter(nil), rowset=16
139
  3 - output([t8.c15]), filter(nil), rowset=16
140
  4 - output([t8.c15]), filter(nil), rowset=16
141
      dop=1
142
  5 - output([t8.c15]), filter(nil), rowset=16
143
      force partition granule
144
  6 - output([t8.c15]), filter(nil), rowset=16
145
      access([t8.c15]), partitions(p[0-19])
146
      is_index_back=false, is_global_index=false, 
147
      range_key([t8.c7], [t8.c6]), range(MIN,MIN ; MAX,MAX)always true
148
  7 - output([t8.c15]), filter(nil), rowset=16
149
  8 - output([t8.c15]), filter(nil), rowset=16
150
      dop=1
151
  9 - output([t8.c15]), filter(nil), rowset=16
152
      force partition granule
153
 10 - output([t8.c15]), filter(nil), rowset=16
154
      access([t8.c15]), partitions(p[0-19])
155
      is_index_back=false, is_global_index=false, 
156
      range_key([t8.c7], [t8.c6]), range(MIN,MIN ; MAX,MAX)always true
157
 11 - output([t8_alias1.c10]), filter(nil), rowset=16
158
      access([t8_alias1.c10]), partitions(p0)
159
      limit(1), offset(nil), is_index_back=false, is_global_index=true, 
160
      range_key([t8_alias1.c10], [t8_alias1.c18], [t8_alias1.c7], [t8_alias1.c6]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true
161
(select /*+USE_HASH_AGGREGATION*/ c15 from (select c15 from t8) except (select c15 from t8) order by 1 limit 1000) union all (select  t8_alias1.c10 from t8 t8_alias1 limit 1);
162
c15
163
ieLhJAFmNCq00000
164
explain (select /*+USE_HASH_AGGREGATION*/ c15 from (select c15 from t8) intersect (select c15 from t8) order by 1 limit 1000) union all (select  t8_alias1.c10 from t8 t8_alias1 limit 1);
165
Query Plan
166
================================================================================
167
|ID|OPERATOR                       |NAME                 |EST.ROWS|EST.TIME(us)|
168
--------------------------------------------------------------------------------
169
|0 |UNION ALL                      |                     |2       |88          |
170
|1 |├─TOP-N SORT                   |                     |1       |86          |
171
|2 |│ └─HASH INTERSECT DISTINCT    |                     |1       |86          |
172
|3 |│   ├─PX COORDINATOR           |                     |1       |43          |
173
|4 |│   │ └─EXCHANGE OUT DISTR     |:EX10000             |1       |43          |
174
|5 |│   │   └─PX PARTITION ITERATOR|                     |1       |43          |
175
|6 |│   │     └─TABLE FULL SCAN    |t8                   |1       |43          |
176
|7 |│   └─PX COORDINATOR           |                     |1       |43          |
177
|8 |│     └─EXCHANGE OUT DISTR     |:EX20000             |1       |43          |
178
|9 |│       └─PX PARTITION ITERATOR|                     |1       |43          |
179
|10|│         └─TABLE FULL SCAN    |t8                   |1       |43          |
180
|11|└─DISTRIBUTED TABLE FULL SCAN  |t8_alias1(i_ahayugXY)|1       |3           |
181
================================================================================
182
Outputs & filters:
183
-------------------------------------
184
  0 - output([UNION([1])]), filter(nil), rowset=16
185
  1 - output([cast(INTERSECT([1]), VARCHAR(127))]), filter(nil), rowset=16
186
      sort_keys([INTERSECT([1]), ASC]), topn(1000)
187
  2 - output([INTERSECT([1])], [cast(INTERSECT([1]), VARCHAR(127))]), filter(nil), rowset=16
188
  3 - output([t8.c15]), filter(nil), rowset=16
189
  4 - output([t8.c15]), filter(nil), rowset=16
190
      dop=1
191
  5 - output([t8.c15]), filter(nil), rowset=16
192
      force partition granule
193
  6 - output([t8.c15]), filter(nil), rowset=16
194
      access([t8.c15]), partitions(p[0-19])
195
      is_index_back=false, is_global_index=false, 
196
      range_key([t8.c7], [t8.c6]), range(MIN,MIN ; MAX,MAX)always true
197
  7 - output([t8.c15]), filter(nil), rowset=16
198
  8 - output([t8.c15]), filter(nil), rowset=16
199
      dop=1
200
  9 - output([t8.c15]), filter(nil), rowset=16
201
      force partition granule
202
 10 - output([t8.c15]), filter(nil), rowset=16
203
      access([t8.c15]), partitions(p[0-19])
204
      is_index_back=false, is_global_index=false, 
205
      range_key([t8.c7], [t8.c6]), range(MIN,MIN ; MAX,MAX)always true
206
 11 - output([t8_alias1.c10]), filter(nil), rowset=16
207
      access([t8_alias1.c10]), partitions(p0)
208
      limit(1), offset(nil), is_index_back=false, is_global_index=true, 
209
      range_key([t8_alias1.c10], [t8_alias1.c18], [t8_alias1.c7], [t8_alias1.c6]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true
210
(select /*+USE_HASH_AGGREGATION*/ c15 from (select c15 from t8) intersect (select c15 from t8) order by 1 limit 1000) union all (select  t8_alias1.c10 from t8 t8_alias1 limit 1);
211
c15
212
0
213
ieLhJAFmNCq00000
214
drop table t8;
215
create table t8 (c1 BINARY(99) ,c2 INTEGER(127) ,c3 VARBINARY(71) ,c4 BINARY(65) ,c5 BIGINT(127) ,c6 INTEGER(127) ,c7 INT(127) ,c8 INTEGER(127) ,c9 NUMERIC(64,25) ,c10 VARBINARY(55) ,c11 BIGINT(127) ,c12 VARCHAR(86) ,c13 INTEGER(127) ,c14 INT(127) ,c15 INTEGER(127) ,c16 DECIMAL(64,15) ,c17 INTEGER(127) ,c18 VARBINARY(76) ,c19 INTEGER(127) , primary key(c7,c6), index i_OkcN(c19), index i_ahayugXY(c10,c18), index i_iA(c16,c2));
216
insert into t8 values('cdxLVdvvJjcoHFBECqXKkRGoMXYgNsTpJRrqYaluQdfEeB00000',0,'RetSgyIigYXIPgBXBsmZHQZQPLWcGoELCf00000','wD00000',0,0,0,0,0.0000000000000000000000000,'ieLhJAFmNCq00000',0,'DcKPncNvJjyYcNJKFmVUVhqvgUlLfINbycifYNHjzqALoLfn00000',0,0,0,0.000000000000000,0,'MojimRsQZuotWXMuvSQVDSdCmOaloSXNLRTxMYYXaxGbtuk00000',0);
217
explain ((select c6 from t8 where c6 < 1000) except (select c6 from t8 where c6 > -1000) order by c6 limit 1000) union all (select  t8_alias1.c10 from t8 t8_alias1 limit 1);
218
Query Plan
219
=============================================================
220
|ID|OPERATOR                |NAME     |EST.ROWS|EST.TIME(us)|
221
-------------------------------------------------------------
222
|0 |UNION ALL               |         |2       |7           |
223
|1 |├─TOP-N SORT            |         |1       |5           |
224
|2 |│ └─HASH EXCEPT DISTINCT|         |1       |5           |
225
|3 |│   ├─TABLE FULL SCAN   |t8       |1       |3           |
226
|4 |│   └─TABLE FULL SCAN   |t8       |1       |3           |
227
|5 |└─TABLE FULL SCAN       |t8_alias1|1       |3           |
228
=============================================================
229
Outputs & filters:
230
-------------------------------------
231
  0 - output([UNION([1])]), filter(nil), rowset=16
232
  1 - output([cast(EXCEPT([1]), VARCHAR(127))]), filter(nil), rowset=16
233
      sort_keys([EXCEPT([1]), ASC]), topn(1000)
234
  2 - output([EXCEPT([1])], [cast(EXCEPT([1]), VARCHAR(127))]), filter(nil), rowset=16
235
  3 - output([t8.c6]), filter([t8.c6 < 1000]), rowset=16
236
      access([t8.c6]), partitions(p0)
237
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
238
      range_key([t8.c7], [t8.c6]), range(MIN,MIN ; MAX,MAX)always true
239
  4 - output([t8.c6]), filter([t8.c6 > -1000]), rowset=16
240
      access([t8.c6]), partitions(p0)
241
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
242
      range_key([t8.c7], [t8.c6]), range(MIN,MIN ; MAX,MAX)always true
243
  5 - output([t8_alias1.c10]), filter(nil), rowset=16
244
      access([t8_alias1.c10]), partitions(p0)
245
      limit(1), offset(nil), is_index_back=false, is_global_index=false, 
246
      range_key([t8_alias1.c7], [t8_alias1.c6]), range(MIN,MIN ; MAX,MAX)always true
247
drop table t1,t2,t3,t4,t5,t6, t8;
248

Использование cookies

Мы используем файлы cookie в соответствии с Политикой конфиденциальности и Политикой использования cookies.

Нажимая кнопку «Принимаю», Вы даете АО «СберТех» согласие на обработку Ваших персональных данных в целях совершенствования нашего веб-сайта и Сервиса GitVerse, а также повышения удобства их использования.

Запретить использование cookies Вы можете самостоятельно в настройках Вашего браузера.