List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:December 6 2006 4:32pm
Subject:Re: SUM() of 1 and NULL is 1 ?
View as plain text  
Aggregates ignore NULL as per SQL standard, so this behaviour
is valid.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


Thanks Visolve, Peter,

This is a serious matter, because:
- within a row: 1 + NULL = NULL
- across rows with SUM(): 1 + NULL = 1

I know the manual says that group functions ignore NULL values (12.10.1),
but it also says: Conceptually, NULL means "a missing unknown value"
(3.3.4.6).
IMHO a NULL with any value should always add to NULL.
I was hoping for an option / setting to change NULL behaviour.
Well, I will try the suggested alternatives.

Thanks, Cor

Thread
SUM() of 1 and NULL is 1 ?C.R.Vegelin6 Dec
  • RE: SUM() of 1 and NULL is 1 ?Peter Lauri6 Dec
  • Re: SUM() of 1 and NULL is 1 ?ViSolve DB Team6 Dec
  • Re: SUM() of 1 and NULL is 1 ?ViSolve DB Team6 Dec
  • Re: SUM() of 1 and NULL is 1 ?Remo Tex7 Dec
Re: SUM() of 1 and NULL is 1 ?C.R.Vegelin6 Dec
  • Re: SUM() of 1 and NULL is 1 ?Martijn Tonies6 Dec
  • Re: SUM() of 1 and NULL is 1 ?Peter Brawley6 Dec