Authorizing select count()
Hello,
Vik Fearing pointed out the inconsistency in the SQL Standard that imposes
using count(*) (with a star) but row)number() without it.
Vik's point of view is that we should be able to use row_number with a
star, which is already implemented in Postgres.
My point of view is we could add support for count(). It does not remove
the compliance with the SQL Standard, it just adds an extra feature.
You will find enclosed a patch proposal to allow count to be used without a
star. I, on purpose, decided not to document this behavior, maybe that's
wrong.
Have a great day,
Lætitia
Attachments:
ability_to_use_count_without_star_v1.patchapplication/octet-stream; name=ability_to_use_count_without_star_v1.patchDownload
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index f71a682cd6..2bd8683347 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -780,17 +780,6 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
aggref->aggtransno = -1;
aggref->location = location;
- /*
- * Reject attempt to call a parameterless aggregate without (*)
- * syntax. This is mere pedantry but some folks insisted ...
- */
- if (fargs == NIL && !agg_star && !agg_within_group)
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("%s(*) must be used to call a parameterless aggregate function",
- NameListToString(funcname)),
- parser_errposition(pstate, location)));
-
if (retset)
ereport(ERROR,
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
@@ -844,17 +833,6 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
errmsg("DISTINCT is not implemented for window functions"),
parser_errposition(pstate, location)));
- /*
- * Reject attempt to call a parameterless aggregate without (*)
- * syntax. This is mere pedantry but some folks insisted ...
- */
- if (wfunc->winagg && fargs == NIL && !agg_star)
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("%s(*) must be used to call a parameterless aggregate function",
- NameListToString(funcname)),
- parser_errposition(pstate, location)));
-
/*
* ordered aggs not allowed in windows yet
*/
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index d78b4c463c..7ac2fb3739 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -3270,10 +3270,6 @@ SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
ERROR: syntax error at or near "ORDER"
LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te...
^
-SELECT count() OVER () FROM tenk1;
-ERROR: count(*) must be used to call a parameterless aggregate function
-LINE 1: SELECT count() OVER () FROM tenk1;
- ^
SELECT generate_series(1, 100) OVER () FROM empsalary;
ERROR: OVER specified, but generate_series is not a window function nor an aggregate function
LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 967b9413de..0f0c2bb622 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -952,8 +952,6 @@ SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY
SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
-SELECT count() OVER () FROM tenk1;
-
SELECT generate_series(1, 100) OVER () FROM empsalary;
SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
On Wed, May 25, 2022 at 12:26:47PM +0200, Laetitia Avrot wrote:
You will find enclosed a patch proposal to allow count to be used without a
star. I, on purpose, decided not to document this behavior, maybe that's
wrong.
This originates from 108fe47, most likely as part of this thread. The
patch proposed by Sergey did not include this restriction, though:
/messages/by-id/Pine.LNX.4.64.0607241340090.19158@lnfm1.sai.msu.ru
Tom?
--
Michael
Michael Paquier <michael@paquier.xyz> writes:
On Wed, May 25, 2022 at 12:26:47PM +0200, Laetitia Avrot wrote:
You will find enclosed a patch proposal to allow count to be used without a
star. I, on purpose, decided not to document this behavior, maybe that's
wrong.
This originates from 108fe47, most likely as part of this thread.
I'm fairly sure that in the past we've considered this idea and rejected
it, mainly on the grounds that it's a completely gratuitous departure
from SQL standard. I quite agree that the syntax without star would be
saner, but once we get into inventing "saner" variants of SQL syntax,
where do we stop? And how much are we buying really?
I definitely don't agree with doing it but not documenting it; that
will just result in endless confusion.
regards, tom lane
I wrote:
I'm fairly sure that in the past we've considered this idea and rejected
it, mainly on the grounds that it's a completely gratuitous departure
from SQL standard.
After some more digging I found the thread that (I think) the "mere
pedantry" comment was referring to:
/messages/by-id/Pine.LNX.4.44.0604131644260.20730-100000@lnfm1.sai.msu.ru
There's other nearby discussion at
/messages/by-id/4476BABD.4080100@zigo.dhs.org
(note that that's referring to the klugy state of affairs before 108fe4730)
Of course, that's just a couple of offhand email threads, which should
not be mistaken for graven stone tablets. But I still don't see much
advantage in deviating from the SQL-standard syntax for COUNT(*).
regards, tom lane