📄 aggbuiltin.out
字号:
0 |107 0 |100 NULL |NULL ij> drop table tmp;0 rows inserted/updated/deletedij> -- some accuracy testscreate table tmp (x int);0 rows inserted/updated/deletedij> 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/deletedij> values(2147483647);1 -----------2147483647 ij> select avg(x) from tmp;1 -----------2147483647 ij> select avg(-(x - 1)) from tmp;1 ------------2147483646ij> select avg(x) from tmp group by x;1 -----------2147483647 ij> select avg(-(x - 1)) from tmp group by x;1 ------------2147483646ij> drop table tmp;0 rows inserted/updated/deletedij> -- now lets try some simple averages to see what-- type of accuracy we getcreate table tmp(x double precision, y int);0 rows inserted/updated/deletedij> 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/deletedij> execute scalar;1 ----------------------1.0 ij> execute vector;1 ----------------------1.0 ij> insert into tmp values (2,1);1 row inserted/updated/deletedij> execute scalar;1 ----------------------1.5 ij> execute vector;1 ----------------------1.5 ij> insert into tmp values (3,1);1 row inserted/updated/deletedij> execute scalar;1 ----------------------2.0 ij> execute vector;1 ----------------------2.0 ij> insert into tmp values (4,1);1 row inserted/updated/deletedij> execute scalar;1 ----------------------2.5 ij> execute vector;1 ----------------------2.5 ij> insert into tmp values (5,1);1 row inserted/updated/deletedij> execute scalar;1 ----------------------3.0 ij> execute vector;1 ----------------------3.0 ij> insert into tmp values (6,1);1 row inserted/updated/deletedij> execute scalar;1 ----------------------3.5 ij> execute vector;1 ----------------------3.5 ij> insert into tmp values (7,1);1 row inserted/updated/deletedij> execute scalar;1 ----------------------4.0 ij> execute vector;1 ----------------------4.0 ij> insert into tmp values (10000,1);1 row inserted/updated/deletedij> 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/deletedij> -- drop tablesdrop table t;0 rows inserted/updated/deletedij> drop table empty;0 rows inserted/updated/deletedij> -- ** insert count.sql-- create an all types tablescreate 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/deletedij> -- empty tablecreate 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/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', 'also duplicated', 200.0e0, 200.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'1234', 111.11);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'1234', 111.11);1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'1234', 111.11);1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'1234', 222.22);1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'1234', 222.22);1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'1234', 111.11);1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'1234', 111.11);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'1234', 111.11);1 row inserted/updated/deletedij> 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('xxxxxxFILTERED-TIMESTAMPxxxxx), X'12af', X'0000111100001111', X'1234', 111.11);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'1234', 111.11);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'1234', 111.11);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'1234', 111.11);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'1234', 111.11);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', 111.11);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'1234', 111.11);1 row inserted/updated/deletedij> ----------------------------- NULL AGGREGATION----------------------------- scalarselect 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 implementedselect count(lbv) from empty;1 -----------0 ij> select count(dc) from empty;1 -----------0 ij> -- variationsselect 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> -- vectorselect 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 -----------⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -