Re: Examples of SQL anomalies?

From: Gene Wirchenko <genew_at_ocis.net>
Date: Fri, 27 Jun 2008 10:00:18 -0700
Message-ID: <bv6a649et7clrgaipvb83kfime8tcblmmi@4ax.com>


Philipp Post <Post.Philipp_at_googlemail.com> wrote:

>> Others have mentioned nulls. One of my pet peeves in this area
>is that in SQL, if I sum zero numbers, the result is null.
>Mathematically, the result is 0. I have an app where I have had make
>adjustments where it is possible that zero numbers are summed. <
>
>SQL Server at least stripps off NULLs in the SUM function and returns
>a number, but not in a simple addition - then it returns NULL if at
>least one entry is NULL.
>
>> SUM(A) + SUM(B) != SUM(A+B) <
>
>The question is, if these issues are due to the SQL specification or
>simply due to a problem in a specific SQL product. Or could it be,

     I am told that it is the spec.

>that the definition is not precise enough in some points, so that
>database vendors implemented it differently? A quick look at the SQL
>92 spec did not sched much light on this for me regarding the source
>of the problem.

     I do not use nulls in my tables. I still get null when I sum what turns out to be zero rows. If I want the total amount charged on invoices for a client in a certain date range, and there are no such invoices, the total is zero. SQL returns null. Every place where that possibility exists, I have to patch the cursor.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.
Received on Fri Jun 27 2008 - 12:00:18 CDT

Original text of this message