Description:
According to documentation (Version 5.0; 9.1.6: NULL Values; http://dev.mysql.com/doc/refman/5.0/en/null-values.html) NULL means "no data".
According to documentation (Version 5.0; 12.2.: Control Flow Functions; http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html) IFNULL() is defined as follows:
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.
Obtaining a value from a query with no result in the meaning of "no data" with IFNULL() returns wrong results; in other words: IFNULL() returns undefined values.
In MS SQL Server and Oracle 10 the syntax equivalents catch correct NULL values and return the NULL replacements.
How to repeat:
CREATE TABLE t (
id int(10) NOT NULL auto_increment,
a int(10) unsigned NOT NULL,
f int(10) unsigned NOT NULL,
p int(11) unsigned NOT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t(a, f, p)
VALUES(1, 1, 1);
SELECT IFNULL(id, 0) INTO @tmp
FROM t
WHERE a = 1 AND f = 1 AND p = 1;
SELECT @tmp;
-- @tmp returns 1 - correct
SELECT IFNULL(id, 0) INTO @tmp
FROM t
WHERE a = 2 AND f = 2 AND p = 2;
SELECT @tmp;
-- @tmp returns 1 - wrong
In the last SELECT statement 0 is expected, because of executing a query with no result in the meaning of no data which results in a NULL value that should be transformed to by IFNULL().
The real return is 1 again because the new query result wasn't written to @tmp.
Suggested fix:
Resetting the variable @tmp to a defined value (e.g. 0) before reusing it as a store for subsequent query results will avoid struggling with that real nice feature.
