Обсуждение: Port Bug Report: sum() + "group by" with an empty result returns one complete empty row
Port Bug Report: sum() + "group by" with an empty result returns one complete empty row
От
Unprivileged user
Дата:
============================================================================ 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: --------- --------------------------------------------------------------------------