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