MySQL Bugs: #26052: MAX() is NOT returning NULL on an empty set

Bug #26052	MAX() is NOT returning NULL on an empty set
Submitted:	3 Feb 2007 18:56	Modified:	9 Mar 2007 16:50
Reporter:	Emanuel Falkenauer	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.0.27-community-nt	OS:	Windows (XPP-SP2)
Assigned to:		CPU Architecture:	Any

Description:
Hello,

Here's a good one for you - and a CRITICAL one for me: MAX() spuriously returns a regular value (1 in my case) even though the set of values it's computed on is empty. According to the manual (and commonsense), it should return NULL.
I'm getting this behavior both at the concole and with ODBC access. Here is how it looks on the console:

mysql> SELECT TCF42 FROM TCF13 WHERE TCF45=(SELECT TCF45 FROM TCF14 WHERE TCF53=0x00);
Empty set (0.00 sec)

mysql> SELECT MAX(TCF42) FROM TCF13 WHERE TCF45=(SELECT TCF45 FROM TCF14 WHERE TCF53=0x00);
+------------+
| MAX(TCF42) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

In other words, the set of TCF42 is empty, yet its MAX is 1. This is serious in my application, because going around it would need an additional query (to see whether there is , in fact, any resulting TCF42), causing a serious performance degradation.

This looks like a tough one to trace down, because it does NOT happen all the time, i.e. sometimes the result IS NULL. In fact, the same statement issued one second later in the same console sometimes produces the other result. Also, it seems to occur only on localhost. Sometimes it gets "fixed" by issuing the same statement while logged from a remote host: on the local host the MAX statement then yields NULL... but one second later it yields 1 again.

Thanks in advance for your help!

How to repeat:
I'm sending you a backup of the database where you SHOULD be able to repeat the bug by issuing the cited statements - hopefully you will reproduce it!

Thank you for a problem report. Please, send your my.ini file content, for completeness. Can you repeat this behaviour with a single session?

Hi,
I added the my.ini file as requested. What do you mean by "Can you repeat this behaviour with a single session?"
Thanks!

Hello,
Just being curious: were you able to reproduce the problem?
Thanks!

I was not able to repeat the behavioue described with latest 5.0.38-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot mysqltest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.38 Source distribution

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

mysql> show tables;
+---------------------+
| Tables_in_mysqltest |
+---------------------+
| tcf13               |
| tcf14               |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT tcf42 FROM tcf13 WHERE tcf45=(SELECT tcf45 FROM tcf14 WHERE tcf53
=0x00);
Empty set (0.00 sec)

mysql> SELECT max(tcf42) FROM tcf13 WHERE tcf45=(SELECT tcf45 FROM tcf14 WHERE
tcf53=0x00);
+------------+
| max(tcf42) |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

So, please, wait for 5.0.37 to be released officially (really soon), and check with it.