Thread: SELECT "bug"?

I figure that its here, should really start using it... the list that is
...

Okay, playing with a v6.5.3 server right now, and trying something that I
would have guessed would be relatively simple:

webcounter=> select count(1) from webhit_details_formatted;
count
-----
    1
(1 row)

webcounter=> select count(1) from webhit_details_formatted where counter_id = 1;
^C
CANCEL request sent
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
We have lost the connection to the backend, so further processing is impossible.  Terminating.
abacus>

The table have zero data in it ... when I did the WHERE clause above, the
query just hung there until I hit ^C ...

Under Oracle, doing the "SELECT count(1)..." is faster then doing "SELECT
count(*)..." (supposedly), since it doesn't have to retrieve all the
information, only count how many records match the WHERE clause ...

Comments?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

The Hermit Hacker <scrappy@hub.org> writes:
> select count(1) from webhit_details_formatted where counter_id = 1;
> [ hangs, then crashes when try to cancel ]

Wow, how'd that escape notice for this long?

play=> create table webhit_details_formatted (counter_id int);
CREATE
play=>  select count(1) from webhit_details_formatted where counter_id = 1;
pqReadData() -- backend closed the channel unexpectedly.

Mine just crashes immediately, no waiting.

The good news is that current sources don't seem to have the problem.


> Under Oracle, doing the "SELECT count(1)..." is faster then doing "SELECT
> count(*)..." (supposedly), since it doesn't have to retrieve all the
> information, only count how many records match the WHERE clause ...

Under Postgres, there's no difference --- in fact, the parser currently
converts count(*) into count(1) ;-)

            regards, tom lane

The Hermit Hacker <scrappy@hub.org> writes:
> webcounter=> select count(1) from webhit_details_formatted where counter_id = 1;
> [ crash ]

OK, this is a boundary condition that I fixed awhile ago: nodeAgg.c
coredumped if no tuples are selected *and* no attributes are mentioned
in the targetlist.  You can work around it in 6.5.* by writing
count(*) instead of count(1).

We could backpatch this, but I'm not sure it's worth the trouble,
considering the lack of complaints and the simplicity of the workaround.

            regards, tom lane