MySQL Bugs: #36365: ifnull returning wrong result

Bug #36365	ifnull returning wrong result
Submitted:	27 Apr 2008 13:31	Modified:	29 Apr 2008 11:47
Reporter:	Charles Loh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	ifnull

Description:
IFNULL is not returning the correct value when performing a join with no valid record.

How to repeat:
CREATE TABLE t1 (
  `Key` int(10) unsigned NOT NULL auto_increment,
  `Val` int(10) unsigned default NULL,
  PRIMARY KEY  (`Key` )
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

CREATE TABLE t2 (
  `Key` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`Key` )
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

insert into t1 values (1,1);
insert into t2 values (2);

select ifnull(t1.val, 999) from t1,t2 where t1.Key = t2.Key and t1.Key=1

can you detail the problem?
To me this looks consistent and OK:

select ifnull(t1.val, 999) from t1,t2 where t1.Key = t2.Key and t1.Key=1;
-- returns empty set

select t1.val from t1,t2 where t1.Key = t2.Key and t1.Key=1;
-- also returns empty set

select t1.Key = t2.Key and t1.Key=1 from t1,t2;
-- returns 0

select t1.Key = t2.Key from t1,t2;
-- returns 0

If the WHERE will filter out every row then ifnull(expr1,expr2) will return an empty set.  Only if rows are found matching the WHERE, then expr1 or expr2 would be returned for the rows found.  

Omitting the WHERE like

select ifnull(t1.val, 999) from t1,t2; -- returns 1 (there is one row that is not null)

The WHERE clause is a filter for what rows to return - but no WHERE will change the result of any row!

What result did you get and could you explain what did you expect?

Peter
(non MySQL person)

also 

CREATE TABLE t1 (
  `Key` int(10) unsigned NOT NULL auto_increment,
  `Val` int(10) unsigned default NULL,
  PRIMARY KEY  (`Key` )
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

insert into t1 values (1,1),(2,NULL);

select ifnull(t1.val, 999) from t1;

/*
returns

ifnull(t1.val, 999)
-------------------
                  1
                999
*/

Documentation http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull says

"IFNULL(expr1,expr2): If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2"

This is 'not a bug' .. :-)

Please, try to repeat with a newer version, 5.0.51a, and inform about the results.

I can not repeat the behaviour described with newer version:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.58-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.23 sec)

mysql> drop table t2;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE t1 (
    ->   `Key` int(10) unsigned NOT NULL auto_increment,
    ->   `Val` int(10) unsigned default NULL,
    ->   PRIMARY KEY  (`Key` )
    -> ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE t2 (
    ->   `Key` int(10) unsigned NOT NULL auto_increment,
    ->   PRIMARY KEY  (`Key` )
    -> ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.09 sec)

mysql> insert into t2 values (2);
Query OK, 1 row affected (0.03 sec)

mysql> select ifnull(t1.val, 999) from t1,t2 where t1.Key = t2.Key and t1.Key=1;

Empty set (0.06 sec)

SELECT IF(((select t1.Key = t2.Key and t1.Key=1 from t1,t2) = 0),999,(select t1.val from t1));
/*
will return

IF(((select t1.Key = t2.Key and t1.Key=1 from t1,t2) = 0),999,(select t1.val from t1))
----------------------------------------------------------------------------------------------
                                                                                           999
and
*/

SELECT IF(((select t1.Key = t2.Key and t1.Key=1 from t1,t2) <> 0),999,(select t1.val from t1));
/*
will return

IF(((select t1.Key = t2.Key and t1.Key=1 from t1,t2) <> 0),999,(select t1.val from t1))
---------------------------------------------------------------------------------------
                                                                                      1
*/

I think that was what you intended?  
(but with more than one row of data you will need to LIMIT the subqueries to return only one row)

I think it is obvious from the context that Charles does not expect an 'empty set' returned, but '999' instead!

My examples were verified on 5.0.51b abd 4.1.23.

Let him explain WHAT he thinks is the bug here!

Hi,
   i apologize for my idiocy.  Indeed, as  Peter pointed out, i was expecting a value of 999.  I realised that i was confused by the 3rd party GUI that i was using for the query.  The GUI was displaying "null" instead of "empty set".

I work with SQL server predominantly and Query Analyzer returns "" for no result instead of "null".