📄 aggbuiltin.out
字号:
1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'ABCD');1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'ABCD');1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'ABCD');1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'ABCD');1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'ffff', X'0000111100001111', X'1234');1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'1111111111111111', X'ABCD');1 row inserted/updated/deletedij> ----------------------------- NULL AGGREGATION----------------------------- scalarselect count(*) from empty;1 -----------0 ij> -- variationsselect count(*), count(*) from empty;1 |2 -----------------------0 |0 ij> -- vectorselect 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 groupingselect 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 expressionselect 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 byselect distinct count(*) from t group by i, dt;1 -----------1 2 13 ij> -- viewcreate view v1 as select * from t;0 rows inserted/updated/deletedij> select count(*) from v1;1 -----------17 ij> select count(*)+count(*) from v1;1 -----------34 ij> drop view v1;0 rows inserted/updated/deletedij> -- insert select create table tmp (x int, y smallint);0 rows inserted/updated/deletedij> insert into tmp (x, y) select count(*), count(*) from t;1 row inserted/updated/deletedij> 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/deletedij> select * from tmp;X |Y ------------------17 |17 14 |14 1 |1 2 |2 ij> drop table tmp;0 rows inserted/updated/deletedij> -- drop tablesdrop table t;0 rows inserted/updated/deletedij> drop table empty;0 rows inserted/updated/deletedij> -- ** 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 tablescreate 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/deletedij> -- empty tablecreate 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/deletedij> -- bit maps to Byte[], so can't test for now-- populate tablesinsert into t (i) values (null);1 row inserted/updated/deletedij> insert into t (i) values (null);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (1, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 200, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 222.22);1 row inserted/updated/deletedij> insert into t values (0, 100, 2000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 222.22);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'goodbye', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'noone is here', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 100.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 100.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'ffff', X'0000111100001111', 111.11);1 row inserted/updated/deletedij> insert into t values (0, 100, 1000000, 'duplicate', 'this is duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'1111111111111111', 111.11);1 row inserted/updated/deletedij> -- bit maps to Byte[], so can't test for now---------------------------------------- NEGATIVE TESTS---------------------------------------- cannot aggregate datatypes that don't support NumberDataValueselect 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 toocreate table t1 (c1 long varchar);0 rows inserted/updated/deletedij> select sum(c1) from t1;ERROR 42Y22: Aggregate SUM cannot operate on type LONG VARCHAR.ij> drop table t1;0 rows inserted/updated/deletedij> -- constantsselect 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('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t;ERROR 42Y22: Aggregate SUM cannot operate on type TIMESTAMP.ij> ----------------------------- NULL AGGREGATION----------------------------- scalarselect 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> -- variationsselect 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> -- vectorselect 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 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -