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

Search Discussions

  • Tom Lane at Oct 2, 2001 at 2:56 pm

    Joel Mc Graw 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
  • Josh Berkus at Oct 3, 2001 at 2:48 pm
    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
  • Stephan Szabo at Oct 3, 2001 at 3:36 pm

    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>.
  • Josh Berkus at Oct 3, 2001 at 5:08 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-sql @
categoriespostgresql
postedOct 2, '01 at 2:23p
activeOct 3, '01 at 5:08p
posts5
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase