Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f93Emun19806 for ; Wed, 3 Oct 2001 10:48:56 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 140852 for ; Wed, 03 Oct 2001 07:48:51 -0700 From: "Josh Berkus" Subject: Re: 7.0.3 and 7.1.3 different results? To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Wed, 03 Oct 2001 07:48:51 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===140852====davinci.ethosmedia.com===_" X-Archive-Number: 200110/33 X-Sequence-Number: 4988 This is a multi-part MIME message --_===140852====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Joel, > A query such as "select sum(pages) from job_documents where > delivery_type='print'" returned 0 in version 7.0.3 if there were no > rows > matching the query. In 7.1.3 the result is NULL if no rows match the > query. Why the change? Which result is "correct" according to the > SQL > standard? While I wasn't aware of the change between versions, returning no rows is correct for any aggregate except COUNT, which returns 0. Although, now that you mention it, I'm not quite sure why that's the rule. I mean, shouldn't COUNT return no rows, too? Goes to show you that the SQL standard isn't even the model of perfect consistency .... BTW, returning no rows is somewhat different than returning NULL. WHat you should be seeing is: SELECT sum(id) FROM tableA WHERE field2 = 'not found'; sum ------------------ rather than sum ------------------ NULL -Josh --_===140852====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===140852====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===140852====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===140852====davinci.ethosmedia.com===_--