Re: Watch out for this one :o)

In article <3d3c63b5$0$8506$cc9e4d1f_at_news.dial.pipex.com>, "Niall says...
>
>Here's the problem I have with this behaviour. You will note that I put bug
>in * characters as I know that this is the behaviour I just can't justify
>it. The result from Pl/SQL is correct given the behaviour of SQL.
>
>How do we sum? answer start at zero and add the value of each of the items
>in the series to a running total till we get to the end. If we have no
>values in the series the answer would be on this understanding zero NOT not
>known.
>
>Take the following scenario:
>You go down to the shops. You buy nothing at all? how much have you spent?
>Answer according to this behaviour of Oracle. "I don't know". Answer
>according to me. "Nothing."

It is the answser according to SQL -- not just Oracle.

You go down to the shops. You count the birds (it is midnight, there are no birds about). How many birds did you see? Answer according to SQL -- 0, Answer according to me -- 0

ops$tkyte_at_ORA9I.WORLD> select count(*) from dual where 1=0;

  COUNT(*)

         0

ops$tkyte_at_ORA9I.WORLD> select count(*), sum( 1 ) from dual where 1 = 0;

  COUNT(*) SUM(1)
---------- ----------

         0

ops$tkyte_at_ORA9I.WORLD> select sum( 1 ) from dual where 1 = 0;

    SUM(1)

ops$tkyte_at_ORA9I.WORLD> select sum(1) from dual where 1=0 group by 1;

no rows selected

If count(*) is to return 0, sum(1) must return something as well. Now, if sum(1) returns NULL when selected with count(*) and since projections are wholly independent of eachother, sum(1) must return NULL as well.

When you have NO GROUP BY -- there is always

o at least one
o at most one

group. Add a group by and the sum goes away as there is nothing to group by. It is in the very definition of "Database Language SQL"

>
>--
>Niall Litchfield
>Oracle DBA
>Audit Commission UK
>*****************************************
>Please include version and platform
>and SQL where applicable
>It makes life easier and increases the
>likelihood of a good answer
>******************************************
>"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message
>news:ahhfu5$qdc$1_at_babylon.agtel.net...
>> > So it seems to me that the *bug* is not in PL/SQL but SQL, given that
>surely
>> > the answer is zero.
>>
>> Surely the answer is NULL (interpreted as "I don't know as there was no
>> data to sum"). :)
>>
>> --
>> Vladimir Zakharychev (bob_at_dpsp-yes.com)
>http://www.dpsp-yes.com
>> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
>applications.
>> All opinions are mine and do not necessarily go in line with those of my
>employer.
>>
>>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp