Registering pre-existing tables int4_tbl INT4_TBL tenk1 tenk2 -- -- WINDOW FUNCTIONS -- CREATE TEMPORARY TABLE empsalary ( depname varchar, empno bigint, salary int, enroll_date date ); INSERT INTO empsalary VALUES ('develop', 10, 5200, '2007-08-01'), ('sales', 1, 5000, '2006-10-01'), ('personnel', 5, 3500, '2007-12-10'), ('sales', 4, 4800, '2007-08-08'), ('personnel', 2, 3900, '2006-12-23'), ('develop', 7, 4200, '2008-01-01'), ('develop', 9, 4500, '2008-01-01'), ('sales', 3, 4800, '2007-08-01'), ('develop', 8, 6000, '2006-10-01'), ('develop', 11, 5200, '2007-08-15'); SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary; -- with GROUP BY SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1 GROUP BY four, ten ORDER BY four, ten; SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname); SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; -stdin-:
: Error: Parse Sql -stdin-:
:1:121: Error: Over is not allowed in: ORDER BY SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; ^ -- empty window specification SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10; SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS (); -- no window operation SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten); -- cumulative aggregate SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10; SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10; SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10; SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: Unsupported function: percent_rank SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: Unsupported function: cume_dist SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: Unsupported function: ntile SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10; ^ SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2; ^ -stdin-:
:1:8: Error: Unsupported function: ntile SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2; ^ SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: Expected one argument in functionlag SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: Expected one argument in functionlag SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ SELECT lag(ten, four, 0.7) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT lag(ten, four, 0.7) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT lag(ten, four, 0.7) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten; ^ -stdin-:
:1:8: Error: Expected one argument in functionlag SELECT lag(ten, four, 0.7) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten; ^ SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: Expected one argument in functionlead SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ -stdin-:
:1:8: Error: Expected one argument in functionlead SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; ^ SELECT lead(ten * 2, 1, -1.4) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT lead(ten * 2, 1, -1.4) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT lead(ten * 2, 1, -1.4) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten; ^ -stdin-:
:1:8: Error: Expected one argument in functionlead SELECT lead(ten * 2, 1, -1.4) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten; ^ SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; -- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window. SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s ORDER BY four, ten; SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four ^ -stdin-:
:1:8: Error: Unsupported function: nth_value SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four ^ SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum FROM tenk1 GROUP BY ten, two; SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10; SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) + sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum FROM tenk1 WHERE unique2 < 10; -- opexpr with different windows evaluation. SELECT * FROM( SELECT count(*) OVER (PARTITION BY four ORDER BY ten) + sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total, count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount, sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum FROM tenk1 )sub WHERE total <> fourcount + twosum; SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10; SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten); -- more than one window with GROUP BY SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(sum(salary)) OVER (ORDER BY depname DESC) FROM empsalary GROUP BY depname; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem -- more than one window with GROUP BY ^ -stdin-:
:1:1: Error: Duplicated member: sum -- more than one window with GROUP BY ^ -- identical windows with different names SELECT sum(salary) OVER w1, count(*) OVER w2 FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary); -- subplan SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten) FROM tenk1 s WHERE unique2 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem -- subplan ^ -stdin-:
:2:8: Error: At function: PgWindowCall SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten) ^ -stdin-:
:2:8: Error: Expected one argument in functionlead SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten) ^ -- empty table SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s; -- mixture of agg/wfunc in the same window SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); -- strict aggs SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM( SELECT *, CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus, CASE WHEN AVG(salary) OVER (PARTITION BY depname) < salary THEN 200 END AS depadj FROM empsalary )s; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgSelect, At function: PgSetItem -- strict aggs ^ -stdin-:
:4:13: Error: Star is incompatible to column reference CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus, ^ -stdin-:
:4:70: Error: Star is incompatible to column reference CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus, ^ -- window function over ungrouped agg over empty row set (bug before 9.1) SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42; -- window function with ORDER BY an expression involving aggregates (9.1 bug) select ten, sum(unique1) + sum(unique2) as res, rank() over (order by sum(unique1) + sum(unique2)) as rank from tenk1 group by ten order by ten; -- window and aggregate with GROUP BY expression (9.2 bug) explain (costs off) select first_value(max(x)) over (), y from (select unique1 as x, ten+four as y from tenk1) ss group by y; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 276 -- window and aggregate with GROUP BY expression (9.2 bug) ^ -- test non-default frame specifications SELECT four, ten, sum(ten) over (partition by four order by ten), last_value(ten) over (partition by four order by ten) FROM (select distinct ten, four from tenk1) ss; SELECT four, ten, sum(ten) over (partition by four order by ten range between unbounded preceding and current row), last_value(ten) over (partition by four order by ten range between unbounded preceding and current row) FROM (select distinct ten, four from tenk1) ss; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT four, ten, ^ -stdin-:
:2:16: Error: At function: PgWindow sum(ten) over (partition by four order by ten range between unbounded preceding and current row), ^ -stdin-:
:2:16: Error: Unsupported frame type: range sum(ten) over (partition by four order by ten range between unbounded preceding and current row), ^ -stdin-:
:3:23: Error: At function: PgWindow last_value(ten) over (partition by four order by ten range between unbounded preceding and current row) ^ -stdin-:
:3:23: Error: Unsupported frame type: range last_value(ten) over (partition by four order by ten range between unbounded preceding and current row) ^ SELECT four, ten, sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following), last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following) FROM (select distinct ten, four from tenk1) ss; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT four, ten, ^ -stdin-:
:2:16: Error: At function: PgWindow sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following), ^ -stdin-:
:2:16: Error: Unsupported frame type: range sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following), ^ -stdin-:
:3:23: Error: At function: PgWindow last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following) ^ -stdin-:
:3:23: Error: Unsupported frame type: range last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following) ^ SELECT four, ten/4 as two, sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row), last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row) FROM (select distinct ten, four from tenk1) ss; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT four, ten/4 as two, ^ -stdin-:
:2:18: Error: At function: PgWindow sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row), ^ -stdin-:
:2:18: Error: Unsupported frame type: range sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row), ^ -stdin-:
:3:25: Error: At function: PgWindow last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row) ^ -stdin-:
:3:25: Error: Unsupported frame type: range last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row) ^ SELECT four, ten/4 as two, sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row), last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row) FROM (select distinct ten, four from tenk1) ss; SELECT sum(unique1) over (order by four range between current row and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four range between current row and unbounded following), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four range between current row and unbounded following), ^ -stdin-:
:1:26: Error: Unsupported frame type: range SELECT sum(unique1) over (order by four range between current row and unbounded following), ^ SELECT sum(unique1) over (rows between current row and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10; SELECT sum(unique1) over (rows between 2 preceding and 2 following), unique1, four FROM tenk1 WHERE unique1 < 10; SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude no others), unique1, four FROM tenk1 WHERE unique1 < 10; SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row), ^ -stdin-:
:1:26: Error: Excludes are not supported SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row), ^ SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group), ^ -stdin-:
:1:26: Error: Excludes are not supported SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group), ^ SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties), ^ -stdin-:
:1:26: Error: Excludes are not supported SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties), ^ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), ^ -stdin-:
:1:34: Error: At function: PgWindow SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), ^ -stdin-:
:1:34: Error: Excludes are not supported SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), ^ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), ^ -stdin-:
:1:34: Error: At function: PgWindow SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), ^ -stdin-:
:1:34: Error: Excludes are not supported SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), ^ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), ^ -stdin-:
:1:34: Error: At function: PgWindow SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), ^ -stdin-:
:1:34: Error: Excludes are not supported SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), ^ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), ^ -stdin-:
:1:33: Error: At function: PgWindow SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), ^ -stdin-:
:1:33: Error: Excludes are not supported SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), ^ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), ^ -stdin-:
:1:33: Error: At function: PgWindow SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), ^ -stdin-:
:1:33: Error: Excludes are not supported SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), ^ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), ^ -stdin-:
:1:33: Error: At function: PgWindow SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), ^ -stdin-:
:1:33: Error: Excludes are not supported SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), ^ SELECT sum(unique1) over (rows between 2 preceding and 1 preceding), unique1, four FROM tenk1 WHERE unique1 < 10; SELECT sum(unique1) over (rows between 1 following and 3 following), unique1, four FROM tenk1 WHERE unique1 < 10; SELECT sum(unique1) over (rows between unbounded preceding and 1 following), unique1, four FROM tenk1 WHERE unique1 < 10; SELECT sum(unique1) over (w range between current row and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (w range between current row and unbounded following), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (w range between current row and unbounded following), ^ -stdin-:
:1:26: Error: Window reference is not supported SELECT sum(unique1) over (w range between current row and unbounded following), ^ SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row), unique1, four FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row), ^ -stdin-:
:1:26: Error: Window reference is not supported SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row), ^ SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group), unique1, four FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group), ^ -stdin-:
:1:26: Error: Window reference is not supported SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group), ^ SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties), unique1, four FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties), ^ -stdin-:
:1:26: Error: Window reference is not supported SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties), ^ SELECT first_value(unique1) over w, nth_value(unique1, 2) over w AS nth_2, last_value(unique1) over w, unique1, four FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four range between current row and unbounded following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT first_value(unique1) over w, ^ -stdin-:
:5:13: Error: At function: PgWindow WINDOW w AS (order by four range between current row and unbounded following); ^ -stdin-:
:5:13: Error: Unsupported frame type: range WINDOW w AS (order by four range between current row and unbounded following); ^ SELECT sum(unique1) over (order by unique1 rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING), unique1 FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Parse Sql -stdin-:
:3:8: Error: SubLinks are not allowed in: FRAME rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING), ^ CREATE TEMP VIEW v_window AS SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following) as sum_rows FROM generate_series(1, 10) i; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: expected at least one target column CREATE TEMP VIEW v_window AS ^ SELECT * FROM v_window; -stdin-:
: Fatal: Table metadata loading -stdin-:
: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.v_window SELECT pg_get_viewdef('v_window'); -stdin-:
: Fatal: Execution -stdin-:
:1:1: Fatal: Execution of node: Result SELECT pg_get_viewdef('v_window'); ^ -stdin-:
:1:1: Fatal: ERROR: relation "v_window" does not exist SELECT pg_get_viewdef('v_window'); ^ CREATE OR REPLACE TEMP VIEW v_window AS SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following exclude current row) as sum_rows FROM generate_series(1, 10) i; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: expected at least one target column CREATE OR REPLACE TEMP VIEW v_window AS ^ SELECT * FROM v_window; -stdin-:
: Fatal: Table metadata loading -stdin-:
: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.v_window SELECT pg_get_viewdef('v_window'); -stdin-:
: Fatal: Execution -stdin-:
:1:1: Fatal: Execution of node: Result SELECT pg_get_viewdef('v_window'); ^ -stdin-:
:1:1: Fatal: ERROR: relation "v_window" does not exist SELECT pg_get_viewdef('v_window'); ^ CREATE OR REPLACE TEMP VIEW v_window AS SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following exclude group) as sum_rows FROM generate_series(1, 10) i; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: expected at least one target column CREATE OR REPLACE TEMP VIEW v_window AS ^ SELECT * FROM v_window; -stdin-:
: Fatal: Table metadata loading -stdin-:
: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.v_window SELECT pg_get_viewdef('v_window'); -stdin-:
: Fatal: Execution -stdin-:
:1:1: Fatal: Execution of node: Result SELECT pg_get_viewdef('v_window'); ^ -stdin-:
:1:1: Fatal: ERROR: relation "v_window" does not exist SELECT pg_get_viewdef('v_window'); ^ CREATE OR REPLACE TEMP VIEW v_window AS SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following exclude ties) as sum_rows FROM generate_series(1, 10) i; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: expected at least one target column CREATE OR REPLACE TEMP VIEW v_window AS ^ SELECT * FROM v_window; -stdin-:
: Fatal: Table metadata loading -stdin-:
: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.v_window SELECT pg_get_viewdef('v_window'); -stdin-:
: Fatal: Execution -stdin-:
:1:1: Fatal: Execution of node: Result SELECT pg_get_viewdef('v_window'); ^ -stdin-:
:1:1: Fatal: ERROR: relation "v_window" does not exist SELECT pg_get_viewdef('v_window'); ^ CREATE OR REPLACE TEMP VIEW v_window AS SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following exclude no others) as sum_rows FROM generate_series(1, 10) i; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: expected at least one target column CREATE OR REPLACE TEMP VIEW v_window AS ^ SELECT * FROM v_window; -stdin-:
: Fatal: Table metadata loading -stdin-:
: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.v_window SELECT pg_get_viewdef('v_window'); -stdin-:
: Fatal: Execution -stdin-:
:1:1: Fatal: Execution of node: Result SELECT pg_get_viewdef('v_window'); ^ -stdin-:
:1:1: Fatal: ERROR: relation "v_window" does not exist SELECT pg_get_viewdef('v_window'); ^ CREATE OR REPLACE TEMP VIEW v_window AS SELECT i, sum(i) over (order by i groups between 1 preceding and 1 following) as sum_rows FROM generate_series(1, 10) i; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: expected at least one target column CREATE OR REPLACE TEMP VIEW v_window AS ^ SELECT * FROM v_window; -stdin-:
: Fatal: Table metadata loading -stdin-:
: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.v_window SELECT pg_get_viewdef('v_window'); -stdin-:
: Fatal: Execution -stdin-:
:1:1: Fatal: Execution of node: Result SELECT pg_get_viewdef('v_window'); ^ -stdin-:
:1:1: Fatal: ERROR: relation "v_window" does not exist SELECT pg_get_viewdef('v_window'); ^ DROP VIEW v_window; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: View not found: 'v_window' DROP VIEW v_window; ^ CREATE TEMP VIEW v_window AS SELECT i, min(i) over (order by i range between '1 day' preceding and '10 days' following) as min_i FROM generate_series(now(), now()+'100 days'::interval, '1 hour') i; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: expected at least one target column CREATE TEMP VIEW v_window AS ^ SELECT pg_get_viewdef('v_window'); -stdin-:
: Fatal: Execution -stdin-:
:1:1: Fatal: Execution of node: Result SELECT pg_get_viewdef('v_window'); ^ -stdin-:
:1:1: Fatal: ERROR: relation "v_window" does not exist SELECT pg_get_viewdef('v_window'); ^ -- RANGE offset PRECEDING/FOLLOWING tests SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Expression evaluation -stdin-:
:2:26: Error: At function: EvaluateExpr SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), ^ -stdin-:
: Error: Type annotation -stdin-:
:2:26: Error: At function: Unwrap SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), ^ -stdin-:
:2:58: Error: Can't unwrap PostgreSQL type SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), ^ SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Expression evaluation -stdin-:
:1:26: Error: At function: EvaluateExpr SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:26: Error: At function: Unwrap SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding), ^ -stdin-:
:1:63: Error: Can't unwrap PostgreSQL type SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding), ^ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Expression evaluation -stdin-:
:1:26: Error: At function: EvaluateExpr SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:26: Error: At function: Unwrap SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others), ^ -stdin-:
:1:58: Error: Can't unwrap PostgreSQL type SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others), ^ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Expression evaluation -stdin-:
:1:26: Error: At function: EvaluateExpr SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:26: Error: At function: Unwrap SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row), ^ -stdin-:
:1:58: Error: Can't unwrap PostgreSQL type SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row), ^ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Expression evaluation -stdin-:
:1:26: Error: At function: EvaluateExpr SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:26: Error: At function: Unwrap SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group), ^ -stdin-:
:1:58: Error: Can't unwrap PostgreSQL type SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group), ^ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Expression evaluation -stdin-:
:1:26: Error: At function: EvaluateExpr SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:26: Error: At function: Unwrap SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties), ^ -stdin-:
:1:58: Error: Can't unwrap PostgreSQL type SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties), ^ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Expression evaluation -stdin-:
:1:26: Error: At function: EvaluateExpr SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:26: Error: At function: Unwrap SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties), ^ -stdin-:
:1:58: Error: Can't unwrap PostgreSQL type SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties), ^ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Expression evaluation -stdin-:
:1:26: Error: At function: EvaluateExpr SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:26: Error: At function: Unwrap SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group), ^ -stdin-:
:1:58: Error: Can't unwrap PostgreSQL type SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group), ^ SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Expression evaluation -stdin-:
:1:26: Error: At function: EvaluateExpr SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:26: Error: At function: Unwrap SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following), ^ -stdin-:
:1:79: Error: Can't unwrap PostgreSQL type SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following), ^ SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following exclude current row),unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Expression evaluation -stdin-:
:1:26: Error: At function: EvaluateExpr SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:26: Error: At function: Unwrap SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following ^ -stdin-:
:1:79: Error: Can't unwrap PostgreSQL type SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following ^ select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:25: Error: At function: EvaluateExpr select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:25: Error: At function: Unwrap select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), ^ -stdin-:
:1:71: Error: Can't unwrap PostgreSQL type select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), ^ select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:25: Error: At function: EvaluateExpr select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:25: Error: At function: Unwrap select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), ^ -stdin-:
:1:76: Error: Can't unwrap PostgreSQL type select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), ^ select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:25: Error: At function: EvaluateExpr select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), ^ -stdin-:
: Error: Type annotation -stdin-:
:1:25: Error: At function: Unwrap select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), ^ -stdin-:
:1:76: Error: Can't unwrap PostgreSQL type select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), ^ select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following exclude current row), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:25: Error: At function: EvaluateExpr select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:25: Error: At function: Unwrap select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following ^ -stdin-:
:1:71: Error: Can't unwrap PostgreSQL type select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following ^ select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following exclude group), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:25: Error: At function: EvaluateExpr select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:25: Error: At function: Unwrap select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following ^ -stdin-:
:1:71: Error: Can't unwrap PostgreSQL type select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following ^ select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:25: Error: At function: EvaluateExpr select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:25: Error: At function: Unwrap select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following ^ -stdin-:
:1:71: Error: Can't unwrap PostgreSQL type select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following ^ select first_value(salary) over(order by salary range between 1000 preceding and 1000 following), lead(salary) over(order by salary range between 1000 preceding and 1000 following), nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following), salary from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select first_value(salary) over(order by salary range between 1000 preceding and 1000 following), ^ -stdin-:
:1:32: Error: At function: PgWindow select first_value(salary) over(order by salary range between 1000 preceding and 1000 following), ^ -stdin-:
:1:32: Error: Unsupported frame type: range select first_value(salary) over(order by salary range between 1000 preceding and 1000 following), ^ -stdin-:
:2:19: Error: At function: PgWindow lead(salary) over(order by salary range between 1000 preceding and 1000 following), ^ -stdin-:
:2:19: Error: Unsupported frame type: range lead(salary) over(order by salary range between 1000 preceding and 1000 following), ^ -stdin-:
:3:27: Error: At function: PgWindow nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following), ^ -stdin-:
:3:27: Error: Unsupported frame type: range nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following), ^ select last_value(salary) over(order by salary range between 1000 preceding and 1000 following), lag(salary) over(order by salary range between 1000 preceding and 1000 following), salary from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select last_value(salary) over(order by salary range between 1000 preceding and 1000 following), ^ -stdin-:
:1:31: Error: At function: PgWindow select last_value(salary) over(order by salary range between 1000 preceding and 1000 following), ^ -stdin-:
:1:31: Error: Unsupported frame type: range select last_value(salary) over(order by salary range between 1000 preceding and 1000 following), ^ -stdin-:
:2:18: Error: At function: PgWindow lag(salary) over(order by salary range between 1000 preceding and 1000 following), ^ -stdin-:
:2:18: Error: Unsupported frame type: range lag(salary) over(order by salary range between 1000 preceding and 1000 following), ^ select first_value(salary) over(order by salary range between 1000 following and 3000 following exclude current row), lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties), nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following exclude ties), salary from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select first_value(salary) over(order by salary range between 1000 following and 3000 following ^ -stdin-:
:1:32: Error: At function: PgWindow select first_value(salary) over(order by salary range between 1000 following and 3000 following ^ -stdin-:
:1:32: Error: Excludes are not supported select first_value(salary) over(order by salary range between 1000 following and 3000 following ^ -stdin-:
:3:19: Error: At function: PgWindow lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties), ^ -stdin-:
:3:19: Error: Excludes are not supported lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties), ^ -stdin-:
:4:27: Error: At function: PgWindow nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following ^ -stdin-:
:4:27: Error: Excludes are not supported nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following ^ select last_value(salary) over(order by salary range between 1000 following and 3000 following exclude group), lag(salary) over(order by salary range between 1000 following and 3000 following exclude group), salary from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select last_value(salary) over(order by salary range between 1000 following and 3000 following ^ -stdin-:
:1:31: Error: At function: PgWindow select last_value(salary) over(order by salary range between 1000 following and 3000 following ^ -stdin-:
:1:31: Error: Excludes are not supported select last_value(salary) over(order by salary range between 1000 following and 3000 following ^ -stdin-:
:3:18: Error: At function: PgWindow lag(salary) over(order by salary range between 1000 following and 3000 following exclude group), ^ -stdin-:
:3:18: Error: Excludes are not supported lag(salary) over(order by salary range between 1000 following and 3000 following exclude group), ^ select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following exclude ties), last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:32: Error: At function: EvaluateExpr select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:32: Error: At function: Unwrap select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ -stdin-:
:1:102: Error: Can't unwrap PostgreSQL type select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following exclude ties), last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:32: Error: At function: EvaluateExpr select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:32: Error: At function: Unwrap select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ -stdin-:
:1:102: Error: Can't unwrap PostgreSQL type select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following exclude group), last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following exclude group), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:32: Error: At function: EvaluateExpr select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:32: Error: At function: Unwrap select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ -stdin-:
:1:102: Error: Can't unwrap PostgreSQL type select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following exclude current row), last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following exclude current row), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:32: Error: At function: EvaluateExpr select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:32: Error: At function: Unwrap select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ -stdin-:
:1:102: Error: Can't unwrap PostgreSQL type select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following ^ -- RANGE offset PRECEDING/FOLLOWING with null values select x, y, first_value(y) over w, last_value(y) over w from (select x, x as y from generate_series(1,5) as x union all select null, 42 union all select null, 43) ss window w as (order by x asc nulls first range between 2 preceding and 2 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem -- RANGE offset PRECEDING/FOLLOWING with null values ^ -stdin-:
:10:3: Error: At function: PgWindow (order by x asc nulls first range between 2 preceding and 2 following); ^ -stdin-:
:10:3: Error: Unsupported frame type: range (order by x asc nulls first range between 2 preceding and 2 following); ^ select x, y, first_value(y) over w, last_value(y) over w from (select x, x as y from generate_series(1,5) as x union all select null, 42 union all select null, 43) ss window w as (order by x asc nulls last range between 2 preceding and 2 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select x, y, ^ -stdin-:
:9:3: Error: At function: PgWindow (order by x asc nulls last range between 2 preceding and 2 following); ^ -stdin-:
:9:3: Error: Unsupported frame type: range (order by x asc nulls last range between 2 preceding and 2 following); ^ select x, y, first_value(y) over w, last_value(y) over w from (select x, x as y from generate_series(1,5) as x union all select null, 42 union all select null, 43) ss window w as (order by x desc nulls first range between 2 preceding and 2 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select x, y, ^ -stdin-:
:9:3: Error: At function: PgWindow (order by x desc nulls first range between 2 preceding and 2 following); ^ -stdin-:
:9:3: Error: Unsupported frame type: range (order by x desc nulls first range between 2 preceding and 2 following); ^ select x, y, first_value(y) over w, last_value(y) over w from (select x, x as y from generate_series(1,5) as x union all select null, 42 union all select null, 43) ss window w as (order by x desc nulls last range between 2 preceding and 2 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select x, y, ^ -stdin-:
:9:3: Error: At function: PgWindow (order by x desc nulls last range between 2 preceding and 2 following); ^ -stdin-:
:9:3: Error: Unsupported frame type: range (order by x desc nulls last range between 2 preceding and 2 following); ^ -- Check overflow behavior for various integer sizes select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following) from generate_series(32764, 32766) x; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem -- Check overflow behavior for various integer sizes ^ -stdin-:
:2:30: Error: At function: PgWindow select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following) ^ -stdin-:
:2:30: Error: Unsupported frame type: range select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following) ^ select x, last_value(x) over (order by x::smallint desc range between current row and 2147450885 following) from generate_series(-32766, -32764) x; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select x, last_value(x) over (order by x::smallint desc range between current row and 2147450885 following) ^ -stdin-:
:1:30: Error: At function: PgWindow select x, last_value(x) over (order by x::smallint desc range between current row and 2147450885 following) ^ -stdin-:
:1:30: Error: Unsupported frame type: range select x, last_value(x) over (order by x::smallint desc range between current row and 2147450885 following) ^ select x, last_value(x) over (order by x range between current row and 4 following) from generate_series(2147483644, 2147483646) x; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select x, last_value(x) over (order by x range between current row and 4 following) ^ -stdin-:
:1:30: Error: At function: PgWindow select x, last_value(x) over (order by x range between current row and 4 following) ^ -stdin-:
:1:30: Error: Unsupported frame type: range select x, last_value(x) over (order by x range between current row and 4 following) ^ select x, last_value(x) over (order by x desc range between current row and 5 following) from generate_series(-2147483646, -2147483644) x; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select x, last_value(x) over (order by x desc range between current row and 5 following) ^ -stdin-:
:1:30: Error: At function: PgWindow select x, last_value(x) over (order by x desc range between current row and 5 following) ^ -stdin-:
:1:30: Error: Unsupported frame type: range select x, last_value(x) over (order by x desc range between current row and 5 following) ^ select x, last_value(x) over (order by x range between current row and 4 following) from generate_series(9223372036854775804, 9223372036854775806) x; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select x, last_value(x) over (order by x range between current row and 4 following) ^ -stdin-:
:1:30: Error: At function: PgWindow select x, last_value(x) over (order by x range between current row and 4 following) ^ -stdin-:
:1:30: Error: Unsupported frame type: range select x, last_value(x) over (order by x range between current row and 4 following) ^ select x, last_value(x) over (order by x desc range between current row and 5 following) from generate_series(-9223372036854775806, -9223372036854775804) x; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select x, last_value(x) over (order by x desc range between current row and 5 following) ^ -stdin-:
:1:30: Error: At function: PgWindow select x, last_value(x) over (order by x desc range between current row and 5 following) ^ -stdin-:
:1:30: Error: Unsupported frame type: range select x, last_value(x) over (order by x desc range between current row and 5 following) ^ -- Test in_range for other numeric datatypes create temp table numerics( id int, f_float4 float4, f_float8 float8, f_numeric numeric ); insert into numerics values (0, '-infinity', '-infinity', '-infinity'), (1, -3, -3, -3), (2, -1, -1, -1), (3, 0, 0, 0), (4, 1.1, 1.1, 1.1), (5, 1.12, 1.12, 1.12), (6, 2, 2, 2), (7, 100, 100, 100), (8, 'infinity', 'infinity', 'infinity'), (9, 'NaN', 'NaN', 'NaN'); select id, f_float4, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float4 range between 1 preceding and 1 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select id, f_float4, first_value(id) over w, last_value(id) over w ^ -stdin-:
:3:13: Error: At function: PgWindow window w as (order by f_float4 range between ^ -stdin-:
:3:13: Error: Unsupported frame type: range window w as (order by f_float4 range between ^ select id, f_float4, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float4 range between 1 preceding and 1.1::float4 following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_float4 range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_float4 range between ^ -stdin-:
:4:35: Error: Can't unwrap PostgreSQL type 1 preceding and 1.1::float4 following); ^ select id, f_float4, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float4 range between 'inf' preceding and 'inf' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_float4 range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_float4 range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 'inf' preceding and 'inf' following); ^ select id, f_float4, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float4 range between 'inf' preceding and 'inf' preceding); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_float4 range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_float4 range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 'inf' preceding and 'inf' preceding); ^ select id, f_float4, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float4 range between 'inf' following and 'inf' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_float4 range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_float4 range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 'inf' following and 'inf' following); ^ select id, f_float4, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float4 range between 1.1 preceding and 'NaN' following); -- error, NaN disallowed -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_float4 range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_float4 range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 1.1 preceding and 'NaN' following); -- error, NaN disallowed ^ select id, f_float8, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float8 range between 1 preceding and 1 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select id, f_float8, first_value(id) over w, last_value(id) over w ^ -stdin-:
:3:13: Error: At function: PgWindow window w as (order by f_float8 range between ^ -stdin-:
:3:13: Error: Unsupported frame type: range window w as (order by f_float8 range between ^ select id, f_float8, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float8 range between 1 preceding and 1.1::float8 following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_float8 range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_float8 range between ^ -stdin-:
:4:35: Error: Can't unwrap PostgreSQL type 1 preceding and 1.1::float8 following); ^ select id, f_float8, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float8 range between 'inf' preceding and 'inf' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_float8 range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_float8 range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 'inf' preceding and 'inf' following); ^ select id, f_float8, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float8 range between 'inf' preceding and 'inf' preceding); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_float8 range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_float8 range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 'inf' preceding and 'inf' preceding); ^ select id, f_float8, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float8 range between 'inf' following and 'inf' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_float8 range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_float8 range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 'inf' following and 'inf' following); ^ select id, f_float8, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float8 range between 1.1 preceding and 'NaN' following); -- error, NaN disallowed -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_float8 range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_float8 range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 1.1 preceding and 'NaN' following); -- error, NaN disallowed ^ select id, f_numeric, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_numeric range between 1 preceding and 1 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select id, f_numeric, first_value(id) over w, last_value(id) over w ^ -stdin-:
:3:13: Error: At function: PgWindow window w as (order by f_numeric range between ^ -stdin-:
:3:13: Error: Unsupported frame type: range window w as (order by f_numeric range between ^ select id, f_numeric, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_numeric range between 1 preceding and 1.1::numeric following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_numeric range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_numeric range between ^ -stdin-:
:4:35: Error: Can't unwrap PostgreSQL type 1 preceding and 1.1::numeric following); ^ select id, f_numeric, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_numeric range between 1 preceding and 1.1::float8 following); -- currently unsupported -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_numeric range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_numeric range between ^ -stdin-:
:4:35: Error: Can't unwrap PostgreSQL type 1 preceding and 1.1::float8 following); -- currently unsupported ^ select id, f_numeric, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_numeric range between 'inf' preceding and 'inf' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_numeric range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_numeric range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 'inf' preceding and 'inf' following); ^ select id, f_numeric, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_numeric range between 'inf' preceding and 'inf' preceding); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_numeric range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_numeric range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 'inf' preceding and 'inf' preceding); ^ select id, f_numeric, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_numeric range between 'inf' following and 'inf' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_numeric range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_numeric range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 'inf' following and 'inf' following); ^ select id, f_numeric, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_numeric range between 1.1 preceding and 'NaN' following); -- error, NaN disallowed -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_numeric range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_numeric range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type 1.1 preceding and 'NaN' following); -- error, NaN disallowed ^ -- Test in_range for other datetime datatypes create temp table datetimes( id int, f_time time, f_timetz timetz, f_interval interval, f_timestamptz timestamptz, f_timestamp timestamp ); insert into datetimes values (1, '11:00', '11:00 BST', '1 year', '2000-10-19 10:23:54+01', '2000-10-19 10:23:54'), (2, '12:00', '12:00 BST', '2 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'), (3, '13:00', '13:00 BST', '3 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'), (4, '14:00', '14:00 BST', '4 years', '2002-10-19 10:23:54+01', '2002-10-19 10:23:54'), (5, '15:00', '15:00 BST', '5 years', '2003-10-19 10:23:54+01', '2003-10-19 10:23:54'), (6, '15:00', '15:00 BST', '5 years', '2004-10-19 10:23:54+01', '2004-10-19 10:23:54'), (7, '17:00', '17:00 BST', '7 years', '2005-10-19 10:23:54+01', '2005-10-19 10:23:54'), (8, '18:00', '18:00 BST', '8 years', '2006-10-19 10:23:54+01', '2006-10-19 10:23:54'), (9, '19:00', '19:00 BST', '9 years', '2007-10-19 10:23:54+01', '2007-10-19 10:23:54'), (10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54'); -stdin-:
: Fatal: Execution -stdin-:
:1:1: Fatal: Execution of node: YtFill! insert into datetimes values ^ -stdin-:
:1:1: Fatal: ERROR: invalid input syntax for type time with time zone: "11:00 BST" insert into datetimes values ^ select id, f_time, first_value(id) over w, last_value(id) over w from datetimes window w as (order by f_time range between '70 min'::interval preceding and '2 hours'::interval following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_time range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_time range between ^ -stdin-:
:4:24: Error: Can't unwrap PostgreSQL type '70 min'::interval preceding and '2 hours'::interval following); ^ select id, f_time, first_value(id) over w, last_value(id) over w from datetimes window w as (order by f_time desc range between '70 min' preceding and '2 hours' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_time desc range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_time desc range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type '70 min' preceding and '2 hours' following); ^ select id, f_timetz, first_value(id) over w, last_value(id) over w from datetimes window w as (order by f_timetz range between '70 min'::interval preceding and '2 hours'::interval following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_timetz range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_timetz range between ^ -stdin-:
:4:24: Error: Can't unwrap PostgreSQL type '70 min'::interval preceding and '2 hours'::interval following); ^ select id, f_timetz, first_value(id) over w, last_value(id) over w from datetimes window w as (order by f_timetz desc range between '70 min' preceding and '2 hours' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_timetz desc range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_timetz desc range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type '70 min' preceding and '2 hours' following); ^ select id, f_interval, first_value(id) over w, last_value(id) over w from datetimes window w as (order by f_interval range between '1 year'::interval preceding and '1 year'::interval following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_interval range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_interval range between ^ -stdin-:
:4:24: Error: Can't unwrap PostgreSQL type '1 year'::interval preceding and '1 year'::interval following); ^ select id, f_interval, first_value(id) over w, last_value(id) over w from datetimes window w as (order by f_interval desc range between '1 year' preceding and '1 year' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_interval desc range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_interval desc range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type '1 year' preceding and '1 year' following); ^ select id, f_timestamptz, first_value(id) over w, last_value(id) over w from datetimes window w as (order by f_timestamptz range between '1 year'::interval preceding and '1 year'::interval following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_timestamptz range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_timestamptz range between ^ -stdin-:
:4:24: Error: Can't unwrap PostgreSQL type '1 year'::interval preceding and '1 year'::interval following); ^ select id, f_timestamptz, first_value(id) over w, last_value(id) over w from datetimes window w as (order by f_timestamptz desc range between '1 year' preceding and '1 year' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_timestamptz desc range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_timestamptz desc range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type '1 year' preceding and '1 year' following); ^ select id, f_timestamp, first_value(id) over w, last_value(id) over w from datetimes window w as (order by f_timestamp range between '1 year'::interval preceding and '1 year'::interval following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_timestamp range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_timestamp range between ^ -stdin-:
:4:24: Error: Can't unwrap PostgreSQL type '1 year'::interval preceding and '1 year'::interval following); ^ select id, f_timestamp, first_value(id) over w, last_value(id) over w from datetimes window w as (order by f_timestamp desc range between '1 year' preceding and '1 year' following); -stdin-:
: Error: Expression evaluation -stdin-:
:3:13: Error: At function: EvaluateExpr window w as (order by f_timestamp desc range between ^ -stdin-:
: Error: Type annotation -stdin-:
:3:13: Error: At function: Unwrap window w as (order by f_timestamp desc range between ^ -stdin-:
:4:14: Error: Can't unwrap PostgreSQL type '1 year' preceding and '1 year' following); ^ -- RANGE offset PRECEDING/FOLLOWING error cases select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:2:25: Error: At function: EvaluateExpr select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:2:25: Error: At function: Unwrap select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following ^ -stdin-:
:2:79: Error: Can't unwrap PostgreSQL type select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following ^ select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:25: Error: At function: EvaluateExpr select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:25: Error: At function: Unwrap select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following ^ -stdin-:
:1:50: Error: Can't unwrap PostgreSQL type select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following ^ select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:25: Error: At function: EvaluateExpr select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:25: Error: At function: Unwrap select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following ^ -stdin-:
:1:67: Error: Can't unwrap PostgreSQL type select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following ^ select max(enroll_date) over (order by enroll_date range between 1 preceding and 2 following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select max(enroll_date) over (order by enroll_date range between 1 preceding and 2 following ^ -stdin-:
:1:30: Error: At function: PgWindow select max(enroll_date) over (order by enroll_date range between 1 preceding and 2 following ^ -stdin-:
:1:30: Error: Excludes are not supported select max(enroll_date) over (order by enroll_date range between 1 preceding and 2 following ^ select max(enroll_date) over (order by salary range between -1 preceding and 2 following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select max(enroll_date) over (order by salary range between -1 preceding and 2 following ^ -stdin-:
:1:30: Error: At function: PgWindow select max(enroll_date) over (order by salary range between -1 preceding and 2 following ^ -stdin-:
:1:30: Error: Excludes are not supported select max(enroll_date) over (order by salary range between -1 preceding and 2 following ^ select max(enroll_date) over (order by salary range between 1 preceding and -2 following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select max(enroll_date) over (order by salary range between 1 preceding and -2 following ^ -stdin-:
:1:30: Error: At function: PgWindow select max(enroll_date) over (order by salary range between 1 preceding and -2 following ^ -stdin-:
:1:30: Error: Excludes are not supported select max(enroll_date) over (order by salary range between 1 preceding and -2 following ^ select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:30: Error: At function: EvaluateExpr select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:30: Error: At function: Unwrap select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following ^ -stdin-:
:1:71: Error: Can't unwrap PostgreSQL type select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following ^ select max(enroll_date) over (order by enroll_date range between '1 year'::interval preceding and '-2 years'::interval following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Expression evaluation -stdin-:
:1:30: Error: At function: EvaluateExpr select max(enroll_date) over (order by enroll_date range between '1 year'::interval preceding and '-2 years'::interval following ^ -stdin-:
: Error: Type annotation -stdin-:
:1:30: Error: At function: Unwrap select max(enroll_date) over (order by enroll_date range between '1 year'::interval preceding and '-2 years'::interval following ^ -stdin-:
:1:76: Error: Can't unwrap PostgreSQL type select max(enroll_date) over (order by enroll_date range between '1 year'::interval preceding and '-2 years'::interval following ^ -- GROUPS tests SELECT sum(unique1) over (order by four groups between unbounded preceding and current row), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem -- GROUPS tests ^ -stdin-:
:2:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between unbounded preceding and current row), ^ -stdin-:
:2:26: Error: Unsupported frame type: groups SELECT sum(unique1) over (order by four groups between unbounded preceding and current row), ^ SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following), ^ -stdin-:
:1:26: Error: Unsupported frame type: groups SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following), ^ SELECT sum(unique1) over (order by four groups between current row and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between current row and unbounded following), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between current row and unbounded following), ^ -stdin-:
:1:26: Error: Unsupported frame type: groups SELECT sum(unique1) over (order by four groups between current row and unbounded following), ^ SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following), ^ -stdin-:
:1:26: Error: Unsupported frame type: groups SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following), ^ SELECT sum(unique1) over (order by four groups between 1 following and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between 1 following and unbounded following), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between 1 following and unbounded following), ^ -stdin-:
:1:26: Error: Unsupported frame type: groups SELECT sum(unique1) over (order by four groups between 1 following and unbounded following), ^ SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following), ^ -stdin-:
:1:26: Error: Unsupported frame type: groups SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following), ^ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding), ^ -stdin-:
:1:26: Error: Unsupported frame type: groups SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding), ^ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following), ^ -stdin-:
:1:26: Error: Unsupported frame type: groups SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following), ^ SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following), ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following), ^ -stdin-:
:1:26: Error: Unsupported frame type: groups SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following), ^ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following exclude current row), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following ^ -stdin-:
:1:26: Error: Excludes are not supported SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following ^ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following exclude group), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following ^ -stdin-:
:1:26: Error: Excludes are not supported SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following ^ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following exclude ties), unique1, four FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following ^ -stdin-:
:1:26: Error: Excludes are not supported SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following ^ SELECT sum(unique1) over (partition by ten order by four groups between 0 preceding and 0 following),unique1, four, ten FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (partition by ten ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (partition by ten ^ -stdin-:
:1:26: Error: Unsupported frame type: groups SELECT sum(unique1) over (partition by ten ^ SELECT sum(unique1) over (partition by ten order by four groups between 0 preceding and 0 following exclude current row), unique1, four, ten FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (partition by ten ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (partition by ten ^ -stdin-:
:1:26: Error: Excludes are not supported SELECT sum(unique1) over (partition by ten ^ SELECT sum(unique1) over (partition by ten order by four groups between 0 preceding and 0 following exclude group), unique1, four, ten FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (partition by ten ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (partition by ten ^ -stdin-:
:1:26: Error: Excludes are not supported SELECT sum(unique1) over (partition by ten ^ SELECT sum(unique1) over (partition by ten order by four groups between 0 preceding and 0 following exclude ties), unique1, four, ten FROM tenk1 WHERE unique1 < 10; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT sum(unique1) over (partition by ten ^ -stdin-:
:1:26: Error: At function: PgWindow SELECT sum(unique1) over (partition by ten ^ -stdin-:
:1:26: Error: Excludes are not supported SELECT sum(unique1) over (partition by ten ^ select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), lead(salary) over(order by enroll_date groups between 1 preceding and 1 following), nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following), salary, enroll_date from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), ^ -stdin-:
:1:32: Error: At function: PgWindow select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), ^ -stdin-:
:1:32: Error: Unsupported frame type: groups select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), ^ -stdin-:
:2:19: Error: At function: PgWindow lead(salary) over(order by enroll_date groups between 1 preceding and 1 following), ^ -stdin-:
:2:19: Error: Unsupported frame type: groups lead(salary) over(order by enroll_date groups between 1 preceding and 1 following), ^ -stdin-:
:3:27: Error: At function: PgWindow nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following), ^ -stdin-:
:3:27: Error: Unsupported frame type: groups nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following), ^ select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), lag(salary) over(order by enroll_date groups between 1 preceding and 1 following), salary, enroll_date from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), ^ -stdin-:
:1:31: Error: At function: PgWindow select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), ^ -stdin-:
:1:31: Error: Unsupported frame type: groups select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), ^ -stdin-:
:2:18: Error: At function: PgWindow lag(salary) over(order by enroll_date groups between 1 preceding and 1 following), ^ -stdin-:
:2:18: Error: Unsupported frame type: groups lag(salary) over(order by enroll_date groups between 1 preceding and 1 following), ^ select first_value(salary) over(order by enroll_date groups between 1 following and 3 following exclude current row), lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties), nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following exclude ties), salary, enroll_date from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select first_value(salary) over(order by enroll_date groups between 1 following and 3 following ^ -stdin-:
:1:32: Error: At function: PgWindow select first_value(salary) over(order by enroll_date groups between 1 following and 3 following ^ -stdin-:
:1:32: Error: Excludes are not supported select first_value(salary) over(order by enroll_date groups between 1 following and 3 following ^ -stdin-:
:3:19: Error: At function: PgWindow lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties), ^ -stdin-:
:3:19: Error: Excludes are not supported lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties), ^ -stdin-:
:4:27: Error: At function: PgWindow nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following ^ -stdin-:
:4:27: Error: Excludes are not supported nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following ^ select last_value(salary) over(order by enroll_date groups between 1 following and 3 following exclude group), lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group), salary, enroll_date from empsalary; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select last_value(salary) over(order by enroll_date groups between 1 following and 3 following ^ -stdin-:
:1:31: Error: At function: PgWindow select last_value(salary) over(order by enroll_date groups between 1 following and 3 following ^ -stdin-:
:1:31: Error: Excludes are not supported select last_value(salary) over(order by enroll_date groups between 1 following and 3 following ^ -stdin-:
:3:18: Error: At function: PgWindow lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group), ^ -stdin-:
:3:18: Error: Excludes are not supported lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group), ^ -- Show differences in offset interpretation between ROWS, RANGE, and GROUPS WITH cte (x) AS ( SELECT * FROM generate_series(1, 35, 2) ) SELECT x, (sum(x) over w) FROM cte WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); WITH cte (x) AS ( SELECT * FROM generate_series(1, 35, 2) ) SELECT x, (sum(x) over w) FROM cte WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem WITH cte (x) AS ( ^ -stdin-:
:6:13: Error: At function: PgWindow WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); ^ -stdin-:
:6:13: Error: Unsupported frame type: range WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); ^ WITH cte (x) AS ( SELECT * FROM generate_series(1, 35, 2) ) SELECT x, (sum(x) over w) FROM cte WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem WITH cte (x) AS ( ^ -stdin-:
:6:13: Error: At function: PgWindow WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); ^ -stdin-:
:6:13: Error: Unsupported frame type: groups WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); ^ WITH cte (x) AS ( select 1 union all select 1 union all select 1 union all SELECT * FROM generate_series(5, 49, 2) ) SELECT x, (sum(x) over w) FROM cte WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); WITH cte (x) AS ( select 1 union all select 1 union all select 1 union all SELECT * FROM generate_series(5, 49, 2) ) SELECT x, (sum(x) over w) FROM cte WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem WITH cte (x) AS ( ^ -stdin-:
:7:13: Error: At function: PgWindow WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); ^ -stdin-:
:7:13: Error: Unsupported frame type: range WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); ^ WITH cte (x) AS ( select 1 union all select 1 union all select 1 union all SELECT * FROM generate_series(5, 49, 2) ) SELECT x, (sum(x) over w) FROM cte WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem WITH cte (x) AS ( ^ -stdin-:
:7:13: Error: At function: PgWindow WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); ^ -stdin-:
:7:13: Error: Unsupported frame type: groups WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); ^ -- with UNION SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; -- check some degenerate cases create temp table t1 (f1 int, f2 int8); insert into t1 values (1,1),(1,2),(2,2); select f1, sum(f1) over (partition by f1 range between 1 preceding and 1 following) from t1 where f1 = f2; -- error, must have order by -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select f1, sum(f1) over (partition by f1 ^ -stdin-:
:1:25: Error: At function: PgWindow select f1, sum(f1) over (partition by f1 ^ -stdin-:
:1:25: Error: Unsupported frame type: range select f1, sum(f1) over (partition by f1 ^ explain (costs off) select f1, sum(f1) over (partition by f1 order by f2 range between 1 preceding and 1 following) from t1 where f1 = f2; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 276 explain (costs off) ^ select f1, sum(f1) over (partition by f1 order by f2 range between 1 preceding and 1 following) from t1 where f1 = f2; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select f1, sum(f1) over (partition by f1 order by f2 ^ -stdin-:
:1:25: Error: At function: PgWindow select f1, sum(f1) over (partition by f1 order by f2 ^ -stdin-:
:1:25: Error: Unsupported frame type: range select f1, sum(f1) over (partition by f1 order by f2 ^ select f1, sum(f1) over (partition by f1, f1 order by f2 range between 2 preceding and 1 preceding) from t1 where f1 = f2; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select f1, sum(f1) over (partition by f1, f1 order by f2 ^ -stdin-:
:1:25: Error: At function: PgWindow select f1, sum(f1) over (partition by f1, f1 order by f2 ^ -stdin-:
:1:25: Error: Unsupported frame type: range select f1, sum(f1) over (partition by f1, f1 order by f2 ^ select f1, sum(f1) over (partition by f1, f2 order by f2 range between 1 following and 2 following) from t1 where f1 = f2; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select f1, sum(f1) over (partition by f1, f2 order by f2 ^ -stdin-:
:1:25: Error: At function: PgWindow select f1, sum(f1) over (partition by f1, f2 order by f2 ^ -stdin-:
:1:25: Error: Unsupported frame type: range select f1, sum(f1) over (partition by f1, f2 order by f2 ^ select f1, sum(f1) over (partition by f1 groups between 1 preceding and 1 following) from t1 where f1 = f2; -- error, must have order by -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select f1, sum(f1) over (partition by f1 ^ -stdin-:
:1:25: Error: At function: PgWindow select f1, sum(f1) over (partition by f1 ^ -stdin-:
:1:25: Error: Unsupported frame type: groups select f1, sum(f1) over (partition by f1 ^ explain (costs off) select f1, sum(f1) over (partition by f1 order by f2 groups between 1 preceding and 1 following) from t1 where f1 = f2; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 276 explain (costs off) ^ select f1, sum(f1) over (partition by f1 order by f2 groups between 1 preceding and 1 following) from t1 where f1 = f2; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select f1, sum(f1) over (partition by f1 order by f2 ^ -stdin-:
:1:25: Error: At function: PgWindow select f1, sum(f1) over (partition by f1 order by f2 ^ -stdin-:
:1:25: Error: Unsupported frame type: groups select f1, sum(f1) over (partition by f1 order by f2 ^ select f1, sum(f1) over (partition by f1, f1 order by f2 groups between 2 preceding and 1 preceding) from t1 where f1 = f2; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select f1, sum(f1) over (partition by f1, f1 order by f2 ^ -stdin-:
:1:25: Error: At function: PgWindow select f1, sum(f1) over (partition by f1, f1 order by f2 ^ -stdin-:
:1:25: Error: Unsupported frame type: groups select f1, sum(f1) over (partition by f1, f1 order by f2 ^ select f1, sum(f1) over (partition by f1, f2 order by f2 groups between 1 following and 2 following) from t1 where f1 = f2; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem select f1, sum(f1) over (partition by f1, f2 order by f2 ^ -stdin-:
:1:25: Error: At function: PgWindow select f1, sum(f1) over (partition by f1, f2 order by f2 ^ -stdin-:
:1:25: Error: Unsupported frame type: groups select f1, sum(f1) over (partition by f1, f2 order by f2 ^ -- ordering by a non-integer constant is allowed SELECT rank() OVER (ORDER BY length('abc')); -- can't order by another window function SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())); -stdin-:
: Error: Parse Sql -stdin-:
:2:30: Error: Over is not allowed in: ORDER BY SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())); ^ -- some other errors SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10; -stdin-:
: Error: Parse Sql -stdin-:
:2:31: Error: Over is not allowed in: WHERE SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10; ^ SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10; -stdin-:
: Error: Parse Sql -stdin-:
:1:45: Error: Over is not allowed in: JOIN ON SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10; ^ SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1; -stdin-:
: Fatal: Execution -stdin-:
:1:1: Fatal: Execution of node: YtMap! SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1; ^ -stdin-:
:1:1: Fatal: ydb/library/yql/providers/common/mkql/yql_provider_mkql.cpp:364 GetCallable(): requirement Callables.cend() != compiler failed, message: Missed callable: PgWindowCall SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1; ^ SELECT * FROM rank() OVER (ORDER BY random()); -stdin-:
: Error: Parse Sql -stdin-:
:1:29: Error: ERROR: syntax error at or near "ORDER" SELECT * FROM rank() OVER (ORDER BY random()); ^ DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10; -stdin-:
: Error: Parse Sql -stdin-:
:1:30: Error: Over is not allowed in: WHERE DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10; ^ DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random()); -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: ResTarget: alternative is not implemented yet : 357 DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random()); ^ SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1); ^ -stdin-:
:1:72: Error: Duplicated window name: w SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1); ^ SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1; -stdin-:
: Error: Parse Sql -stdin-:
:1:41: Error: ERROR: syntax error at or near "ORDER" SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1; ^ SELECT count() OVER () FROM tenk1; -stdin-:
: Error: Parse Sql -stdin-:
:1:8: Error: FuncCall: count(*) must be used to call a parameterless aggregate function SELECT count() OVER () FROM tenk1; ^ SELECT generate_series(1, 100) OVER () FROM empsalary; -stdin-:
: Error: Parse Sql -stdin-:
:1:8: Error: Generator functions are not allowed in: SELECT SELECT generate_series(1, 100) OVER () FROM empsalary; ^ SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; ^ -stdin-:
:1:8: Error: Unsupported function: ntile SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; ^ SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; ^ -stdin-:
:1:8: Error: Unsupported function: nth_value SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; ^ -- filter SELECT sum(salary), row_number() OVER (ORDER BY depname), sum( sum(salary) FILTER (WHERE enroll_date > '2007-01-01') ) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum", depname FROM empsalary GROUP BY depname; -stdin-:
: Error: Parse Sql -stdin-:
:2:59: Error: FuncCall: unsupported agg_filter SELECT sum(salary), row_number() OVER (ORDER BY depname), sum( ^ -- Test pushdown of quals into a subquery containing window functions -- pushdown is safe because all PARTITION BY clauses include depname: EXPLAIN (COSTS OFF) SELECT * FROM (SELECT depname, sum(salary) OVER (PARTITION BY depname) depsalary, min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary FROM empsalary) emp WHERE depname = 'sales'; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 276 -- Test pushdown of quals into a subquery containing window functions ^ -- pushdown is unsafe because there's a PARTITION BY clause without depname: EXPLAIN (COSTS OFF) SELECT * FROM (SELECT depname, sum(salary) OVER (PARTITION BY enroll_date) enroll_salary, min(salary) OVER (PARTITION BY depname) depminsalary FROM empsalary) emp WHERE depname = 'sales'; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 276 -- pushdown is unsafe because there's a PARTITION BY clause without depname: ^ -- Test Sort node collapsing EXPLAIN (COSTS OFF) SELECT * FROM (SELECT depname, sum(salary) OVER (PARTITION BY depname order by empno) depsalary, min(salary) OVER (PARTITION BY depname, empno order by enroll_date) depminsalary FROM empsalary) emp WHERE depname = 'sales'; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 276 -- Test Sort node collapsing ^ -- Test Sort node reordering EXPLAIN (COSTS OFF) SELECT lead(1) OVER (PARTITION BY depname ORDER BY salary, enroll_date), lag(1) OVER (PARTITION BY depname ORDER BY salary,enroll_date,empno) FROM empsalary; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 276 -- Test Sort node reordering ^ -- Test incremental sorting EXPLAIN (COSTS OFF) SELECT * FROM (SELECT depname, empno, salary, enroll_date, row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp, row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp FROM empsalary) emp WHERE first_emp = 1 OR last_emp = 1; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 276 -- Test incremental sorting ^ SELECT * FROM (SELECT depname, empno, salary, enroll_date, row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp, row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp FROM empsalary) emp WHERE first_emp = 1 OR last_emp = 1; -- cleanup DROP TABLE empsalary; -- test user-defined window function with named args and default args CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value'; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 261 -- test user-defined window function with named args and default args ^ SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s; -stdin-:
: Error: Parse Sql -stdin-:
:1:8: Error: alternative is not implemented yet : 118 SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four ^ SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four ^ -stdin-:
:1:8: Error: At function: PgWindowCall SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four ^ -stdin-:
:1:8: Error: Unsupported function: nth_value_def SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four ^ -- -- Test the basic moving-aggregate machinery -- -- create aggregates that record the series of transform calls (these are -- intentionally not true inverses) CREATE FUNCTION logging_sfunc_nonstrict(text, anyelement) RETURNS text AS $$ SELECT COALESCE($1, '') || '*' || quote_nullable($2) $$ LANGUAGE SQL IMMUTABLE; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 261 -- ^ CREATE FUNCTION logging_msfunc_nonstrict(text, anyelement) RETURNS text AS $$ SELECT COALESCE($1, '') || '+' || quote_nullable($2) $$ LANGUAGE SQL IMMUTABLE; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 261 CREATE FUNCTION logging_msfunc_nonstrict(text, anyelement) RETURNS text AS ^ CREATE FUNCTION logging_minvfunc_nonstrict(text, anyelement) RETURNS text AS $$ SELECT $1 || '-' || quote_nullable($2) $$ LANGUAGE SQL IMMUTABLE; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 261 CREATE FUNCTION logging_minvfunc_nonstrict(text, anyelement) RETURNS text AS ^ CREATE AGGREGATE logging_agg_nonstrict (anyelement) ( stype = text, sfunc = logging_sfunc_nonstrict, mstype = text, msfunc = logging_msfunc_nonstrict, minvfunc = logging_minvfunc_nonstrict ); -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 255 CREATE AGGREGATE logging_agg_nonstrict (anyelement) ^ CREATE AGGREGATE logging_agg_nonstrict_initcond (anyelement) ( stype = text, sfunc = logging_sfunc_nonstrict, mstype = text, msfunc = logging_msfunc_nonstrict, minvfunc = logging_minvfunc_nonstrict, initcond = 'I', minitcond = 'MI' ); -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 255 CREATE AGGREGATE logging_agg_nonstrict_initcond (anyelement) ^ CREATE FUNCTION logging_sfunc_strict(text, anyelement) RETURNS text AS $$ SELECT $1 || '*' || quote_nullable($2) $$ LANGUAGE SQL STRICT IMMUTABLE; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 261 CREATE FUNCTION logging_sfunc_strict(text, anyelement) RETURNS text AS ^ CREATE FUNCTION logging_msfunc_strict(text, anyelement) RETURNS text AS $$ SELECT $1 || '+' || quote_nullable($2) $$ LANGUAGE SQL STRICT IMMUTABLE; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 261 CREATE FUNCTION logging_msfunc_strict(text, anyelement) RETURNS text AS ^ CREATE FUNCTION logging_minvfunc_strict(text, anyelement) RETURNS text AS $$ SELECT $1 || '-' || quote_nullable($2) $$ LANGUAGE SQL STRICT IMMUTABLE; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 261 CREATE FUNCTION logging_minvfunc_strict(text, anyelement) RETURNS text AS ^ CREATE AGGREGATE logging_agg_strict (text) ( stype = text, sfunc = logging_sfunc_strict, mstype = text, msfunc = logging_msfunc_strict, minvfunc = logging_minvfunc_strict ); -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 255 CREATE AGGREGATE logging_agg_strict (text) ^ CREATE AGGREGATE logging_agg_strict_initcond (anyelement) ( stype = text, sfunc = logging_sfunc_strict, mstype = text, msfunc = logging_msfunc_strict, minvfunc = logging_minvfunc_strict, initcond = 'I', minitcond = 'MI' ); -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 255 CREATE AGGREGATE logging_agg_strict_initcond (anyelement) ^ -- test strict and non-strict cases SELECT p::text || ',' || i::text || ':' || COALESCE(v::text, 'NULL') AS row, logging_agg_nonstrict(v) over wnd as nstrict, logging_agg_nonstrict_initcond(v) over wnd as nstrict_init, logging_agg_strict(v::text) over wnd as strict, logging_agg_strict_initcond(v) over wnd as strict_init FROM (VALUES (1, 1, NULL), (1, 2, 'a'), (1, 3, 'b'), (1, 4, NULL), (1, 5, NULL), (1, 6, 'c'), (2, 1, NULL), (2, 2, 'x'), (3, 1, 'z') ) AS t(p, i, v) WINDOW wnd AS (PARTITION BY P ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY p, i; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem -- test strict and non-strict cases ^ -stdin-:
:1:1: Error: At function: PgResultItem -- test strict and non-strict cases ^ -stdin-:
:4:2: Error: At function: PgWindowCall logging_agg_nonstrict(v) over wnd as nstrict, ^ -stdin-:
:4:2: Error: Unsupported function: logging_agg_nonstrict logging_agg_nonstrict(v) over wnd as nstrict, ^ -stdin-:
:1:1: Error: At function: PgResultItem -- test strict and non-strict cases ^ -stdin-:
:5:2: Error: At function: PgWindowCall logging_agg_nonstrict_initcond(v) over wnd as nstrict_init, ^ -stdin-:
:5:2: Error: Unsupported function: logging_agg_nonstrict_initcond logging_agg_nonstrict_initcond(v) over wnd as nstrict_init, ^ -stdin-:
:1:1: Error: At function: PgResultItem -- test strict and non-strict cases ^ -stdin-:
:6:2: Error: At function: PgWindowCall logging_agg_strict(v::text) over wnd as strict, ^ -stdin-:
:6:2: Error: Unsupported function: logging_agg_strict logging_agg_strict(v::text) over wnd as strict, ^ -stdin-:
:1:1: Error: At function: PgResultItem -- test strict and non-strict cases ^ -stdin-:
:7:2: Error: At function: PgWindowCall logging_agg_strict_initcond(v) over wnd as strict_init ^ -stdin-:
:7:2: Error: Unsupported function: logging_agg_strict_initcond logging_agg_strict_initcond(v) over wnd as strict_init ^ -- and again, but with filter SELECT p::text || ',' || i::text || ':' || CASE WHEN f THEN COALESCE(v::text, 'NULL') ELSE '-' END as row, logging_agg_nonstrict(v) filter(where f) over wnd as nstrict_filt, logging_agg_nonstrict_initcond(v) filter(where f) over wnd as nstrict_init_filt, logging_agg_strict(v::text) filter(where f) over wnd as strict_filt, logging_agg_strict_initcond(v) filter(where f) over wnd as strict_init_filt FROM (VALUES (1, 1, true, NULL), (1, 2, false, 'a'), (1, 3, true, 'b'), (1, 4, false, NULL), (1, 5, false, NULL), (1, 6, false, 'c'), (2, 1, false, NULL), (2, 2, true, 'x'), (3, 1, true, 'z') ) AS t(p, i, f, v) WINDOW wnd AS (PARTITION BY p ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY p, i; -stdin-:
: Error: Parse Sql -stdin-:
:5:2: Error: FuncCall: unsupported agg_filter logging_agg_nonstrict(v) filter(where f) over wnd as nstrict_filt, ^ -- test that volatile arguments disable moving-aggregate mode SELECT i::text || ':' || COALESCE(v::text, 'NULL') as row, logging_agg_strict(v::text) over wnd as inverse, logging_agg_strict(v::text || CASE WHEN random() < 0 then '?' ELSE '' END) over wnd as noinverse FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c') ) AS t(i, v) WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY i; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem -- test that volatile arguments disable moving-aggregate mode ^ -stdin-:
:1:1: Error: At function: PgResultItem -- test that volatile arguments disable moving-aggregate mode ^ -stdin-:
:4:2: Error: At function: PgWindowCall logging_agg_strict(v::text) ^ -stdin-:
:4:2: Error: Unsupported function: logging_agg_strict logging_agg_strict(v::text) ^ -stdin-:
:1:1: Error: At function: PgResultItem -- test that volatile arguments disable moving-aggregate mode ^ -stdin-:
:6:2: Error: At function: PgWindowCall logging_agg_strict(v::text || CASE WHEN random() < 0 then '?' ELSE '' END) ^ -stdin-:
:6:2: Error: Unsupported function: logging_agg_strict logging_agg_strict(v::text || CASE WHEN random() < 0 then '?' ELSE '' END) ^ SELECT i::text || ':' || COALESCE(v::text, 'NULL') as row, logging_agg_strict(v::text) filter(where true) over wnd as inverse, logging_agg_strict(v::text) filter(where random() >= 0) over wnd as noinverse FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c') ) AS t(i, v) WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ORDER BY i; -stdin-:
: Error: Parse Sql -stdin-:
:3:2: Error: FuncCall: unsupported agg_filter logging_agg_strict(v::text) filter(where true) ^ -- test that non-overlapping windows don't use inverse transitions SELECT logging_agg_strict(v::text) OVER wnd FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c') ) AS t(i, v) WINDOW wnd AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW) ORDER BY i; -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem -- test that non-overlapping windows don't use inverse transitions ^ -stdin-:
:3:2: Error: At function: PgWindowCall logging_agg_strict(v::text) OVER wnd ^ -stdin-:
:3:2: Error: Unsupported function: logging_agg_strict logging_agg_strict(v::text) OVER wnd ^ -- test that returning NULL from the inverse transition functions -- restarts the aggregation from scratch. The second aggregate is supposed -- to test cases where only some aggregates restart, the third one checks -- that one aggregate restarting doesn't cause others to restart. CREATE FUNCTION sum_int_randrestart_minvfunc(int4, int4) RETURNS int4 AS $$ SELECT CASE WHEN random() < 0.2 THEN NULL ELSE $1 - $2 END $$ LANGUAGE SQL STRICT; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 261 -- test that returning NULL from the inverse transition functions ^ CREATE AGGREGATE sum_int_randomrestart (int4) ( stype = int4, sfunc = int4pl, mstype = int4, msfunc = int4pl, minvfunc = sum_int_randrestart_minvfunc ); -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 255 CREATE AGGREGATE sum_int_randomrestart (int4) ^ WITH vs AS ( SELECT i, (random() * 100)::int4 AS v FROM generate_series(1, 100) AS i ), sum_following AS ( SELECT i, SUM(v) OVER (ORDER BY i DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s FROM vs ) SELECT DISTINCT sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1, -sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2, 100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3 FROM vs JOIN sum_following ON sum_following.i = vs.i WINDOW fwd AS ( ORDER BY vs.i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ); -stdin-:
: Error: Type annotation -stdin-:
:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem WITH ^ -stdin-:
:1:1: Error: At function: PgResultItem WITH ^ -stdin-:
:12:18: Error: At function: PgOp sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1, ^ -stdin-:
:12:20: Error: At function: PgWindowCall sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1, ^ -stdin-:
:12:20: Error: Unsupported function: sum_int_randomrestart sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1, ^ -stdin-:
:1:1: Error: At function: PgResultItem WITH ^ -stdin-:
:13:19: Error: At function: PgOp -sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2, ^ -stdin-:
:13:21: Error: At function: PgWindowCall -sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2, ^ -stdin-:
:13:21: Error: Unsupported function: sum_int_randomrestart -sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2, ^ -stdin-:
:1:1: Error: At function: PgResultItem WITH ^ -stdin-:
:14:19: Error: At function: PgOp 100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3 ^ -stdin-:
:14:21: Error: At function: PgCall 100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3 ^ -stdin-:
:14:28: Error: At function: PgWindowCall 100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3 ^ -stdin-:
:14:28: Error: Unsupported function: logging_agg_nonstrict 100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3 ^ -- -- Test various built-in aggregates that have moving-aggregate support -- -- test inverse transition functions handle NULLs properly SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v); SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v); SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v); SELECT i,SUM(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v); SELECT i,SUM(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v); SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1.01),(2,2),(3,3)) v(i,n); SELECT i,COUNT(v) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); SELECT VAR_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VAR_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VAR_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VAR_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VAR_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VARIANCE(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT STDDEV_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); SELECT STDDEV_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); SELECT STDDEV_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); SELECT STDDEV_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); SELECT STDDEV_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n); SELECT STDDEV(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n); -- test that inverse transition functions work with various frame options SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW) FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v); SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v); -- ensure aggregate over numeric properly recovers from NaN values SELECT a, b, SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b); -- It might be tempting for someone to add an inverse trans function for -- float and double precision. This should not be done as it can give incorrect -- results. This test should fail if anyone ever does this without thinking too -- hard about it. SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),'999999999999999999999D9') FROM (VALUES(1,1e20),(2,1)) n(i,n); SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b) WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING); -- Tests for problems with failure to walk or mutate expressions -- within window frame clauses. -- test walker (fails with collation error if expressions are not walked) SELECT array_agg(i) OVER w FROM generate_series(1,5) i WINDOW w AS (ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW); -stdin-:
: Error: Expression evaluation -stdin-:
:6:13: Error: At function: EvaluateExpr WINDOW w AS (ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW); ^ -stdin-:
: Error: Type annotation -stdin-:
:6:13: Error: At function: Unwrap WINDOW w AS (ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW); ^ -stdin-:
:6:59: Error: Can't unwrap PostgreSQL type WINDOW w AS (ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW); ^ -- test mutator (fails when inlined if expressions are not mutated) CREATE FUNCTION pg_temp.f(group_size BIGINT) RETURNS SETOF integer[] AS $$ SELECT array_agg(s) OVER w FROM generate_series(1,5) s WINDOW w AS (ORDER BY s ROWS BETWEEN CURRENT ROW AND GROUP_SIZE FOLLOWING) $$ LANGUAGE SQL STABLE; -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 261 -- test mutator (fails when inlined if expressions are not mutated) ^ EXPLAIN (costs off) SELECT * FROM pg_temp.f(2); -stdin-:
: Error: Parse Sql -stdin-:
:1:1: Error: RawStmt: alternative is not implemented yet : 276 EXPLAIN (costs off) SELECT * FROM pg_temp.f(2); ^ SELECT * FROM pg_temp.f(2); -stdin-:
: Error: Parse Sql -stdin-:
:1:15: Error: FuncCall: expected pg_catalog, but got: pg_temp SELECT * FROM pg_temp.f(2); ^