List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:July 30 1999 11:26am
Subject:Re: SUM() changed in 3.23?
View as plain text  
>>>>> "Sasha" == Sasha Pachev <sasha@stripped> writes:

Sasha> Paul DuBois wrote:
>> 
>> At 4:16 PM -0500 7/19/99, Sasha Pachev wrote:
>> >Scott Hess wrote:
>> >>
>> >> Sasha Pachev <sasha@stripped> wrote:
>> >> > Balint TOTH wrote:
>> >> > > Is it OK if sum(field) returns NULL if the table has no
> records? Older
>> >> > > versions (like 3.22.22) returned 0.
>> >> >
>> >> > This is more logical, I think. If this is not what you want, you
> can do
>> >> > ifnull(sum(field), 0)
>> >>
>> >> Except insofar as it breaks existing code!  Just this weekend this
> became a
>> >> problem as I tested against 3.22.23 (we were using 3.22.15).
>> >>
>> >> Is this how SUM() is supposed to work?  When I described the problem at
> the
>> >> office, my colleagues were pretty much amazed that this happened, and
> think
>> >> it must be a bug.  If it's considered a feature, I need to send some
> emails
>> >> around...
>> >>
>> >> Later,
>> >> scott
>> >
>> >If you have too much code that depends on this, and you cannot just
>> >change it in the code, you can change a couple of lines in the sources
>> >of mysql and recompile.
>> 
>> Where?  Which file?  What lines?
>> 

Sasha> OK, Paul, you made me look for it :-) This actually good, since it gave
Sasha> me a chance to better familiarize myself with MySQL sources. The magic
Sasha> line is in
Sasha> sql/item_sum.h ( line 97) :

Sasha> void fix_length_and_dec() { maybe_null=null_value=1; }

Sasha> change 1 to 0 - this is the way it was in 3.22 versions

Sasha> I did test it - changing it to 0 makes the sum on an empty table return
Sasha> 0 and it does not break normal sum - as far as I can tell there should
Sasha> not be any other issues involved, but I would like to have Monty confirm
Sasha> it before I say anything authoritative.

Hi!

Your patch is correct, but unfortunately SUM() is supposed to return
NULL on a empty set according to the ANSI SQL standard.

For example C.J.DATE (the author of A Guide to THE SQL STANDARD)
thinks also this is stupid, but it's in the standard :(

I have now updated the 'upgrading to 3.23' manual section about this.

Regards,
Monty
Thread
SUM() changed in 3.23?Balint TOTH19 Jul
  • Re: SUM() changed in 3.23?Sasha Pachev19 Jul
  • Re: SUM() changed in 3.23?Scott Hess20 Jul
  • Re: SUM() changed in 3.23?Sasha Pachev20 Jul
    • Re: SUM() changed in 3.23?Paul DuBois20 Jul
  • Re: SUM() changed in 3.23?Sasha Pachev20 Jul
    • Re: SUM() changed in 3.23?Michael Widenius30 Jul