Bug #3268 Functions around fields don't work as expected in sub-queries
Submitted: 23 Mar 2004 2:08 Modified: 23 Mar 2004 3:33
Reporter: Azza Azza69 Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.1.alpha OS:Windows (Win2k)
Assigned to: CPU Architecture:Any

[23 Mar 2004 2:08] Azza Azza69
Description:
Don't know if this is a bug or my design but this query:
SELECT
  (SELECT IFNULL(NULL,0) FROM detailfile WHERE False)
FROM
  headerfile h

Gives me NULL but this one gives me 0:
SELECT
  IFNULL((SELECT NULL FROM detailfile WHERE False),0)
FROM
  headerfile h

These queries have been completely cut down just to illustrate the problem (I get the same problems when joining files, etc).

How to repeat:
Perform both of the queries; they are not table specific so just change detailfile and headerfile aliases to valid table names.

Suggested fix:
n/a
[23 Mar 2004 3:33] Alexander Keremidarski
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

You get exactly the result you requested.

SELECT (SELECT IFNULL(NULL,0) FROM tbl WHERE False); -> NULL

and SELECT IFNULL(NULL, 0) -> 0
therefore

mysql> SELECT (SELECT IFNULL(NULL,0) FROM tbl WHERE False) AS sub1, IFNULL((SELECT IFNULL(NULL,0) FROM tbl WHERE False), 0) AS sub2;
+------+------+
| sub1 | sub2 |
+------+------+
| NULL | 0    |
+------+------+

I do not see anything unexpected here.
[23 Mar 2004 5:58] Azza Azza69
Is it not right that sub1 gives NULL where as sub2 gives 0 when they both use the IFNULL function to convert NULL's to 0?