| 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> |
