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; ERROR 42X01: Syntax error: Encountered "in" at line 2, column 11. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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>