From: Martijn Tonies
Date: December 6 2006 4:32pm
Subject: Re: SUM() of 1 and NULL is 1 ?
List-Archive: http://lists.mysql.com/mysql/203737
Message-Id: <02fe01c71954$2d8c38a0$9902a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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