Thread: 7.0.3 and 7.1.3 different results?

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.

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
valuein TXA. These results                are determined using the comparison rules specified in
Subclause8.2, "<comparison predicate>".
 
            iv) If SUM is specified, then the result is the sum of the                values in TXA. If the sum is not
withinthe range of the                data type of the result, then an exception condition is                raised:
dataexception-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

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

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
functionspecifica-      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>.
 

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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco