Port Bug Report: sum() + "group by" with an empty result returns one complete empty row - Mailing list pgsql-ports

From Unprivileged user
Subject Port Bug Report: sum() + "group by" with an empty result returns one complete empty row
Date
Msg-id 199906211106.HAA06043@hub.org
Whole thread Raw
List pgsql-ports
============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Jochen Scharrlach
Your email address      : Jochen.Scharrlach@schwaben.de

Category                : runtime: back-end: SQL
Severity                : non-critical

Summary: sum() + "group by" with an empty result returns one complete empty row

System Configuration
--------------------
  Operating System   : Linux RedHat 5.2

  PostgreSQL version : 6.5

  Compiler used      : gcc 2.7.3

Hardware:
---------
Linux cepheus 2.2.7 #12 Mit Mai 12 18:21:15 CEST 1999 i686 unknown
96MB RAM, PPro 200MHz

Versions of other tools:
------------------------


--------------------------------------------------------------------------

Problem Description:
--------------------
My Java-app uses some group-by statements which are expected
to return empty resultsets if the result *is* empty - and it
doesn't like getting one row consisting of null-values...

It works fine with Oracle and it seems to be reasonable the
way Oracle handles it, so I *think* it's a bug :)

--------------------------------------------------------------------------

Test Case:
----------
psql output of a simple example:

----snip----
js=> create table testi (number int, name varchar(40));
CREATE
js=> insert into testi values (1, 'myname');
INSERT 151274 1
js=> select sum(number) from testi where name='myname';
sum
---
  1
(1 row)

js=> select * from testi where name='notthere';
number|name
------+----
(0 rows)

js=> select sum(number) , name from testi where name='notthere' group by
name;
sum|name
---+----
   |
(1 row)
----snip----


This crashes my little app (because of the empty row) which works fine
with Oracle:

----snip----
SQL> create table testi (n int, name varchar(40));

Table created.

SQL> insert into testi values (1, 'myname');

1 row created.

SQL> select sum(n) from testi where name='myname';

    SUM(N)
----------
         1

SQL> select * from testi where name='notthere';

no rows selected

SQL> select sum(n), name from testi where name='notthere' group by name;

no rows selected
----snip----


--------------------------------------------------------------------------

Solution:
---------


--------------------------------------------------------------------------


pgsql-ports by date:

Previous
From: twright@perigee.net
Date:
Subject: Installation of PostgreSQL 6.5
Next
From: Benoit SIBAUD
Date:
Subject: Installation of V6.5