| 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. |
| -- |
| -- |
| -- this test shows union functionality |
| -- |
| |
| -- create the tables |
| create table t1 (i int, s smallint, d double precision, r real, c10 char(10), |
| c30 char(30), vc10 varchar(10), vc30 varchar(30)); |
| 0 rows inserted/updated/deleted |
| ij> create table t2 (i int, s smallint, d double precision, r real, c10 char(10), |
| c30 char(30), vc10 varchar(10), vc30 varchar(30)); |
| 0 rows inserted/updated/deleted |
| ij> create table dups (i int, s smallint, d double precision, r real, c10 char(10), |
| c30 char(30), vc10 varchar(10), vc30 varchar(30)); |
| 0 rows inserted/updated/deleted |
| ij> -- populate the tables |
| insert into t1 values (null, null, null, null, null, null, null, null); |
| 1 row inserted/updated/deleted |
| ij> insert into t1 values (1, 1, 1e1, 1e1, '11111', '11111 11', '11111', |
| '11111 11'); |
| 1 row inserted/updated/deleted |
| ij> insert into t1 values (2, 2, 2e1, 2e1, '22222', '22222 22', '22222', |
| '22222 22'); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (null, null, null, null, null, null, null, null); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (3, 3, 3e1, 3e1, '33333', '33333 33', '33333', |
| '33333 33'); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (4, 4, 4e1, 4e1, '44444', '44444 44', '44444', |
| '44444 44'); |
| 1 row inserted/updated/deleted |
| ij> insert into dups select * from t1 union all select * from t2; |
| 6 rows inserted/updated/deleted |
| ij> -- simple cases |
| values (1, 2, 3, 4) union values (5, 6, 7, 8); |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| 5 |6 |7 |8 |
| ij> values (1, 2, 3, 4) union values (1, 2, 3, 4); |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| ij> values (1, 2, 3, 4) union distinct values (5, 6, 7, 8); |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| 5 |6 |7 |8 |
| ij> values (1, 2, 3, 4) union distinct values (1, 2, 3, 4); |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| ij> values (1, 2, 3, 4) union values (5, 6, 7, 8) union values (9, 10, 11, 12); |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| 5 |6 |7 |8 |
| 9 |10 |11 |12 |
| ij> values (1, 2, 3, 4) union values (1, 2, 3, 4) union values (1, 2, 3, 4); |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| ij> select * from t1 union select * from t2; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 |
| 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 |
| 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 |
| 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> select * from t1 union select * from t1; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 |
| 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> select * from t1 union select * from t2 union select * from dups; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 |
| 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 |
| 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 |
| 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> select * from t1 union select i, s, d, r, c10, c30, vc10, vc30 from t2; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 |
| 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 |
| 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 |
| 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> select * from t1 union select i, s, d, r, c10, c30, vc10, vc30 from t2 |
| union select * from dups; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 |
| 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 |
| 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 |
| 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> -- derived tables |
| select * from (values (1, 2, 3, 4) union values (5, 6, 7, 8)) a; |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| 5 |6 |7 |8 |
| ij> select * from (values (1, 2, 3, 4) union values (5, 6, 7, 8) union |
| values (1, 2, 3, 4)) a; |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| 5 |6 |7 |8 |
| ij> -- mix unions and union alls |
| select i from t1 union select i from t2 union all select i from dups; |
| I |
| ----------- |
| 1 |
| 2 |
| 3 |
| 4 |
| NULL |
| NULL |
| 1 |
| 2 |
| NULL |
| 3 |
| 4 |
| ij> (select i from t1 union select i from t2) union all select i from dups; |
| I |
| ----------- |
| 1 |
| 2 |
| 3 |
| 4 |
| NULL |
| NULL |
| 1 |
| 2 |
| NULL |
| 3 |
| 4 |
| ij> select i from t1 union (select i from t2 union all select i from dups); |
| I |
| ----------- |
| 1 |
| 2 |
| 3 |
| 4 |
| NULL |
| ij> select i from t1 union all select i from t2 union select i from dups; |
| I |
| ----------- |
| 1 |
| 2 |
| 3 |
| 4 |
| NULL |
| ij> (select i from t1 union all select i from t2) union select i from dups; |
| I |
| ----------- |
| 1 |
| 2 |
| 3 |
| 4 |
| NULL |
| ij> select i from t1 union all (select i from t2 union select i from dups); |
| I |
| ----------- |
| NULL |
| 1 |
| 2 |
| 1 |
| 2 |
| 3 |
| 4 |
| NULL |
| ij> -- joins |
| select a.i, b.i from t1 a, t2 b union select b.i, a.i from t1 a, t2 b; |
| I |I |
| ----------------------- |
| 1 |3 |
| 1 |4 |
| 1 |NULL |
| 2 |3 |
| 2 |4 |
| 2 |NULL |
| 3 |1 |
| 3 |2 |
| 3 |NULL |
| 4 |1 |
| 4 |2 |
| 4 |NULL |
| NULL |1 |
| NULL |2 |
| NULL |3 |
| NULL |4 |
| NULL |NULL |
| ij> values (9, 10) union |
| select a.i, b.i from t1 a, t2 b union select b.i, a.i from t1 a, t2 b; |
| 1 |2 |
| ----------------------- |
| 1 |3 |
| 1 |4 |
| 1 |NULL |
| 2 |3 |
| 2 |4 |
| 2 |NULL |
| 3 |1 |
| 3 |2 |
| 3 |NULL |
| 4 |1 |
| 4 |2 |
| 4 |NULL |
| 9 |10 |
| NULL |1 |
| NULL |2 |
| NULL |3 |
| NULL |4 |
| NULL |NULL |
| ij> select a.i, b.i from t1 a, t2 b union |
| select b.i, a.i from t1 a, t2 b union values (9, 10); |
| 1 |2 |
| ----------------------- |
| 1 |3 |
| 1 |4 |
| 1 |NULL |
| 2 |3 |
| 2 |4 |
| 2 |NULL |
| 3 |1 |
| 3 |2 |
| 3 |NULL |
| 4 |1 |
| 4 |2 |
| 4 |NULL |
| 9 |10 |
| NULL |1 |
| NULL |2 |
| NULL |3 |
| NULL |4 |
| NULL |NULL |
| ij> -- non-correlated subqueries |
| |
| -- positive tests |
| select i from t1 where i = (values 1 union values 1); |
| I |
| ----------- |
| 1 |
| ij> select i from t1 where i = (values 1 union values 1 union values 1); |
| I |
| ----------- |
| 1 |
| ij> -- expression subquery |
| select i from t1 where i = (select 1 from t2 union values 1); |
| I |
| ----------- |
| 1 |
| ij> -- in subquery |
| select i from t1 where i in (select i from t2 union values 1 union values 2); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> select i from t1 where i in |
| (select a from (select i from t2 union values 1 union values 2) a (a)); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> -- not in subquery |
| select i from t1 where i not in (select i from t2 union values 1 union values 2); |
| I |
| ----------- |
| ij> select i from t1 where i not in (select i from t2 where i is not null union |
| values 1 union values 22); |
| I |
| ----------- |
| 2 |
| ij> select i from t1 where i not in |
| (select a from (select i from t2 where i is not null union |
| values 111 union values 2) a (a)); |
| I |
| ----------- |
| 1 |
| ij> -- correlated union subquery |
| select i from t1 a where i in (select i from t2 where 1 = 0 union |
| select a.i from t2 where a.i < i); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> select i from t1 a where i in (select a.i from t2 where a.i < i union |
| select i from t2 where 1 < 0); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> -- exists subquery |
| select i from t1 where exists (select * from t2 union select * from t2); |
| I |
| ----------- |
| NULL |
| 1 |
| 2 |
| ij> select i from t1 where exists (select 1 from t2 union select 2 from t2); |
| I |
| ----------- |
| NULL |
| 1 |
| 2 |
| ij> select i from t1 where exists (select 1 from t2 where 1 = 0 union |
| select 2 from t2 where t1.i < i); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> select i from t1 where exists (select i from t2 where t1.i < i union |
| select i from t2 where 1 = 0 union |
| select i from t2 where t1.i < i union |
| select i from t2 where 1 = 0); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> -- These next two should fail because left/right children do not have |
| -- the same number of result columns. |
| select i from t1 where exists (select 1 from t2 where 1 = 0 union |
| select * from t2 where t1.i < i); |
| ERROR 42X58: The number of columns on the left and right sides of the UNION must be the same. |
| ij> select i from t1 where exists (select i from t2 where t1.i < i union |
| select * from t2 where 1 = 0 union |
| select * from t2 where t1.i < i union |
| select i from t2 where 1 = 0); |
| ERROR 42X58: The number of columns on the left and right sides of the UNION must be the same. |
| ij> -- order by tests |
| select i from t1 union select i from dups order by i desc; |
| I |
| ----------- |
| NULL |
| 4 |
| 3 |
| 2 |
| 1 |
| ij> select i, s from t1 union select s as i, 1 as s from dups order by s desc, i; |
| I |S |
| ----------------------- |
| NULL |NULL |
| 2 |2 |
| 1 |1 |
| 2 |1 |
| 3 |1 |
| 4 |1 |
| NULL |1 |
| ij> -- insert tests |
| create table insert_test (i int, s smallint, d double precision, r real, |
| c10 char(10), c30 char(30), vc10 varchar(10), vc30 varchar(30)); |
| 0 rows inserted/updated/deleted |
| ij> -- simple tests |
| insert into insert_test select * from t1 union select * from dups; |
| 5 rows inserted/updated/deleted |
| ij> select * from insert_test; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 |
| 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 |
| 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 |
| 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> delete from insert_test; |
| 5 rows inserted/updated/deleted |
| ij> insert into insert_test (s, i) values (2, 1) union values (4, 3); |
| 2 rows inserted/updated/deleted |
| ij> select * from insert_test; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 |2 |NULL |NULL |NULL |NULL |NULL |NULL |
| 3 |4 |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> delete from insert_test; |
| 2 rows inserted/updated/deleted |
| ij> -- test type dominance/length/nullability |
| insert into insert_test (vc30) select vc10 from t1 union select c30 from t2; |
| 5 rows inserted/updated/deleted |
| ij> select * from insert_test; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |11111 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |22222 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |33333 33 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |44444 44 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> delete from insert_test; |
| 5 rows inserted/updated/deleted |
| ij> insert into insert_test (c30) |
| select vc10 from t1 |
| union |
| select c30 from t2 |
| union |
| select c10 from t1; |
| 5 rows inserted/updated/deleted |
| ij> select * from insert_test; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| NULL |NULL |NULL |NULL |NULL |11111 |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |22222 |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |33333 33 |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |44444 44 |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> delete from insert_test; |
| 5 rows inserted/updated/deleted |
| ij> -- test NormalizeResultSet generation |
| select i, d from t1 union select d, i from t2; |
| 1 |2 |
| ------------------------------------------------- |
| 1.0 |10.0 |
| 2.0 |20.0 |
| 30.0 |3.0 |
| 40.0 |4.0 |
| NULL |NULL |
| ij> select vc10, c30 from t1 union select c30, vc10 from t2; |
| 1 |2 |
| ------------------------------------------------------------- |
| 11111 |11111 11 |
| 22222 |22222 22 |
| 33333 33 |33333 |
| 44444 44 |44444 |
| NULL |NULL |
| ij> create table insert_test2 (s smallint not null, vc30 varchar(30) not null); |
| 0 rows inserted/updated/deleted |
| ij> -- the following should fail due to null constraint |
| insert into insert_test2 select s, c10 from t1 union select s, c30 from t2; |
| ERROR 23502: Column 'S' cannot accept a NULL value. |
| ij> select * from insert_test2; |
| S |VC30 |
| ------------------------------------- |
| ij> -- negative tests |
| -- ? in select list of union |
| select ? from insert_test union select vc30 from insert_test; |
| ERROR 42X34: There is a ? parameter in the select list. This is not allowed. |
| ij> select vc30 from insert_test union select ? from insert_test; |
| ERROR 42X34: There is a ? parameter in the select list. This is not allowed. |
| ij> -- DB2 requires matching target and result column for insert |
| insert into insert_test values (1, 2) union values (3, 4); |
| ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. |
| ij> -- try some unions of different types. |
| -- types should be ok if comparable. |
| values (1) union values (1.1); |
| 1 |
| -------------- |
| 1.0 |
| 1.1 |
| ij> values (1) union values (1.1e1); |
| 1 |
| ------------------------ |
| 1.0 |
| 11.0 |
| ij> values (1.1) union values (1); |
| 1 |
| -------------- |
| 1.0 |
| 1.1 |
| ij> values (1.1e1) union values (1); |
| 1 |
| ------------------------ |
| 1.0 |
| 11.0 |
| ij> -- negative cases |
| values (x'aa') union values (1); |
| ERROR 42X61: Types 'CHAR () FOR BIT DATA' and 'INTEGER' are not UNION compatible. |
| ij> -- drop the tables |
| drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> drop table t2; |
| 0 rows inserted/updated/deleted |
| ij> drop table dups; |
| 0 rows inserted/updated/deleted |
| ij> drop table insert_test; |
| 0 rows inserted/updated/deleted |
| ij> drop table insert_test2; |
| 0 rows inserted/updated/deleted |
| ij> -- |
| -- this test shows the current supported union all functionality |
| -- |
| -- RESOLVE - whats not tested |
| -- type compatability |
| -- nullability of result |
| -- type dominance |
| -- correlated subqueries |
| -- table constructors |
| |
| -- create the tables |
| create table t1 (i int, s smallint, d double precision, r real, c10 char(10), |
| c30 char(30), vc10 varchar(10), vc30 varchar(30)); |
| 0 rows inserted/updated/deleted |
| ij> create table t2 (i int, s smallint, d double precision, r real, c10 char(10), |
| c30 char(30), vc10 varchar(10), vc30 varchar(30)); |
| 0 rows inserted/updated/deleted |
| ij> -- populate the tables |
| insert into t1 values (null, null, null, null, null, null, null, null); |
| 1 row inserted/updated/deleted |
| ij> insert into t1 values (1, 1, 1e1, 1e1, '11111', '11111 11', '11111', |
| '11111 11'); |
| 1 row inserted/updated/deleted |
| ij> insert into t1 values (2, 2, 2e1, 2e1, '22222', '22222 22', '22222', |
| '22222 22'); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (null, null, null, null, null, null, null, null); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (3, 3, 3e1, 3e1, '33333', '33333 33', '33333', |
| '33333 33'); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (4, 4, 4e1, 4e1, '44444', '44444 44', '44444', |
| '44444 44'); |
| 1 row inserted/updated/deleted |
| ij> -- negative tests |
| |
| -- non matching number of columns |
| select * from t1 union all select * from t1, t2; |
| ERROR 42X58: The number of columns on the left and right sides of the UNION must be the same. |
| ij> select * from t1 union all values (1, 2, 3, 4); |
| ERROR 42X58: The number of columns on the left and right sides of the UNION must be the same. |
| ij> values (1, 2, 3, 4) union all select * from t1; |
| ERROR 42X58: The number of columns on the left and right sides of the UNION must be the same. |
| ij> -- simple cases |
| values (1, 2, 3, 4) union all values (5, 6, 7, 8); |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| 5 |6 |7 |8 |
| ij> values (1, 2, 3, 4) union all values (5, 6, 7, 8) union all values (9, 10, 11, 12); |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| 5 |6 |7 |8 |
| 9 |10 |11 |12 |
| ij> select * from t1 union all select * from t2; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 |
| 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 |
| 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 |
| ij> select * from t1 union all select i, s, d, r, c10, c30, vc10, vc30 from t2; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 |
| 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 |
| 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 |
| ij> -- derived tables |
| select * from (values (1, 2, 3, 4) union all values (5, 6, 7, 8)) a; |
| 1 |2 |3 |4 |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| 5 |6 |7 |8 |
| ij> select * from (values (1, 2, 3, 4) union all values (5, 6, 7, 8)) a (a, b, c, d); |
| A |B |C |D |
| ----------------------------------------------- |
| 1 |2 |3 |4 |
| 5 |6 |7 |8 |
| ij> select b, d from (values (1, 2, 3, 4) union all values (5, 6, 7, 8)) a (a, b, c, d); |
| B |D |
| ----------------------- |
| 2 |4 |
| 6 |8 |
| ij> select * from (select i, s, c10, vc10 from t1 union all select i, s, c10, vc10 from t2) a; |
| I |S |C10 |VC10 |
| ---------------------------------------- |
| NULL |NULL |NULL |NULL |
| 1 |1 |11111 |11111 |
| 2 |2 |22222 |22222 |
| NULL |NULL |NULL |NULL |
| 3 |3 |33333 |33333 |
| 4 |4 |44444 |44444 |
| ij> select * from (select i, s, c10, vc10 from t1 union all |
| select i, s, c10, vc10 from t2) a (j, k, l, m), |
| (select i, s, c10, vc10 from t1 union all |
| select i, s, c10, vc10 from t2) b (j, k, l, m) |
| where a.j = b.j; |
| J |K |L |M |J |K |L |M |
| --------------------------------------------------------------------------------- |
| 1 |1 |11111 |11111 |1 |1 |11111 |11111 |
| 2 |2 |22222 |22222 |2 |2 |22222 |22222 |
| 3 |3 |33333 |33333 |3 |3 |33333 |33333 |
| 4 |4 |44444 |44444 |4 |4 |44444 |44444 |
| ij> -- joins |
| select a.i, b.i from t1 a, t2 b union all select b.i, a.i from t1 a, t2 b; |
| I |I |
| ----------------------- |
| NULL |NULL |
| NULL |3 |
| NULL |4 |
| 1 |NULL |
| 1 |3 |
| 1 |4 |
| 2 |NULL |
| 2 |3 |
| 2 |4 |
| NULL |NULL |
| 3 |NULL |
| 4 |NULL |
| NULL |1 |
| 3 |1 |
| 4 |1 |
| NULL |2 |
| 3 |2 |
| 4 |2 |
| ij> values (9, 10) union all |
| select a.i, b.i from t1 a, t2 b union all select b.i, a.i from t1 a, t2 b; |
| 1 |2 |
| ----------------------- |
| 9 |10 |
| NULL |NULL |
| NULL |3 |
| NULL |4 |
| 1 |NULL |
| 1 |3 |
| 1 |4 |
| 2 |NULL |
| 2 |3 |
| 2 |4 |
| NULL |NULL |
| 3 |NULL |
| 4 |NULL |
| NULL |1 |
| 3 |1 |
| 4 |1 |
| NULL |2 |
| 3 |2 |
| 4 |2 |
| ij> select a.i, b.i from t1 a, t2 b union all |
| select b.i, a.i from t1 a, t2 b union all values (9, 10); |
| 1 |2 |
| ----------------------- |
| NULL |NULL |
| NULL |3 |
| NULL |4 |
| 1 |NULL |
| 1 |3 |
| 1 |4 |
| 2 |NULL |
| 2 |3 |
| 2 |4 |
| NULL |NULL |
| 3 |NULL |
| 4 |NULL |
| NULL |1 |
| 3 |1 |
| 4 |1 |
| NULL |2 |
| 3 |2 |
| 4 |2 |
| 9 |10 |
| ij> -- incompatible types |
| select date('9999-11-11') from t1 union all select time('11:11:11') from t2; |
| ERROR 42X61: Types 'DATE' and 'TIME' are not UNION compatible. |
| ij> -- non-correlated subqueries |
| |
| -- negative tests |
| |
| -- select * in subquery |
| select i from t1 where i = (select * from t2 union all select 1 from t1); |
| ERROR 42X38: 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries. |
| ij> select i from t1 where i = (select 1 from t2 union all select * from t1); |
| ERROR 42X38: 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries. |
| ij> -- too many columns |
| select i from t1 where i = (values (1, 2, 3) union all values (1, 2, 3)); |
| ERROR 42X39: Subquery is only allowed to return a single column. |
| ij> select i from t1 where i = (select i, s from t2 union all select i, s from t1); |
| ERROR 42X39: Subquery is only allowed to return a single column. |
| ij> -- cardinality violation |
| select i from t1 where i = (values 1 union all values 1); |
| ERROR 21000: Scalar subquery is only allowed to return a single row. |
| ij> -- both sides of union have same type, which is incompatible with LHS |
| select i from t1 where i in (select date('1999-02-04') from t2 union all select date('1999-03-08') from t2); |
| ERROR 42818: Comparisons between 'INTEGER' and 'DATE' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') |
| ij> -- positive tests |
| |
| -- expression subquery |
| select i from t1 where i = (select i from t2 where 1 = 0 union all values 1); |
| I |
| ----------- |
| 1 |
| ij> -- in subquery |
| select i from t1 where i in (select i from t2 union all values 1 union all values 2); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> select i from t1 where i in |
| (select a from (select i from t2 union all values 1 union all values 2) a (a)); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> -- not in subquery |
| select i from t1 where i not in (select i from t2 union all values 1 union all values 2); |
| I |
| ----------- |
| ij> select i from t1 where i not in (select i from t2 where i is not null union all |
| values 1 union all values 22); |
| I |
| ----------- |
| 2 |
| ij> select i from t1 where i not in |
| (select a from (select i from t2 where i is not null union all |
| values 111 union all values 2) a (a)); |
| I |
| ----------- |
| 1 |
| ij> -- correlated union subquery |
| select i from t1 a where i in (select i from t2 where 1 = 0 union all |
| select a.i from t2 where a.i < i); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> select i from t1 a where i in (select a.i from t2 where a.i < i union all |
| select i from t2 where 1 < 0); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> -- exists subquery |
| select i from t1 where exists (select * from t2 union all select * from t2); |
| I |
| ----------- |
| NULL |
| 1 |
| 2 |
| ij> select i from t1 where exists (select 1 from t2 union all select 2 from t2); |
| I |
| ----------- |
| NULL |
| 1 |
| 2 |
| ij> select i from t1 where exists (select 1 from t2 where 1 = 0 union all |
| select 2 from t2 where t1.i < i); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> select i from t1 where exists (select i from t2 where t1.i < i union all |
| select i from t2 where 1 = 0 union all |
| select i from t2 where t1.i < i union all |
| select i from t2 where 1 = 0); |
| I |
| ----------- |
| 1 |
| 2 |
| ij> -- These next two should fail because left/right children do not have |
| -- the same number of result columns. |
| select i from t1 where exists (select 1 from t2 where 1 = 0 union all |
| select * from t2 where t1.i < i); |
| ERROR 42X58: The number of columns on the left and right sides of the UNION must be the same. |
| ij> select i from t1 where exists (select i from t2 where t1.i < i union all |
| select * from t2 where 1 = 0 union all |
| select * from t2 where t1.i < i union all |
| select i from t2 where 1 = 0); |
| ERROR 42X58: The number of columns on the left and right sides of the UNION must be the same. |
| ij> -- insert tests |
| create table insert_test (i int, s smallint, d double precision, r real, c10 char(10), |
| c30 char(30), vc10 varchar(10), vc30 varchar(30)); |
| 0 rows inserted/updated/deleted |
| ij> -- simple tests |
| insert into insert_test select * from t1 union all select * from t2; |
| 6 rows inserted/updated/deleted |
| ij> select * from insert_test; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 |
| 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 |
| 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 |
| ij> delete from insert_test; |
| 6 rows inserted/updated/deleted |
| ij> insert into insert_test (s, i) values (2, 1) union all values (4, 3); |
| 2 rows inserted/updated/deleted |
| ij> select * from insert_test; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 |2 |NULL |NULL |NULL |NULL |NULL |NULL |
| 3 |4 |NULL |NULL |NULL |NULL |NULL |NULL |
| ij> delete from insert_test; |
| 2 rows inserted/updated/deleted |
| ij> -- type conversions between union all and target table |
| insert into insert_test select s, i, r, d, vc10, vc30, c10, c30 from t1 union all |
| select s, i, r, d, vc10, vc30, c10, vc30 from t2; |
| 6 rows inserted/updated/deleted |
| ij> select * from insert_test; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| 1 |1 |10.0 |10.0 |11111 |11111 11 |11111 |11111 11 |
| 2 |2 |20.0 |20.0 |22222 |22222 22 |22222 |22222 22 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| 3 |3 |30.0 |30.0 |33333 |33333 33 |33333 |33333 33 |
| 4 |4 |40.0 |40.0 |44444 |44444 44 |44444 |44444 44 |
| ij> delete from insert_test; |
| 6 rows inserted/updated/deleted |
| ij> -- test type dominance/length/nullability |
| select vc10 from t1 union all select c30 from t2; |
| 1 |
| ------------------------------ |
| NULL |
| 11111 |
| 22222 |
| NULL |
| 33333 33 |
| 44444 44 |
| ij> insert into insert_test (vc30) select vc10 from t1 union all select c30 from t2; |
| 6 rows inserted/updated/deleted |
| ij> select * from insert_test; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |11111 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |22222 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |33333 33 |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |44444 44 |
| ij> delete from insert_test; |
| 6 rows inserted/updated/deleted |
| ij> insert into insert_test (c30) |
| select vc10 from t1 |
| union all |
| select c30 from t2 |
| union all |
| select c10 from t1; |
| 9 rows inserted/updated/deleted |
| ij> select * from insert_test; |
| I |S |D |R |C10 |C30 |VC10 |VC30 |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |11111 |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |22222 |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |33333 33 |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |44444 44 |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |11111 |NULL |NULL |
| NULL |NULL |NULL |NULL |NULL |22222 |NULL |NULL |
| ij> delete from insert_test; |
| 9 rows inserted/updated/deleted |
| ij> -- test NormalizeResultSet generation |
| select i, d from t1 union all select d, i from t2; |
| 1 |2 |
| ------------------------------------------------- |
| NULL |NULL |
| 1.0 |10.0 |
| 2.0 |20.0 |
| NULL |NULL |
| 30.0 |3.0 |
| 40.0 |4.0 |
| ij> select vc10, c30 from t1 union all select c30, vc10 from t2; |
| 1 |2 |
| ------------------------------------------------------------- |
| NULL |NULL |
| 11111 |11111 11 |
| 22222 |22222 22 |
| NULL |NULL |
| 33333 33 |33333 |
| 44444 44 |44444 |
| ij> create table insert_test2 (s smallint not null, vc30 varchar(30) not null); |
| 0 rows inserted/updated/deleted |
| ij> -- the following should fail due to null constraint |
| insert into insert_test2 select s, c10 from t1 union all select s, c30 from t2; |
| ERROR 23502: Column 'S' cannot accept a NULL value. |
| ij> select * from insert_test2; |
| S |VC30 |
| ------------------------------------- |
| ij> -- negative tests |
| -- ? in select list of union |
| select ? from insert_test union all select vc30 from insert_test; |
| ERROR 42X34: There is a ? parameter in the select list. This is not allowed. |
| ij> select vc30 from insert_test union all select ? from insert_test; |
| ERROR 42X34: There is a ? parameter in the select list. This is not allowed. |
| ij> -- DB2 requires matching target and result columns |
| insert into insert_test values (1, 2) union all values (3, 4); |
| ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. |
| ij> -- Beetle 4454 - test multiple union alls in a subquery |
| select vc10 from (select vc10 from t1 union all |
| select vc10 from t1 union all |
| select vc10 from t1 union all |
| select vc10 from t1 union all |
| select vc10 from t1 union all |
| select vc10 from t1 union all |
| select vc10 from t1) t; |
| VC10 |
| ---------- |
| NULL |
| 11111 |
| 22222 |
| NULL |
| 11111 |
| 22222 |
| NULL |
| 11111 |
| 22222 |
| NULL |
| 11111 |
| 22222 |
| NULL |
| 11111 |
| 22222 |
| NULL |
| 11111 |
| 22222 |
| NULL |
| 11111 |
| 22222 |
| ij> -- force union all on right side |
| select vc10 from (select vc10 from t1 union all (select vc10 from t1 union all |
| select vc10 from t1)) t; |
| VC10 |
| ---------- |
| NULL |
| 11111 |
| 22222 |
| NULL |
| 11111 |
| 22222 |
| NULL |
| 11111 |
| 22222 |
| ij> -- drop the tables |
| drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> drop table t2; |
| 0 rows inserted/updated/deleted |
| ij> drop table insert_test; |
| 0 rows inserted/updated/deleted |
| ij> drop table insert_test2; |
| 0 rows inserted/updated/deleted |
| ij> -- DERBY-1967 |
| -- NULLIF with UNION throws SQLSTATE 23502. |
| |
| create table a (f1 varchar(10)); |
| 0 rows inserted/updated/deleted |
| ij> create table b (f2 varchar(10)); |
| 0 rows inserted/updated/deleted |
| ij> insert into b values('test'); |
| 1 row inserted/updated/deleted |
| ij> -- this used to throw 23502 |
| select nullif('x','x') as f0, f1 from a |
| union all |
| select nullif('x','x') as f0, nullif('x','x') as f1 from b; |
| F0 |F1 |
| --------------- |
| NULL|NULL |
| ij> drop table a; |
| 0 rows inserted/updated/deleted |
| ij> drop table b; |
| 0 rows inserted/updated/deleted |
| ij> create table a (f1 int); |
| 0 rows inserted/updated/deleted |
| ij> create table b (f2 int); |
| 0 rows inserted/updated/deleted |
| ij> insert into b values(1); |
| 1 row inserted/updated/deleted |
| ij> -- ok |
| select nullif('x','x') as f0, f1 from a |
| union all |
| select nullif('x','x') as f0, nullif(1,1) as f1 from b; |
| F0 |F1 |
| ---------------- |
| NULL|NULL |
| ij> drop table a; |
| 0 rows inserted/updated/deleted |
| ij> drop table b; |
| 0 rows inserted/updated/deleted |
| ij> -- DERBY-681. Check union with group by/having |
| create table o (name varchar(20), ord int); |
| 0 rows inserted/updated/deleted |
| ij> create table a (ord int, amount int); |
| 0 rows inserted/updated/deleted |
| ij> create view v1 (vx, vy) |
| as select name, sum(ord) from o where ord > 0 group by name, ord |
| having ord <= ANY (select ord from a); |
| 0 rows inserted/updated/deleted |
| ij> select vx, vy from v1 |
| union select vx, sum(vy) from v1 group by vx, vy having (vy / 2) > 15; |
| VX |2 |
| -------------------------------- |
| ij> drop view v1; |
| 0 rows inserted/updated/deleted |
| ij> drop table o; |
| 0 rows inserted/updated/deleted |
| ij> drop table a; |
| 0 rows inserted/updated/deleted |
| ij> -- DERBY-1852: Incorrect results when a UNION U1 (with no "ALL") appears |
| -- in the FROM list of a SELECT query, AND there are duplicate rows |
| -- across the left and/or right result sets of U1, AND U1 is the left or |
| -- right child of another set operator. |
| |
| create table t1 (i int, j int); |
| 0 rows inserted/updated/deleted |
| ij> create table t2 (i int, j int); |
| 0 rows inserted/updated/deleted |
| ij> insert into t1 values (1, 2), (2, 4), (3, 6), (4, 8), (5, 10); |
| 5 rows inserted/updated/deleted |
| ij> insert into t2 values (1, 2), (2, -4), (3, 6), (4, -8), (5, 10); |
| 5 rows inserted/updated/deleted |
| ij> insert into t2 values (3, 6), (4, 8), (3, -6), (4, -8); |
| 4 rows inserted/updated/deleted |
| ij> -- U1 is left child of another UNION; top-level query. |
| select * from t1 union select * from t2 union select * from t1; |
| I |J |
| ----------------------- |
| 1 |2 |
| 2 |-4 |
| 2 |4 |
| 3 |-6 |
| 3 |6 |
| 4 |-8 |
| 4 |8 |
| 5 |10 |
| ij> -- U1 is left child of another UNION; subquery in FROM list. |
| select * from |
| (select * from t1 union select * from t2 union select * from t1) x; |
| I |J |
| ----------------------- |
| 1 |2 |
| 2 |-4 |
| 2 |4 |
| 3 |-6 |
| 3 |6 |
| 4 |-8 |
| 4 |8 |
| 5 |10 |
| ij> -- Same kind of thing, but in the form of a view (which is a |
| -- more likely use-ccase). |
| create view uv as |
| select * from t1 union select * from t2 union select * from t1; |
| 0 rows inserted/updated/deleted |
| ij> select * from uv; |
| I |J |
| ----------------------- |
| 1 |2 |
| 2 |-4 |
| 2 |4 |
| 3 |-6 |
| 3 |6 |
| 4 |-8 |
| 4 |8 |
| 5 |10 |
| ij> drop view uv; |
| 0 rows inserted/updated/deleted |
| ij> -- U1 is left child of a UNION ALL; top-level query. |
| select * from t1 union select * from t2 union all select * from t1; |
| I |J |
| ----------------------- |
| 1 |2 |
| 2 |-4 |
| 2 |4 |
| 3 |-6 |
| 3 |6 |
| 4 |-8 |
| 4 |8 |
| 5 |10 |
| 1 |2 |
| 2 |4 |
| 3 |6 |
| 4 |8 |
| 5 |10 |
| ij> -- U1 is left child of a UNION ALL; subquery in FROM list. |
| select * from |
| (select * from t1 union select * from t2 union all select * from t1) x; |
| I |J |
| ----------------------- |
| 1 |2 |
| 2 |-4 |
| 2 |4 |
| 3 |-6 |
| 3 |6 |
| 4 |-8 |
| 4 |8 |
| 5 |10 |
| 1 |2 |
| 2 |4 |
| 3 |6 |
| 4 |8 |
| 5 |10 |
| ij> -- U1 is left child of an EXCEPT; top-level query. |
| select * from t1 union select * from t2 except select * from t1; |
| I |J |
| ----------------------- |
| 2 |-4 |
| 3 |-6 |
| 4 |-8 |
| ij> -- U1 is left child of an EXCEPT; subquery in FROM list. |
| select * from |
| (select * from t1 union select * from t2 except select * from t1) x; |
| I |J |
| ----------------------- |
| 2 |-4 |
| 3 |-6 |
| 4 |-8 |
| ij> -- U1 is left child of an EXCEPT ALL; top-level query. |
| select * from t1 union select * from t2 except all select * from t1; |
| I |J |
| ----------------------- |
| 2 |-4 |
| 3 |-6 |
| 4 |-8 |
| ij> -- U1 is left child of an EXCEPT ALL; subquery in FROM list. |
| select * from |
| (select * from t1 union select * from t2 except all select * from t1) x; |
| I |J |
| ----------------------- |
| 2 |-4 |
| 3 |-6 |
| 4 |-8 |
| ij> -- U1 is left child of an INTERSECT; top-level query. |
| -- Note: intersect has higher precedence than union so we have to use |
| -- quotes to force the UNION to be a child of the intersect. |
| (select * from t1 union select * from t2) intersect select * from t2; |
| I |J |
| ----------------------- |
| 1 |2 |
| 2 |-4 |
| 3 |-6 |
| 3 |6 |
| 4 |-8 |
| 4 |8 |
| 5 |10 |
| ij> -- U1 is left child of an INTERSECT; subquery in FROM list. |
| create view iv as |
| (select * from t1 union select * from t2) intersect select * from t2; |
| 0 rows inserted/updated/deleted |
| ij> select * from iv; |
| I |J |
| ----------------------- |
| 1 |2 |
| 2 |-4 |
| 3 |-6 |
| 3 |6 |
| 4 |-8 |
| 4 |8 |
| 5 |10 |
| ij> drop view iv; |
| 0 rows inserted/updated/deleted |
| ij> -- U1 is left child of an INTERSECT ALL; top-level query. |
| (select * from t1 union select * from t2) intersect all select * from t2; |
| I |J |
| ----------------------- |
| 1 |2 |
| 2 |-4 |
| 3 |-6 |
| 3 |6 |
| 4 |-8 |
| 4 |8 |
| 5 |10 |
| ij> -- U1 is left child of an INTERSECT ALL; subquery in FROM list. |
| create view iv as |
| (select * from t1 union select * from t2) intersect all select * from t2; |
| 0 rows inserted/updated/deleted |
| ij> select * from iv; |
| I |J |
| ----------------------- |
| 1 |2 |
| 2 |-4 |
| 3 |-6 |
| 3 |6 |
| 4 |-8 |
| 4 |8 |
| 5 |10 |
| ij> drop view iv; |
| 0 rows inserted/updated/deleted |
| ij> -- Just as a sanity check, make sure things work if U1 is a child of |
| -- an explicit JoinNode (since JoinNode is an instanceof TableOperatorNode |
| -- and TableOperatorNode is where the bug for DERBY-1852 was fixed). |
| select * from |
| (select * from t1 union select * from t2) x2 left join t2 on x2.i = t2.i; |
| I |J |I |J |
| ----------------------------------------------- |
| 1 |2 |1 |2 |
| 2 |-4 |2 |-4 |
| 2 |4 |2 |-4 |
| 3 |-6 |3 |6 |
| 3 |-6 |3 |6 |
| 3 |-6 |3 |-6 |
| 3 |6 |3 |6 |
| 3 |6 |3 |6 |
| 3 |6 |3 |-6 |
| 4 |-8 |4 |-8 |
| 4 |-8 |4 |8 |
| 4 |-8 |4 |-8 |
| 4 |8 |4 |-8 |
| 4 |8 |4 |8 |
| 4 |8 |4 |-8 |
| 5 |10 |5 |10 |
| ij> -- cleanup. |
| drop table t1; |
| 0 rows inserted/updated/deleted |
| ij> drop table t2; |
| 0 rows inserted/updated/deleted |
| ij> -- Regression test for DERBY-4391. These UNION queries used to throw a |
| -- NullPointerException during compilation. Now all of them should compile |
| -- successfully, but some of them fail during execution if their subqueries |
| -- return more than one row. |
| create table d4391(a int not null primary key, b int); |
| 0 rows inserted/updated/deleted |
| ij> insert into d4391 values (0, 4), (1, 3), (2, 2), (3, 1), (4, 0); |
| 5 rows inserted/updated/deleted |
| ij> select * from d4391 where a < (values 2 union values 2); |
| A |B |
| ----------------------- |
| 0 |4 |
| 1 |3 |
| ij> select * from d4391 where a < (select 4 from d4391 union select b from d4391); |
| ERROR 21000: Scalar subquery is only allowed to return a single row. |
| ij> select * from d4391 where a < (select a+b from d4391 union select 4 from d4391); |
| A |B |
| ----------------------- |
| 0 |4 |
| 1 |3 |
| 2 |2 |
| 3 |1 |
| ij> select * from d4391 where a < (select a+b from d4391 union select a from d4391); |
| ERROR 21000: Scalar subquery is only allowed to return a single row. |
| ij> select * from d4391 where a < (select sum(a) from d4391 union select sum(b) from d4391); |
| A |B |
| ----------------------- |
| 0 |4 |
| 1 |3 |
| 2 |2 |
| 3 |1 |
| 4 |0 |
| ij> drop table d4391; |
| 0 rows inserted/updated/deleted |
| ij> -- Regression test for DERBY-4411. The predicate 1=0 used to be lost when the |
| -- SELECT statement was compiled, and the statement would fail with a message |
| -- saying that a scalar sub-query should return exactly one row. |
| create table d4411(a int primary key, b int); |
| 0 rows inserted/updated/deleted |
| ij> insert into d4411 values (0, 4), (1, 3), (2, 2), (3, 1), (4, 0); |
| 5 rows inserted/updated/deleted |
| ij> select * from d4411 where a < (values 2 union select b from d4411 where 1=0); |
| A |B |
| ----------------------- |
| 0 |4 |
| 1 |3 |
| ij> drop table d4411; |
| 0 rows inserted/updated/deleted |
| ij> |
