From george@flex.ro Fri Mar 12 09:40:39 1999 Received: from flex.flex.ro (IDENT:root@[193.230.187.164]) by hub.org (8.9.2/8.9.1) with ESMTP id EAA84637 for ; Fri, 12 Mar 1999 04:40:39 -0500 (EST) (envelope-from george@flex.ro) Received: from flex.ro (george.flex.ro [193.230.255.93]) by flex.flex.ro (8.9.1/8.9.1) with ESMTP id LAA24794; Fri, 12 Mar 1999 11:38:31 +0200 Sender: gestoc@flex.flex.ro Message-ID: <36E8E089.2897F517@flex.ro> Date: Fri, 12 Mar 1999 11:38:18 +0200 From: George Moga X-Mailer: Mozilla 4.5 [en] (X11; I; Linux 2.2.2 i586) X-Accept-Language: ro MIME-Version: 1.0 To: chairudin@prima.net.id, SQL PostgreSQL Subject: Re: [SQL] sum gives different answer References: <36E9213D.151B90E1@prima.net.id> Content-Type: multipart/alternative; boundary="------------CC974095FA48B2DAA427B475" --------------CC974095FA48B2DAA427B475 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Chairudin Sentosa wrote: > Hi all, > > I have two SQL statements that I expect to give (0 rows) as output. > However the first one with 'sum' does not do that. > Why the first SQL statement gives (1 row) and empty? > While the second SQL statement can give (0 row). > I am using postgresql 6.4.2. > > First > ------ > select pin, sum(duration) from tablename where date(start_time)= > (select date 'today') group by pin; > > pin|sum > ---+--- > | > (1 row) > > Second > ------ > select pin, duration from tablename where date(start_time)= > (select date 'today') group by pin; > > pin|duration > ---+-------- > (0 rows) > > Could anyone tell me why, please? > > Regards > Chai If I understund your problem ... An aggregate function (like sum, min, max etc.) always return one row. If the result of function is null, the aggregate result column is empty: Example: test=> create sequence s1; CREATE test=> CREATE TABLE test1 (id int4 DEFAULT nextval ('s1'), value float4, explication text); CREATE test=> INSERT INTO test1 (value, explication) values (100, 'first'); INSERT 568765 1 test=> INSERT INTO test1 (value, explication) values (300, 'second'); INSERT 568766 1 test=> INSERT INTO test1 (value, explication) values (123, 'third'); INSERT 568767 1 test=> SELECT * FROM test1; id|value|explication --+-----+----------- 1| 100|first 2| 300|second 3| 123|third (3 rows) test=> SELECT sum(value) as sum_value FROM test1; sum_value --------- 523 (1 row) test=> SELECT sum(value) as sum_value FROM test1 WHERE value < 100; sum_value --------- (1 row) test=> SELECT value FROM test1 WHERE value < 100; value ----- (0 rows) If you want to use the resoult of one function like this in your applicatons, you mast verify the content of aggregate column result. Sorry for my english, George -- Best, George Moga, george@flex.ro Braila, ROMANIA --------------CC974095FA48B2DAA427B475 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Chairudin Sentosa wrote:
Hi all,

I have two SQL statements that I expect to give (0 rows) as output.
However the first one with 'sum' does not do that.
Why the first SQL statement gives (1 row) and empty?
While the second SQL statement can give (0 row).
I am using postgresql 6.4.2.

First
------
select pin, sum(duration) from tablename where date(start_time)=
(select date 'today') group by pin;

pin|sum
---+---
   |
(1 row)

Second
------
select pin, duration from tablename where date(start_time)=
(select date 'today') group by pin;

pin|duration
---+--------
(0 rows)

Could anyone tell me why, please?

Regards
Chai


If I understund your problem ...

An aggregate function (like sum, min, max etc.) always return one row. If the result of function is null, the aggregate result column is empty:

Example:

test=> create sequence s1;
CREATE
test=> CREATE TABLE test1 (id int4 DEFAULT nextval ('s1'), value float4, explication text);
CREATE
test=> INSERT INTO test1 (value, explication) values (100, 'first');
INSERT 568765 1
test=> INSERT INTO test1 (value, explication) values (300, 'second');
INSERT 568766 1
test=> INSERT INTO test1 (value, explication) values (123, 'third');
INSERT 568767 1
test=> SELECT * FROM test1;
id|value|explication
--+-----+-----------
 1|  100|first
 2|  300|second
 3|  123|third
(3 rows)

test=> SELECT sum(value) as sum_value FROM test1;
sum_value
---------
523
(1 row)

test=> SELECT sum(value) as sum_value FROM test1 WHERE value < 100;
sum_value
---------

(1 row)

test=> SELECT value FROM test1 WHERE value < 100;
value
-----
(0 rows)
 

If you want to use the resoult of one function like this in your applicatons, you mast verify the content of aggregate column result.
 

Sorry for my english,

George

-- 
Best,
  George Moga,
  george@flex.ro
  Braila, ROMANIA
  --------------CC974095FA48B2DAA427B475-- From chairudin@prima.net.id Fri Mar 12 04:27:51 1999 Received: from ns1.prima.net.id ([202.57.0.8]) by hub.org (8.9.2/8.9.1) with ESMTP id XAA59566 for ; Thu, 11 Mar 1999 23:27:51 -0500 (EST) (envelope-from chairudin@prima.net.id) Received: from prima.net.id (mail.prima.net.id [202.57.0.8]) by ns1.prima.net.id (8.8.4/8.7.2) with ESMTP id SAA23833 for ; Fri, 12 Mar 1999 18:28:41 +0700 (GMT) Sender: root@ns1.prima.net.id Message-ID: <36E8FA69.4CA1832E@prima.net.id> Date: Fri, 12 Mar 1999 04:28:41 -0700 From: Chairudin Sentosa Reply-To: chairudin@prima.net.id Organization: PT. Khasanah Timur Indonesia X-Mailer: Mozilla 4.5 [en] (X11; I; SunOS 5.5.1 sun4u) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: sum gives different answer Content-Type: multipart/alternative; boundary="------------8024B0AB69593ED70AB5A64F" --------------8024B0AB69593ED70AB5A64F Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi all, I have two SQL statements that I expect to give (0 rows) as output. However the first one with 'sum' does not do that. Why the first SQL statement gives (1 row) and empty? While the second SQL statement can give (0 row). I am using postgresql 6.4.2. First ------ select pin, sum(duration) from tablename where date(start_time)= (select date 'today') group by pin; pin|sum ---+--- | (1 row) Second ------ select pin, duration from tablename where date(start_time)= (select date 'today') group by pin; pin|duration ---+-------- (0 rows) Could anyone tell me why, please? Regards Chai --------------8024B0AB69593ED70AB5A64F Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi all,

I have two SQL statements that I expect to give (0 rows) as output.
However the first one with 'sum' does not do that.
Why the first SQL statement gives (1 row) and empty?
While the second SQL statement can give (0 row).
I am using postgresql 6.4.2.

First
------
select pin, sum(duration) from tablename where date(start_time)=
(select date 'today') group by pin;

pin|sum
---+---
   |
(1 row)
 

Second
------
select pin, duration from tablename where date(start_time)=
(select date 'today') group by pin;

pin|duration
---+--------
(0 rows)

Could anyone tell me why, please?

Regards
Chai
 
  --------------8024B0AB69593ED70AB5A64F-- From chairudin@prima.net.id Fri Mar 12 05:23:18 1999 Received: from ns1.prima.net.id ([202.57.0.8]) by hub.org (8.9.2/8.9.1) with ESMTP id AAA64819 for ; Fri, 12 Mar 1999 00:23:18 -0500 (EST) (envelope-from chairudin@prima.net.id) Received: from prima.net.id (mail.prima.net.id [202.57.0.8]) by ns1.prima.net.id (8.8.4/8.7.2) with ESMTP id TAA24563 for ; Fri, 12 Mar 1999 19:24:12 +0700 (GMT) Sender: root@ns1.prima.net.id Message-ID: <36E9076C.2850C00@prima.net.id> Date: Fri, 12 Mar 1999 05:24:12 -0700 From: Chairudin Sentosa Reply-To: chairudin@prima.net.id Organization: PT. Khasanah Timur Indonesia X-Mailer: Mozilla 4.5 [en] (X11; I; SunOS 5.5.1 sun4u) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: sum gives different answer Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi all, I have two SQL statements that I expect to give (0 rows) as output. However the first one with 'sum' does not do that. Why the first SQL statement gives (1 row) and empty? While the second SQL statement can give (0 row). I am using postgresql 6.4.2. First ------ select pin, sum(duration) from tablename where date(start_time)= (select date 'today') group by pin; pin|sum ---+--- | (1 row) Second ------ select pin, duration from tablename where date(start_time)= (select date 'today') group by pin; pin|duration ---+-------- (0 rows) Could anyone tell me why, please? Regards Chai From chairudin@prima.net.id Fri Mar 12 07:13:31 1999 Received: from ns1.prima.net.id ([202.57.0.8]) by hub.org (8.9.2/8.9.1) with ESMTP id CAA73778 for ; Fri, 12 Mar 1999 02:13:31 -0500 (EST) (envelope-from chairudin@prima.net.id) Received: from prima.net.id (mail.prima.net.id [202.57.0.8]) by ns1.prima.net.id (8.8.4/8.7.2) with ESMTP id VAA25811 for ; Fri, 12 Mar 1999 21:14:21 +0700 (GMT) Sender: root@ns1.prima.net.id Message-ID: <36E9213D.151B90E1@prima.net.id> Date: Fri, 12 Mar 1999 07:14:21 -0700 From: Chairudin Sentosa Reply-To: chairudin@prima.net.id Organization: PT. Khasanah Timur Indonesia X-Mailer: Mozilla 4.5 [en] (X11; I; SunOS 5.5.1 sun4u) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: sum gives different answer Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi all, I have two SQL statements that I expect to give (0 rows) as output. However the first one with 'sum' does not do that. Why the first SQL statement gives (1 row) and empty? While the second SQL statement can give (0 row). I am using postgresql 6.4.2. First ------ select pin, sum(duration) from tablename where date(start_time)= (select date 'today') group by pin; pin|sum ---+--- | (1 row) Second ------ select pin, duration from tablename where date(start_time)= (select date 'today') group by pin; pin|duration ---+-------- (0 rows) Could anyone tell me why, please? Regards Chai From tgl@sss.pgh.pa.us Sun Mar 14 18:04:58 1999 Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.2/8.9.1) with ESMTP id NAA15457 for ; Sun, 14 Mar 1999 13:04:58 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id NAA29743 for ; Sun, 14 Mar 1999 13:04:27 -0500 (EST) To: pgsql-sql@hub.org Subject: Re: sum gives different answer In-reply-to: Your message of Sat, 13 Mar 1999 23:00:29 -0500 (EST) <199903140400.XAA11118@hub.org> Date: Sun, 14 Mar 1999 13:04:27 -0500 Message-ID: <29740.921434667@sss.pgh.pa.us> From: Tom Lane Chairudin Sentosa writes: > I have two SQL statements that I expect to give (0 rows) as output. > However the first one with 'sum' does not do that. > select pin, sum(duration) from tablename where date(start_time)= > (select date 'today') group by pin; > pin|sum > - ---+--- > | > (1 row) > select pin, duration from tablename where date(start_time)= > (select date 'today') group by pin; > pin|duration > - ---+-------- > (0 rows) As George Moga pointed out, SUM() applied to an empty collection of tuples still gives a result (one tuple containing NULL). Aggregates in general will give a result of some kind for an empty input collection --- for example, you'd surely want COUNT() to return 0 not nothing. However what you're talking about here is different: there are no groups in the result, therefore SUM() should never have been applied at all, not applied once to an empty set of tuples. I'm firmly of the opinion that the first example above is a bug. The hackers list has been around on this question a couple of times, and there are some folks who claim that the current behavior is OK, but I'm at a loss to follow their reasoning. regards, tom lane From clark.evans@manhattanproject.com Sun Mar 14 18:14:21 1999 Received: from monster.clarkevans.com (IDENT:root@pool-209-138-192-156.nwrk.grid.net [209.138.192.156]) by hub.org (8.9.2/8.9.1) with ESMTP id NAA17181 for ; Sun, 14 Mar 1999 13:14:21 -0500 (EST) (envelope-from clark.evans@manhattanproject.com) Received: from manhattanproject.com (clark@localhost [127.0.0.1]) by monster.clarkevans.com (8.8.7/8.8.7) with ESMTP id NAA00812; Sun, 14 Mar 1999 13:10:11 -0500 Sender: clark@monster.clarkevans.com Message-ID: <36EBFB83.F425A2FE@manhattanproject.com> Date: Sun, 14 Mar 1999 18:10:11 +0000 From: Clark Evans X-Mailer: Mozilla 4.5 [en] (X11; U; Linux 2.0.36 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: pgsql-sql@hub.org Subject: Re: [SQL] Re: sum gives different answer References: <29740.921434667@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Chairudin Sentosa writes: > select pin, sum(duration) from tablename where date(start_time)= > (select date 'today') group by pin; > pin|sum > - ---+--- > | > (1 row) Definately a bug. Tom Lane wrote: > SUM() should never have been applied at all Yep. Clark Evans