pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed7.0.3 and 7.1.3 different results?
5+ messages / 4 participants
[nested] [flat]
* 7.0.3 and 7.1.3 different results?
@ 2001-10-01 21:14 Joel Mc Graw <jpmcgraw1@home.com>
2001-10-02 14:56 ` Re: 7.0.3 and 7.1.3 different results? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Joel Mc Graw @ 2001-10-01 21:14 UTC (permalink / raw)
To: pgsql-sql
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?
I'm running PostgreSQL 7.1.3 on FreeBSD 4.3 (x86).
TIA.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 7.0.3 and 7.1.3 different results?
2001-10-01 21:14 7.0.3 and 7.1.3 different results? Joel Mc Graw <jpmcgraw1@home.com>
@ 2001-10-02 14:56 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-10-02 14:56 UTC (permalink / raw)
To: Joel Mc Graw <jpmcgraw1@home.com>; +Cc: pgsql-sql
Joel Mc Graw <jpmcgraw1@home.com> writes:
> 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?
NULL is correct according to the SQL standard (yes, I think it's
brain-dead too). SQL92 section 6.5 saith:
b) If AVG, MAX, MIN, or SUM is specified, then
Case:
i) If TXA is empty, then the result is the null value.
--------------------------------------------------
ii) If AVG is specified, then the result is the average of the
values in TXA.
iii) If MAX or MIN is specified, then the result is respec-
tively the maximum or minimum value in TXA. These results
are determined using the comparison rules specified in
Subclause 8.2, "<comparison predicate>".
iv) If SUM is specified, then the result is the sum of the
values in TXA. If the sum is not within the range of the
data type of the result, then an exception condition is
raised: data exception-numeric value out of range.
I'd suggest COALESCE(SUM(foo), 0) if you need a zero result.
However, I dispute your assertion that 7.0.3 returned zero. I just
double checked, and I get a NULL there too. If we ever returned zero,
it was a long time ago.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 7.0.3 and 7.1.3 different results?
@ 2001-10-03 14:48 Josh Berkus <josh@agliodbs.com>
2001-10-03 15:36 ` Re: 7.0.3 and 7.1.3 different results? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2001-10-03 14:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 7.0.3 and 7.1.3 different results?
2001-10-03 14:48 Re: 7.0.3 and 7.1.3 different results? Josh Berkus <josh@agliodbs.com>
@ 2001-10-03 15:36 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-03 17:07 ` Re: 7.0.3 and 7.1.3 different results? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2001-10-03 15:36 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Wed, 3 Oct 2001, Josh Berkus wrote:
> 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:
Actually it seems to me that one NULL row is correct...
7.9 <query specification>
1) Case:
a) If T is not a grouped table, then
Case:
i) If the <select list> contains a <set function specifica-
tion> that contains a reference to a column of T or di-
rectly contains a <set function specification> that does
not contain an outer reference, then T is the argument or
argument source of each such <set function specification>
and the result of the <query specification> is a table con-
sisting of 1 row. The i-th value of the row is the value
specified by the i-th <value expression>.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 7.0.3 and 7.1.3 different results?
2001-10-03 14:48 Re: 7.0.3 and 7.1.3 different results? Josh Berkus <josh@agliodbs.com>
2001-10-03 15:36 ` Re: 7.0.3 and 7.1.3 different results? Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-10-03 17:07 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2001-10-03 17:07 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Stephan,
> Actually it seems to me that one NULL row is correct...
>
> 7.9 <query specification>
> 1) Case:
> a) If T is not a grouped table, then
You are correct according to the SQL spec.
However, depending on what interface I use for the database, I can get
an empty recordset rather than a single NULL row. This has more to do
with the interface translation (such as ODBC) than it does with what
PostgreSQL is returning.
Partly I think this is DB vendors and interpreters getting confused over
a consistency issue between SELECT SUM() and SELECT SUM() ... GROUP BY.
SELECT sum(totalamount)
FROM invoices
WHERE invoice_no > invoice_no;
-----------
NULL
SELECT invoice_no, sum(totalamount)
FROM invoices
WHERE invoice_no > invoice_no
GROUP BY invoice_no;
invoice_no|sum
--------------
(empty recordset)
Obviously not particularly troublesome behavior, as these results seem
to be more or less consistent across most vendor implementations. Plus
in many languages the tests for NULL and empty recordset overlap or are
easily combined. And this is pretty clearly defined in SQL 92, as you
point out.
-Josh
P.S. Can you answer my question about indexing, please please?
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-10-03 17:07 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-01 21:14 7.0.3 and 7.1.3 different results? Joel Mc Graw <jpmcgraw1@home.com>
2001-10-02 14:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-10-03 14:48 Re: 7.0.3 and 7.1.3 different results? Josh Berkus <josh@agliodbs.com>
2001-10-03 15:36 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-03 17:07 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox