David Johnston <polobo@yahoo.com> writes:
> Tom Lane-2 wrote
>> For that to return zero, it would also be necessary for "SELECT 2+2"
>> to return zero rows. Which would be consistent with some views of the
>> universe, but not particularly useful.
> Given that:
> SELECT *;
> Results in:
> SQL Error: ERROR: SELECT * with no tables specified is not valid
That has nothing to do with the number of rows, though. That's
complaining that there are no columns for the * to refer to.
(Note that "count(*)" is an unrelated idiom -- the * there really has
nothing to do with its usage in SELECT *.)
> I get that the horse has already left the barn on this one but neither "0"
> nor "1" seem particularly sound answers to the question "SELECT count(*)".
Yeah, it's more about convenience than principle. AFAICS there are three
defensible answers to what an omitted FROM clause ought to mean:
1. It's not legal (the SQL spec's answer).
2. It implicitly means a table of no columns and 1 row (PG's answer).
3. It implicitly means a table of no columns and 0 rows (which is what I take Gurjeet to be advocating for).
Only #2 allows the "SELECT <expression>" idiom to do anything useful.
But once you accept that, the behaviors of the aggregates fall out of
that.
regards, tom lane