Thread: count(*) of zero rows returns 1
<div dir="ltr">Can somebody explain why a standalone count(*) returns 1?<br /><br />postgres=# select count(*);<br /> count<br />-------<br /> 1<br />(1 row)<br /><br />I agree it's an odd thing for someone to query, but I feel it shouldreturn 0, and not 1.<br clear="all" /><br />-- <br /><div dir="ltr">Gurjeet Singh<br /><br /><a href="http://gurjeet.singh.im/"target="_blank">http://gurjeet.singh.im/</a><br /></div></div>
Gurjeet Singh <singh.gurjeet@gmail.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@sss.pgh.pa.us> wrote:
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.
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 <singh.gurjeet@gmail.com> writes:The Oracle equivalent of that would be "SELECT count(*) FROM dual".
> Can somebody explain why a standalone count(*) returns 1?
> postgres=# select count(*);
> count
> -------
> 1
> (1 row)
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.
For that to return zero, it would also be necessary for "SELECT 2+2"
> I agree it's an odd thing for someone to query, but I feel it should return
> 0, and not 1.
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.
--
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.
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
On Mon, Jan 14, 2013 at 3:09 PM, David Johnston <polobo@yahoo.com> wrote:
AFAICR, 'dual' table has one column named 'DUMMY' and one row with value, single character X.
-- What does "SELECT * FROM dual" in Oracle yield?
AFAICR, 'dual' table has one column named 'DUMMY' and one row with value, single character X.
On Mon, Jan 14, 2013 at 4:15 PM, Tom Lane <tgl@sss.pgh.pa.us> 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 wasn't advocating it, but was trying to wrap my head around why Postgres would do something like count(*) of nothing == 1.
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:That has nothing to do with the number of rows, though. That's
> SELECT *;
> Results in:
> SQL Error: ERROR: SELECT * with no tables specified is not valid
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"Yeah, it's more about convenience than principle. AFAICS there are three
> nor "1" seem particularly sound answers to the question "SELECT count(*)".
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 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@2ndquadrant.com> wrote:
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 escribió:Yeah.
> 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)
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 <singh.gurjeet@gmail.com> writes: > On Mon, Jan 14, 2013 at 4:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> David Johnston <polobo@yahoo.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@momjian.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@yahoo.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@momjian.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@optionshouse.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@optionshouse.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.gurjeet@gmail.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@juffo.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@sss.pgh.pa.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