From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Add tests for UNBOUNDED syntax ambiguity
Date: Thu, 24 Jun 2021 11:01:32 +0200
Message-ID: <b2a09a77-3c8f-7c68-c9b7-824054f87d98@enterprisedb.com> (raw)
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 <peter@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.outindex 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.sqlindex 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
Attachments:
[text/plain] 0001-Add-tests-for-UNBOUNDED-syntax-ambiguity.patch (7.7K, ../b2a09a77-3c8f-7c68-c9b7-824054f87d98@enterprisedb.com/2-0001-Add-tests-for-UNBOUNDED-syntax-ambiguity.patch)
download | inline diff:
From 59cbb06dc1bd58d76652916edffe69304ebbc797 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter.eisentraut@enterprisedb.com
Subject: Re: Add tests for UNBOUNDED syntax ambiguity
In-Reply-To: <b2a09a77-3c8f-7c68-c9b7-824054f87d98@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox