📄 aggregate.out
字号:
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 t1union allselect 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, t2where t1.c1 = t2.c1;1 |2 -----------------------10 |10 ij> select max(t1.c1), max(t2.c2) from t1, t2where t1.c1 = t2.c1group by t1.c1;1 |2 -----------------------1 |1 2 |1 3 |1 10 |10 ij> ---------------------------------------- Having---------------------------------------- having with agg on a joinselect max(t1.c1), max(t2.c2) from t1, t2where t1.c1 = t2.c1group by t1.c1having count(*) > 0;1 |2 -----------------------1 |1 2 |1 3 |1 10 |10 ij> -- having with subqueries and aggs, agg on grouping colselect c1 from t1group by c1having max(c2) in (select c1 from t2);C1 -----------1 2 3 10 ij> -- agg not on grouping columnselect c1 from t1group by c1having max(c2) in (select c1 from t2);C1 -----------1 2 3 10 ij> -- having with a subquery that returns a single valueselect c1 from t1group by c1having avg(c2) in (select max(t2.c1) from t2);C1 -----------10 ij> -- similar to aboveselect c1 from t1group by c1having (select max(t2.c1) from t2) = avg(c2);C1 -----------10 ij> -- various and sundry column references in the having clauseselect c1 from t1group by c1having 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 subqueriesselect c1 from t1group by c1having (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 listselect 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 resultscreate table bd (i decimal(31,30));0 rows inserted/updated/deletedij> insert into bd values(0.1);1 row inserted/updated/deletedij> insert into bd values(0.2);1 row inserted/updated/deletedij> select * from bd;I ----------------------------------0.100000000000000000000000000000 0.200000000000000000000000000000 ij> -- should be the sameselect avg(i), sum(i)/count(i) from bd;1 |2 ---------------------------------------------------------------------0.150000000000000000000000000000 |0.150000000000000000000000000000 ij> drop table bd;0 rows inserted/updated/deletedij> create table it (i int);0 rows inserted/updated/deletedij> insert into it values (1);1 row inserted/updated/deletedij> insert into it values (0);1 row inserted/updated/deletedij> insert into it values (0);1 row inserted/updated/deletedij> insert into it values (0);1 row inserted/updated/deletedij> insert into it values (0);1 row inserted/updated/deletedij> insert into it values (0);1 row inserted/updated/deletedij> insert into it values (0);1 row inserted/updated/deletedij> insert into it values (0);1 row inserted/updated/deletedij> insert into it values (0);1 row inserted/updated/deletedij> insert into it values (0);1 row inserted/updated/deletedij> insert into it values (200001);1 row inserted/updated/deletedij> -- should be the sameselect 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/deletedij> --- test avg cases where the sum will overflowcreate table ovf_int (i int);0 rows inserted/updated/deletedij> insert into ovf_int values (2147483647);1 row inserted/updated/deletedij> insert into ovf_int values (2147483647 - 1);1 row inserted/updated/deletedij> insert into ovf_int values (2147483647 - 2);1 row inserted/updated/deletedij> select avg(i), 2147483647 - 1 from ovf_int;1 |2 -----------------------2147483646 |2147483646 ij> drop table ovf_int;0 rows inserted/updated/deletedij> create table ovf_small (i smallint);0 rows inserted/updated/deletedij> insert into ovf_small values (32767);1 row inserted/updated/deletedij> insert into ovf_small values (32767 - 1);1 row inserted/updated/deletedij> insert into ovf_small values (32767 - 2);1 row inserted/updated/deletedij> select avg(i), 32767 - 1 from ovf_small;1 |2 ------------------32766 |32766 ij> drop table ovf_small;0 rows inserted/updated/deletedij> create table ovf_long (i bigint);0 rows inserted/updated/deletedij> insert into ovf_long values (9223372036854775807);1 row inserted/updated/deletedij> insert into ovf_long values (9223372036854775807 - 1);1 row inserted/updated/deletedij> insert into ovf_long values (9223372036854775807 - 2);1 row inserted/updated/deletedij> -- beetle 5571 - transient boolean type not allowed in DB2 UDBselect 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 UDBselect 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/deletedij> -- Test that AVG is not limited by columns type precision-- using DB2 MAX REAL VALUEScreate table ovf_real (i real);0 rows inserted/updated/deletedij> insert into ovf_real values (+3.402E+38);1 row inserted/updated/deletedij> insert into ovf_real values (+3.402E+38 - 1);1 row inserted/updated/deletedij> insert into ovf_real values (+3.402E+38 - 2);1 row inserted/updated/deletedij> select avg(i) from ovf_real;1 -------------3.402E38 ij> drop table ovf_real;0 rows inserted/updated/deletedij> -- Test that AVG is not limited by columns type precision-- using DB2 MAX DOUBLE VALUEScreate table ovf_double (i double precision);0 rows inserted/updated/deletedij> insert into ovf_double values (+1.79769E+308);1 row inserted/updated/deletedij> insert into ovf_double values (+1.79769E+308 - 1);1 row inserted/updated/deletedij> insert into ovf_double values (+1.79769E+308 - 2);1 row inserted/updated/deletedij> select avg(i) from ovf_double;1 ----------------------1.79769E308 ij> drop table ovf_double;0 rows inserted/updated/deletedij> ---------------------------------------- CLEAN UP--------------------------------------drop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table oneRow;0 rows inserted/updated/deletedij> drop table empty;0 rows inserted/updated/deletedij> drop table emptyNull;0 rows inserted/updated/deletedij> -- ** insert aggregateNegative.sql-- For aggregates. General issuesautocommit on;ij> create table t (i int, l bigint);0 rows inserted/updated/deletedij> create table t1 (c1 int);0 rows inserted/updated/deletedij> create table t2 (c1 int);0 rows inserted/updated/deletedij> ---------------------------------------- NEGATIVE TESTS---------------------------------------- only a single distinct is supportedselect sum(distinct i), sum(distinct l) from t;ERROR 42Z02: Multiple DISTINCT aggregates are not supported at this time.ij> -- parameters in aggregateprepare p1 as 'select max(?) from t';ERROR 42X36: The 'MAX' operator is not allowed to take a ? parameter as an operand.ij> -- aggregates in aggregatesselect 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 setselect 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 updatableget 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 4423create table t3(a int);0 rows inserted/updated/deletedij> insert into t3 values(1),(2),(3),(4),(5);5 rows inserted/updated/deletedij> create table t4(a int);0 rows inserted/updated/deletedij> insert into t4 select a from t3;5 rows inserted/updated/deletedij> create index tindex on t3(a);0 rows inserted/updated/deletedij> select max(t3.a)from t3, t4where t3.a = t4.aand t3.a = 1;1 -----------1 ij> drop table t;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t3;0 rows inserted/updated/deletedij> drop table t4;0 rows inserted/updated/deletedij> -- beetle 5122, aggregate on JoinNodeCREATE TABLE DOCUMENT_VERSION ( DOCUMENT_ID INT, DOCUMENT_STATUS_ID INT );0 rows inserted/updated/deletedij> 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/deletedij> CREATE VIEW MAX_DOCUMENT_VERSION AS SELECT DOCUMENT_ID FROM DOCUMENT_VERSION;0 rows inserted/updated/deletedij> 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/deletedij> 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/deletedij> select * from LATEST_DOC_VERSION;DOCUMENT_ID-----------10 10 ij> drop view LATEST_DOC_VERSION;0 rows inserted/updated/deletedij> drop view MAX_DOCUMENT_VERSION_AND_STATUS_ID;0 rows inserted/updated/deletedij> drop view MAX_DOCUMENT_VERSION;0 rows inserted/updated/deletedij> drop table DOCUMENT_VERSION;0 rows inserted/updated/deletedij> -- Defect 5737. Prevent aggregates being used in VALUES clause or WHERE clause.create table tmax(i int);0 rows inserted/updated/deletedij> 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/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -