Re: Postgres 7.3.5 and count('x')

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