udf/postgreSQL/udf-aggregates_part3.sql

0001 --
0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
0003 --
0004 --
0005 -- AGGREGATES [Part 3]
0006 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/aggregates.sql#L352-L605
0007
0008 -- This test file was converted from postgreSQL/aggregates_part3.sql.
0009
0010 -- [SPARK-28865] Table inheritance
0011 -- try it on an inheritance tree
0012 -- create table minmaxtest(f1 int);
0013 -- create table minmaxtest1() inherits (minmaxtest);
0014 -- create table minmaxtest2() inherits (minmaxtest);
0015 -- create table minmaxtest3() inherits (minmaxtest);
0016 -- create index minmaxtesti on minmaxtest(f1);
0017 -- create index minmaxtest1i on minmaxtest1(f1);
0018 -- create index minmaxtest2i on minmaxtest2(f1 desc);
0019 -- create index minmaxtest3i on minmaxtest3(f1) where f1 is not null;
0020
0021 -- insert into minmaxtest values(11), (12);
0022 -- insert into minmaxtest1 values(13), (14);
0023 -- insert into minmaxtest2 values(15), (16);
0024 -- insert into minmaxtest3 values(17), (18);
0025
0026 -- explain (costs off)
0027 --   select min(f1), max(f1) from minmaxtest;
0028 -- select min(f1), max(f1) from minmaxtest;
0029
0030 -- DISTINCT doesn't do anything useful here, but it shouldn't fail
0031 -- explain (costs off)
0032 --   select distinct min(f1), max(f1) from minmaxtest;
0033 -- select distinct min(f1), max(f1) from minmaxtest;
0034
0035 -- drop table minmaxtest cascade;
0036
0037 -- check for correct detection of nested-aggregate errors
0038 select udf(max(min(unique1))) from tenk1;
0039 -- select (select max(min(unique1)) from int8_tbl) from tenk1;
0040
0041 -- These tests only test the explain. Skip these tests.
0042 --
0043 -- Test removal of redundant GROUP BY columns
0044 --
0045
0046 -- create temp table t1 (a int, b int, c int, d int, primary key (a, b));
0047 -- create temp table t2 (x int, y int, z int, primary key (x, y));
0048 -- create temp table t3 (a int, b int, c int, primary key(a, b) deferrable);
0049
0050 -- Non-primary-key columns can be removed from GROUP BY
0051 -- explain (costs off) select * from t1 group by a,b,c,d;
0052
0053 -- No removal can happen if the complete PK is not present in GROUP BY
0054 -- explain (costs off) select a,c from t1 group by a,c,d;
0055
0056 -- Test removal across multiple relations
0057 -- explain (costs off) select *
0058 -- from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
0059 -- group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.y,t2.z;
0060
0061 -- Test case where t1 can be optimized but not t2
0062 -- explain (costs off) select t1.*,t2.x,t2.z
0063 -- from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
0064 -- group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.z;
0065
0066 -- Cannot optimize when PK is deferrable
0067 -- explain (costs off) select * from t3 group by a,b,c;
0068
0069 -- drop table t1;
0070 -- drop table t2;
0071 -- drop table t3;
0072
0073 -- [SPARK-27974] Add built-in Aggregate Function: array_agg
0074 --
0075 -- Test combinations of DISTINCT and/or ORDER BY
0076 --
0077
0078 -- select array_agg(a order by b)
0079 --   from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
0080 -- select array_agg(a order by a)
0081 --   from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
0082 -- select array_agg(a order by a desc)
0083 --   from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
0084 -- select array_agg(b order by a desc)
0085 --   from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
0086
0087 -- select array_agg(distinct a)
0088 --   from (values (1),(2),(1),(3),(null),(2)) v(a);
0089 -- select array_agg(distinct a order by a)
0090 --   from (values (1),(2),(1),(3),(null),(2)) v(a);
0091 -- select array_agg(distinct a order by a desc)
0092 --   from (values (1),(2),(1),(3),(null),(2)) v(a);
0093 -- select array_agg(distinct a order by a desc nulls last)
0094 --   from (values (1),(2),(1),(3),(null),(2)) v(a);
0095
0096 -- Skip the test below because it requires 4 UDAFs: aggf_trans, aggfns_trans, aggfstr, and aggfns
0097 -- multi-arg aggs, strict/nonstrict, distinct/order by
0098
0099 -- select aggfstr(a,b,c)
0100 --   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
0101 -- select aggfns(a,b,c)
0102 --   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
0103
0104 -- select aggfstr(distinct a,b,c)
0105 --   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0106 --        generate_series(1,3) i;
0107 -- select aggfns(distinct a,b,c)
0108 --   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0109 --        generate_series(1,3) i;
0110
0111 -- select aggfstr(distinct a,b,c order by b)
0112 --   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0113 --        generate_series(1,3) i;
0114 -- select aggfns(distinct a,b,c order by b)
0115 --   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0116 --        generate_series(1,3) i;
0117
0118 -- test specific code paths
0119
0120 -- [SPARK-28768] Implement more text pattern operators
0121 -- select aggfns(distinct a,a,c order by c using ~<~,a)
0122 --   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0123 --        generate_series(1,2) i;
0124 -- select aggfns(distinct a,a,c order by c using ~<~)
0125 --   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0126 --        generate_series(1,2) i;
0127 -- select aggfns(distinct a,a,c order by a)
0128 --   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0129 --        generate_series(1,2) i;
0130 -- select aggfns(distinct a,b,c order by a,c using ~<~,b)
0131 --   from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0132 --        generate_series(1,2) i;
0133
0134 -- check node I/O via view creation and usage, also deparsing logic
0135
0136 -- create view agg_view1 as
0137 --   select aggfns(a,b,c)
0138 --     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
0139
0140 -- select * from agg_view1;
0141 -- select pg_get_viewdef('agg_view1'::regclass);
0142
0143 -- create or replace view agg_view1 as
0144 --   select aggfns(distinct a,b,c)
0145 --     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0146 --          generate_series(1,3) i;
0147
0148 -- select * from agg_view1;
0149 -- select pg_get_viewdef('agg_view1'::regclass);
0150
0151 -- create or replace view agg_view1 as
0152 --   select aggfns(distinct a,b,c order by b)
0153 --     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0154 --          generate_series(1,3) i;
0155
0156 -- select * from agg_view1;
0157 -- select pg_get_viewdef('agg_view1'::regclass);
0158
0159 -- create or replace view agg_view1 as
0160 --   select aggfns(a,b,c order by b+1)
0161 --     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
0162
0163 -- select * from agg_view1;
0164 -- select pg_get_viewdef('agg_view1'::regclass);
0165
0166 -- create or replace view agg_view1 as
0167 --   select aggfns(a,a,c order by b)
0168 --     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
0169
0170 -- select * from agg_view1;
0171 -- select pg_get_viewdef('agg_view1'::regclass);
0172
0173 -- create or replace view agg_view1 as
0174 --   select aggfns(a,b,c order by c using ~<~)
0175 --     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
0176
0177 -- select * from agg_view1;
0178 -- select pg_get_viewdef('agg_view1'::regclass);
0179
0180 -- create or replace view agg_view1 as
0181 --   select aggfns(distinct a,b,c order by a,c using ~<~,b)
0182 --     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0183 --          generate_series(1,2) i;
0184
0185 -- select * from agg_view1;
0186 -- select pg_get_viewdef('agg_view1'::regclass);
0187
0188 -- drop view agg_view1;
0189
0190 -- incorrect DISTINCT usage errors
0191
0192 -- select aggfns(distinct a,b,c order by i)
0193 --   from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
0194 -- select aggfns(distinct a,b,c order by a,b+1)
0195 --   from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
0196 -- select aggfns(distinct a,b,c order by a,b,i,c)
0197 --   from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
0198 -- select aggfns(distinct a,a,c order by a,b)
0199 --   from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
0200
0201 -- [SPARK-27978] Add built-in Aggregate Functions: string_agg
0202 -- string_agg tests
0203 -- select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
0204 -- select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
0205 -- select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a);
0206 -- select string_agg(a,',') from (values(null),(null)) g(a);
0207
0208 -- check some implicit casting cases, as per bug #5564
0209 -- select string_agg(distinct f1, ',' order by f1) from varchar_tbl;  -- ok
0210 -- select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl;  -- not ok
0211 -- select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl;  -- not ok
0212 -- select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl;  -- ok
0213
0214 -- [SPARK-28121] decode can not accept 'hex' as charset
0215 -- string_agg bytea tests
0216 -- CREATE TABLE bytea_test_table(v BINARY) USING parquet;
0217
0218 -- select string_agg(v, '') from bytea_test_table;
0219
0220 -- insert into bytea_test_table values(decode('ff','hex'));
0221
0222 -- select string_agg(v, '') from bytea_test_table;
0223
0224 -- insert into bytea_test_table values(decode('aa','hex'));
0225
0226 -- select string_agg(v, '') from bytea_test_table;
0227 -- select string_agg(v, NULL) from bytea_test_table;
0228 -- select string_agg(v, decode('ee', 'hex')) from bytea_test_table;
0229
0230 -- drop table bytea_test_table;
0231
0232 -- FILTER tests
0233
0234 -- select min(unique1) filter (where unique1 > 100) from tenk1;
0235
0236 -- select sum(1/ten) filter (where ten > 0) from tenk1;
0237
0238 -- select ten, sum(distinct four) filter (where four::text ~ '123') from onek a
0239 -- group by ten;
0240
0241 -- select ten, sum(distinct four) filter (where four > 10) from onek a
0242 -- group by ten
0243 -- having exists (select 1 from onek b where sum(distinct a.four) = b.four);
0244
0245 -- [SPARK-28682] ANSI SQL: Collation Support
0246 -- select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0')
0247 -- from (values ('a', 'b')) AS v(foo,bar);
0248
0249 -- outer reference in FILTER (PostgreSQL extension)
0250 select udf((select udf(count(*))
0251         from (values (1)) t0(inner_c))) as col
0252 from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
0253 -- select (select count(*) filter (where outer_c <> 0)
0254 --         from (values (1)) t0(inner_c))
0255 -- from (values (2),(3)) t1(outer_c); -- outer query is aggregation query
0256 -- select (select count(inner_c) filter (where outer_c <> 0)
0257 --         from (values (1)) t0(inner_c))
0258 -- from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
0259 -- select
0260 --   (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1))
0261 --      filter (where o.unique1 < 10))
0262 -- from tenk1 o;                                        -- outer query is aggregation query
0263
0264 -- subquery in FILTER clause (PostgreSQL extension)
0265 -- select sum(unique1) FILTER (WHERE
0266 --  unique1 IN (SELECT unique1 FROM onek where unique1 < 100)) FROM tenk1;
0267
0268 -- exercise lots of aggregate parts with FILTER
0269 -- select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1)
0270 --     from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
0271 --     generate_series(1,2) i;