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. -- -- Note that bug 5704 occurs throughout this test -- Decimal results may be outside the range of valid types in Cloudscape -- ** insert avg.sql -- create an all types tables create table t (i int, s smallint, l bigint, c char(10), v varchar(50), lvc long varchar, d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, lbv long varchar for bit data, dc decimal(5,2)); 0 rows inserted/updated/deleted ij> -- empty table create table empty (i int, s smallint, l bigint, c char(10), v varchar(50), lvc long varchar, d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, lbv long varchar for bit data, dc decimal(5,2)); 0 rows inserted/updated/deleted ij> -- populate tables insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', x'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (1, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 200, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 222.22); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 2000000, 'duplicate', 'this is duplicated', 'also duplicated', date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 222.22); ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. ij> insert into t values (0, 100, 1000000, 'goodbye', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'noone is here', 'jimmie noone was here', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 100.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 100.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'ffff', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'1111111111111111', X'1234', 111.11); 1 row inserted/updated/deleted ij> -------------------------------------- -- NEGATIVE TESTS -------------------------------------- -- cannot aggregate datatypes that don't support NumberDataValue select avg(c) from t; ERROR 42Y22: Aggregate AVG cannot operate on type CHAR. ij> select avg(v) from t; ERROR 42Y22: Aggregate AVG cannot operate on type VARCHAR. ij> select avg(lvc) from t; ERROR 42Y22: Aggregate AVG cannot operate on type LONG VARCHAR. ij> select avg(dt) from t; ERROR 42Y22: Aggregate AVG cannot operate on type DATE. ij> select avg(t) from t; ERROR 42Y22: Aggregate AVG cannot operate on type TIME. ij> select avg(ts) from t; ERROR 42Y22: Aggregate AVG cannot operate on type TIMESTAMP. ij> select avg(b) from t; ERROR 42Y22: Aggregate AVG cannot operate on type CHAR () FOR BIT DATA. ij> select avg(bv) from t; ERROR 42Y22: Aggregate AVG cannot operate on type VARCHAR () FOR BIT DATA. ij> select avg(lbv) from t; ERROR 42Y22: Aggregate AVG cannot operate on type LONG VARCHAR FOR BIT DATA. ij> select avg(c) from t group by c; ERROR 42Y22: Aggregate AVG cannot operate on type CHAR. ij> select avg(v) from t group by c; ERROR 42Y22: Aggregate AVG cannot operate on type VARCHAR. ij> select avg(lvc) from t group by c; ERROR 42Y22: Aggregate AVG cannot operate on type LONG VARCHAR. ij> select avg(dt) from t group by c; ERROR 42Y22: Aggregate AVG cannot operate on type DATE. ij> select avg(t) from t group by c; ERROR 42Y22: Aggregate AVG cannot operate on type TIME. ij> select avg(ts) from t group by c; ERROR 42Y22: Aggregate AVG cannot operate on type TIMESTAMP. ij> select avg(b) from t group by c; ERROR 42Y22: Aggregate AVG cannot operate on type CHAR () FOR BIT DATA. ij> select avg(bv) from t group by c; ERROR 42Y22: Aggregate AVG cannot operate on type VARCHAR () FOR BIT DATA. ij> select avg(lbv) from t group by c; ERROR 42Y22: Aggregate AVG cannot operate on type LONG VARCHAR FOR BIT DATA. ij> -- long varchar datatypes too create table t1 (c1 long varchar); 0 rows inserted/updated/deleted ij> select avg(c1) from t1; ERROR 42Y22: Aggregate AVG cannot operate on type LONG VARCHAR. ij> drop table t1; 0 rows inserted/updated/deleted ij> -- constants select avg('hello') from t; ERROR 42Y22: Aggregate AVG cannot operate on type CHAR. ij> select avg(X'11') from t; ERROR 42Y22: Aggregate AVG cannot operate on type CHAR () FOR BIT DATA. ij> select avg(date('1999-06-06')) from t; ERROR 42Y22: Aggregate AVG cannot operate on type DATE. ij> select avg(time('12:30:30')) from t; ERROR 42Y22: Aggregate AVG cannot operate on type TIME. ij> select avg(timestamp('1999-06-06 12:30:30')) from t; ERROR 42Y22: Aggregate AVG cannot operate on type TIMESTAMP. ij> --------------------------- -- NULL AGGREGATION --------------------------- -- scalar select avg(i) from empty; 1 ----------- NULL ij> select avg(s) from empty; 1 ------ NULL ij> select avg(d) from empty; 1 ---------------------- NULL ij> select avg(l) from empty; 1 -------------------- NULL ij> select avg(r) from empty; 1 ------------- NULL ij> select avg(dc) from empty; 1 ----------- NULL ij> -- variations select avg(i), avg(s), avg(r), avg(l) from empty; 1 |2 |3 |4 ----------------------------------------------------- NULL |NULL |NULL |NULL ij> select avg(i+1) from empty; 1 ----------- NULL ij> -- vector select avg(i) from empty group by i; 1 ----------- ij> select avg(s) from empty group by s; 1 ------ ij> select avg(d) from empty group by d; 1 ---------------------- ij> select avg(l) from empty group by l; 1 -------------------- ij> select avg(r) from empty group by r; 1 ------------- ij> select avg(dc) from empty group by dc; 1 ----------- ij> -------------------------------- -- BASIC ACCEPTANCE TESTS -------------------------------- select avg(i) from t; 1 ----------- 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(s) from t; 1 ------ 107 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(d) from t; 1 ---------------------- 192.85714285714286 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(l) from t; 1 -------------------- 1000000 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(r) from t; 1 ------------- 192.85715 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(dc) from t; 1 ----------- 119.0464 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(i) from t group by i; 1 ----------- 0 1 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(s) from t group by s; 1 ------ 100 200 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(d) from t group by d; 1 ---------------------- 100.0 200.0 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(l) from t group by l; 1 -------------------- 1000000 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(r) from t group by r; 1 ------------- 100.0 200.0 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select avg(dc), sum(dc), count(dc) from t group by dc; 1 |2 |3 ----------------------------------- 111.1100 |1444.43 |13 222.2200 |222.22 |1 NULL |NULL |0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- constants select avg(1) from t; 1 ----------- 1 ij> select avg(1.1) from t; 1 --------- 1.1000 ij> select avg(1e1) from t; 1 ---------------------- 10.0 ij> select avg(1) from t group by i; 1 ----------- 1 1 1 ij> select avg(1.1) from t group by r; 1 --------- 1.1000 1.1000 1.1000 ij> select avg(1e1) from t group by r; 1 ---------------------- 10.0 10.0 10.0 ij> -- multicolumn grouping select avg(i), avg(l), avg(r) from t group by i, dt, b; 1 |2 |3 ---------------------------------------------- 0 |1000000 |190.90909 0 |1000000 |200.0 0 |1000000 |200.0 1 |1000000 |200.0 NULL |NULL |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select i, dt, avg(i), avg(r), avg(l), l from t group by i, dt, b, l; I |DT |3 |4 |5 |L ------------------------------------------------------------------------------------------ 0 |1992-01-01|0 |190.90909 |1000000 |1000000 0 |1992-01-01|0 |200.0 |1000000 |1000000 0 |1992-09-09|0 |200.0 |1000000 |1000000 1 |1992-01-01|1 |200.0 |1000000 |1000000 NULL |NULL |NULL |NULL |NULL |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- group by expression select avg(expr1), avg(expr2) from (select i * s, r * 2 from t) t (expr1, expr2) group by expr2, expr1; 1 |2 ------------------------- 0 |200.0 0 |400.0 100 |400.0 NULL |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- distinct and group by select distinct avg(i) from t group by i, dt; 1 ----------- 0 WARNING 01003: Null values were eliminated from the argument of a column function. 1 NULL ij> -- insert select create table tmp (x int, y smallint); 0 rows inserted/updated/deleted ij> insert into tmp (x, y) select avg(i), avg(s) from t; 1 row inserted/updated/deleted ij> select * from tmp; X |Y ------------------ 0 |107 ij> insert into tmp (x, y) select avg(i), avg(s) from t group by b; 3 rows inserted/updated/deleted ij> select * from tmp; X |Y ------------------ 0 |107 0 |107 0 |100 NULL |NULL ij> drop table tmp; 0 rows inserted/updated/deleted ij> -- some accuracy tests create table tmp (x int); 0 rows inserted/updated/deleted ij> insert into tmp values (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647); 22 rows inserted/updated/deleted ij> values(2147483647); 1 ----------- 2147483647 ij> select avg(x) from tmp; 1 ----------- 2147483647 ij> select avg(-(x - 1)) from tmp; 1 ----------- -2147483646 ij> select avg(x) from tmp group by x; 1 ----------- 2147483647 ij> select avg(-(x - 1)) from tmp group by x; 1 ----------- -2147483646 ij> drop table tmp; 0 rows inserted/updated/deleted ij> -- now lets try some simple averages to see what -- type of accuracy we get create table tmp(x double precision, y int); 0 rows inserted/updated/deleted ij> prepare scalar as 'select avg(x) from tmp'; ij> prepare vector as 'select avg(x) from tmp group by y'; ij> insert into tmp values (1,1); 1 row inserted/updated/deleted ij> execute scalar; 1 ---------------------- 1.0 ij> execute vector; 1 ---------------------- 1.0 ij> insert into tmp values (2,1); 1 row inserted/updated/deleted ij> execute scalar; 1 ---------------------- 1.5 ij> execute vector; 1 ---------------------- 1.5 ij> insert into tmp values (3,1); 1 row inserted/updated/deleted ij> execute scalar; 1 ---------------------- 2.0 ij> execute vector; 1 ---------------------- 2.0 ij> insert into tmp values (4,1); 1 row inserted/updated/deleted ij> execute scalar; 1 ---------------------- 2.5 ij> execute vector; 1 ---------------------- 2.5 ij> insert into tmp values (5,1); 1 row inserted/updated/deleted ij> execute scalar; 1 ---------------------- 3.0 ij> execute vector; 1 ---------------------- 3.0 ij> insert into tmp values (6,1); 1 row inserted/updated/deleted ij> execute scalar; 1 ---------------------- 3.5 ij> execute vector; 1 ---------------------- 3.5 ij> insert into tmp values (7,1); 1 row inserted/updated/deleted ij> execute scalar; 1 ---------------------- 4.0 ij> execute vector; 1 ---------------------- 4.0 ij> insert into tmp values (10000,1); 1 row inserted/updated/deleted ij> execute scalar; 1 ---------------------- 1253.5 ij> execute vector; 1 ---------------------- 1253.5 ij> remove vector; ij> remove scalar; ij> drop table tmp; 0 rows inserted/updated/deleted ij> -- drop tables drop table t; 0 rows inserted/updated/deleted ij> drop table empty; 0 rows inserted/updated/deleted ij> -- ** insert count.sql -- create an all types tables create table t (i int, s smallint, l bigint, c char(10), v varchar(50), lvc long varchar, d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, lbv long varchar for bit data, dc decimal(5,2)); 0 rows inserted/updated/deleted ij> -- empty table create table empty (i int, s smallint, l bigint, c char(10), v varchar(50), lvc long varchar, d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, lbv long varchar for bit data, dc decimal(5,2)); 0 rows inserted/updated/deleted ij> -- bit maps to Byte[], so can't test for now -- populate tables insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (1, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 200, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 222.22); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 2000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 222.22); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'goodbye', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'noone is here', 'jimmie noone was here', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 100.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 100.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'), X'12af', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'ffff', X'0000111100001111', X'1234', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'1111111111111111', X'1234', 111.11); 1 row inserted/updated/deleted ij> --------------------------- -- NULL AGGREGATION --------------------------- -- scalar select count(i) from empty; 1 ----------- 0 ij> select count(s) from empty; 1 ----------- 0 ij> select count(l) from empty; 1 ----------- 0 ij> select count(c) from empty; 1 ----------- 0 ij> select count(v) from empty; 1 ----------- 0 ij> select count(lvc) from empty; 1 ----------- 0 ij> select count(d) from empty; 1 ----------- 0 ij> select count(r) from empty; 1 ----------- 0 ij> select count(dt) from empty; 1 ----------- 0 ij> select count(t) from empty; 1 ----------- 0 ij> select count(ts) from empty; 1 ----------- 0 ij> select count(b) from empty; 1 ----------- 0 ij> select count(bv) from empty; 1 ----------- 0 ij> -- bug: should fail in db2 mode -- after for bit data is completely implemented select count(lbv) from empty; 1 ----------- 0 ij> select count(dc) from empty; 1 ----------- 0 ij> -- variations select count(i), count(b), count(i), count(s) from empty; 1 |2 |3 |4 ----------------------------------------------- 0 |0 |0 |0 ij> select count(i+1) from empty; 1 ----------- 0 ij> -- vector select count(i) from empty group by i; 1 ----------- ij> select count(s) from empty group by s; 1 ----------- ij> select count(l) from empty group by l; 1 ----------- ij> select count(c) from empty group by c; 1 ----------- ij> select count(v) from empty group by v; 1 ----------- ij> select count(d) from empty group by d; 1 ----------- ij> select count(r) from empty group by r; 1 ----------- ij> select count(dt) from empty group by dt; 1 ----------- ij> select count(t) from empty group by t; 1 ----------- ij> select count(ts) from empty group by ts; 1 ----------- ij> select count(b) from empty group by b; 1 ----------- ij> select count(bv) from empty group by bv; 1 ----------- ij> select count(lbv) from empty group by lbv; ERROR X0X67: Columns of type 'LONG VARCHAR FOR BIT DATA' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> select count(dc) from empty group by dc; 1 ----------- ij> -------------------------------- -- BASIC ACCEPTANCE TESTS -------------------------------- select count(i) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(s) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(l) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(c) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(v) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(lvc) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(d) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(r) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(dt) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(t) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(ts) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(b) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(bv) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(lbv) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(dc) from t; 1 ----------- 15 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(i) from t group by i; 1 ----------- 14 1 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(s) from t group by s; 1 ----------- 14 1 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(l) from t group by l; 1 ----------- 14 1 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(c) from t group by c; 1 ----------- 14 1 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(v) from t group by v; 1 ----------- 1 14 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(d) from t group by d; 1 ----------- 1 14 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(r) from t group by r; 1 ----------- 1 14 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(dt) from t group by dt; 1 ----------- 14 1 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(t) from t group by t; 1 ----------- 14 1 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(ts) from t group by ts; 1 ----------- 14 1 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(b) from t group by b; 1 ----------- 14 1 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(bv) from t group by bv; 1 ----------- 14 1 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(lbv) from t group by lbv; ERROR X0X67: Columns of type 'LONG VARCHAR FOR BIT DATA' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. ij> select count(dc) from t group by dc; 1 ----------- 13 2 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- constants select count(1) from t; 1 ----------- 17 ij> select count('hello') from t; 1 ----------- 17 ij> select count(1.1) from t; 1 ----------- 17 ij> select count(1e1) from t; 1 ----------- 17 ij> select count(X'11') from t; 1 ----------- 17 ij> select count(date('1999-06-06')) from t; 1 ----------- 17 ij> select count(time('12:30:30')) from t; 1 ----------- 17 ij> select count(timestamp('1999-06-06 12:30:30')) from t; 1 ----------- 17 ij> select count(1) from t group by i; 1 ----------- 14 1 2 ij> select count('hello') from t group by c; 1 ----------- 14 1 2 ij> select count(1.1) from t group by dc; 1 ----------- 13 2 2 ij> select count(1e1) from t group by r; 1 ----------- 1 14 2 ij> select count(X'11') from t group by b; 1 ----------- 14 1 2 ij> select count(date('1999-06-06')) from t group by dt; 1 ----------- 14 1 2 ij> select count(time('12:30:30')) from t group by t; 1 ----------- 14 1 2 ij> select count(timestamp('1999-06-06 12:30:30')) from t group by ts; 1 ----------- 14 1 2 ij> -- multicolumn grouping select count(i), count(dt), count(b) from t group by i, dt, b; 1 |2 |3 ----------------------------------- 12 |12 |12 1 |1 |1 1 |1 |1 1 |1 |1 0 |0 |0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select l, dt, count(i), count(dt), count(b), i from t group by i, dt, b, l; L |DT |3 |4 |5 |I ------------------------------------------------------------------------------- 1000000 |1992-01-01|11 |11 |11 |0 2000000 |1992-01-01|1 |1 |1 |0 1000000 |1992-01-01|1 |1 |1 |0 1000000 |1992-09-09|1 |1 |1 |0 1000000 |1992-01-01|1 |1 |1 |1 NULL |NULL |0 |0 |0 |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- group by expression select count(expr1), count(expr2) from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1; 1 |2 ----------------------- 1 |1 12 |12 1 |1 1 |1 0 |0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- distinct and group by select distinct count(i) from t group by i, dt; 1 ----------- 0 WARNING 01003: Null values were eliminated from the argument of a column function. 1 13 ij> -- insert select create table tmp (x int, y smallint); 0 rows inserted/updated/deleted ij> insert into tmp (x, y) select count(i), count(c) from t; 1 row inserted/updated/deleted ij> select * from tmp; X |Y ------------------ 15 |15 ij> insert into tmp (x, y) select count(i), count(c) from t group by b; 3 rows inserted/updated/deleted ij> select * from tmp; X |Y ------------------ 15 |15 14 |14 1 |1 0 |0 ij> drop table tmp; 0 rows inserted/updated/deleted ij> -- drop tables drop table t; 0 rows inserted/updated/deleted ij> drop table empty; 0 rows inserted/updated/deleted ij> -- ** insert countStar.sql -- Test the COUNT() aggregate -- create an all types tables create table t (i int, s smallint, l bigint, c char(10), v varchar(50), lvc long varchar, d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, lbv long varchar for bit data); 0 rows inserted/updated/deleted ij> -- empty table create table empty (i int, s smallint, l bigint, c char(10), v varchar(50), lvc long varchar, d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, lbv long varchar for bit data); 0 rows inserted/updated/deleted ij> -- populate tables insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (1, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 200, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 2000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'goodbye', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'noone is here', 'jimmie noone was here', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 100.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 100.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'), X'12af', X'0000111100001111', X'ABCD'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'ffff', X'0000111100001111', X'1234'); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'1111111111111111', X'ABCD'); 1 row inserted/updated/deleted ij> --------------------------- -- NULL AGGREGATION --------------------------- -- scalar select count(*) from empty; 1 ----------- 0 ij> -- variations select count(*), count(*) from empty; 1 |2 ----------------------- 0 |0 ij> -- vector select count(*) from empty group by i; 1 ----------- ij> -------------------------------- -- BASIC ACCEPTANCE TESTS -------------------------------- select count(*) from t; 1 ----------- 17 ij> select count(*) from t group by i; 1 ----------- 14 1 2 ij> -- multicolumn grouping select count(*), count(*), count(*) from t group by i, dt, b; 1 |2 |3 ----------------------------------- 12 |12 |12 1 |1 |1 1 |1 |1 1 |1 |1 2 |2 |2 ij> -- group by expression select count(*), count(*) from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1; 1 |2 ----------------------- 1 |1 12 |12 1 |1 1 |1 2 |2 ij> -- distinct and group by select distinct count(*) from t group by i, dt; 1 ----------- 1 2 13 ij> -- view create view v1 as select * from t; 0 rows inserted/updated/deleted ij> select count(*) from v1; 1 ----------- 17 ij> select count(*)+count(*) from v1; 1 ----------- 34 ij> drop view v1; 0 rows inserted/updated/deleted ij> -- insert select create table tmp (x int, y smallint); 0 rows inserted/updated/deleted ij> insert into tmp (x, y) select count(*), count(*) from t; 1 row inserted/updated/deleted ij> select * from tmp; X |Y ------------------ 17 |17 ij> insert into tmp (x, y) select count(*), count(*) from t group by b; 3 rows inserted/updated/deleted ij> select * from tmp; X |Y ------------------ 17 |17 14 |14 1 |1 2 |2 ij> drop table tmp; 0 rows inserted/updated/deleted ij> -- drop tables drop table t; 0 rows inserted/updated/deleted ij> drop table empty; 0 rows inserted/updated/deleted ij> -- ** insert sum.sql --BUGS: sum() on decimal may overflow the decimal, --w/o the type system knowing. so, given dec(1,0), --result might be dec(2,0), but return length passed --to connectivity is 1 which is wrong. if we allow --the decimal to grow beyond the preset type, we need --to all the type system to get it. alternatively, --need to cast/normalize/setWidth() the result to ensure --right type. -- create an all types tables create table t (i int, s smallint, l bigint, c char(10), v varchar(50), d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2)); 0 rows inserted/updated/deleted ij> -- empty table create table empty (i int, s smallint, l bigint, c char(10), v varchar(50), d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2)); 0 rows inserted/updated/deleted ij> -- bit maps to Byte[], so can't test for now -- populate tables insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (1, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 200, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 222.22); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 2000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 222.22); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'goodbye', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'noone is here', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 100.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 100.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'ffff', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'1111111111111111', 111.11); 1 row inserted/updated/deleted ij> -- bit maps to Byte[], so can't test for now -------------------------------------- -- NEGATIVE TESTS -------------------------------------- -- cannot aggregate datatypes that don't support NumberDataValue select sum(c) from t; ERROR 42Y22: Aggregate SUM cannot operate on type CHAR. ij> select sum(v) from t; ERROR 42Y22: Aggregate SUM cannot operate on type VARCHAR. ij> select sum(dt) from t; ERROR 42Y22: Aggregate SUM cannot operate on type DATE. ij> select sum(t) from t; ERROR 42Y22: Aggregate SUM cannot operate on type TIME. ij> select sum(ts) from t; ERROR 42Y22: Aggregate SUM cannot operate on type TIMESTAMP. ij> select sum(b) from t; ERROR 42Y22: Aggregate SUM cannot operate on type CHAR () FOR BIT DATA. ij> select sum(bv) from t; ERROR 42Y22: Aggregate SUM cannot operate on type VARCHAR () FOR BIT DATA. ij> select sum(c) from t group by c; ERROR 42Y22: Aggregate SUM cannot operate on type CHAR. ij> select sum(v) from t group by c; ERROR 42Y22: Aggregate SUM cannot operate on type VARCHAR. ij> select sum(dt) from t group by c; ERROR 42Y22: Aggregate SUM cannot operate on type DATE. ij> select sum(t) from t group by c; ERROR 42Y22: Aggregate SUM cannot operate on type TIME. ij> select sum(ts) from t group by c; ERROR 42Y22: Aggregate SUM cannot operate on type TIMESTAMP. ij> select sum(b) from t group by c; ERROR 42Y22: Aggregate SUM cannot operate on type CHAR () FOR BIT DATA. ij> select sum(bv) from t group by c; ERROR 42Y22: Aggregate SUM cannot operate on type VARCHAR () FOR BIT DATA. ij> -- long varchar datatypes too create table t1 (c1 long varchar); 0 rows inserted/updated/deleted ij> select sum(c1) from t1; ERROR 42Y22: Aggregate SUM cannot operate on type LONG VARCHAR. ij> drop table t1; 0 rows inserted/updated/deleted ij> -- constants select sum('hello') from t; ERROR 42Y22: Aggregate SUM cannot operate on type CHAR. ij> select sum(X'11') from t; ERROR 42Y22: Aggregate SUM cannot operate on type CHAR () FOR BIT DATA. ij> select sum(date('1999-06-06')) from t; ERROR 42Y22: Aggregate SUM cannot operate on type DATE. ij> select sum(time('12:30:30')) from t; ERROR 42Y22: Aggregate SUM cannot operate on type TIME. ij> select sum(timestamp('1999-06-06 12:30:30')) from t; ERROR 42Y22: Aggregate SUM cannot operate on type TIMESTAMP. ij> --------------------------- -- NULL AGGREGATION --------------------------- -- scalar select sum(i) from empty; 1 ----------- NULL ij> select sum(s) from empty; 1 ------ NULL ij> select sum(d) from empty; 1 ---------------------- NULL ij> select sum(l) from empty; 1 -------------------- NULL ij> select sum(r) from empty; 1 ------------- NULL ij> select sum(dc) from empty; 1 ----------- NULL ij> -- variations select sum(i), sum(s), sum(r), sum(l) from empty; 1 |2 |3 |4 ----------------------------------------------------- NULL |NULL |NULL |NULL ij> select sum(i+1) from empty; 1 ----------- NULL ij> -- vector select sum(i) from empty group by i; 1 ----------- ij> select sum(s) from empty group by s; 1 ------ ij> select sum(d) from empty group by d; 1 ---------------------- ij> select sum(l) from empty group by l; 1 -------------------- ij> select sum(r) from empty group by r; 1 ------------- ij> select sum(dc) from empty group by dc; 1 ----------- ij> -------------------------------- -- BASIC ACCEPTANCE TESTS -------------------------------- select sum(i) from t; 1 ----------- 1 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(s) from t; 1 ------ 1600 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(d) from t; 1 ---------------------- 2900.0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(l) from t; 1 -------------------- 16000000 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(r) from t; 1 ------------- 2900.0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(dc) from t; 1 ----------- 1888.87 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(i) from t group by i; 1 ----------- 0 1 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(s) from t group by s; 1 ------ 1400 200 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(d) from t group by d; 1 ---------------------- 100.0 2800.0 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(l) from t group by l; 1 -------------------- 14000000 2000000 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(r) from t group by r; 1 ------------- 100.0 2800.0 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select sum(dc) from t group by dc; 1 ----------- 1444.43 444.44 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- constants select sum(1) from t; 1 ----------- 17 ij> select sum(1.1) from t; 1 ------ 18.7 ij> select sum(1e1) from t; 1 ---------------------- 170.0 ij> select sum(1) from t group by i; 1 ----------- 14 1 2 ij> select sum(1.1) from t group by r; 1 ------ 1.1 15.4 2.2 ij> select sum(1e1) from t group by r; 1 ---------------------- 10.0 140.0 20.0 ij> -- multicolumn grouping select sum(i), sum(l), sum(r) from t group by i, dt, b; 1 |2 |3 ---------------------------------------------- 0 |13000000 |2300.0 0 |1000000 |200.0 0 |1000000 |200.0 1 |1000000 |200.0 NULL |NULL |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select i, dt, sum(i), sum(r), sum(l), l from t group by i, dt, b, l; I |DT |3 |4 |5 |L ------------------------------------------------------------------------------------------ 0 |1992-01-01|0 |2100.0 |11000000 |1000000 0 |1992-01-01|0 |200.0 |2000000 |2000000 0 |1992-01-01|0 |200.0 |1000000 |1000000 0 |1992-09-09|0 |200.0 |1000000 |1000000 1 |1992-01-01|1 |200.0 |1000000 |1000000 NULL |NULL |NULL |NULL |NULL |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- group by expression select sum(expr1), sum(expr2) from (select i * s, r * 2 from t) t (expr1, expr2) group by expr2, expr1; 1 |2 ------------------------- 0 |200.0 0 |5200.0 100 |400.0 NULL |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- distinct and group by select distinct sum(i) from t group by i, dt; 1 ----------- 0 WARNING 01003: Null values were eliminated from the argument of a column function. 1 NULL ij> -- insert select create table tmp (x int, y smallint); 0 rows inserted/updated/deleted ij> insert into tmp (x, y) select sum(i), sum(s) from t; 1 row inserted/updated/deleted ij> select * from tmp; X |Y ------------------ 1 |1600 ij> insert into tmp (x, y) select sum(i), sum(s) from t group by b; 3 rows inserted/updated/deleted ij> select * from tmp; X |Y ------------------ 1 |1600 1 |1500 0 |100 NULL |NULL ij> drop table tmp; 0 rows inserted/updated/deleted ij> -- overflow create table tmp (x int); 0 rows inserted/updated/deleted ij> insert into tmp values (2147483647), (2147483647); 2 rows inserted/updated/deleted ij> select sum(x) from tmp; 1 ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> drop table tmp; 0 rows inserted/updated/deleted ij> create table tmp (x double precision); 0 rows inserted/updated/deleted ij> insert into tmp values (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647); 9 rows inserted/updated/deleted ij> select sum(x) from tmp; 1 ---------------------- 1.9327352823E10 ij> drop table tmp; 0 rows inserted/updated/deleted ij> -- drop tables drop table t; 0 rows inserted/updated/deleted ij> drop table empty; 0 rows inserted/updated/deleted ij> -- ** insert max.sql -- create an all types tables create table t (i int, s smallint, l bigint, c char(10), v varchar(50), d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2)); 0 rows inserted/updated/deleted ij> -- empty table create table empty (i int, s smallint, l bigint, c char(10), v varchar(50), d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2)); 0 rows inserted/updated/deleted ij> -- bit maps to Byte[], so can't test for now -- populate tables insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (1, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 200, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 222.22); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 2000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 222.22); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'goodbye', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'noone is here', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 100.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 100.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'ffff', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'1111111111111111', 111.11); 1 row inserted/updated/deleted ij> -------------------------------------- -- NEGATIVE TESTS -------------------------------------- -- long varchar datatypes too create table t1 (c1 long varchar); 0 rows inserted/updated/deleted ij> select max(c1) from t1; ERROR 42Y22: Aggregate MAX cannot operate on type LONG VARCHAR. ij> drop table t1; 0 rows inserted/updated/deleted ij> --------------------------- -- NULL AGGREGATION --------------------------- -- scalar select max(i) from empty; 1 ----------- NULL ij> select max(s) from empty; 1 ------ NULL ij> select max(l) from empty; 1 -------------------- NULL ij> select max(c) from empty; 1 ---------- NULL ij> select max(v) from empty; 1 -------------------------------------------------- NULL ij> select max(d) from empty; 1 ---------------------- NULL ij> select max(r) from empty; 1 ------------- NULL ij> select max(dt) from empty; 1 ---------- NULL ij> select max(t) from empty; 1 -------- NULL ij> select max(ts) from empty; 1 -------------------------- NULL ij> select max(b) from empty; 1 ---- NULL ij> select max(bv) from empty; 1 ---------------- NULL ij> select max(dc) from empty; 1 ------- NULL ij> -- variations select max(i), max(b), max(i), max(s) from empty; 1 |2 |3 |4 ----------------------------------- NULL |NULL|NULL |NULL ij> select max(i+1) from empty; 1 ----------- NULL ij> -- vector select max(i) from empty group by i; 1 ----------- ij> select max(s) from empty group by s; 1 ------ ij> select max(l) from empty group by l; 1 -------------------- ij> select max(c) from empty group by c; 1 ---------- ij> select max(v) from empty group by v; 1 -------------------------------------------------- ij> select max(d) from empty group by d; 1 ---------------------- ij> select max(r) from empty group by r; 1 ------------- ij> select max(dt) from empty group by dt; 1 ---------- ij> select max(t) from empty group by t; 1 -------- ij> select max(ts) from empty group by ts; 1 -------------------------- ij> select max(b) from empty group by b; 1 ---- ij> select max(bv) from empty group by bv; 1 ---------------- ij> select max(dc) from empty group by dc; 1 ------- ij> -------------------------------- -- BASIC ACCEPTANCE TESTS -------------------------------- select max(i) from t; 1 ----------- 1 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(s) from t; 1 ------ 200 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(l) from t; 1 -------------------- 2000000 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(c) from t; 1 ---------- goodbye WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(v) from t; 1 -------------------------------------------------- this is duplicated WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(d) from t; 1 ---------------------- 200.0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(r) from t; 1 ------------- 200.0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(dt) from t; 1 ---------- 1992-09-09 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(t) from t; 1 -------- 12:55:55 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(ts) from t; 1 -------------------------- 1992-01-01 12:55:55.0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(b) from t; 1 ---- ffff WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(bv) from t; 1 ---------------- 1111111111111111 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(dc) from t; 1 ------- 222.22 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(i) from t group by i; 1 ----------- 0 1 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(s) from t group by s; 1 ------ 100 200 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(l) from t group by l; 1 -------------------- 1000000 2000000 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(c) from t group by c; 1 ---------- duplicate goodbye NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(v) from t group by v; 1 -------------------------------------------------- noone is here this is duplicated NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(d) from t group by d; 1 ---------------------- 100.0 200.0 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(r) from t group by r; 1 ------------- 100.0 200.0 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(dt) from t group by dt; 1 ---------- 1992-01-01 1992-09-09 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(t) from t group by t; 1 -------- 12:30:30 12:55:55 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(ts) from t group by ts; 1 -------------------------- 1992-01-01 12:30:30.0 1992-01-01 12:55:55.0 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(b) from t group by b; 1 ---- 12af ffff NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(bv) from t group by bv; 1 ---------------- 0000111100001111 1111111111111111 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(dc) from t group by dc; 1 ------- 111.11 222.22 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- constants select max(1) from t; 1 ----------- 1 ij> select max('hello') from t; 1 ----- hello ij> select max(1.1) from t; 1 ---- 1.1 ij> select max(1e1) from t; 1 ---------------------- 10.0 ij> select max(X'11') from t; 1 ---- 11 ij> select max(date('1999-06-06')) from t; 1 ---------- 1999-06-06 ij> select max(time('12:30:30')) from t; 1 -------- 12:30:30 ij> select max(timestamp('1999-06-06 12:30:30')) from t; 1 -------------------------- 1999-06-06 12:30:30.0 ij> select max(1) from t group by i; 1 ----------- 1 1 1 ij> select max('hello') from t group by c; 1 ----- hello hello hello ij> select max(1.1) from t group by dc; 1 ---- 1.1 1.1 1.1 ij> select max(1e1) from t group by d; 1 ---------------------- 10.0 10.0 10.0 ij> select max(X'11') from t group by b; 1 ---- 11 11 11 ij> select max(date('1999-06-06')) from t group by dt; 1 ---------- 1999-06-06 1999-06-06 1999-06-06 ij> select max(time('12:30:30')) from t group by t; 1 -------- 12:30:30 12:30:30 12:30:30 ij> select max(timestamp('1999-06-06 12:30:30')) from t group by ts; 1 -------------------------- 1999-06-06 12:30:30.0 1999-06-06 12:30:30.0 1999-06-06 12:30:30.0 ij> -- multicolumn grouping select max(i), max(dt), max(b) from t group by i, dt, b; 1 |2 |3 --------------------------- 0 |1992-01-01|12af 0 |1992-01-01|ffff 0 |1992-09-09|12af 1 |1992-01-01|12af NULL |NULL |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select l, dt, max(i), max(dt), max(b), i from t group by i, dt, b, l; L |DT |3 |4 |5 |I ----------------------------------------------------------------------- 1000000 |1992-01-01|0 |1992-01-01|12af|0 2000000 |1992-01-01|0 |1992-01-01|12af|0 1000000 |1992-01-01|0 |1992-01-01|ffff|0 1000000 |1992-09-09|0 |1992-09-09|12af|0 1000000 |1992-01-01|1 |1992-01-01|12af|1 NULL |NULL |NULL |NULL |NULL|NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- group by expression select max(expr1), max(expr2) from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1; 1 |2 ------------------------------------------------------------------------ 0 |duplicate noone is here 0 |duplicate this is duplicated 100 |duplicate this is duplicated 0 |goodbye this is duplicated NULL |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- distinct and group by select distinct max(i) from t group by i, dt; 1 ----------- 0 WARNING 01003: Null values were eliminated from the argument of a column function. 1 NULL ij> -- insert select create table tmp (x int, y char(20)); 0 rows inserted/updated/deleted ij> insert into tmp (x, y) select max(i), max(c) from t; 1 row inserted/updated/deleted ij> select * from tmp; X |Y -------------------------------- 1 |goodbye ij> insert into tmp (x, y) select max(i), max(c) from t group by b; 3 rows inserted/updated/deleted ij> select * from tmp; X |Y -------------------------------- 1 |goodbye 1 |goodbye 0 |duplicate NULL |NULL ij> drop table tmp; 0 rows inserted/updated/deleted ij> -- drop tables drop table t; 0 rows inserted/updated/deleted ij> drop table empty; 0 rows inserted/updated/deleted ij> -- ** insert min.sql -- create an all types tables create table t (i int, s smallint, l bigint, c char(10), v varchar(50), d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2)); 0 rows inserted/updated/deleted ij> -- empty table create table empty (i int, s smallint, l bigint, c char(10), v varchar(50), d double precision, r real, dt date, t time, ts timestamp, b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2)); 0 rows inserted/updated/deleted ij> -- populate tables insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (1, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 200, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 222.22); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 2000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 222.22); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'goodbye', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'noone is here', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 100.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 100.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'), X'12af', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'ffff', X'0000111100001111', 111.11); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'), X'12af', X'1111111111111111', 111.11); 1 row inserted/updated/deleted ij> -------------------------------------- -- NEGATIVE TESTS -------------------------------------- -- long varchar datatypes too create table t1 (c1 long varchar); 0 rows inserted/updated/deleted ij> select min(c1) from t1; ERROR 42Y22: Aggregate MIN cannot operate on type LONG VARCHAR. ij> drop table t1; 0 rows inserted/updated/deleted ij> --------------------------- -- NULL AGGREGATION --------------------------- -- scalar select min(i) from empty; 1 ----------- NULL ij> select min(s) from empty; 1 ------ NULL ij> select min(l) from empty; 1 -------------------- NULL ij> select min(c) from empty; 1 ---------- NULL ij> select min(v) from empty; 1 -------------------------------------------------- NULL ij> select min(d) from empty; 1 ---------------------- NULL ij> select min(r) from empty; 1 ------------- NULL ij> select min(dt) from empty; 1 ---------- NULL ij> select min(t) from empty; 1 -------- NULL ij> select min(ts) from empty; 1 -------------------------- NULL ij> select min(b) from empty; 1 ---- NULL ij> select min(bv) from empty; 1 ---------------- NULL ij> select min(dc) from empty; 1 ------- NULL ij> -- variations select min(i), min(b), min(i), min(s) from empty; 1 |2 |3 |4 ----------------------------------- NULL |NULL|NULL |NULL ij> select min(i+1) from empty; 1 ----------- NULL ij> -- vector select min(i) from empty group by i; 1 ----------- ij> select min(s) from empty group by s; 1 ------ ij> select min(l) from empty group by l; 1 -------------------- ij> select min(c) from empty group by c; 1 ---------- ij> select min(v) from empty group by v; 1 -------------------------------------------------- ij> select min(d) from empty group by d; 1 ---------------------- ij> select min(r) from empty group by r; 1 ------------- ij> select min(dt) from empty group by dt; 1 ---------- ij> select min(t) from empty group by t; 1 -------- ij> select min(ts) from empty group by ts; 1 -------------------------- ij> select min(b) from empty group by b; 1 ---- ij> select min(bv) from empty group by bv; 1 ---------------- ij> select min(dc) from empty group by dc; 1 ------- ij> -------------------------------- -- BASIC ACCEPTANCE TESTS -------------------------------- select min(i) from t; 1 ----------- 0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(s) from t; 1 ------ 100 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(l) from t; 1 -------------------- 1000000 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(c) from t; 1 ---------- duplicate WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(v) from t; 1 -------------------------------------------------- noone is here WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(d) from t; 1 ---------------------- 100.0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(r) from t; 1 ------------- 100.0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(dt) from t; 1 ---------- 1992-01-01 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(t) from t; 1 -------- 12:30:30 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(ts) from t; 1 -------------------------- 1992-01-01 12:30:30.0 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(b) from t; 1 ---- 12af WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(bv) from t; 1 ---------------- 0000111100001111 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(dc) from t; 1 ------- 111.11 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(i) from t group by i; 1 ----------- 0 1 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(s) from t group by s; 1 ------ 100 200 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(l) from t group by l; 1 -------------------- 1000000 2000000 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(c) from t group by c; 1 ---------- duplicate goodbye NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(v) from t group by v; 1 -------------------------------------------------- noone is here this is duplicated NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(d) from t group by d; 1 ---------------------- 100.0 200.0 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(r) from t group by r; 1 ------------- 100.0 200.0 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(dt) from t group by dt; 1 ---------- 1992-01-01 1992-09-09 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(t) from t group by t; 1 -------- 12:30:30 12:55:55 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(ts) from t group by ts; 1 -------------------------- 1992-01-01 12:30:30.0 1992-01-01 12:55:55.0 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(b) from t group by b; 1 ---- 12af ffff NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(bv) from t group by bv; 1 ---------------- 0000111100001111 1111111111111111 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(dc) from t group by dc; 1 ------- 111.11 222.22 NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- constants select min(1) from t; 1 ----------- 1 ij> select min('hello') from t; 1 ----- hello ij> select min(1.1) from t; 1 ---- 1.1 ij> select min(1e1) from t; 1 ---------------------- 10.0 ij> select min(X'11') from t; 1 ---- 11 ij> select min(date('1999-06-06')) from t; 1 ---------- 1999-06-06 ij> select min(time('12:30:30')) from t; 1 -------- 12:30:30 ij> select min(timestamp('1999-06-06 12:30:30')) from t; 1 -------------------------- 1999-06-06 12:30:30.0 ij> select min(1) from t group by i; 1 ----------- 1 1 1 ij> select min('hello') from t group by c; 1 ----- hello hello hello ij> select min(1.1) from t group by dc; 1 ---- 1.1 1.1 1.1 ij> select min(1e1) from t group by d; 1 ---------------------- 10.0 10.0 10.0 ij> select min(X'11') from t group by b; 1 ---- 11 11 11 ij> select min(date('1999-06-06')) from t group by dt; 1 ---------- 1999-06-06 1999-06-06 1999-06-06 ij> select min(time('12:30:30')) from t group by t; 1 -------- 12:30:30 12:30:30 12:30:30 ij> select min(timestamp('1999-06-06 12:30:30')) from t group by ts; 1 -------------------------- 1999-06-06 12:30:30.0 1999-06-06 12:30:30.0 1999-06-06 12:30:30.0 ij> -- multicolumn grouping select min(i), min(dt), min(b) from t group by i, dt, b; 1 |2 |3 --------------------------- 0 |1992-01-01|12af 0 |1992-01-01|ffff 0 |1992-09-09|12af 1 |1992-01-01|12af NULL |NULL |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> select l, dt, min(i), min(dt), min(b), i from t group by i, dt, b, l; L |DT |3 |4 |5 |I ----------------------------------------------------------------------- 1000000 |1992-01-01|0 |1992-01-01|12af|0 2000000 |1992-01-01|0 |1992-01-01|12af|0 1000000 |1992-01-01|0 |1992-01-01|ffff|0 1000000 |1992-09-09|0 |1992-09-09|12af|0 1000000 |1992-01-01|1 |1992-01-01|12af|1 NULL |NULL |NULL |NULL |NULL|NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- group by expression select min(expr1), min(expr2) from (select i * s, c || v from t) t (expr1, expr2) group by expr2, expr1; 1 |2 ------------------------------------------------------------------------ 0 |duplicate noone is here 0 |duplicate this is duplicated 100 |duplicate this is duplicated 0 |goodbye this is duplicated NULL |NULL WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- distinct and group by select distinct min(i) from t group by i, dt; 1 ----------- 0 WARNING 01003: Null values were eliminated from the argument of a column function. 1 NULL ij> -- insert select create table tmp (x int, y char(20)); 0 rows inserted/updated/deleted ij> insert into tmp (x, y) select min(i), min(c) from t; 1 row inserted/updated/deleted ij> select * from tmp; X |Y -------------------------------- 0 |duplicate ij> insert into tmp (x, y) select min(i), min(c) from t group by b; 3 rows inserted/updated/deleted ij> select * from tmp; X |Y -------------------------------- 0 |duplicate 0 |duplicate 0 |duplicate NULL |NULL ij> drop table tmp; 0 rows inserted/updated/deleted ij> -- drop tables drop table t; 0 rows inserted/updated/deleted ij> drop table empty; 0 rows inserted/updated/deleted ij>