Registering pre-existing tables
onek
int4_tbl
INT4_TBL
tenk1
VARCHAR_TBL
aggtest
student
varchar_tbl
int8_tbl
--
-- AGGREGATES
--
-- avoid bit-exact output here because operations may not be bit-exact.
SET extra_float_digits = 0;
SELECT avg(four) AS avg_1 FROM onek;
SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
-- In 7.1, avg(float4) is computed using float8 arithmetic.
-- Round the result to 3 digits to avoid platform-specific results.
SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
SELECT sum(four) AS sum_1500 FROM onek;
SELECT sum(a) AS sum_198 FROM aggtest;
SELECT sum(b) AS avg_431_773 FROM aggtest;
SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
SELECT max(four) AS max_3 FROM onek;
SELECT max(a) AS max_100 FROM aggtest;
SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
SELECT max(student.gpa) AS max_3_7 FROM student;
SELECT stddev_pop(b) FROM aggtest;
SELECT stddev_samp(b) FROM aggtest;
SELECT var_pop(b) FROM aggtest;
SELECT var_samp(b) FROM aggtest;
SELECT stddev_pop(b::numeric) FROM aggtest;
SELECT stddev_samp(b::numeric) FROM aggtest;
SELECT var_pop(b::numeric) FROM aggtest;
SELECT var_samp(b::numeric) FROM aggtest;
-- population variance is defined for a single tuple, sample variance
-- is not
SELECT var_pop(1.0::float8), var_samp(2.0::float8);
SELECT stddev_pop(3.0::float8), stddev_samp(4.0::float8);
SELECT var_pop('inf'::float8), var_samp('inf'::float8);
SELECT stddev_pop('inf'::float8), stddev_samp('inf'::float8);
SELECT var_pop('nan'::float8), var_samp('nan'::float8);
SELECT stddev_pop('nan'::float8), stddev_samp('nan'::float8);
SELECT var_pop(1.0::float4), var_samp(2.0::float4);
SELECT stddev_pop(3.0::float4), stddev_samp(4.0::float4);
SELECT var_pop('inf'::float4), var_samp('inf'::float4);
SELECT stddev_pop('inf'::float4), stddev_samp('inf'::float4);
SELECT var_pop('nan'::float4), var_samp('nan'::float4);
SELECT stddev_pop('nan'::float4), stddev_samp('nan'::float4);
SELECT var_pop(1.0::numeric), var_samp(2.0::numeric);
SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
SELECT var_pop('inf'::numeric), var_samp('inf'::numeric);
SELECT stddev_pop('inf'::numeric), stddev_samp('inf'::numeric);
SELECT var_pop('nan'::numeric), var_samp('nan'::numeric);
SELECT stddev_pop('nan'::numeric), stddev_samp('nan'::numeric);
-- verify correct results for null and NaN inputs
select sum(null::int4) from generate_series(1,3);
select sum(null::int8) from generate_series(1,3);
select sum(null::numeric) from generate_series(1,3);
select sum(null::float8) from generate_series(1,3);
select avg(null::int4) from generate_series(1,3);
select avg(null::int8) from generate_series(1,3);
select avg(null::numeric) from generate_series(1,3);
select avg(null::float8) from generate_series(1,3);
select sum('NaN'::numeric) from generate_series(1,3);
select avg('NaN'::numeric) from generate_series(1,3);
-- verify correct results for infinite inputs
SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('1'), ('infinity')) v(x);
SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('infinity'), ('1')) v(x);
SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('infinity'), ('infinity')) v(x);
SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('-infinity'), ('infinity')) v(x);
SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('-infinity'), ('-infinity')) v(x);
SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
FROM (VALUES ('1'), ('infinity')) v(x);
SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
FROM (VALUES ('infinity'), ('1')) v(x);
SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
FROM (VALUES ('infinity'), ('infinity')) v(x);
SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
FROM (VALUES ('-infinity'), ('infinity')) v(x);
SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
FROM (VALUES ('-infinity'), ('-infinity')) v(x);
-- test accuracy with a large input offset
SELECT avg(x::float8), var_pop(x::float8)
FROM (VALUES (100000003), (100000004), (100000006), (100000007)) v(x);
SELECT avg(x::float8), var_pop(x::float8)
FROM (VALUES (7000000000005), (7000000000007)) v(x);
-- SQL2003 binary aggregates
SELECT regr_count(b, a) FROM aggtest;
SELECT regr_sxx(b, a) FROM aggtest;
SELECT regr_syy(b, a) FROM aggtest;
SELECT regr_sxy(b, a) FROM aggtest;
SELECT regr_avgx(b, a), regr_avgy(b, a) FROM aggtest;
SELECT regr_r2(b, a) FROM aggtest;
SELECT regr_slope(b, a), regr_intercept(b, a) FROM aggtest;
SELECT covar_pop(b, a), covar_samp(b, a) FROM aggtest;
SELECT corr(b, a) FROM aggtest;
-- check single-tuple behavior
SELECT covar_pop(1::float8,2::float8), covar_samp(3::float8,4::float8);
SELECT covar_pop(1::float8,'inf'::float8), covar_samp(3::float8,'inf'::float8);
SELECT covar_pop(1::float8,'nan'::float8), covar_samp(3::float8,'nan'::float8);
-- test accum and combine functions directly
CREATE TABLE regr_test (x float8, y float8);
INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200);
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
FROM regr_test WHERE x IN (10,20,30,80);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
^
-stdin-::1:1: Error: Duplicated member: sum
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
^
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
FROM regr_test;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
^
-stdin-::1:1: Error: Duplicated member: sum
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
^
SELECT float8_accum('{4,140,2900}'::float8[], 100);
SELECT float8_regr_accum('{4,140,2900,1290,83075,15050}'::float8[], 200, 100);
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
FROM regr_test WHERE x IN (10,20,30);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
^
-stdin-::1:1: Error: Duplicated member: sum
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
^
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
FROM regr_test WHERE x IN (80,100);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
^
-stdin-::1:1: Error: Duplicated member: sum
SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x)
^
SELECT float8_combine('{3,60,200}'::float8[], '{0,0,0}'::float8[]);
SELECT float8_combine('{0,0,0}'::float8[], '{2,180,200}'::float8[]);
SELECT float8_combine('{3,60,200}'::float8[], '{2,180,200}'::float8[]);
SELECT float8_regr_combine('{3,60,200,750,20000,2000}'::float8[],
'{0,0,0,0,0,0}'::float8[]);
SELECT float8_regr_combine('{0,0,0,0,0,0}'::float8[],
'{2,180,200,740,57800,-3400}'::float8[]);
SELECT float8_regr_combine('{3,60,200,750,20000,2000}'::float8[],
'{2,180,200,740,57800,-3400}'::float8[]);
DROP TABLE regr_test;
-- test count, distinct
SELECT count(four) AS cnt_1000 FROM onek;
SELECT count(DISTINCT four) AS cnt_4 FROM onek;
select ten, count(*), sum(four) from onek
group by ten order by ten;
select ten, count(four), sum(DISTINCT four) from onek
group by ten order by ten;
-- user-defined aggregates
SELECT newavg(four) AS avg_1 FROM onek;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
-- user-defined aggregates
^
-stdin-::2:8: Error: At function: PgCall
SELECT newavg(four) AS avg_1 FROM onek;
^
-stdin-::2:8: Error: No such proc: newavg
SELECT newavg(four) AS avg_1 FROM onek;
^
SELECT newsum(four) AS sum_1500 FROM onek;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
SELECT newsum(four) AS sum_1500 FROM onek;
^
-stdin-::1:8: Error: At function: PgCall
SELECT newsum(four) AS sum_1500 FROM onek;
^
-stdin-::1:8: Error: No such proc: newsum
SELECT newsum(four) AS sum_1500 FROM onek;
^
SELECT newcnt(four) AS cnt_1000 FROM onek;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
SELECT newcnt(four) AS cnt_1000 FROM onek;
^
-stdin-::1:8: Error: At function: PgCall
SELECT newcnt(four) AS cnt_1000 FROM onek;
^
-stdin-::1:8: Error: No such proc: newcnt
SELECT newcnt(four) AS cnt_1000 FROM onek;
^
SELECT newcnt(*) AS cnt_1000 FROM onek;
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: * is expected only in count function
SELECT newcnt(*) AS cnt_1000 FROM onek;
^
SELECT oldcnt(*) AS cnt_1000 FROM onek;
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: * is expected only in count function
SELECT oldcnt(*) AS cnt_1000 FROM onek;
^
SELECT sum2(q1,q2) FROM int8_tbl;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
SELECT sum2(q1,q2) FROM int8_tbl;
^
-stdin-::1:8: Error: At function: PgCall
SELECT sum2(q1,q2) FROM int8_tbl;
^
-stdin-::1:8: Error: No such proc: sum2
SELECT sum2(q1,q2) FROM int8_tbl;
^
-- test for outer-level aggregates
-- this should work
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b where sum(distinct a.four) = b.four);
-stdin-:: Error: Parse Sql
-stdin-::5:43: Error: Aggregate functions are not allowed in: WHERE
having exists (select 1 from onek b where sum(distinct a.four) = b.four);
^
-- this should fail because subquery has an agg of its own in WHERE
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b
where sum(distinct a.four + b.four) = b.four);
-stdin-:: Error: Parse Sql
-stdin-::5:22: Error: Aggregate functions are not allowed in: WHERE
where sum(distinct a.four + b.four) = b.four);
^
-- Test handling of sublinks within outer-level aggregates.
-- Per bug report from Daniel Grace.
select
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
from tenk1 o;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
-- Test handling of sublinks within outer-level aggregates.
^
-stdin-::4:3: Error: At function: PgSubLink, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
^
-stdin-::4:11: Error: At function: PgAgg
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
^
-stdin-::4:15: Error: At function: PgSubLink, At function: PgSelect, At function: PgSetItem
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
^
-stdin-::4:64: Error: No such column: unique1
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
^
-- Test handling of Params within aggregate arguments in hashed aggregation.
-- Per bug report from Jeevan Chalke.
explain (verbose, costs off)
select s1, s2, sm
from generate_series(1, 3) s1,
lateral (select s2, sum(s1 + s2) sm
from generate_series(1, 3) s2 group by s2) ss
order by 1, 2;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- Test handling of Params within aggregate arguments in hashed aggregation.
^
select s1, s2, sm
from generate_series(1, 3) s1,
lateral (select s2, sum(s1 + s2) sm
from generate_series(1, 3) s2 group by s2) ss
order by 1, 2;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RangeSubselect: unsupported lateral
select s1, s2, sm
^
explain (verbose, costs off)
select array(select sum(x+y) s
from generate_series(1,3) y group by y order by s)
from generate_series(1,3) x;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (verbose, costs off)
^
select array(select sum(x+y) s
from generate_series(1,3) y group by y order by s)
from generate_series(1,3) x;
--
-- test for bitwise integer aggregates
--
CREATE TEMPORARY TABLE bitwise_test(
i2 INT2,
i4 INT4,
i8 INT8,
i INTEGER,
x INT2,
y BIT(4)
);
-- empty case
SELECT
BIT_AND(i2) AS "?",
BIT_OR(i4) AS "?",
BIT_XOR(i8) AS "?"
FROM bitwise_test;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
-- empty case
^
-stdin-::1:1: Error: Duplicated member: ?
-- empty case
^
COPY bitwise_test FROM STDIN NULL 'null';
1 1 1 1 1 B0101
3 3 3 null 2 B0100
7 7 7 3 4 B1100
\.
Metacommand \. is not supported
SELECT
BIT_AND(i2) AS "1",
BIT_AND(i4) AS "1",
BIT_AND(i8) AS "1",
BIT_AND(i) AS "?",
BIT_AND(x) AS "0",
BIT_AND(y) AS "0100",
BIT_OR(i2) AS "7",
BIT_OR(i4) AS "7",
BIT_OR(i8) AS "7",
BIT_OR(i) AS "?",
BIT_OR(x) AS "7",
BIT_OR(y) AS "1101",
BIT_XOR(i2) AS "5",
BIT_XOR(i4) AS "5",
BIT_XOR(i8) AS "5",
BIT_XOR(i) AS "?",
BIT_XOR(x) AS "7",
BIT_XOR(y) AS "1101"
FROM bitwise_test;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT
^
-stdin-::1:1: Error: Duplicated member: 1
SELECT
^
--
-- test boolean aggregates
--
-- first test all possible transition and final states
SELECT
-- boolean and transitions
-- null because strict
booland_statefunc(NULL, NULL) IS NULL AS "t",
booland_statefunc(TRUE, NULL) IS NULL AS "t",
booland_statefunc(FALSE, NULL) IS NULL AS "t",
booland_statefunc(NULL, TRUE) IS NULL AS "t",
booland_statefunc(NULL, FALSE) IS NULL AS "t",
-- and actual computations
booland_statefunc(TRUE, TRUE) AS "t",
NOT booland_statefunc(TRUE, FALSE) AS "t",
NOT booland_statefunc(FALSE, TRUE) AS "t",
NOT booland_statefunc(FALSE, FALSE) AS "t";
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
--
^
-stdin-::1:1: Error: Duplicated member: t
--
^
SELECT
-- boolean or transitions
-- null because strict
boolor_statefunc(NULL, NULL) IS NULL AS "t",
boolor_statefunc(TRUE, NULL) IS NULL AS "t",
boolor_statefunc(FALSE, NULL) IS NULL AS "t",
boolor_statefunc(NULL, TRUE) IS NULL AS "t",
boolor_statefunc(NULL, FALSE) IS NULL AS "t",
-- actual computations
boolor_statefunc(TRUE, TRUE) AS "t",
boolor_statefunc(TRUE, FALSE) AS "t",
boolor_statefunc(FALSE, TRUE) AS "t",
NOT boolor_statefunc(FALSE, FALSE) AS "t";
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT
^
-stdin-::1:1: Error: Duplicated member: t
SELECT
^
CREATE TEMPORARY TABLE bool_test(
b1 BOOL,
b2 BOOL,
b3 BOOL,
b4 BOOL);
-- empty case
SELECT
BOOL_AND(b1) AS "n",
BOOL_OR(b3) AS "n"
FROM bool_test;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
-- empty case
^
-stdin-::1:1: Error: Duplicated member: n
-- empty case
^
COPY bool_test FROM STDIN NULL 'null';
TRUE null FALSE null
FALSE TRUE null null
null TRUE FALSE null
\.
Metacommand \. is not supported
SELECT
BOOL_AND(b1) AS "f",
BOOL_AND(b2) AS "t",
BOOL_AND(b3) AS "f",
BOOL_AND(b4) AS "n",
BOOL_AND(NOT b2) AS "f",
BOOL_AND(NOT b3) AS "t"
FROM bool_test;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT
^
-stdin-::1:1: Error: Duplicated member: f
SELECT
^
SELECT
EVERY(b1) AS "f",
EVERY(b2) AS "t",
EVERY(b3) AS "f",
EVERY(b4) AS "n",
EVERY(NOT b2) AS "f",
EVERY(NOT b3) AS "t"
FROM bool_test;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT
^
-stdin-::1:1: Error: Duplicated member: f
SELECT
^
SELECT
BOOL_OR(b1) AS "t",
BOOL_OR(b2) AS "t",
BOOL_OR(b3) AS "f",
BOOL_OR(b4) AS "n",
BOOL_OR(NOT b2) AS "f",
BOOL_OR(NOT b3) AS "t"
FROM bool_test;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
SELECT
^
-stdin-::1:1: Error: Duplicated member: f
SELECT
^
--
-- Test cases that should be optimized into indexscans instead of
-- the generic aggregate implementation.
--
-- Basic cases
explain (costs off)
select min(unique1) from tenk1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
select min(unique1) from tenk1;
explain (costs off)
select max(unique1) from tenk1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select max(unique1) from tenk1;
explain (costs off)
select max(unique1) from tenk1 where unique1 < 42;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select max(unique1) from tenk1 where unique1 < 42;
explain (costs off)
select max(unique1) from tenk1 where unique1 > 42;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select max(unique1) from tenk1 where unique1 > 42;
-- the planner may choose a generic aggregate here if parallel query is
-- enabled, since that plan will be parallel safe and the "optimized"
-- plan, which has almost identical cost, will not be. we want to test
-- the optimized plan, so temporarily disable parallel query.
begin;
set local max_parallel_workers_per_gather = 0;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: VariableSetStmt, not supported name: max_parallel_workers_per_gather
set local max_parallel_workers_per_gather = 0;
^
explain (costs off)
select max(unique1) from tenk1 where unique1 > 42000;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select max(unique1) from tenk1 where unique1 > 42000;
rollback;
-- multi-column index (uses tenk1_thous_tenthous)
explain (costs off)
select max(tenthous) from tenk1 where thousand = 33;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- multi-column index (uses tenk1_thous_tenthous)
^
select max(tenthous) from tenk1 where thousand = 33;
explain (costs off)
select min(tenthous) from tenk1 where thousand = 33;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select min(tenthous) from tenk1 where thousand = 33;
-- check parameter propagation into an indexscan subquery
explain (costs off)
select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
from int4_tbl;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- check parameter propagation into an indexscan subquery
^
select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
from int4_tbl;
-- check some cases that were handled incorrectly in 8.3.0
explain (costs off)
select distinct max(unique2) from tenk1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- check some cases that were handled incorrectly in 8.3.0
^
select distinct max(unique2) from tenk1;
explain (costs off)
select max(unique2) from tenk1 order by 1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select max(unique2) from tenk1 order by 1;
explain (costs off)
select max(unique2) from tenk1 order by max(unique2);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select max(unique2) from tenk1 order by max(unique2);
explain (costs off)
select max(unique2) from tenk1 order by max(unique2)+1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select max(unique2) from tenk1 order by max(unique2)+1;
explain (costs off)
select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
-stdin-:: Error: Parse Sql
-stdin-::1:22: Error: Generator functions are not allowed in: SELECT
select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
^
-- interesting corner case: constant gets optimized into a seqscan
explain (costs off)
select max(100) from tenk1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- interesting corner case: constant gets optimized into a seqscan
^
select max(100) from tenk1;
-- try it on an inheritance tree
create table minmaxtest(f1 int);
create table minmaxtest1() inherits (minmaxtest);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
create table minmaxtest1() inherits (minmaxtest);
^
create table minmaxtest2() inherits (minmaxtest);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
create table minmaxtest2() inherits (minmaxtest);
^
create table minmaxtest3() inherits (minmaxtest);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
create table minmaxtest3() inherits (minmaxtest);
^
create index minmaxtesti on minmaxtest(f1);
create index minmaxtest1i on minmaxtest1(f1);
create index minmaxtest2i on minmaxtest2(f1 desc);
create index minmaxtest3i on minmaxtest3(f1) where f1 is not null;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: partial index is not supported yet
create index minmaxtest3i on minmaxtest3(f1) where f1 is not null;
^
insert into minmaxtest values(11), (12);
insert into minmaxtest1 values(13), (14);
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.minmaxtest1
insert into minmaxtest2 values(15), (16);
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.minmaxtest2
insert into minmaxtest3 values(17), (18);
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.minmaxtest3
explain (costs off)
select min(f1), max(f1) from minmaxtest;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select min(f1), max(f1) from minmaxtest;
-- DISTINCT doesn't do anything useful here, but it shouldn't fail
explain (costs off)
select distinct min(f1), max(f1) from minmaxtest;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- DISTINCT doesn't do anything useful here, but it shouldn't fail
^
select distinct min(f1), max(f1) from minmaxtest;
drop table minmaxtest cascade;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: CASCADE is not implemented
drop table minmaxtest cascade;
^
-- check for correct detection of nested-aggregate errors
select max(min(unique1)) from tenk1;
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: Result
-- check for correct detection of nested-aggregate errors
^
-stdin-::1:1: Fatal: ydb/library/yql/providers/common/mkql/yql_provider_mkql.cpp:364 GetCallable(): requirement Callables.cend() != compiler failed, message: Missed callable: PgAgg
-- check for correct detection of nested-aggregate errors
^
select (select max(min(unique1)) from int8_tbl) from tenk1;
-stdin-:: Fatal: Execution
-stdin-::1:8: Fatal: Execution of node: YtMapReduce!
select (select max(min(unique1)) from int8_tbl) from tenk1;
^
-stdin-::1:8: Fatal: ydb/library/yql/providers/common/mkql/yql_provider_mkql.cpp:364 GetCallable(): requirement Callables.cend() != compiler failed, message: Missed callable: PgAgg
select (select max(min(unique1)) from int8_tbl) from tenk1;
^
--
-- Test removal of redundant GROUP BY columns
--
create temp table t1 (a int, b int, c int, d int, primary key (a, b));
create temp table t2 (x int, y int, z int, primary key (x, y));
create temp table t3 (a int, b int, c int, primary key(a, b) deferrable);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: DEFERRABLE constraints not supported
create temp table t3 (a int, b int, c int, primary key(a, b) deferrable);
^
-- Non-primary-key columns can be removed from GROUP BY
explain (costs off) select * from t1 group by a,b,c,d;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- Non-primary-key columns can be removed from GROUP BY
^
-- No removal can happen if the complete PK is not present in GROUP BY
explain (costs off) select a,c from t1 group by a,c,d;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- No removal can happen if the complete PK is not present in GROUP BY
^
-- Test removal across multiple relations
explain (costs off) select *
from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.y,t2.z;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- Test removal across multiple relations
^
-- Test case where t1 can be optimized but not t2
explain (costs off) select t1.*,t2.x,t2.z
from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.z;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- Test case where t1 can be optimized but not t2
^
-- Cannot optimize when PK is deferrable
explain (costs off) select * from t3 group by a,b,c;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- Cannot optimize when PK is deferrable
^
create temp table t1c () inherits (t1);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
create temp table t1c () inherits (t1);
^
-- Ensure we don't remove any columns when t1 has a child table
explain (costs off) select * from t1 group by a,b,c,d;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- Ensure we don't remove any columns when t1 has a child table
^
-- Okay to remove columns if we're only querying the parent.
explain (costs off) select * from only t1 group by a,b,c,d;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- Okay to remove columns if we're only querying the parent.
^
create temp table p_t1 (
a int,
b int,
c int,
d int,
primary key(a,b)
) partition by list(a);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: PARTITION BY clause not supported
create temp table p_t1 (
^
create temp table p_t1_1 partition of p_t1 for values in(1);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
create temp table p_t1_1 partition of p_t1 for values in(1);
^
create temp table p_t1_2 partition of p_t1 for values in(2);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
create temp table p_t1_2 partition of p_t1 for values in(2);
^
-- Ensure we can remove non-PK columns for partitioned tables.
explain (costs off) select * from p_t1 group by a,b,c,d;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- Ensure we can remove non-PK columns for partitioned tables.
^
drop table t1 cascade;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: CASCADE is not implemented
drop table t1 cascade;
^
drop table t2;
drop table t3;
drop table p_t1;
--
-- Test GROUP BY matching of join columns that are type-coerced due to USING
--
create temp table t1(f1 int, f2 bigint);
create temp table t2(f1 bigint, f22 bigint);
select f1 from t1 left join t2 using (f1) group by f1;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
select f1 from t1 left join t2 using (f1) group by f1;
^
-stdin-::1:1: Error: Can't find column: f1
select f1 from t1 left join t2 using (f1) group by f1;
^
select f1 from t1 left join t2 using (f1) group by t1.f1;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
select f1 from t1 left join t2 using (f1) group by t1.f1;
^
-stdin-::1:1: Error: Can't find column: f1
select f1 from t1 left join t2 using (f1) group by t1.f1;
^
select t1.f1 from t1 left join t2 using (f1) group by t1.f1;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
select t1.f1 from t1 left join t2 using (f1) group by t1.f1;
^
-stdin-::1:1: Error: Can't find column: f1
select t1.f1 from t1 left join t2 using (f1) group by t1.f1;
^
-- only this one should fail:
select t1.f1 from t1 left join t2 using (f1) group by f1;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
-- only this one should fail:
^
-stdin-::1:1: Error: Can't find column: f1
-- only this one should fail:
^
drop table t1, t2;
--
-- Test combinations of DISTINCT and/or ORDER BY
--
select array_agg(a order by b)
from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
-stdin-:: Error: Parse Sql
-stdin-::4:8: Error: FuncCall: unsupported agg_order
select array_agg(a order by b)
^
select array_agg(a order by a)
from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select array_agg(a order by a)
^
select array_agg(a order by a desc)
from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select array_agg(a order by a desc)
^
select array_agg(b order by a desc)
from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select array_agg(b order by a desc)
^
select array_agg(distinct a)
from (values (1),(2),(1),(3),(null),(2)) v(a);
select array_agg(distinct a order by a)
from (values (1),(2),(1),(3),(null),(2)) v(a);
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select array_agg(distinct a order by a)
^
select array_agg(distinct a order by a desc)
from (values (1),(2),(1),(3),(null),(2)) v(a);
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select array_agg(distinct a order by a desc)
^
select array_agg(distinct a order by a desc nulls last)
from (values (1),(2),(1),(3),(null),(2)) v(a);
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select array_agg(distinct a order by a desc nulls last)
^
-- multi-arg aggs, strict/nonstrict, distinct/order by
select aggfstr(a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
-- multi-arg aggs, strict/nonstrict, distinct/order by
^
-stdin-::2:8: Error: At function: PgCall
select aggfstr(a,b,c)
^
-stdin-::2:8: Error: No such proc: aggfstr
select aggfstr(a,b,c)
^
select aggfns(a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
select aggfns(a,b,c)
^
-stdin-::1:8: Error: At function: PgCall
select aggfns(a,b,c)
^
-stdin-::1:8: Error: No such proc: aggfns
select aggfns(a,b,c)
^
select aggfstr(distinct a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: agg_distinct must be set only for aggregate functions
select aggfstr(distinct a,b,c)
^
select aggfns(distinct a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: agg_distinct must be set only for aggregate functions
select aggfns(distinct a,b,c)
^
select aggfstr(distinct a,b,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select aggfstr(distinct a,b,c order by b)
^
select aggfns(distinct a,b,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select aggfns(distinct a,b,c order by b)
^
-- test specific code paths
select aggfns(distinct a,a,c order by c using ~<~,a)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
-stdin-:: Error: Parse Sql
-stdin-::2:8: Error: FuncCall: unsupported agg_order
select aggfns(distinct a,a,c order by c using ~<~,a)
^
select aggfns(distinct a,a,c order by c using ~<~)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select aggfns(distinct a,a,c order by c using ~<~)
^
select aggfns(distinct a,a,c order by a)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select aggfns(distinct a,a,c order by a)
^
select aggfns(distinct a,b,c order by a,c using ~<~,b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
-stdin-:: Error: Parse Sql
-stdin-::1:8: Error: FuncCall: unsupported agg_order
select aggfns(distinct a,b,c order by a,c using ~<~,b)
^
-- check node I/O via view creation and usage, also deparsing logic
create view agg_view1 as
select aggfns(a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
-- check node I/O via view creation and usage, also deparsing logic
^
select * from agg_view1;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.agg_view1
select pg_get_viewdef('agg_view1'::regclass);
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: Result
select pg_get_viewdef('agg_view1'::regclass);
^
-stdin-::1:1: Fatal: ERROR: relation "agg_view1" does not exist
select pg_get_viewdef('agg_view1'::regclass);
^
create or replace view agg_view1 as
select aggfns(distinct a,b,c)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
create or replace view agg_view1 as
^
select * from agg_view1;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.agg_view1
select pg_get_viewdef('agg_view1'::regclass);
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: Result
select pg_get_viewdef('agg_view1'::regclass);
^
-stdin-::1:1: Fatal: ERROR: relation "agg_view1" does not exist
select pg_get_viewdef('agg_view1'::regclass);
^
create or replace view agg_view1 as
select aggfns(distinct a,b,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,3) i;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
create or replace view agg_view1 as
^
select * from agg_view1;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.agg_view1
select pg_get_viewdef('agg_view1'::regclass);
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: Result
select pg_get_viewdef('agg_view1'::regclass);
^
-stdin-::1:1: Fatal: ERROR: relation "agg_view1" does not exist
select pg_get_viewdef('agg_view1'::regclass);
^
create or replace view agg_view1 as
select aggfns(a,b,c order by b+1)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
create or replace view agg_view1 as
^
select * from agg_view1;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.agg_view1
select pg_get_viewdef('agg_view1'::regclass);
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: Result
select pg_get_viewdef('agg_view1'::regclass);
^
-stdin-::1:1: Fatal: ERROR: relation "agg_view1" does not exist
select pg_get_viewdef('agg_view1'::regclass);
^
create or replace view agg_view1 as
select aggfns(a,a,c order by b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
create or replace view agg_view1 as
^
select * from agg_view1;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.agg_view1
select pg_get_viewdef('agg_view1'::regclass);
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: Result
select pg_get_viewdef('agg_view1'::regclass);
^
-stdin-::1:1: Fatal: ERROR: relation "agg_view1" does not exist
select pg_get_viewdef('agg_view1'::regclass);
^
create or replace view agg_view1 as
select aggfns(a,b,c order by c using ~<~)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
create or replace view agg_view1 as
^
select * from agg_view1;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.agg_view1
select pg_get_viewdef('agg_view1'::regclass);
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: Result
select pg_get_viewdef('agg_view1'::regclass);
^
-stdin-::1:1: Fatal: ERROR: relation "agg_view1" does not exist
select pg_get_viewdef('agg_view1'::regclass);
^
create or replace view agg_view1 as
select aggfns(distinct a,b,c order by a,c using ~<~,b)
from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
generate_series(1,2) i;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
create or replace view agg_view1 as
^
select * from agg_view1;
-stdin-:: Fatal: Table metadata loading
-stdin-: