MySQL Bugs: #62482: SQL_MODE=SENSIBLE_NULLS to take the headaches out of null.

Bug #62482	SQL_MODE=SENSIBLE_NULLS to take the headaches out of null.
Submitted:	20 Sep 2011 17:18	Modified:	22 Sep 2011 18:34
Reporter:	John Boero	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.1.40sp1-enterprise-gpl-advanced	OS:	Any (Any version)
Assigned to:		CPU Architecture:	Any
Tags:	concat, is vs =, SENSIBLE NULL, SQL_MODE

Description:
I don't care what ANSI says, or anyone else defending standards for years on these forums. I would love a SQL mode option that treats null more sensibly - like (dare I say) Oracle:

CONCAT appends values.  It should not wipe out an entire statement if you let one null slip into a concat() or || statement.

null = null is a tautology - regardless of the need for "is".  In a trigger, select * from my_table where val1=new.val1 will leave you scratching your head without using coalesce or ifnull around every field.

If I have to explode one more statement with ifnulls or coalesces around every field, I'm going to puke - or just dig into the code and add it myself.  If anyone else agrees, please chime in.

How to repeat:
* this is a feature request *

Suggested fix:
SQL_MODE=SENSIBLE_NULLS

Referencing threads/notes:

http://forums.mysql.com/read.php?97,109881,109881
http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Looks like a duplicate of Bug#31131.

Similar, but it's more the inverse of #31131.  31131 says "treat empty string as null" for purposes of fields tagged NOT NULL.  I say basically the opposite: "treat null as empty string" for concat and comparison purposes.