Skip site navigation (1) Skip section navigation (2)

Re: count(*) of zero rows returns 1

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Johnston <polobo(at)yahoo(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: count(*) of zero rows returns 1
Date: 2013-01-14 21:15:36
Message-ID: 5196.1358198136@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
David Johnston <polobo(at)yahoo(dot)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


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2013-01-14 21:35:48
Subject: Curious buildfarm failures
Previous:From: Kevin GrittnerDate: 2013-01-14 20:56:23
Subject: Re: [Pgbuildfarm-members] Version 4.10 of buildfarm client released.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group