As many will be aware, there is a syntactic ambiguity in the SQL
standard regarding the keyword UNBOUNDED. 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.
I brought this issue to the SQL standard working group, and a fix has
been agreed. (Since long-standing syntax obviously can't be changed,
the fix is basically just an additional rule saying, "if you see this,
it means the keyword".) While working on that, I wrote a few test cases
to explore this and check how PostgreSQL actually handles this. I
figure these test cases are worth committing so that we have a record of
this and future grammar refactorings can maintain the behavior.
From 59cbb06dc1bd58d76652916edffe69304ebbc797 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 24 Jun 2021 10:53:32 +0200
Subject: [PATCH] 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.
---
src/test/regress/expected/window.out | 140 +++++++++++++++++++++++++++
src/test/regress/sql/window.sql | 66 +++++++++++++
2 files changed, 206 insertions(+)
diff --git a/src/test/regress/expected/window.out
b/src/test/regress/expected/window.out
index 19e2ac518a..bb9ff7f07b 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -1839,6 +1839,146 @@ window w as
| 43 | 42 | 43
(7 rows)
+-- 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. The following
+-- tests record this behavior.
+CREATE FUNCTION unbounded_syntax_test1a(x int) RETURNS TABLE (a int, b int, c
int)
+LANGUAGE SQL
+BEGIN ATOMIC
+ SELECT sum(unique1) over (rows between x preceding and x following),
+ unique1, four
+ FROM tenk1 WHERE unique1 < 10;
+END;
+CREATE FUNCTION unbounded_syntax_test1b(x int) RETURNS TABLE (a int, b int, c
int)
+LANGUAGE SQL
+AS $$
+ SELECT sum(unique1) over (rows between x preceding and x following),
+ unique1, four
+ FROM tenk1 WHERE unique1 < 10;
+$$;
+-- These will apply the argument to the window specification inside the
function.
+SELECT * FROM unbounded_syntax_test1a(2);
+ a | b | c
+----+---+---
+ 7 | 4 | 0
+ 13 | 2 | 2
+ 22 | 1 | 1
+ 26 | 6 | 2
+ 29 | 9 | 1
+ 31 | 8 | 0
+ 32 | 5 | 1
+ 23 | 3 | 3
+ 15 | 7 | 3
+ 10 | 0 | 0
+(10 rows)
+
+SELECT * FROM unbounded_syntax_test1b(2);
+ a | b | c
+----+---+---
+ 7 | 4 | 0
+ 13 | 2 | 2
+ 22 | 1 | 1
+ 26 | 6 | 2
+ 29 | 9 | 1
+ 31 | 8 | 0
+ 32 | 5 | 1
+ 23 | 3 | 3
+ 15 | 7 | 3
+ 10 | 0 | 0
+(10 rows)
+
+CREATE FUNCTION unbounded_syntax_test2a(unbounded int) RETURNS TABLE (a int, b
int, c int)
+LANGUAGE SQL
+BEGIN ATOMIC
+ SELECT sum(unique1) over (rows between unbounded preceding and unbounded
following),
+ unique1, four
+ FROM tenk1 WHERE unique1 < 10;
+END;
+CREATE FUNCTION unbounded_syntax_test2b(unbounded int) RETURNS TABLE (a int, b
int, c int)
+LANGUAGE SQL
+AS $$
+ SELECT sum(unique1) over (rows between unbounded preceding and unbounded
following),
+ unique1, four
+ FROM tenk1 WHERE unique1 < 10;
+$$;
+-- These will not apply the argument but instead treat UNBOUNDED as a keyword.
+SELECT * FROM unbounded_syntax_test2a(2);
+ a | b | c
+----+---+---
+ 45 | 4 | 0
+ 45 | 2 | 2
+ 45 | 1 | 1
+ 45 | 6 | 2
+ 45 | 9 | 1
+ 45 | 8 | 0
+ 45 | 5 | 1
+ 45 | 3 | 3
+ 45 | 7 | 3
+ 45 | 0 | 0
+(10 rows)
+
+SELECT * FROM unbounded_syntax_test2b(2);
+ a | b | c
+----+---+---
+ 45 | 4 | 0
+ 45 | 2 | 2
+ 45 | 1 | 1
+ 45 | 6 | 2
+ 45 | 9 | 1
+ 45 | 8 | 0
+ 45 | 5 | 1
+ 45 | 3 | 3
+ 45 | 7 | 3
+ 45 | 0 | 0
+(10 rows)
+
+DROP FUNCTION unbounded_syntax_test1a, unbounded_syntax_test1b,
+ unbounded_syntax_test2a, unbounded_syntax_test2b;
+-- Other tests with token UNBOUNDED in potentially problematic position
+CREATE FUNCTION unbounded(x int) RETURNS int LANGUAGE SQL IMMUTABLE RETURN x;
+SELECT sum(unique1) over (rows between 1 preceding and 1 following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 6 | 4 | 0
+ 7 | 2 | 2
+ 9 | 1 | 1
+ 16 | 6 | 2
+ 23 | 9 | 1
+ 22 | 8 | 0
+ 16 | 5 | 1
+ 15 | 3 | 3
+ 10 | 7 | 3
+ 7 | 0 | 0
+(10 rows)
+
+SELECT sum(unique1) over (rows between unbounded(1) preceding and unbounded(1)
following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 6 | 4 | 0
+ 7 | 2 | 2
+ 9 | 1 | 1
+ 16 | 6 | 2
+ 23 | 9 | 1
+ 22 | 8 | 0
+ 16 | 5 | 1
+ 15 | 3 | 3
+ 10 | 7 | 3
+ 7 | 0 | 0
+(10 rows)
+
+SELECT sum(unique1) over (rows between unbounded.x preceding and unbounded.x
following),
+ unique1, four
+FROM tenk1, (values (1)) as unbounded(x) WHERE unique1 < 10;
+ERROR: argument of ROWS must not contain variables
+LINE 1: SELECT sum(unique1) over (rows between unbounded.x preceding...
+ ^
+DROP FUNCTION unbounded;
-- 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;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index eae5fa6017..41a8e0d152 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -471,6 +471,72 @@ CREATE TEMP VIEW v_window AS
window w as
(order by x desc nulls last range between 2 preceding and 2 following);
+-- 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. The following
+-- tests record this behavior.
+
+CREATE FUNCTION unbounded_syntax_test1a(x int) RETURNS TABLE (a int, b int, c
int)
+LANGUAGE SQL
+BEGIN ATOMIC
+ SELECT sum(unique1) over (rows between x preceding and x following),
+ unique1, four
+ FROM tenk1 WHERE unique1 < 10;
+END;
+
+CREATE FUNCTION unbounded_syntax_test1b(x int) RETURNS TABLE (a int, b int, c
int)
+LANGUAGE SQL
+AS $$
+ SELECT sum(unique1) over (rows between x preceding and x following),
+ unique1, four
+ FROM tenk1 WHERE unique1 < 10;
+$$;
+
+-- These will apply the argument to the window specification inside the
function.
+SELECT * FROM unbounded_syntax_test1a(2);
+SELECT * FROM unbounded_syntax_test1b(2);
+
+CREATE FUNCTION unbounded_syntax_test2a(unbounded int) RETURNS TABLE (a int, b
int, c int)
+LANGUAGE SQL
+BEGIN ATOMIC
+ SELECT sum(unique1) over (rows between unbounded preceding and unbounded
following),
+ unique1, four
+ FROM tenk1 WHERE unique1 < 10;
+END;
+
+CREATE FUNCTION unbounded_syntax_test2b(unbounded int) RETURNS TABLE (a int, b
int, c int)
+LANGUAGE SQL
+AS $$
+ SELECT sum(unique1) over (rows between unbounded preceding and unbounded
following),
+ unique1, four
+ FROM tenk1 WHERE unique1 < 10;
+$$;
+
+-- These will not apply the argument but instead treat UNBOUNDED as a keyword.
+SELECT * FROM unbounded_syntax_test2a(2);
+SELECT * FROM unbounded_syntax_test2b(2);
+
+DROP FUNCTION unbounded_syntax_test1a, unbounded_syntax_test1b,
+ unbounded_syntax_test2a, unbounded_syntax_test2b;
+
+-- Other tests with token UNBOUNDED in potentially problematic position
+CREATE FUNCTION unbounded(x int) RETURNS int LANGUAGE SQL IMMUTABLE RETURN x;
+
+SELECT sum(unique1) over (rows between 1 preceding and 1 following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (rows between unbounded(1) preceding and unbounded(1)
following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (rows between unbounded.x preceding and unbounded.x
following),
+ unique1, four
+FROM tenk1, (values (1)) as unbounded(x) WHERE unique1 < 10;
+
+DROP FUNCTION unbounded;
+
-- Check overflow behavior for various integer sizes
select x, last_value(x) over (order by x::smallint range between current row
and 2147450884 following)
--
2.32.0
