github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_var_pop.result (about) 1 SELECT var_pop(null); 2 var_pop(null) 3 null 4 create table t1(a tinyint, b SMALLINT, c BIGINT, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,10), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 5 insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf"); 6 insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf"); 7 insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f"); 8 insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf"); 9 select var_pop(a) from t1; 10 var_pop(a) 11 918.75 12 select var_pop(b) from t1; 13 var_pop(b) 14 918.75 15 select var_pop(c) from t1; 16 var_pop(c) 17 67.6875 18 select var_pop(d) from t1; 19 var_pop(d) 20 217.6875 21 select var_pop(e) from t1; 22 var_pop(e) 23 450.1875 24 select var_pop(f) from t1; 25 var_pop(f) 26 420.75 27 select var_pop(g) from t1; 28 var_pop(g) 29 32502.248712250002 30 select var_pop(h) from t1; 31 var_pop(h) 32 2254.078700000000 33 select var_pop(i) from t1; 34 invalid argument aggregate function var_pop, bad value [DATE] 35 select var_pop(k) from t1; 36 invalid argument aggregate function var_pop, bad value [DATETIME] 37 select var_pop(l) from t1; 38 invalid argument aggregate function var_pop, bad value [TIMESTAMP] 39 select var_pop(m) from t1; 40 invalid argument aggregate function var_pop, bad value [CHAR] 41 select var_pop(n) from t1; 42 invalid argument aggregate function var_pop, bad value [VARCHAR] 43 drop table t1; 44 select var_pop(99999999999999999.99999); 45 var_pop(99999999999999999.99999) 46 0E-12 47 select var_pop(999999999999999933193939.99999); 48 var_pop(999999999999999933193939.99999) 49 0E-12 50 select var_pop(9999999999999999999999999999999999.9999999999999); 51 var_pop(9999999999999999999999999999999999.9999999999999) 52 0E-12 53 select var_pop(-99999999999999999.99999); 54 var_pop(-99999999999999999.99999) 55 0E-12 56 select var_pop(-999999999999999933193939.99999); 57 var_pop(-999999999999999933193939.99999) 58 0E-12 59 select var_pop(-9999999999999999999999999999999999.9999999999999); 60 var_pop(-9999999999999999999999999999999999.9999999999999) 61 0E-12 62 create table t1(a bigint); 63 select var_pop(a) from t1; 64 var_pop(a) 65 null 66 insert into t1 values(null),(null),(null),(null); 67 select var_pop(a) from t1; 68 var_pop(a) 69 null 70 insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515); 71 select var_pop(a) from t1; 72 var_pop(a) 73 3.3332822532651034E27 74 drop table t1; 75 create table t1 ( a int not null default 1, big bigint ); 76 insert into t1 (big) values (-1),(1234567890167),(92233720368547),(18446744073709515); 77 select * from t1; 78 a big 79 1 -1 80 1 1234567890167 81 1 92233720368547 82 1 18446744073709515 83 select distinct var_pop(big),max(big),var_pop(big)-1 from t1; 84 var_pop(big) max(big) var_pop(big) - 1 85 6.358900172438704E31 18446744073709515 6.358900172438704E31 86 select var_pop(big),max(big),var_pop(big)-1 from t1 group by a; 87 var_pop(big) max(big) var_pop(big) - 1 88 6.358900172438704E31 18446744073709515 6.358900172438704E31 89 insert into t1 (big) values (184467440737615); 90 select * from t1; 91 a big 92 1 -1 93 1 1234567890167 94 1 92233720368547 95 1 18446744073709515 96 1 184467440737615 97 select var_pop(big),max(big),var_pop(big)-1 from t1; 98 var_pop(big) max(big) var_pop(big) - 1 99 5.404043539963766E31 18446744073709515 5.404043539963766E31 100 select var_pop(big),max(big),var_pop(big)-1 from t1 group by a; 101 var_pop(big) max(big) var_pop(big) - 1 102 5.404043539963766E31 18446744073709515 5.404043539963766E31 103 drop table t1; 104 CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL); 105 INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50); 106 select Fld1, var_pop(Fld2) as q from t1 group by Fld1 having q is not null; 107 fld1 q 108 1 25.0 109 3 0.0 110 select Fld1, var_pop(Fld2) from t1 group by Fld1 having var_pop(Fld2) is not null; 111 fld1 var_pop(fld2) 112 1 25.0 113 3 0.0 114 select Fld1, var_pop(Fld2) from t1 group by Fld1 having avg(Fld2) is not null; 115 fld1 var_pop(fld2) 116 1 25.0 117 3 0.0 118 select Fld1, var_pop(Fld2) from t1 group by Fld1 having var_pop(Fld2) is not null; 119 fld1 var_pop(fld2) 120 1 25.0 121 3 0.0 122 drop table t1; 123 SELECT var_pop(1)<var_pop(2); 124 var_pop(1) < var_pop(2) 125 false 126 CREATE TABLE t1(i INT); 127 INSERT INTO t1 VALUES (NULL),(1); 128 SELECT var_pop(i)+0 as splus0, i+0 as plain FROM t1 GROUP BY i ; 129 splus0 plain 130 null null 131 0.0 1 132 DROP TABLE t1; 133 drop database if exists test; 134 create database test; 135 use test; 136 drop table if exists decimal01; 137 create table decimal01(col1 decimal(10,1)); 138 insert into decimal01 values(1234124.2134324321); 139 insert into decimal01 values(23413432.32423); 140 insert into decimal01 values(0); 141 insert into decimal01 values(-13421.34234); 142 select var_pop(col1) from decimal01; 143 var_pop(col1) 144 99500475119008.215000000000 145 drop table decimal01; 146 drop table if exists decimal02; 147 create table decimal02(col1 decimal(38,0)); 148 insert into decimal02 values(12312312312314565789874569874512456879); 149 insert into decimal02 values(99999999999999999999999999999999999999); 150 insert into decimal02 values(10000000000000000000000000000000000000); 151 insert into decimal02 values(32482438247328742831193201993219039203); 152 insert into decimal02 values(99999999999999999999999999999999999999.73289342); 153 insert into decimal02 values(-298432432433242412313123321231321313); 154 insert into decimal02 values(-34243214324324342321313321321342342343.123143); 155 insert into decimal02 values(-0.12813218391321939921391929391293912); 156 select * from decimal02; 157 col1 158 12312312312314565789874569874512456879 159 99999999999999999999999999999999999999 160 10000000000000000000000000000000000000 161 32482438247328742831193201993219039203 162 10000000000000000000000000000000000000 163 -298432432433242412313123321231321313 164 -34243214324324342321313321321342342343 165 0 166 select var_pop(col1) from decimal02; 167 internal error: Decimal128 overflowed 168 drop table decimal02; 169 drop table if exists test02; 170 create table test02(col1 decimal(37)); 171 insert into test02 values(9999999999999999999999999999999999999.123141); 172 insert into test02 values(1122312131421321313131314356569876544.9); 173 insert into test02 values(-2121313214325334213213257654323234324.90); 174 insert into test02 values(-123.2314123412321); 175 insert into test02 values(9932129321093029302930290439029432423.5); 176 select * from test02; 177 col1 178 9999999999999999999999999999999999999 179 1122312131421321313131314356569876545 180 -2121313214325334213213257654323234325 181 -123 182 9932129321093029302930290439029432424 183 select var_pop(col1) from test02; 184 internal error: Decimal128 overflowed 185 drop table test02; 186 drop table if exists test03; 187 create table test03(a decimal(38,2)); 188 insert into test03 values(9999999999999999999999999999999.1); 189 insert into test03 values(-0.00001); 190 insert into test03 values(NULL); 191 select var_pop(a) from test03; 192 internal error: Decimal128 overflowed 193 drop table test03; 194 drop table if exists test04; 195 create table test04(a decimal(38,20)); 196 insert into test04 values(123123242399999900.22231134568909999999); 197 insert into test04 values(999999999999999999.11111111111112222222); 198 insert into test04 values(100000000000000000.00000000000000000001); 199 insert into test04 values(-999999999999999999.9); 200 insert into test04 values(-0.00000000000000000001); 201 select * from test04; 202 a 203 123123242399999900.22231134568909999999 204 999999999999999999.11111111111112222222 205 100000000000000000.00000000000000000001 206 -999999999999999999.90000000000000000000 207 -1E-20 208 select var_pop(a) from test04; 209 internal error: Decimal128 overflowed 210 drop table test04; 211 drop table if exists test05; 212 create table test05(a decimal(20,10)); 213 insert into test05 values(-9999999999.192012); 214 insert into test05 values(9999999999.9102938129); 215 insert into test05 values(7382184932.3892039843); 216 insert into test05 values(0.3029302394); 217 select * from test05; 218 a 219 -9999999999.1920120000 220 9999999999.9102938129 221 7382184932.3892039843 222 0.3029302394 223 select var_pop(a) from test05; 224 var_pop(a) 225 60218122690068093824.133657202503 226 drop table test05; 227 drop table if exists test06; 228 create table test06(a decimal(20,10)); 229 insert into test06 values(-9999999999.192012); 230 insert into test06 values(9999999999.9102938129); 231 insert into test06 values(NULL); 232 insert into test06 values(0.3029302394); 233 select * from test06; 234 a 235 -9999999999.1920120000 236 9999999999.9102938129 237 null 238 0.3029302394 239 select var_pop(a) from test06; 240 var_pop(a) 241 66666666660682038752.801677950945 242 drop table test06; 243 drop database test;