1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
|
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
create table t1( id integer not null primary key, i1 integer, i2 integer, c10 char(10), c30 char(30), tm time);
0 rows inserted/updated/deleted
ij> create table t2( id integer not null primary key, i1 integer, i2 integer, vc20 varchar(20), d double, dt date);
0 rows inserted/updated/deleted
ij> insert into t1(id,i1,i2,c10,c30) values
(1,1,1,'a','123456789012345678901234567890'),
(2,1,2,'a','bb'),
(3,1,3,'b','bb'),
(4,1,3,'zz','5'),
(5,null,null,null,'1.0'),
(6,null,null,null,'a');
6 rows inserted/updated/deleted
ij> insert into t2(id,i1,i2,vc20,d) values
(1,1,1,'a',1.0),
(2,1,2,'a',1.1),
(5,null,null,'12345678901234567890',3),
(100,1,3,'zz',3),
(101,1,2,'bb',null),
(102,5,5,'',null),
(103,1,3,' a',null),
(104,1,3,'null',7.4);
8 rows inserted/updated/deleted
ij> -- no duplicates
select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by id DESC,i1,i2;
ID |I1 |I2
-----------------------------------
5 |NULL |NULL
2 |1 |2
1 |1 |1
ij> select id,i1,i2 from t1 intersect distinct select id,i1,i2 from t2 order by id DESC,i1,i2;
ID |I1 |I2
-----------------------------------
5 |NULL |NULL
2 |1 |2
1 |1 |1
ij> select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 1,2,3;
ID |I1 |I2
-----------------------------------
1 |1 |1
2 |1 |2
5 |NULL |NULL
ij> -- Only specify order by on some columns
select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by i2, id DESC;
ID |I1 |I2
-----------------------------------
1 |1 |1
2 |1 |2
5 |NULL |NULL
ij> select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 3 DESC, 1;
ID |I1 |I2
-----------------------------------
5 |NULL |NULL
2 |1 |2
1 |1 |1
ij> -- duplicates
select i1,i2 from t1 intersect select i1,i2 from t2 order by 1,2;
I1 |I2
-----------------------
1 |1
1 |2
1 |3
NULL |NULL
ij> select i1,i2 from t1 intersect distinct select i1,i2 from t2 order by 1,2;
I1 |I2
-----------------------
1 |1
1 |2
1 |3
NULL |NULL
ij> select i1,i2 from t1 intersect all select i1,i2 from t2 order by 1,2;
I1 |I2
-----------------------
1 |1
1 |2
1 |3
1 |3
NULL |NULL
ij> -- right side is empty
select i1,i2 from t1 intersect select i1,i2 from t2 where id = -1;
I1 |I2
-----------------------
ij> select i1,i2 from t1 intersect all select i1,i2 from t2 where id = -1;
I1 |I2
-----------------------
ij> -- left side is empty
select i1,i2 from t1 where id = -1 intersect all select i1,i2 from t2;
I1 |I2
-----------------------
ij> -- check precedence
select i1,i2 from t1 intersect all select i1,i2 from t2 intersect values(5,5),(1,3) order by 1,2;
1 |2
-----------------------
1 |3
ij> (select i1,i2 from t1 intersect all select i1,i2 from t2) intersect values(5,5),(1,3) order by 1,2;
1 |2
-----------------------
1 |3
ij> values(-1,-1,-1) union select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by 1,2,3;
1 |2 |3
-----------------------------------
-1 |-1 |-1
1 |1 |1
2 |1 |2
5 |NULL |NULL
ij> select id,i1,i2 from t1 intersect select id,i1,i2 from t2 union values(-1,-1,-1) order by 1,2,3;
1 |2 |3
-----------------------------------
-1 |-1 |-1
1 |1 |1
2 |1 |2
5 |NULL |NULL
ij> -- check conversions
select c10 from t1 intersect select vc20 from t2 order by 1;
1
--------------------
a
zz
ij> select c30 from t1 intersect select vc20 from t2;
1
------------------------------
a
bb
ij> select c30 from t1 intersect all select vc20 from t2;
1
------------------------------
a
bb
ij> -- check insert intersect into table and intersect without order by
create table r( i1 integer, i2 integer);
0 rows inserted/updated/deleted
ij> insert into r select i1,i2 from t1 intersect select i1,i2 from t2;
4 rows inserted/updated/deleted
ij> select i1,i2 from r order by 1,2;
I1 |I2
-----------------------
1 |1
1 |2
1 |3
NULL |NULL
ij> delete from r;
4 rows inserted/updated/deleted
ij> insert into r select i1,i2 from t1 intersect all select i1,i2 from t2;
5 rows inserted/updated/deleted
ij> select i1,i2 from r order by 1,2;
I1 |I2
-----------------------
1 |1
1 |2
1 |3
1 |3
NULL |NULL
ij> delete from r;
5 rows inserted/updated/deleted
ij> -- test LOB
create table t3( i1 integer, cl clob(64), bl blob(1M));
0 rows inserted/updated/deleted
ij> insert into t3 values
(1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));
1 row inserted/updated/deleted
ij> create table t4( i1 integer, cl clob(64), bl blob(1M));
0 rows inserted/updated/deleted
ij> insert into t4 values
(1, cast( 'aa' as clob(64)), cast(X'01' as blob(1M)));
1 row inserted/updated/deleted
ij> select cl from t3 intersect select cl from t4 order by 1;
ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> select bl from t3 intersect select bl from t4 order by 1;
ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- invalid conversion
select tm from t1 intersect select dt from t2;
ERROR 42X61: Types 'TIME' and 'DATE' are not INTERSECT compatible.
ij> select c30 from t1 intersect select d from t2;
ERROR 42X61: Types 'CHAR' and 'DOUBLE' are not INTERSECT compatible.
ij> -- different number of columns
select i1 from t1 intersect select i1,i2 from t2;
ERROR 42X58: The number of columns on the left and right sides of the INTERSECT must be the same.
ij> -- ? in select list of intersect
select ? from t1 intersect select i1 from t2;
ERROR 42X34: There is a ? parameter in the select list. This is not allowed.
ij> select i1 from t1 intersect select ? from t2;
ERROR 42X34: There is a ? parameter in the select list. This is not allowed.
ij> -- except tests
select id,i1,i2 from t1 except select id,i1,i2 from t2 order by id,i1,i2;
ID |I1 |I2
-----------------------------------
3 |1 |3
4 |1 |3
6 |NULL |NULL
ij> select id,i1,i2 from t1 except distinct select id,i1,i2 from t2 order by id,i1,i2;
ID |I1 |I2
-----------------------------------
3 |1 |3
4 |1 |3
6 |NULL |NULL
ij> select id,i1,i2 from t1 except all select id,i1,i2 from t2 order by 1 DESC,2,3;
ID |I1 |I2
-----------------------------------
6 |NULL |NULL
4 |1 |3
3 |1 |3
ij> select id,i1,i2 from t2 except select id,i1,i2 from t1 order by 1,2,3;
ID |I1 |I2
-----------------------------------
100 |1 |3
101 |1 |2
102 |5 |5
103 |1 |3
104 |1 |3
ij> select id,i1,i2 from t2 except all select id,i1,i2 from t1 order by 1,2,3;
ID |I1 |I2
-----------------------------------
100 |1 |3
101 |1 |2
102 |5 |5
103 |1 |3
104 |1 |3
ij> select i1,i2 from t1 except select i1,i2 from t2 order by 1,2;
I1 |I2
-----------------------
ij> select i1,i2 from t1 except distinct select i1,i2 from t2 order by 1,2;
I1 |I2
-----------------------
ij> select i1,i2 from t1 except all select i1,i2 from t2 order by 1,2;
I1 |I2
-----------------------
NULL |NULL
ij> select i1,i2 from t2 except select i1,i2 from t1 order by 1,2;
I1 |I2
-----------------------
5 |5
ij> select i1,i2 from t2 except all select i1,i2 from t1 order by 1,2;
I1 |I2
-----------------------
1 |2
1 |3
5 |5
ij> -- right side is empty
select i1,i2 from t1 except select i1,i2 from t2 where id = -1 order by 1,2;
I1 |I2
-----------------------
1 |1
1 |2
1 |3
NULL |NULL
ij> select i1,i2 from t1 except all select i1,i2 from t2 where id = -1 order by 1,2;
I1 |I2
-----------------------
1 |1
1 |2
1 |3
1 |3
NULL |NULL
NULL |NULL
ij> -- left side is empty
select i1,i2 from t1 where id = -1 except select i1,i2 from t2 order by 1,2;
I1 |I2
-----------------------
ij> select i1,i2 from t1 where id = -1 except all select i1,i2 from t2 order by 1,2;
I1 |I2
-----------------------
ij> -- Check precedence. Union and except have the same precedence. Intersect has higher precedence.
select i1,i2 from t1 except select i1,i2 from t2 intersect values(-1,-1) order by 1,2;
1 |2
-----------------------
1 |1
1 |2
1 |3
NULL |NULL
ij> select i1,i2 from t1 except (select i1,i2 from t2 intersect values(-1,-1)) order by 1,2;
1 |2
-----------------------
1 |1
1 |2
1 |3
NULL |NULL
ij> select i1,i2 from t2 except select i1,i2 from t1 union values(5,5) order by 1,2;
1 |2
-----------------------
5 |5
ij> (select i1,i2 from t2 except select i1,i2 from t1) union values(5,5) order by 1,2;
1 |2
-----------------------
5 |5
ij> select i1,i2 from t2 except all select i1,i2 from t1 except select i1,i2 from t1 where id = 3 order by 1,2;
I1 |I2
-----------------------
1 |2
5 |5
ij> (select i1,i2 from t2 except all select i1,i2 from t1) except select i1,i2 from t1 where id = 3 order by 1,2;
I1 |I2
-----------------------
1 |2
5 |5
ij> -- check conversions
select c10 from t1 except select vc20 from t2 order by 1;
1
--------------------
b
NULL
ij> select c30 from t1 except select vc20 from t2 order by 1;
1
------------------------------
1.0
123456789012345678901234567890
5
ij> select c30 from t1 except all select vc20 from t2;
1
------------------------------
1.0
123456789012345678901234567890
5
bb
ij> -- check insert except into table and except without order by
insert into r select i1,i2 from t2 except select i1,i2 from t1;
1 row inserted/updated/deleted
ij> select i1,i2 from r order by 1,2;
I1 |I2
-----------------------
5 |5
ij> delete from r;
1 row inserted/updated/deleted
ij> insert into r select i1,i2 from t2 except all select i1,i2 from t1;
3 rows inserted/updated/deleted
ij> select i1,i2 from r order by 1,2;
I1 |I2
-----------------------
1 |2
1 |3
5 |5
ij> delete from r;
3 rows inserted/updated/deleted
ij> -- test LOB
select cl from t3 except select cl from t4 order by 1;
ERROR X0X67: Columns of type 'CLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> select bl from t3 except select bl from t4 order by 1;
ERROR X0X67: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
ij> -- invalid conversion
select tm from t1 except select dt from t2;
ERROR 42X61: Types 'TIME' and 'DATE' are not EXCEPT compatible.
ij> select c30 from t1 except select d from t2;
ERROR 42X61: Types 'CHAR' and 'DOUBLE' are not EXCEPT compatible.
ij> -- different number of columns
select i1 from t1 except select i1,i2 from t2;
ERROR 42X58: The number of columns on the left and right sides of the EXCEPT must be the same.
ij> -- ? in select list of except
select ? from t1 except select i1 from t2;
ERROR 42X34: There is a ? parameter in the select list. This is not allowed.
ij> -- Invalid order by
select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by t1.i1;
ERROR 42877: A qualified column name 'T1.I1' is not allowed in the ORDER BY clause.
ij> select id,i1,i2 from t1 except select id,i1,i2 from t2 order by t1.i1;
ERROR 42877: A qualified column name 'T1.I1' is not allowed in the ORDER BY clause.
ij> -- views using intersect and except
create view view_intr_uniq as select id,i1,i2 from t1 intersect select id,i1,i2 from t2;
0 rows inserted/updated/deleted
ij> select * from view_intr_uniq order by 1 DESC,2,3;
ID |I1 |I2
-----------------------------------
5 |NULL |NULL
2 |1 |2
1 |1 |1
ij> create view view_intr_all as select id,i1,i2 from t1 intersect all select id,i1,i2 from t2;
0 rows inserted/updated/deleted
ij> select * from view_intr_all order by 1,2,3;
ID |I1 |I2
-----------------------------------
1 |1 |1
2 |1 |2
5 |NULL |NULL
ij> create view view_ex_uniq as select id,i1,i2 from t1 except select id,i1,i2 from t2;
0 rows inserted/updated/deleted
ij> select * from view_ex_uniq order by 1,2,3;
ID |I1 |I2
-----------------------------------
3 |1 |3
4 |1 |3
6 |NULL |NULL
ij> create view view_ex_all as select id,i1,i2 from t1 except all select id,i1,i2 from t2;
0 rows inserted/updated/deleted
ij> select * from view_ex_all order by 1 DESC,2,3;
ID |I1 |I2
-----------------------------------
6 |NULL |NULL
4 |1 |3
3 |1 |3
ij> -- intersect joins
select t1.id,t1.i1,t2.i1 from t1 join t2 on t1.id = t2.id
intersect select t1.id,t1.i2,t2.i2 from t1 join t2 on t1.id = t2.id;
ID |2 |3
-----------------------------------
1 |1 |1
5 |NULL |NULL
ij> -- DERBY-4433: Insert from INTERSECT/EXCEPT into subset of columns
create table d4433_t1(x int);
0 rows inserted/updated/deleted
ij> insert into d4433_t1 values 1,2,3,4;
4 rows inserted/updated/deleted
ij> create table d4433_t2(x int);
0 rows inserted/updated/deleted
ij> insert into d4433_t2 values 3,4,5,6;
4 rows inserted/updated/deleted
ij> create table d4433_t3(x int, y int);
0 rows inserted/updated/deleted
ij> insert into d4433_t3(x) select x from d4433_t1 intersect select x from d4433_t2;
2 rows inserted/updated/deleted
ij> select * from d4433_t3 order by x, y;
X |Y
-----------------------
3 |NULL
4 |NULL
ij> insert into d4433_t3(x) select x from d4433_t1 except select x from d4433_t2;
2 rows inserted/updated/deleted
ij> select * from d4433_t3 order by x, y;
X |Y
-----------------------
1 |NULL
2 |NULL
3 |NULL
4 |NULL
ij>
|
