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
