← Back to branch summary

~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

« back to all changes in this revision

Viewing changes to src/test/regress/sql/aggregates.sql

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2011-05-11 10:41:53 UTC
  • Revision ID: james.westby@ubuntu.com-20110511104153-psbh2o58553fv1m0
Tags: upstream-9.1~beta1
Import upstream version 9.1~beta1

Show diffs side-by-side

added added

removed removed

Lines of Context:
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 cases that should be optimized into indexscans instead of
 
209
-- the generic aggregate implementation.
 
210
--
 
211
analyze tenk1;          -- ensure we get consistent plans here
 
212
 
 
213
-- Basic cases
 
214
explain (costs off)
 
215
  select min(unique1) from tenk1;
 
216
select min(unique1) from tenk1;
 
217
explain (costs off)
 
218
  select max(unique1) from tenk1;
 
219
select max(unique1) from tenk1;
 
220
explain (costs off)
 
221
  select max(unique1) from tenk1 where unique1 < 42;
 
222
select max(unique1) from tenk1 where unique1 < 42;
 
223
explain (costs off)
 
224
  select max(unique1) from tenk1 where unique1 > 42;
 
225
select max(unique1) from tenk1 where unique1 > 42;
 
226
explain (costs off)
 
227
  select max(unique1) from tenk1 where unique1 > 42000;
 
228
select max(unique1) from tenk1 where unique1 > 42000;
 
229
 
 
230
-- multi-column index (uses tenk1_thous_tenthous)
 
231
explain (costs off)
 
232
  select max(tenthous) from tenk1 where thousand = 33;
 
233
select max(tenthous) from tenk1 where thousand = 33;
 
234
explain (costs off)
 
235
  select min(tenthous) from tenk1 where thousand = 33;
 
236
select min(tenthous) from tenk1 where thousand = 33;
 
237
 
 
238
-- check parameter propagation into an indexscan subquery
 
239
explain (costs off)
 
240
  select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
 
241
    from int4_tbl;
 
242
select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
 
243
  from int4_tbl;
 
244
 
 
245
-- check some cases that were handled incorrectly in 8.3.0
 
246
explain (costs off)
 
247
  select distinct max(unique2) from tenk1;
 
248
select distinct max(unique2) from tenk1;
 
249
explain (costs off)
 
250
  select max(unique2) from tenk1 order by 1;
 
251
select max(unique2) from tenk1 order by 1;
 
252
explain (costs off)
 
253
  select max(unique2) from tenk1 order by max(unique2);
 
254
select max(unique2) from tenk1 order by max(unique2);
 
255
explain (costs off)
 
256
  select max(unique2) from tenk1 order by max(unique2)+1;
 
257
select max(unique2) from tenk1 order by max(unique2)+1;
 
258
explain (costs off)
 
259
  select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
 
260
select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
 
261
 
 
262
-- try it on an inheritance tree
 
263
create table minmaxtest(f1 int);
 
264
create table minmaxtest1() inherits (minmaxtest);
 
265
create table minmaxtest2() inherits (minmaxtest);
 
266
create table minmaxtest3() inherits (minmaxtest);
 
267
create index minmaxtesti on minmaxtest(f1);
 
268
create index minmaxtest1i on minmaxtest1(f1);
 
269
create index minmaxtest2i on minmaxtest2(f1 desc);
 
270
create index minmaxtest3i on minmaxtest3(f1) where f1 is not null;
 
271
 
 
272
insert into minmaxtest values(11), (12);
 
273
insert into minmaxtest1 values(13), (14);
 
274
insert into minmaxtest2 values(15), (16);
 
275
insert into minmaxtest3 values(17), (18);
 
276
 
 
277
explain (costs off)
 
278
  select min(f1), max(f1) from minmaxtest;
 
279
select min(f1), max(f1) from minmaxtest;
 
280
 
 
281
drop table minmaxtest cascade;
 
282
 
 
283
--
 
284
-- Test combinations of DISTINCT and/or ORDER BY
 
285
--
 
286
 
 
287
select array_agg(a order by b)
 
288
  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
 
289
select array_agg(a order by a)
 
290
  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
 
291
select array_agg(a order by a desc)
 
292
  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
 
293
select array_agg(b order by a desc)
 
294
  from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
 
295
 
 
296
select array_agg(distinct a)
 
297
  from (values (1),(2),(1),(3),(null),(2)) v(a);
 
298
select array_agg(distinct a order by a)
 
299
  from (values (1),(2),(1),(3),(null),(2)) v(a);
 
300
select array_agg(distinct a order by a desc)
 
301
  from (values (1),(2),(1),(3),(null),(2)) v(a);
 
302
select array_agg(distinct a order by a desc nulls last)
 
303
  from (values (1),(2),(1),(3),(null),(2)) v(a);
 
304
 
 
305
-- multi-arg aggs, strict/nonstrict, distinct/order by
 
306
 
 
307
select aggfstr(a,b,c)
 
308
  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 
309
select aggfns(a,b,c)
 
310
  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 
311
 
 
312
select aggfstr(distinct a,b,c)
 
313
  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
314
       generate_series(1,3) i;
 
315
select aggfns(distinct a,b,c)
 
316
  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
317
       generate_series(1,3) i;
 
318
 
 
319
select aggfstr(distinct a,b,c order by b)
 
320
  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
321
       generate_series(1,3) i;
 
322
select aggfns(distinct a,b,c order by b)
 
323
  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
324
       generate_series(1,3) i;
 
325
 
 
326
-- test specific code paths
 
327
 
 
328
select aggfns(distinct a,a,c order by c using ~<~,a)
 
329
  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
330
       generate_series(1,2) i;
 
331
select aggfns(distinct a,a,c order by c using ~<~)
 
332
  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
333
       generate_series(1,2) i;
 
334
select aggfns(distinct a,a,c order by a)
 
335
  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
336
       generate_series(1,2) i;
 
337
select aggfns(distinct a,b,c order by a,c using ~<~,b)
 
338
  from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
339
       generate_series(1,2) i;
 
340
 
 
341
-- check node I/O via view creation and usage, also deparsing logic
 
342
 
 
343
create view agg_view1 as
 
344
  select aggfns(a,b,c)
 
345
    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 
346
 
 
347
select * from agg_view1;
 
348
select pg_get_viewdef('agg_view1'::regclass);
 
349
 
 
350
create or replace view agg_view1 as
 
351
  select aggfns(distinct a,b,c)
 
352
    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
353
         generate_series(1,3) i;
 
354
 
 
355
select * from agg_view1;
 
356
select pg_get_viewdef('agg_view1'::regclass);
 
357
 
 
358
create or replace view agg_view1 as
 
359
  select aggfns(distinct a,b,c order by b)
 
360
    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
361
         generate_series(1,3) i;
 
362
 
 
363
select * from agg_view1;
 
364
select pg_get_viewdef('agg_view1'::regclass);
 
365
 
 
366
create or replace view agg_view1 as
 
367
  select aggfns(a,b,c order by b+1)
 
368
    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 
369
 
 
370
select * from agg_view1;
 
371
select pg_get_viewdef('agg_view1'::regclass);
 
372
 
 
373
create or replace view agg_view1 as
 
374
  select aggfns(a,a,c order by b)
 
375
    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 
376
 
 
377
select * from agg_view1;
 
378
select pg_get_viewdef('agg_view1'::regclass);
 
379
 
 
380
create or replace view agg_view1 as
 
381
  select aggfns(a,b,c order by c using ~<~)
 
382
    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
 
383
 
 
384
select * from agg_view1;
 
385
select pg_get_viewdef('agg_view1'::regclass);
 
386
 
 
387
create or replace view agg_view1 as
 
388
  select aggfns(distinct a,b,c order by a,c using ~<~,b)
 
389
    from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
 
390
         generate_series(1,2) i;
 
391
 
 
392
select * from agg_view1;
 
393
select pg_get_viewdef('agg_view1'::regclass);
 
394
 
 
395
drop view agg_view1;
 
396
 
 
397
-- incorrect DISTINCT usage errors
 
398
 
 
399
select aggfns(distinct a,b,c order by i)
 
400
  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
 
401
select aggfns(distinct a,b,c order by a,b+1)
 
402
  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
 
403
select aggfns(distinct a,b,c order by a,b,i,c)
 
404
  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
 
405
select aggfns(distinct a,a,c order by a,b)
 
406
  from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
 
407
 
 
408
-- string_agg tests
 
409
select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
 
410
select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
 
411
select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a);
 
412
select string_agg(a,',') from (values(null),(null)) g(a);
 
413
 
 
414
-- check some implicit casting cases, as per bug #5564
 
415
select string_agg(distinct f1, ',' order by f1) from varchar_tbl;  -- ok
 
416
select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl;  -- not ok
 
417
select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl;  -- not ok
 
418
select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl;  -- ok