Select count(*) /*from*/ table

Started by Alex Shulginalmost 15 years ago2 messagesgeneral
Jump to latest
#1Alex Shulgin
alex.shulgin@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Alex Shulgin (#1)
Re: Select count(*) /*from*/ table

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