Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit71ba45a

Browse files
committed
Add tests for UNBOUNDED syntax ambiguity
There is a syntactic ambiguity in the SQL standard. Since UNBOUNDEDis a non-reserved word, it could be the name of a function parameterand be used as an expression. There is a grammar hack to resolve suchcases 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 parent3788c66 commit71ba45a

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 was
472472
(order by xdesc nulls last range between2 precedingand2 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+
CREATEFUNCTIONunbounded_syntax_test1a(xint) RETURNS TABLE (aint, bint, cint)
481+
LANGUAGE SQL
482+
BEGIN ATOMIC
483+
SELECTsum(unique1) over (rows between x precedingand x following),
484+
unique1, four
485+
FROM tenk1WHERE unique1<10;
486+
END;
487+
488+
CREATEFUNCTIONunbounded_syntax_test1b(xint) RETURNS TABLE (aint, bint, cint)
489+
LANGUAGE SQL
490+
AS $$
491+
SELECTsum(unique1) over (rows between x precedingand x following),
492+
unique1, four
493+
FROM tenk1WHERE 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+
CREATEFUNCTIONunbounded_syntax_test2a(unboundedint) RETURNS TABLE (aint, bint, cint)
501+
LANGUAGE SQL
502+
BEGIN ATOMIC
503+
SELECTsum(unique1) over (rows between unbounded precedingand unbounded following),
504+
unique1, four
505+
FROM tenk1WHERE unique1<10;
506+
END;
507+
508+
CREATEFUNCTIONunbounded_syntax_test2b(unboundedint) RETURNS TABLE (aint, bint, cint)
509+
LANGUAGE SQL
510+
AS $$
511+
SELECTsum(unique1) over (rows between unbounded precedingand unbounded following),
512+
unique1, four
513+
FROM tenk1WHERE 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+
DROPFUNCTION 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+
CREATEFUNCTIONunbounded(xint) RETURNSint LANGUAGE SQL IMMUTABLE RETURN x;
525+
526+
SELECTsum(unique1) over (rows between1 precedingand1 following),
527+
unique1, four
528+
FROM tenk1WHERE unique1<10;
529+
530+
SELECTsum(unique1) over (rows between unbounded(1) precedingand unbounded(1) following),
531+
unique1, four
532+
FROM tenk1WHERE unique1<10;
533+
534+
SELECTsum(unique1) over (rows betweenunbounded.x precedingandunbounded.x following),
535+
unique1, four
536+
FROM tenk1, (values (1))as unbounded(x)WHERE unique1<10;
537+
538+
DROPFUNCTION 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 rowand2147450884 following)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp