Thread: Select count(*) /*from*/ table

Hello,

Today I've mistyped a SELECT (effectively omitting the FROM clause):

$ SELECT COUNT(*) my_table;
 my_table
----------
        1
(1 row)

Apparently, my_table was treated as an alias to the COUNT(*)
expression.  This has been discussed before, e.g. here:
http://archives.postgresql.org/pgsql-general/2011-03/msg00331.php

OK, but why it gives 1, and not 0?

A hint might be that if you try "SELECT 1+1, COUNT(*)" or any other
SELECT query w/o the FROM list, the result set always(?) consists of
exactly 1 row.

But does it really make sense?  You cannot run a "SELECT *" w/o the
FROM list, and why "COUNT(*)" on empty FROM list would return a value
greater than on e.g. FROM empty_table?

I understand that there's really not much point in running COUNT w/o
the FROM list, but maybe we should just disallow COUNT(*) with empty
FROM list?  It will also save from interpreting the result "1" as the
number of rows in the "table" when you mistakenly forgot to add FROM.

--
Regards,
Alex

Alexander Shulgin <alex.shulgin@gmail.com> writes:
> I understand that there's really not much point in running COUNT w/o
> the FROM list, but maybe we should just disallow COUNT(*) with empty
> FROM list?

While I don't offhand see a use case for aggregates without FROM,
it's a long way from there to asserting that there is no use case.

The real foot-gun here IMO is the ability to omit "AS", which
unfortunately we're kind of stuck with because it's in the SQL standard.

            regards, tom lane