Re: count(*) of zero rows returns 1

Can somebody explain why a standalone count(*) returns 1?

postgres=# select count(*);
count
-------
1
(1 row)

I agree it's an odd thing for someone to query, but I feel it should return
0, and not 1.

--
Gurjeet Singh

http://gurjeet.singh.im/

Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> 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.

regards, tom lane

On Sun, Jan 13, 2013 at 4:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> 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?
>

For a user, Oracle's case makes perfect sense, since the command is
querying a single-row table. In Postgres' case, there's nothing being
queried, so the result's got to be either 0 or NULL.

>
> > 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.
>

Hmm.. Now that you put it that way, I agree it's a useful feature, or
shall I say, a quirk with useful side effect.

--
Gurjeet Singh

http://gurjeet.singh.im/

Tom Lane-2 wrote
> Gurjeet Singh &lt;

> singh.gurjeet@

> &gt; 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.

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

On Mon, Jan 14, 2013 at 3:09 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> What does "SELECT * FROM dual" in Oracle yield?
>

AFAICR, 'dual' table has one column named 'DUMMY' and one row with value,
single character X.

--
Gurjeet Singh

http://gurjeet.singh.im/

On Mon, Jan 14, 2013 at 4:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.
>

Interesting to note that SELECT * FROM table_with_zero_cols does not
complain of anything.

postgres=# select * from test1;
--
(0 rows)

This I believe result of the fact that we allow user to drop all columns of
a table.

On a side note, Postgres allows me to do this (which I don't think is a bug
or useless): I inserted some rows into a table, and then dropped the
columns. The resulting table has no columns, but live rows.

postgres=# select * from test_0_col_table ;
--
(200000 rows)

> 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).
>

I wasn't advocating it, but was trying to wrap my head around why Postgres
would do something like count(*) of nothing == 1.

--
Gurjeet Singh

http://gurjeet.singh.im/

Gurjeet Singh escribió:

> Interesting to note that SELECT * FROM table_with_zero_cols does not
> complain of anything.
>
> postgres=# select * from test1;
> --
> (0 rows)
>
> This I believe result of the fact that we allow user to drop all columns of
> a table.
>
> On a side note, Postgres allows me to do this (which I don't think is a bug
> or useless): I inserted some rows into a table, and then dropped the
> columns. The resulting table has no columns, but live rows.
>
> postgres=# select * from test_0_col_table ;
> --
> (200000 rows)

Yeah.

alvherre=# create table foo ();
CREATE TABLE
alvherre=# insert into foo default values;
INSERT 0 1
alvherre=# insert into foo default values;
INSERT 0 1
alvherre=# insert into foo default values;
INSERT 0 1
alvherre=# insert into foo default values;
INSERT 0 1
alvherre=# insert into foo select * from foo;
INSERT 0 4
alvherre=# insert into foo select * from foo;
INSERT 0 8
alvherre=# insert into foo select * from foo;
INSERT 0 16
alvherre=# insert into foo select * from foo;
INSERT 0 32
alvherre=# insert into foo select * from foo;
INSERT 0 64
alvherre=# select count(*) from foo;
count
-------
128
(1 fila)

alvherre=# select * from foo;
--
(128 filas)

If you examine the ctid system column you can even see that those empty
rows consume some storage space.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Jan 14, 2013 at 11:03 PM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com>wrote:

> Gurjeet Singh escribió:
>
> > Interesting to note that SELECT * FROM table_with_zero_cols does not
> > complain of anything.
> >
> > postgres=# select * from test1;
> > --
> > (0 rows)
> >
> > This I believe result of the fact that we allow user to drop all columns
> of
> > a table.
> >
> > On a side note, Postgres allows me to do this (which I don't think is a
> bug
> > or useless): I inserted some rows into a table, and then dropped the
> > columns. The resulting table has no columns, but live rows.
> >
> > postgres=# select * from test_0_col_table ;
> > --
> > (200000 rows)
>
> Yeah.
>
> alvherre=# create table foo ();
> CREATE TABLE
> alvherre=# insert into foo default values;
> INSERT 0 1
> alvherre=# insert into foo default values;
> INSERT 0 1
> alvherre=# insert into foo default values;
> INSERT 0 1
> alvherre=# insert into foo default values;
> INSERT 0 1
> alvherre=# insert into foo select * from foo;
> INSERT 0 4
> alvherre=# insert into foo select * from foo;
> INSERT 0 8
> alvherre=# insert into foo select * from foo;
> INSERT 0 16
> alvherre=# insert into foo select * from foo;
> INSERT 0 32
> alvherre=# insert into foo select * from foo;
> INSERT 0 64
> alvherre=# select count(*) from foo;
> count
> -------
> 128
> (1 fila)
>
> alvherre=# select * from foo;
> --
> (128 filas)
>
> If you examine the ctid system column you can even see that those empty
> rows consume some storage space.

I was trying to build a case and propose that we issue a TRUNCATE on the
table after the last column is dropped. But then realized that the rows may
have become invisible, but they can be brought back to visibility by simply
adding a new column. These rows with get the new column's DEFAULT value
(NULL by default), and then the result of a SELECT * will show all the rows
again.

--
Gurjeet Singh

http://gurjeet.singh.im/

Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> writes:
> On Mon, Jan 14, 2013 at 4:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> David Johnston <polobo(at)yahoo(dot)com> writes:
>>> SELECT *;
>>> Results in:
>>> SQL Error: ERROR: SELECT * with no tables specified is not valid

> Interesting to note that SELECT * FROM table_with_zero_cols does not
> complain of anything.

Yeah. IIRC that error check long predates our support for zero-column
tables (which we allowed when we introduced ALTER TABLE DROP COLUMN,
so that dropping the only column doesn't have to be an error case).
You could argue that the existence of zero-column tables makes the
error check inconsistent, but I think it's probably good as-is. The
possibility that "*" can sometimes validly expand to no columns doesn't
mean that "SELECT *;" isn't almost surely a typo.

regards, tom lane

On Mon, Jan 14, 2013 at 10:47:58PM -0500, Gurjeet Singh wrote:
> Interesting to note that SELECT * FROM table_with_zero_cols does not complain
> of anything.
>
> postgres=# select * from test1;
> --
> (0 rows)
>
> This I believe result of the fact that we allow user to drop all columns of a
> table.
>
> On a side note, Postgres allows me to do this (which I don't think is a bug or
> useless): I inserted some rows into a table, and then dropped the columns. The
> resulting table has no columns, but live rows.
>
> postgres=# select * from test_0_col_table ;
> --
> (200000 rows)

I found this funny.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Mon, Jan 14, 2013 at 10:25:39PM -0500, Gurjeet Singh wrote:
> On Mon, Jan 14, 2013 at 3:09 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
>
> What does "SELECT * FROM dual" in Oracle yield?
>
>
> AFAICR, 'dual' table has one column named 'DUMMY' and one row with value,
> single character X.

How elegant. :-(

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 01/15/2013 01:18 PM, Bruce Momjian wrote:

>> AFAICR, 'dual' table has one column named 'DUMMY' and one row with
>> value, single character X.
>
> How elegant. :-(

Let's see what EnterpriseDB produces:

test=> select * from dual;

dummy
-------
X
(1 row)

Yep, elegant gets my vote. ;) But then again, Oracle also created
VARCHAR2 and told everyone to start using that, just in case they ever
modified VARCHAR to be SQL compliant. Thankfully we have you guys, so PG
won't go down a similar route.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On Tue, Jan 15, 2013 at 2:26 PM, Shaun Thomas <sthomas(at)optionshouse(dot)com> wrote:
> Let's see what EnterpriseDB produces:
>
> test=> select * from dual;
>
> dummy
> -------
> X
> (1 row)

Hey, don't blame us. We didn't come up with this bad idea ... just
trying to make life easier for those who are used to it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Jan 15, 2013 at 5:47 AM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> postgres=# select * from test_0_col_table ;
> --
> (200000 rows)

Interestingly, PostgreSQL 9.2 has regressed here. Not sure if we care,
but worth mentioning:

psql (9.2.2)

test=# select count(*) from foo1;
count
----------
10000000
(1 row)
Time: 632.907 ms

test=# select * from foo1;
(No rows)
Time: 1012.567 ms

Regards,
Marti

Marti Raudsepp <marti(at)juffo(dot)org> writes:
> Interestingly, PostgreSQL 9.2 has regressed here. Not sure if we care,
> but worth mentioning:

Regressed? The output looks the same to me as it has for some time.

> test=# select * from foo1;
> (No rows)
> Time: 1012.567 ms

How did you get that? I don't believe it's possible in the default
output format.

regards, tom lane

On Mon, Jan 21, 2013 at 9:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> test=# select * from foo1;
>> (No rows)
>> Time: 1012.567 ms
>
> How did you get that? I don't believe it's possible in the default
> output format.

Oh I see, it's because I have \x auto in my .psqlrc. If I set \x auto
or \x on then it says "(No rows)"

Regards,
Marti