granicus.if.org Git - postgresql/blob - src/test/regress/sql/aggregates.sql

   1 --
   2 -- AGGREGATES
   3 --
   4
   5 SELECT avg(four) AS avg_1 FROM onek;
   6
   7 SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
   8
   9 -- In 7.1, avg(float4) is computed using float8 arithmetic.
  10 -- Round the result to 3 digits to avoid platform-specific results.
  11
  12 SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
  13
  14 SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
  15
  16
  17 SELECT sum(four) AS sum_1500 FROM onek;
  18 SELECT sum(a) AS sum_198 FROM aggtest;
  19 SELECT sum(b) AS avg_431_773 FROM aggtest;
  20 SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
  21
  22 SELECT max(four) AS max_3 FROM onek;
  23 SELECT max(a) AS max_100 FROM aggtest;
  24 SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
  25 SELECT max(student.gpa) AS max_3_7 FROM student;
  26
  27 SELECT stddev_pop(b) FROM aggtest;
  28 SELECT stddev_samp(b) FROM aggtest;
  29 SELECT var_pop(b) FROM aggtest;
  30 SELECT var_samp(b) FROM aggtest;
  31
  32 SELECT stddev_pop(b::numeric) FROM aggtest;
  33 SELECT stddev_samp(b::numeric) FROM aggtest;
  34 SELECT var_pop(b::numeric) FROM aggtest;
  35 SELECT var_samp(b::numeric) FROM aggtest;
  36
  37 -- population variance is defined for a single tuple, sample variance
  38 -- is not
  39 SELECT var_pop(1.0), var_samp(2.0);
  40 SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
  41
  42 -- SQL2003 binary aggregates
  43 SELECT regr_count(b, a) FROM aggtest;
  44 SELECT regr_sxx(b, a) FROM aggtest;
  45 SELECT regr_syy(b, a) FROM aggtest;
  46 SELECT regr_sxy(b, a) FROM aggtest;
  47 SELECT regr_avgx(b, a), regr_avgy(b, a) FROM aggtest;
  48 SELECT regr_r2(b, a) FROM aggtest;
  49 SELECT regr_slope(b, a), regr_intercept(b, a) FROM aggtest;
  50 SELECT covar_pop(b, a), covar_samp(b, a) FROM aggtest;
  51 SELECT corr(b, a) FROM aggtest;
  52
  53 SELECT count(four) AS cnt_1000 FROM onek;
  54 SELECT count(DISTINCT four) AS cnt_4 FROM onek;
  55
  56 select ten, count(*), sum(four) from onek
  57 group by ten order by ten;
  58
  59 select ten, count(four), sum(DISTINCT four) from onek
  60 group by ten order by ten;
  61
  62 -- user-defined aggregates
  63 SELECT newavg(four) AS avg_1 FROM onek;
  64 SELECT newsum(four) AS sum_1500 FROM onek;
  65 SELECT newcnt(four) AS cnt_1000 FROM onek;
  66 SELECT newcnt(*) AS cnt_1000 FROM onek;
  67 SELECT oldcnt(*) AS cnt_1000 FROM onek;
  68 SELECT sum2(q1,q2) FROM int8_tbl;
  69
  70 -- test for outer-level aggregates
  71
  72 -- this should work
  73 select ten, sum(distinct four) from onek a
  74 group by ten
  75 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
  76
  77 -- this should fail because subquery has an agg of its own in WHERE
  78 select ten, sum(distinct four) from onek a
  79 group by ten
  80 having exists (select 1 from onek b
  81                where sum(distinct a.four + b.four) = b.four);
  82
  83 -- Test handling of sublinks within outer-level aggregates.
  84 -- Per bug report from Daniel Grace.
  85 select
  86   (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
  87 from tenk1 o;
  88
  89 --
  90 -- test for bitwise integer aggregates
  91 --
  92 CREATE TEMPORARY TABLE bitwise_test(
  93   i2 INT2,
  94   i4 INT4,
  95   i8 INT8,
  96   i INTEGER,
  97   x INT2,
  98   y BIT(4)
  99 );
 100
 101 -- empty case
 102 SELECT
 103   BIT_AND(i2) AS "?",
 104   BIT_OR(i4)  AS "?"
 105 FROM bitwise_test;
 106
 107 COPY bitwise_test FROM STDIN NULL 'null';
 108 1       1       1       1       1       B0101
 109 3       3       3       null    2       B0100
 110 7       7       7       3       4       B1100
 111 \.
 112
 113 SELECT
 114   BIT_AND(i2) AS "1",
 115   BIT_AND(i4) AS "1",
 116   BIT_AND(i8) AS "1",
 117   BIT_AND(i)  AS "?",
 118   BIT_AND(x)  AS "0",
 119   BIT_AND(y)  AS "0100",
 120
 121   BIT_OR(i2)  AS "7",
 122   BIT_OR(i4)  AS "7",
 123   BIT_OR(i8)  AS "7",
 124   BIT_OR(i)   AS "?",
 125   BIT_OR(x)   AS "7",
 126   BIT_OR(y)   AS "1101"
 127 FROM bitwise_test;
 128
 129 --
 130 -- test boolean aggregates
 131 --
 132 -- first test all possible transition and final states
 133
 134 SELECT
 135   -- boolean and transitions
 136   -- null because strict
 137   booland_statefunc(NULL, NULL)  IS NULL AS "t",
 138   booland_statefunc(TRUE, NULL)  IS NULL AS "t",
 139   booland_statefunc(FALSE, NULL) IS NULL AS "t",
 140   booland_statefunc(NULL, TRUE)  IS NULL AS "t",
 141   booland_statefunc(NULL, FALSE) IS NULL AS "t",
 142   -- and actual computations
 143   booland_statefunc(TRUE, TRUE) AS "t",
 144   NOT booland_statefunc(TRUE, FALSE) AS "t",
 145   NOT booland_statefunc(FALSE, TRUE) AS "t",
 146   NOT booland_statefunc(FALSE, FALSE) AS "t";
 147
 148 SELECT
 149   -- boolean or transitions
 150   -- null because strict
 151   boolor_statefunc(NULL, NULL)  IS NULL AS "t",
 152   boolor_statefunc(TRUE, NULL)  IS NULL AS "t",
 153   boolor_statefunc(FALSE, NULL) IS NULL AS "t",
 154   boolor_statefunc(NULL, TRUE)  IS NULL AS "t",
 155   boolor_statefunc(NULL, FALSE) IS NULL AS "t",
 156   -- actual computations
 157   boolor_statefunc(TRUE, TRUE) AS "t",
 158   boolor_statefunc(TRUE, FALSE) AS "t",
 159   boolor_statefunc(FALSE, TRUE) AS "t",
 160   NOT boolor_statefunc(FALSE, FALSE) AS "t";
 161
 162 CREATE TEMPORARY TABLE bool_test(
 163   b1 BOOL,
 164   b2 BOOL,
 165   b3 BOOL,
 166   b4 BOOL);
 167
 168 -- empty case
 169 SELECT
 170   BOOL_AND(b1)   AS "n",
 171   BOOL_OR(b3)    AS "n"
 172 FROM bool_test;
 173
 174 COPY bool_test FROM STDIN NULL 'null';
 175 TRUE    null    FALSE   null
 176 FALSE   TRUE    null    null
 177 null    TRUE    FALSE   null
 178 \.
 179
 180 SELECT
 181   BOOL_AND(b1)     AS "f",
 182   BOOL_AND(b2)     AS "t",
 183   BOOL_AND(b3)     AS "f",
 184   BOOL_AND(b4)     AS "n",
 185   BOOL_AND(NOT b2) AS "f",
 186   BOOL_AND(NOT b3) AS "t"
 187 FROM bool_test;
 188
 189 SELECT
 190   EVERY(b1)     AS "f",
 191   EVERY(b2)     AS "t",
 192   EVERY(b3)     AS "f",
 193   EVERY(b4)     AS "n",
 194   EVERY(NOT b2) AS "f",
 195   EVERY(NOT b3) AS "t"
 196 FROM bool_test;
 197
 198 SELECT
 199   BOOL_OR(b1)      AS "t",
 200   BOOL_OR(b2)      AS "t",
 201   BOOL_OR(b3)      AS "f",
 202   BOOL_OR(b4)      AS "n",
 203   BOOL_OR(NOT b2)  AS "f",
 204   BOOL_OR(NOT b3)  AS "t"
 205 FROM bool_test;
 206
 207 --
 208 -- Test several cases that should be optimized into indexscans instead of
 209 -- the generic aggregate implementation.  We can't actually verify that they
 210 -- are done as indexscans, but we can check that the results are correct.
 211 --
 212
 213 -- Basic cases
 214 select max(unique1) from tenk1;
 215 select max(unique1) from tenk1 where unique1 < 42;
 216 select max(unique1) from tenk1 where unique1 > 42;
 217 select max(unique1) from tenk1 where unique1 > 42000;
 218
 219 -- multi-column index (uses tenk1_thous_tenthous)
 220 select max(tenthous) from tenk1 where thousand = 33;
 221 select min(tenthous) from tenk1 where thousand = 33;
 222
 223 -- check parameter propagation into an indexscan subquery
 224 select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
 225 from int4_tbl;
 226
 227 -- check some cases that were handled incorrectly in 8.3.0
 228 select distinct max(unique2) from tenk1;
 229 select max(unique2) from tenk1 order by 1;
 230 select max(unique2) from tenk1 order by max(unique2);
 231 select max(unique2) from tenk1 order by max(unique2)+1;
 232 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
 233
 234 --
 235 -- Test combinations of DISTINCT and/or ORDER BY
 236 --
 237
 238 select array_agg(a order by b)
 239   from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
 240 select array_agg(a order by a)
 241   from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
 242 select array_agg(a order by a desc)
 243   from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
 244 select array_agg(b order by a desc)
 245   from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
 246
 247 select array_agg(distinct a)
 248   from (values (1),(2),(1),(3),(null),(2)) v(a);
 249 select array_agg(distinct a order by a)
 250   from (values (1),(2),(1),(3),(null),(2)) v(a);
 251 select array_agg(distinct a order by a desc)
 252   from (values (1),(2),(1),(3),(null),(2)) v(a);
 253 select array_agg(distinct a order by a desc nulls last)
 254   from (values (1),(2),(1),(3),(null),(2)) v(a);
 255
 256 -- multi-arg aggs, strict/nonstrict, distinct/order by
 257
 258 select aggfstr(a,b,c)
 259   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 260 select aggfns(a,b,c)
 261   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 262
 263 select aggfstr(distinct a,b,c)
 264   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 265        generate_series(1,3) i;
 266 select aggfns(distinct a,b,c)
 267   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 268        generate_series(1,3) i;
 269
 270 select aggfstr(distinct a,b,c order by b)
 271   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 272        generate_series(1,3) i;
 273 select aggfns(distinct a,b,c order by b)
 274   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 275        generate_series(1,3) i;
 276
 277 -- test specific code paths
 278
 279 select aggfns(distinct a,a,c order by c using ~<~,a)
 280   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 281        generate_series(1,2) i;
 282 select aggfns(distinct a,a,c order by c using ~<~)
 283   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 284        generate_series(1,2) i;
 285 select aggfns(distinct a,a,c order by a)
 286   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 287        generate_series(1,2) i;
 288 select aggfns(distinct a,b,c order by a,c using ~<~,b)
 289   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 290        generate_series(1,2) i;
 291
 292 -- check node I/O via view creation and usage, also deparsing logic
 293
 294 create view agg_view1 as
 295   select aggfns(a,b,c)
 296     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 297
 298 select * from agg_view1;
 299 select pg_get_viewdef('agg_view1'::regclass);
 300
 301 create or replace view agg_view1 as
 302   select aggfns(distinct a,b,c)
 303     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 304          generate_series(1,3) i;
 305
 306 select * from agg_view1;
 307 select pg_get_viewdef('agg_view1'::regclass);
 308
 309 create or replace view agg_view1 as
 310   select aggfns(distinct a,b,c order by b)
 311     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 312          generate_series(1,3) i;
 313
 314 select * from agg_view1;
 315 select pg_get_viewdef('agg_view1'::regclass);
 316
 317 create or replace view agg_view1 as
 318   select aggfns(a,b,c order by b+1)
 319     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 320
 321 select * from agg_view1;
 322 select pg_get_viewdef('agg_view1'::regclass);
 323
 324 create or replace view agg_view1 as
 325   select aggfns(a,a,c order by b)
 326     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 327
 328 select * from agg_view1;
 329 select pg_get_viewdef('agg_view1'::regclass);
 330
 331 create or replace view agg_view1 as
 332   select aggfns(a,b,c order by c using ~<~)
 333     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 334
 335 select * from agg_view1;
 336 select pg_get_viewdef('agg_view1'::regclass);
 337
 338 create or replace view agg_view1 as
 339   select aggfns(distinct a,b,c order by a,c using ~<~,b)
 340     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 341          generate_series(1,2) i;
 342
 343 select * from agg_view1;
 344 select pg_get_viewdef('agg_view1'::regclass);
 345
 346 drop view agg_view1;
 347
 348 -- incorrect DISTINCT usage errors
 349
 350 select aggfns(distinct a,b,c order by i)
 351   from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
 352 select aggfns(distinct a,b,c order by a,b+1)
 353   from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
 354 select aggfns(distinct a,b,c order by a,b,i,c)
 355   from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
 356 select aggfns(distinct a,a,c order by a,b)
 357   from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
 358
 359 -- string_agg tests
 360 select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
 361 select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
 362 select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a);
 363 select string_agg(a,',') from (values(null),(null)) g(a);
 364
 365 -- check some implicit casting cases, as per bug #5564
 366 select string_agg(distinct f1, ',' order by f1) from varchar_tbl;  -- ok
 367 select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl;  -- not ok
 368 select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl;  -- not ok
 369 select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl;  -- ok