Peter Eisentraut <peter_e@gmx.net> writes:
> In general,
> SELECT count(expr) FROM table1;
> counts the number of rows in table1 where expr evaluates to not null.
Right. Edwin obscured the datatype issue by leaving off a table, but
the issue is real anyway:
regression=# select count(1) from tenk1;count
-------10000
(1 row)
regression=# select count('x') from tenk1;
ERROR: cannot accept a value of type any
We need to do something about that, I think. The "long form" solution
would be to cause 'x' to be promoted to type text in this context, but
I think it may be enough to remove the elog() in any_in() and just let
a dummy value be returned.
> If table1 is not specified, that rule no longer holds. At best you
> could assume that table1 is empty and return 0. But a result of 1 I
> cannot see justified.
Mumble. An Oracle person would say that our locution
"SELECT expression" is a shorthand for "SELECT expression FROM dual"
(or whatever the name of that standard one-row table of theirs is).
With that understanding, the behavior of "SELECT count(1)" is entirely
proper. If you assume that "SELECT expression" means to select from
a dummy table of no rows, then it should produce no result rows,
which would be pretty useless. So I don't see the argument for saying
that count() should produce zero in that case.
I could see an argument for putting in a special case to error out if
an aggregate appears in this context ... but the current behavior seems
perfectly okay to me. Except for the datatype problem.
regards, tom lane