Google Git
Sign in
apache/derby/a8e109a98394159f3decd0a7b305ea6c7e5c8eea/./java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out
blob: 8eadd130c7acc5e091e88c6976db94e5291cc4b6 [file]
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.
--
-- ** insert aggregatesPositive.sql
autocommit on;
ij> -- General aggregate tests. Aggregate
-- specifics are tested in specific test (e.g. sum.jsql).
--
-- Note that this test does NOT test multiple datatypes,
-- that is exercised in the specific aggregate tests.
-- INSERT SELECT is also in the specific aggregate tests.
--
-- need to add: objects
create table t1 (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> create table t2 (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> create table oneRow (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> insert into oneRow values(1,1);
1 row inserted/updated/deleted
ij> create table empty (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> create table emptyNull (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> insert into emptyNull values (null, null);
1 row inserted/updated/deleted
ij> insert into t1 values (null, null), (1,1), (null, null), (2,1), (3,1), (10,10);
6 rows inserted/updated/deleted
ij> insert into t2 values (null, null), (1,1), (null, null), (2,1), (3,1), (10,10);
6 rows inserted/updated/deleted
ij> select * from t1;
C1 |C2
-----------------------
NULL |NULL
1 |1
NULL |NULL
2 |1
3 |1
10 |10
ij> --------------------------------------
-- Expressions within an aggregate
--------------------------------------
select max(c1+10) from t1;
1
-----------
20
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(c1+10) from t1 group by c2;
1
-----------
13
20
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(2*10) from t1;
1
-----------
20
ij> select max(2*10) from t1 group by c2;
1
-----------
20
20
20
ij> -- conditional operator within aggregate
select max(case when c1 <> 1 then 666 else 999 end) from oneRow;
1
-----------
999
ij> select max(case when c1 = 1 then 666 else c2 end) from oneRow;
1
-----------
666
ij> select max(case when c1 = 1 then 666 else c1 end) from oneRow;
1
-----------
666
ij> -- subquery in aggregate
select max((select c1 from empty)) from t1;
1
-----------
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- cast to string in aggregate
select max(cast (c1 as char(1))) from oneRow;
1
----
1
ij> -- cast to string in aggregate and concatenate with another
select max(cast(c1 as char(1)) || cast (c2 as char(1))) from oneRow;
1
----
11
ij> -- unary
select max(-c1) from t1;
1
-----------
-1
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- count
select count(c1) from t1;
1
-----------
4
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- cast
select count(cast (null as int)) from t1;
1
-----------
0
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- avg
-- DB2 returns error 22003
-- CS returns no error!
select avg(2147483647) from t1;
1
-----------
2147483647
ij> --------------------------------------
-- Expressions on an aggregates/with aggregates
--------------------------------------
select 10+sum(c1) from t1;
1
-----------
26
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select 10+sum(c1+10) from t1;
1
-----------
66
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- conditional operator on aggregate
select (case when max(c1) = 1 then 666 else 1 end) from t1;
1
-----------
1
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select (case when max(c1) = 1 then 666 else c1 end) from t1 group by c1;
1
-----------
666
2
3
10
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- method call on aggregate, cannot use nulls
select cast (max(c1) as char(1)) from oneRow;
1
----
1
ij> select cast (max(c1) as char(1)) from oneRow group by c1;
1
----
1
ij> select (cast(c1 as char(1)) || (cast (max(c2) as char(1)))) from oneRow group by c1;
1
----
11
ij> -- subquery on aggregate
select (select max(c1) from t2)from t1;
1
-----------
10
10
10
10
10
10
ij> select (select max(c1) from oneRow group by c2)from t1;
1
-----------
1
1
1
1
1
1
ij> -- unary
select -max(c1) from t1;
1
-----------
-10
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select -max(c1) from t1 group by c1;
1
-----------
-1
-2
-3
-10
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- cast
select cast (null as int), count(c1) from t1 group by c1;
1 |2
-----------------------
NULL |1
NULL |1
NULL |1
NULL |1
NULL |0
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count(cast (null as int)) from t1 group by c1;
1
-----------
0
WARNING 01003: Null values were eliminated from the argument of a column function.
0
WARNING 01003: Null values were eliminated from the argument of a column function.
0
WARNING 01003: Null values were eliminated from the argument of a column function.
0
WARNING 01003: Null values were eliminated from the argument of a column function.
0
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- binary list operator
select (1 in (1,2)), count(c1) from t1 group by c1;
1 |2
-----------------
true |1
true |1
true |1
true |1
true |0
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select count((1 in (1,2))) from t1 group by c1;
1
-----------
1
1
1
1
2
ij> -- some group by specific tests
select c2, 10+sum(c1), c2 from t1 group by c2;
C2 |2 |C2
-----------------------------------
1 |16 |1
10 |20 |10
NULL |NULL |NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select c2, 10+sum(c1+10), c2*2 from t1 group by c2;
C2 |2 |3
-----------------------------------
1 |46 |2
10 |30 |20
NULL |NULL |NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select c2+sum(c1)+c2 from t1 group by c2;
1
-----------
8
30
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select (c2+sum(c1)+c2)+10, c1, c2 from t1 group by c1, c2;
1 |C1 |C2
-----------------------------------
13 |1 |1
14 |2 |1
15 |3 |1
40 |10 |10
NULL |NULL |NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select c1+10, c2, c1*1, c1, c2*5 from t1 group by c1, c2;
1 |C2 |3 |C1 |5
-----------------------------------------------------------
11 |1 |1 |1 |5
12 |1 |2 |2 |5
13 |1 |3 |3 |5
20 |10 |10 |10 |50
NULL |NULL |NULL |NULL |NULL
ij> --------------------------------------
-- Distincts
--------------------------------------
select sum(c1) from t1;
1
-----------
16
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1) from t1;
1
-----------
16
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1), sum(c1) from t1;
1 |2
-----------------------
16 |16
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1), sum(c1) from oneRow;
1 |2
-----------------------
1 |1
ij> select max(c1), sum(distinct c1), sum(c1) from t1;
1 |2 |3
-----------------------------------
10 |16 |16
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1) from empty;
1
-----------
NULL
ij> select sum(distinct c1) from emptyNull;
1
-----------
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(c1) from t1 group by c2;
1
-----------
6
10
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1) from t1 group by c2;
1
-----------
6
10
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1), sum(c1) from t1 group by c2;
1 |2
-----------------------
6 |6
10 |10
NULL |NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1), sum(c1) from oneRow group by c2;
1 |2
-----------------------
1 |1
ij> select max(c1), sum(distinct c1), sum(c1) from t1 group by c2;
1 |2 |3
-----------------------------------
3 |6 |6
10 |10 |10
NULL |NULL |NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select c2, max(c1), c2+1, sum(distinct c1), c2+2, sum(c1) from t1 group by c2;
C2 |2 |3 |4 |5 |6
-----------------------------------------------------------------------
1 |3 |2 |6 |3 |6
10 |10 |11 |10 |12 |10
NULL |NULL |NULL |NULL |NULL |NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select sum(distinct c1) from empty group by c2;
1
-----------
ij> select sum(distinct c1) from emptyNull group by c2;
1
-----------
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> --------------------------------------
-- Subqueries in where clause
--------------------------------------
-- subqueries that might return more than 1 row
select c1 from t1 where c1 not in (select sum(c1) from t2);
C1
-----------
1
WARNING 01003: Null values were eliminated from the argument of a column function.
2
3
10
ij> select c1 from t1 where c1 not in (select sum(distinct c1) from t2);
C1
-----------
1
WARNING 01003: Null values were eliminated from the argument of a column function.
2
3
10
ij> select c1 from t1 where c1 not in (select sum(distinct c1)+10 from t2);
C1
-----------
1
WARNING 01003: Null values were eliminated from the argument of a column function.
2
3
10
ij> select c1 from t1 where c1 in (select max(c1) from t2 group by c2);
C1
-----------
3
WARNING 01003: Null values were eliminated from the argument of a column function.
10
ij> select c1 from t1 where c1 in (select max(distinct c1) from t2 group by c2);
C1
-----------
3
WARNING 01003: Null values were eliminated from the argument of a column function.
10
ij> select c1 from t1 where c1 in (select max(distinct c1)+10 from t2 group by c2);
C1
-----------
ij> -- subqueries that return 1 row
select c1 from t1 where c1 = (select max(c1) from t2);
C1
-----------
10
ij> select c1 from t1 where c1 = (select max(distinct c1) from t2);
C1
-----------
10
ij> select c1 from t1 where c1 = (select max(distinct c1)+10 from t2);
C1
-----------
ij> select c1 from t1 where c1 = (select max(c1) from oneRow group by c2);
C1
-----------
1
ij> select c1 from t1 where c1 = (select max(distinct c1) from oneRow group by c2);
C1
-----------
1
ij> select c1 from t1 where c1 = (select max(distinct c1)+10 from oneRow group by c2);
C1
-----------
ij> --------------------------------------
-- From Subqueries (aka table expressions)
--------------------------------------
select tmpC1 from
(select max(c1+10) from t1) as tmp (tmpC1);
TMPC1
-----------
20
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(tmpC1) from
(select max(c1+10) from t1) as tmp (tmpC1);
1
-----------
20
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select tmpC1 from
(select max(c1+10) from t1 group by c2) as tmp (tmpC1);
TMPC1
-----------
13
20
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(tmpC1) from
(select max(c1+10) from t1 group by c2) as tmp (tmpC1);
1
-----------
20
WARNING 01003: Null values were eliminated from the argument of a column function.
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(tmpC1), tmpC2 from
(select max(c1+10), c2 from t1 group by c2) as tmp (tmpC1, tmpC2)
group by tmpC2;
1 |TMPC2
-----------------------
13 |1
WARNING 01003: Null values were eliminated from the argument of a column function.
20 |10
NULL |NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> --------------------------------------
-- Cartesian product on from subquery: forces
-- multiple opens/closes on the sort
-- result set (bug 447)
--------------------------------------
select * from t1, (select max(c1) from t1) as mytab(c1);
C1 |C2 |C1
-----------------------------------
NULL |NULL |10
WARNING 01003: Null values were eliminated from the argument of a column function.
1 |1 |10
NULL |NULL |10
2 |1 |10
3 |1 |10
10 |10 |10
ij> select * from t1, (select max(c1) from t1 group by c1) as mytab(c1);
C1 |C2 |C1
-----------------------------------
NULL |NULL |1
1 |1 |1
NULL |NULL |1
2 |1 |1
3 |1 |1
10 |10 |1
NULL |NULL |2
1 |1 |2
NULL |NULL |2
2 |1 |2
3 |1 |2
10 |10 |2
NULL |NULL |3
1 |1 |3
NULL |NULL |3
2 |1 |3
3 |1 |3
10 |10 |3
NULL |NULL |10
1 |1 |10
NULL |NULL |10
2 |1 |10
3 |1 |10
10 |10 |10
NULL |NULL |NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
1 |1 |NULL
NULL |NULL |NULL
2 |1 |NULL
3 |1 |NULL
10 |10 |NULL
ij> --------------------------------------
-- Union
--------------------------------------
select max(c1) from t1
union all
select max(c1) from t2;
1
-----------
10
WARNING 01003: Null values were eliminated from the argument of a column function.
10
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> --------------------------------------
-- Joins
--------------------------------------
select max(t1.c1), max(t2.c2)
from t1, t2
where t1.c1 = t2.c1;
1 |2
-----------------------
10 |10
ij> select max(t1.c1), max(t2.c2)
from t1, t2
where t1.c1 = t2.c1
group by t1.c1;
1 |2
-----------------------
1 |1
2 |1
3 |1
10 |10
ij> --------------------------------------
-- Having
--------------------------------------
-- having with agg on a join
select max(t1.c1), max(t2.c2)
from t1, t2
where t1.c1 = t2.c1
group by t1.c1
having count(*) > 0;
1 |2
-----------------------
1 |1
2 |1
3 |1
10 |10
ij> -- having with subqueries and aggs, agg on grouping col
select c1 from t1
group by c1
having max(c2) in (select c1 from t2);
C1
-----------
1
2
3
10
ij> -- agg not on grouping column
select c1 from t1
group by c1
having max(c2) in (select c1 from t2);
C1
-----------
1
2
3
10
ij> -- having with a subquery that returns a single value
select c1 from t1
group by c1
having avg(c2) in (select max(t2.c1) from t2);
C1
-----------
10
ij> -- similar to above
select c1 from t1
group by c1
having (select max(t2.c1) from t2) = avg(c2);
C1
-----------
10
ij> -- various and sundry column references in the having clause
select c1 from t1
group by c1
having max(c2) > (select avg(t2.c1 + t1.c1)-20 from t2);
C1
-----------
1
WARNING 01003: Null values were eliminated from the argument of a column function.
2
WARNING 01003: Null values were eliminated from the argument of a column function.
3
WARNING 01003: Null values were eliminated from the argument of a column function.
10
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- multiple subqueries
select c1 from t1
group by c1
having (max(c2) in (select c1 from t2)) OR
(max(c1) in (select c2-999 from t2)) OR
(count(*) > 0)
;
C1
-----------
1
2
3
10
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- non-correlated subquery w/o aggregate in aggreate select list
select max(c1), (select c1 from oneRow) from t1;
1 |2
-----------------------
10 |1
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(c1), (select c1 from oneRow) from t1 group by c1;
1 |2
-----------------------
1 |1
2 |1
3 |1
10 |1
NULL |1
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> --- tests of exact numeric results
create table bd (i decimal(31,30));
0 rows inserted/updated/deleted
ij> insert into bd values(0.1);
1 row inserted/updated/deleted
ij> insert into bd values(0.2);
1 row inserted/updated/deleted
ij> select * from bd;
I
---------------------------------
0.100000000000000000000000000000
0.200000000000000000000000000000
ij> -- should be the same
select avg(i), sum(i)/count(i) from bd;
1 |2
---------------------------------------------------------------------
0.150000000000000000000000000000 |0.150000000000000000000000000000
ij> drop table bd;
0 rows inserted/updated/deleted
ij> create table it (i int);
0 rows inserted/updated/deleted
ij> insert into it values (1);
1 row inserted/updated/deleted
ij> insert into it values (0);
1 row inserted/updated/deleted
ij> insert into it values (0);
1 row inserted/updated/deleted
ij> insert into it values (0);
1 row inserted/updated/deleted
ij> insert into it values (0);
1 row inserted/updated/deleted
ij> insert into it values (0);
1 row inserted/updated/deleted
ij> insert into it values (0);
1 row inserted/updated/deleted
ij> insert into it values (0);
1 row inserted/updated/deleted
ij> insert into it values (0);
1 row inserted/updated/deleted
ij> insert into it values (0);
1 row inserted/updated/deleted
ij> insert into it values (200001);
1 row inserted/updated/deleted
ij> -- should be the same
select avg(i), sum(i)/count(i), sum(i), count(i) from it;
1 |2 |3 |4
-----------------------------------------------
18182 |18182 |200002 |11
ij> drop table it;
0 rows inserted/updated/deleted
ij> --- test avg cases where the sum will overflow
create table ovf_int (i int);
0 rows inserted/updated/deleted
ij> insert into ovf_int values (2147483647);
1 row inserted/updated/deleted
ij> insert into ovf_int values (2147483647 - 1);
1 row inserted/updated/deleted
ij> insert into ovf_int values (2147483647 - 2);
1 row inserted/updated/deleted
ij> select avg(i), 2147483647 - 1 from ovf_int;
1 |2
-----------------------
2147483646 |2147483646
ij> drop table ovf_int;
0 rows inserted/updated/deleted
ij> create table ovf_small (i smallint);
0 rows inserted/updated/deleted
ij> insert into ovf_small values (32767);
1 row inserted/updated/deleted
ij> insert into ovf_small values (32767 - 1);
1 row inserted/updated/deleted
ij> insert into ovf_small values (32767 - 2);
1 row inserted/updated/deleted
ij> select avg(i), 32767 - 1 from ovf_small;
1 |2
------------------
32766 |32766
ij> drop table ovf_small;
0 rows inserted/updated/deleted
ij> create table ovf_long (i bigint);
0 rows inserted/updated/deleted
ij> insert into ovf_long values (9223372036854775807);
1 row inserted/updated/deleted
ij> insert into ovf_long values (9223372036854775807 - 1);
1 row inserted/updated/deleted
ij> insert into ovf_long values (9223372036854775807 - 2);
1 row inserted/updated/deleted
ij> -- beetle 5571 - transient boolean type not allowed in DB2 UDB
select avg(i), 9223372036854775807 - 1 from ovf_long;
1 |2
-----------------------------------------
9223372036854775806 |9223372036854775806
ij> select avg(i), 9223372036854775807 from ovf_long;
1 |2
-----------------------------------------
9223372036854775806 |9223372036854775807
ij> -- operands are allowed in DB2 UDB
select avg(i) from ovf_long;
1
--------------------
9223372036854775806
ij> select avg(i) - 1 from ovf_long;
1
--------------------
9223372036854775805
ij> drop table ovf_long;
0 rows inserted/updated/deleted
ij> -- Test that AVG is not limited by columns type precision
-- using DB2 MAX REAL VALUES
create table ovf_real (i real);
0 rows inserted/updated/deleted
ij> insert into ovf_real values (+3.402E+38);
1 row inserted/updated/deleted
ij> insert into ovf_real values (+3.402E+38 - 1);
1 row inserted/updated/deleted
ij> insert into ovf_real values (+3.402E+38 - 2);
1 row inserted/updated/deleted
ij> select avg(i) from ovf_real;
1
---------------
3.402E38
ij> drop table ovf_real;
0 rows inserted/updated/deleted
ij> -- Test that AVG is not limited by columns type precision
-- using DB2 MAX DOUBLE VALUES
create table ovf_double (i double precision);
0 rows inserted/updated/deleted
ij> insert into ovf_double values (+1.79769E+308);
1 row inserted/updated/deleted
ij> insert into ovf_double values (+1.79769E+308 - 1);
1 row inserted/updated/deleted
ij> insert into ovf_double values (+1.79769E+308 - 2);
1 row inserted/updated/deleted
ij> select avg(i) from ovf_double;
1
------------------------
1.79769E308
ij> drop table ovf_double;
0 rows inserted/updated/deleted
ij> --------------------------------------
-- CLEAN UP
--------------------------------------
drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table oneRow;
0 rows inserted/updated/deleted
ij> drop table empty;
0 rows inserted/updated/deleted
ij> drop table emptyNull;
0 rows inserted/updated/deleted
ij> -- ** insert aggregateNegative.sql
-- For aggregates. General issues
autocommit on;
ij> create table t (i int, l bigint);
0 rows inserted/updated/deleted
ij> create table t1 (c1 int);
0 rows inserted/updated/deleted
ij> create table t2 (c1 int);
0 rows inserted/updated/deleted
ij> --------------------------------------
-- NEGATIVE TESTS
--------------------------------------
-- only a single distinct is supported
select sum(distinct i), sum(distinct l) from t;
ERROR 42Z02: Multiple DISTINCT aggregates are not supported at this time.
ij> -- parameters in aggregate
prepare p1 as 'select max(?) from t';
ERROR 42X36: The 'MAX' operator is not allowed to take a ? parameter as an operand.
ij> -- aggregates in aggregates
select max(max(i)) from t;
ERROR 42Y33: Aggregate MAX contains one or more aggregates.
ij> select max(1+1+1+max(i)) from t;
ERROR 42Y33: Aggregate MAX contains one or more aggregates.
ij> -- TEMPORARY RESTRICTION, aggregates in the select list
-- of a subquery on an aggregated result set
select max(c1), (select max(c1) from t2) from t1;
ERROR 42Y29: The SELECT list of a non-grouped query contains at least one invalid expression. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
ij> select max(c1), (select max(t1.c1) from t2) from t1;
ERROR 42Y29: The SELECT list of a non-grouped query contains at least one invalid expression. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
ij> select max(c1), max(c1), (select max(c1) from t1) from t1;
ERROR 42Y29: The SELECT list of a non-grouped query contains at least one invalid expression. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
ij> -- cursor with aggregate is not updatable
get cursor c1 as 'select max(i) from t group by i for update';
ERROR 42Y90: FOR UPDATE is not permitted in this type of statement.
ij> -- max over a join on a column with an index -- Beetle 4423
create table t3(a int);
0 rows inserted/updated/deleted
ij> insert into t3 values(1),(2),(3),(4),(5);
5 rows inserted/updated/deleted
ij> create table t4(a int);
0 rows inserted/updated/deleted
ij> insert into t4 select a from t3;
5 rows inserted/updated/deleted
ij> create index tindex on t3(a);
0 rows inserted/updated/deleted
ij> select max(t3.a)
from t3, t4
where t3.a = t4.a
and t3.a = 1;
1
-----------
1
ij> drop table t;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t4;
0 rows inserted/updated/deleted
ij> -- beetle 5122, aggregate on JoinNode
CREATE TABLE DOCUMENT_VERSION
(
DOCUMENT_ID INT,
DOCUMENT_STATUS_ID INT
)
;
0 rows inserted/updated/deleted
ij> insert into DOCUMENT_VERSION values (2,2),(9,9),(5,5),(1,3),(10,5),(1,6),(10,8),(1,10);
8 rows inserted/updated/deleted
ij> CREATE VIEW MAX_DOCUMENT_VERSION
AS SELECT DOCUMENT_ID FROM DOCUMENT_VERSION
;
0 rows inserted/updated/deleted
ij> CREATE VIEW MAX_DOCUMENT_VERSION_AND_STATUS_ID
AS SELECT MAX(DV.DOCUMENT_STATUS_ID) AS MAX_DOCUMENT_STATUS_ID
FROM DOCUMENT_VERSION AS DV , MAX_DOCUMENT_VERSION
WHERE DV.DOCUMENT_ID = 1;
0 rows inserted/updated/deleted
ij> CREATE VIEW LATEST_DOC_VERSION
AS SELECT DOCUMENT_ID
FROM DOCUMENT_VERSION AS DV, MAX_DOCUMENT_VERSION_AND_STATUS_ID AS MDVASID
WHERE DV.DOCUMENT_ID = MDVASID.MAX_DOCUMENT_STATUS_ID;
0 rows inserted/updated/deleted
ij> select * from LATEST_DOC_VERSION;
DOCUMENT_ID
-----------
10
10
ij> drop view LATEST_DOC_VERSION;
0 rows inserted/updated/deleted
ij> drop view MAX_DOCUMENT_VERSION_AND_STATUS_ID;
0 rows inserted/updated/deleted
ij> drop view MAX_DOCUMENT_VERSION;
0 rows inserted/updated/deleted
ij> drop table DOCUMENT_VERSION;
0 rows inserted/updated/deleted
ij> -- Defect 5737. Prevent aggregates being used in VALUES clause or WHERE clause.
create table tmax(i int);
0 rows inserted/updated/deleted
ij> values sum(1);
ERROR 42903: Invalid use of an aggregate function.
ij> values max(3);
ERROR 42903: Invalid use of an aggregate function.
ij> select * from tmax where sum(i)=1;
ERROR 42903: Invalid use of an aggregate function.
ij> select i from tmax where substr('abc', sum(1), 3) = 'abc';
ERROR 42903: Invalid use of an aggregate function.
ij> drop table tmax;
0 rows inserted/updated/deleted
ij>