From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: count(*) of zero rows returns 1 |
Date: | 2013-01-14 20:09:59 |
Message-ID: | 1358194198873-5740160.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane-2 wrote
> Gurjeet Singh <
> singh.gurjeet@
> > writes:
>> Can somebody explain why a standalone count(*) returns 1?
>> postgres=# select count(*);
>> count
>> -------
>> 1
>> (1 row)
>
> The Oracle equivalent of that would be "SELECT count(*) FROM dual".
> Does it make more sense to you thought of that way?
>
>> I agree it's an odd thing for someone to query, but I feel it should
>> return
>> 0, and not 1.
>
> 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. Another counterexample is
>
> regression=# select sum(42);
> sum
> -----
> 42
> (1 row)
>
> which by your argument would need to return NULL, since that would be
> SUM's result over zero rows.
Given that:
SELECT *;
Results in:
SQL Error: ERROR: SELECT * with no tables specified is not valid
then an aggregate over an error should not magically cause the error to go
away.
I am curious on some points:
Is there something in the standard that makes "SELECT count(*)" valid?
What does "SELECT * FROM dual" in Oracle yield?
Is there a meaningful use case for "SELECT sum(42)", or more specifically
any aggregate query where there are no table/value inputs?
I get the "SELECT 2+2" and its ilk as there needs to be some way to evaluate
constants.
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(*)".
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/count-of-zero-rows-returns-1-tp5739973p5740160.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-01-14 20:14:41 | Re: Validation in to_date() |
Previous Message | Simon Riggs | 2013-01-14 19:50:43 | Re: Hash twice |