📄 aggbuiltin.out
字号:
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> -- constantsselect 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 groupingselect 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 expressionselect 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 byselect 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 selectcreate table tmp (x int, y smallint);0 rows inserted/updated/deletedij> insert into tmp (x, y) select sum(i), sum(s) from t;1 row inserted/updated/deletedij> 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/deletedij> select * from tmp;X |Y ------------------1 |1600 1 |1500 0 |100 NULL |NULL ij> drop table tmp;0 rows inserted/updated/deletedij> -- overflowcreate table tmp (x int);0 rows inserted/updated/deletedij> insert into tmp values (2147483647), (2147483647);2 rows inserted/updated/deletedij> 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/deletedij> create table tmp (x double precision);0 rows inserted/updated/deletedij> insert into tmp values (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647), (2147483647);9 rows inserted/updated/deletedij> select sum(x) from tmp;1 ----------------------1.9327352823E10 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 max.sql-- 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> ---------------------------------------- NEGATIVE TESTS---------------------------------------- long varchar datatypes toocreate table t1 (c1 long varchar);0 rows inserted/updated/deletedij> select max(c1) from t1;ERROR 42Y22: Aggregate MAX cannot operate on type LONG VARCHAR.ij> drop table t1;0 rows inserted/updated/deletedij> ----------------------------- NULL AGGREGATION----------------------------- scalarselect 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 ----NULLij> select max(bv) from empty;1 ----------------NULL ij> select max(dc) from empty;1 --------NULL ij> -- variationsselect 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> -- vectorselect 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⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -