Skip to content

Commit 71ba45a

Browse files
peterepetere
committed
Add tests for UNBOUNDED syntax ambiguity
There is a syntactic ambiguity in the SQL standard. Since UNBOUNDED is a non-reserved word, it could be the name of a function parameter and be used as an expression. There is a grammar hack to resolve such cases as the keyword. Add some tests to record this behavior. Reviewed-by: Heikki Linnakangas <hlinnaka@iki.fi> Discussion: https://www.postgresql.org/message-id/flat/b2a09a77-3c8f-7c68-c9b7-824054f87d98%40enterprisedb.com
1 parent 3788c66 commit 71ba45a

File tree

2 files changed

+206
-0
lines changed

2 files changed

+206
-0
lines changed

‎src/test/regress/expected/window.out

Lines changed: 140 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1839,6 +1839,146 @@ window w as
18391839
| 43 | 42 | 43
18401840
(7 rows)
18411841

1842+
-- There is a syntactic ambiguity in the SQL standard. Since
1843+
-- UNBOUNDED is a non-reserved word, it could be the name of a
1844+
-- function parameter and be used as an expression. There is a
1845+
-- grammar hack to resolve such cases as the keyword. The following
1846+
-- tests record this behavior.
1847+
CREATE FUNCTION unbounded_syntax_test1a(x int) RETURNS TABLE (a int, b int, c int)
1848+
LANGUAGE SQL
1849+
BEGIN ATOMIC
1850+
SELECT sum(unique1) over (rows between x preceding and x following),
1851+
unique1, four
1852+
FROM tenk1 WHERE unique1 < 10;
1853+
END;
1854+
CREATE FUNCTION unbounded_syntax_test1b(x int) RETURNS TABLE (a int, b int, c int)
1855+
LANGUAGE SQL
1856+
AS $$
1857+
SELECT sum(unique1) over (rows between x preceding and x following),
1858+
unique1, four
1859+
FROM tenk1 WHERE unique1 < 10;
1860+
$$;
1861+
-- These will apply the argument to the window specification inside the function.
1862+
SELECT * FROM unbounded_syntax_test1a(2);
1863+
a | b | c
1864+
----+---+---
1865+
7 | 4 | 0
1866+
13 | 2 | 2
1867+
22 | 1 | 1
1868+
26 | 6 | 2
1869+
29 | 9 | 1
1870+
31 | 8 | 0
1871+
32 | 5 | 1
1872+
23 | 3 | 3
1873+
15 | 7 | 3
1874+
10 | 0 | 0
1875+
(10 rows)
1876+
1877+
SELECT * FROM unbounded_syntax_test1b(2);
1878+
a | b | c
1879+
----+---+---
1880+
7 | 4 | 0
1881+
13 | 2 | 2
1882+
22 | 1 | 1
1883+
26 | 6 | 2
1884+
29 | 9 | 1
1885+
31 | 8 | 0
1886+
32 | 5 | 1
1887+
23 | 3 | 3
1888+
15 | 7 | 3
1889+
10 | 0 | 0
1890+
(10 rows)
1891+
1892+
CREATE FUNCTION unbounded_syntax_test2a(unbounded int) RETURNS TABLE (a int, b int, c int)
1893+
LANGUAGE SQL
1894+
BEGIN ATOMIC
1895+
SELECT sum(unique1) over (rows between unbounded preceding and unbounded following),
1896+
unique1, four
1897+
FROM tenk1 WHERE unique1 < 10;
1898+
END;
1899+
CREATE FUNCTION unbounded_syntax_test2b(unbounded int) RETURNS TABLE (a int, b int, c int)
1900+
LANGUAGE SQL
1901+
AS $$
1902+
SELECT sum(unique1) over (rows between unbounded preceding and unbounded following),
1903+
unique1, four
1904+
FROM tenk1 WHERE unique1 < 10;
1905+
$$;
1906+
-- These will not apply the argument but instead treat UNBOUNDED as a keyword.
1907+
SELECT * FROM unbounded_syntax_test2a(2);
1908+
a | b | c
1909+
----+---+---
1910+
45 | 4 | 0
1911+
45 | 2 | 2
1912+
45 | 1 | 1
1913+
45 | 6 | 2
1914+
45 | 9 | 1
1915+
45 | 8 | 0
1916+
45 | 5 | 1
1917+
45 | 3 | 3
1918+
45 | 7 | 3
1919+
45 | 0 | 0
1920+
(10 rows)
1921+
1922+
SELECT * FROM unbounded_syntax_test2b(2);
1923+
a | b | c
1924+
----+---+---
1925+
45 | 4 | 0
1926+
45 | 2 | 2
1927+
45 | 1 | 1
1928+
45 | 6 | 2
1929+
45 | 9 | 1
1930+
45 | 8 | 0
1931+
45 | 5 | 1
1932+
45 | 3 | 3
1933+
45 | 7 | 3
1934+
45 | 0 | 0
1935+
(10 rows)
1936+
1937+
DROP FUNCTION unbounded_syntax_test1a, unbounded_syntax_test1b,
1938+
unbounded_syntax_test2a, unbounded_syntax_test2b;
1939+
-- Other tests with token UNBOUNDED in potentially problematic position
1940+
CREATE FUNCTION unbounded(x int) RETURNS int LANGUAGE SQL IMMUTABLE RETURN x;
1941+
SELECT sum(unique1) over (rows between 1 preceding and 1 following),
1942+
unique1, four
1943+
FROM tenk1 WHERE unique1 < 10;
1944+
sum | unique1 | four
1945+
-----+---------+------
1946+
6 | 4 | 0
1947+
7 | 2 | 2
1948+
9 | 1 | 1
1949+
16 | 6 | 2
1950+
23 | 9 | 1
1951+
22 | 8 | 0
1952+
16 | 5 | 1
1953+
15 | 3 | 3
1954+
10 | 7 | 3
1955+
7 | 0 | 0
1956+
(10 rows)
1957+
1958+
SELECT sum(unique1) over (rows between unbounded(1) preceding and unbounded(1) following),
1959+
unique1, four
1960+
FROM tenk1 WHERE unique1 < 10;
1961+
sum | unique1 | four
1962+
-----+---------+------
1963+
6 | 4 | 0
1964+
7 | 2 | 2
1965+
9 | 1 | 1
1966+
16 | 6 | 2
1967+
23 | 9 | 1
1968+
22 | 8 | 0
1969+
16 | 5 | 1
1970+
15 | 3 | 3
1971+
10 | 7 | 3
1972+
7 | 0 | 0
1973+
(10 rows)
1974+
1975+
SELECT sum(unique1) over (rows between unbounded.x preceding and unbounded.x following),
1976+
unique1, four
1977+
FROM tenk1, (values (1)) as unbounded(x) WHERE unique1 < 10;
1978+
ERROR: argument of ROWS must not contain variables
1979+
LINE 1: SELECT sum(unique1) over (rows between unbounded.x preceding...
1980+
^
1981+
DROP FUNCTION unbounded;
18421982
-- Check overflow behavior for various integer sizes
18431983
select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following)
18441984
from generate_series(32764, 32766) x;

‎src/test/regress/sql/window.sql

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -471,6 +471,72 @@ from
471471
window w as
472472
(order by x desc nulls last range between 2 preceding and 2 following);
473473

474+
-- There is a syntactic ambiguity in the SQL standard. Since
475+
-- UNBOUNDED is a non-reserved word, it could be the name of a
476+
-- function parameter and be used as an expression. There is a
477+
-- grammar hack to resolve such cases as the keyword. The following
478+
-- tests record this behavior.
479+
480+
CREATE FUNCTION unbounded_syntax_test1a(x int) RETURNS TABLE (a int, b int, c int)
481+
LANGUAGE SQL
482+
BEGIN ATOMIC
483+
SELECT sum(unique1) over (rows between x preceding and x following),
484+
unique1, four
485+
FROM tenk1 WHERE unique1 < 10;
486+
END;
487+
488+
CREATE FUNCTION unbounded_syntax_test1b(x int) RETURNS TABLE (a int, b int, c int)
489+
LANGUAGE SQL
490+
AS $$
491+
SELECT sum(unique1) over (rows between x preceding and x following),
492+
unique1, four
493+
FROM tenk1 WHERE unique1 < 10;
494+
$$;
495+
496+
-- These will apply the argument to the window specification inside the function.
497+
SELECT * FROM unbounded_syntax_test1a(2);
498+
SELECT * FROM unbounded_syntax_test1b(2);
499+
500+
CREATE FUNCTION unbounded_syntax_test2a(unbounded int) RETURNS TABLE (a int, b int, c int)
501+
LANGUAGE SQL
502+
BEGIN ATOMIC
503+
SELECT sum(unique1) over (rows between unbounded preceding and unbounded following),
504+
unique1, four
505+
FROM tenk1 WHERE unique1 < 10;
506+
END;
507+
508+
CREATE FUNCTION unbounded_syntax_test2b(unbounded int) RETURNS TABLE (a int, b int, c int)
509+
LANGUAGE SQL
510+
AS $$
511+
SELECT sum(unique1) over (rows between unbounded preceding and unbounded following),
512+
unique1, four
513+
FROM tenk1 WHERE unique1 < 10;
514+
$$;
515+
516+
-- These will not apply the argument but instead treat UNBOUNDED as a keyword.
517+
SELECT * FROM unbounded_syntax_test2a(2);
518+
SELECT * FROM unbounded_syntax_test2b(2);
519+
520+
DROP FUNCTION unbounded_syntax_test1a, unbounded_syntax_test1b,
521+
unbounded_syntax_test2a, unbounded_syntax_test2b;
522+
523+
-- Other tests with token UNBOUNDED in potentially problematic position
524+
CREATE FUNCTION unbounded(x int) RETURNS int LANGUAGE SQL IMMUTABLE RETURN x;
525+
526+
SELECT sum(unique1) over (rows between 1 preceding and 1 following),
527+
unique1, four
528+
FROM tenk1 WHERE unique1 < 10;
529+
530+
SELECT sum(unique1) over (rows between unbounded(1) preceding and unbounded(1) following),
531+
unique1, four
532+
FROM tenk1 WHERE unique1 < 10;
533+
534+
SELECT sum(unique1) over (rows between unbounded.x preceding and unbounded.x following),
535+
unique1, four
536+
FROM tenk1, (values (1)) as unbounded(x) WHERE unique1 < 10;
537+
538+
DROP FUNCTION unbounded;
539+
474540
-- Check overflow behavior for various integer sizes
475541

476542
select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following)

0 commit comments

Comments
 (0)