Page 1 of 1

MAX() returns 1 recordset when it should be 0

Posted: Wed Jun 22, 2005 6:09 pm
by Swede78
I wanted to know if anyone else has stumbled across this or knows why this happens...

When I use the MAX() (Aggregate) Function in MySQL, I get one recordset even when I know it should be zero. Why does this happen? This is scary, because I usually count the rows, and if they're more than zero or equal to one, etc., I do something with that results. I haven't tested any other Aggregates, but imagine this isn't the only one.

Examples (in PHP):

Using the MAX() function...

Code: Select all

$query = &quote;SELECT MAX(Age) AS MaxAge FROM people WHERE Age > 99999&quote;;
$result = mysql_query($query, $connection) or die(mysql_error());
echo mysql_num_rows($result).' records returned';
This will result in "1 records returned".

If I remove the MAX() function...

Code: Select all

$query = &quote;SELECT Age FROM people WHERE Age > 99999&quote;;
$result = mysql_query($query, $connection) or die(mysql_error());
echo mysql_num_rows($result).' records returned';
This will result in "0 records returned".

Posted: Wed Jun 22, 2005 8:08 pm
by nhan
Try to do the query in your mysql manually and check the results... :wink:

Posted: Wed Jun 22, 2005 8:55 pm
by timvw
So the resultset is for "SELECT MAX(age) FROM people WHERE age > 99999"

MAX(age)
---------
0

Thus, there is 1 row (containing the maximum, which is 0)

Posted: Thu Jun 23, 2005 3:33 am
by JayBird
You really shouldnt use MAX() to count rows...very bad.

how about COUNT()...sounds like it would fit the bill better

Posted: Thu Jun 23, 2005 11:05 am
by Swede78

Code: Select all

Try to do the query in your mysql manually and check the results...
I tried this... when using MAX(), I get a NULL value returned in 1 row. When I do not use MAX(), I get "Empty set". Same results as when I use PHP to interface with MySQL. This was actually the reason I was testing it in PHP. Someone asked me to try this with PHP, because they found this little quirk while testing their code in VB/MySQL. So, it definitely is MySQL.

Code: Select all

So the resultset is for &quote;SELECT MAX(age) FROM people WHERE age > 99999&quote; 

MAX(age) 
--------- 
0
No, actually, both queries return NULL, not 0. But, I can kind of see where you're going. Maybe MAX() forces that something is returned.

Code: Select all

You really should use MAX() to count rows...very bad. 

how about COUNT()...sounds like it would fit the bill better
Huh??? I assume you meant "shouldn't". But, even if you did, I'm not using MAX() to count rows. I'm using the mysql_num_rows() function to count the rows returned. I'm not in need of a better way to count rows, because the count is accurate. It's the fact that I'm getting a row is the problem.

Posted: Thu Jun 23, 2005 6:54 pm
by timvw
Swede78 wrote:

Code: Select all

So the resultset is for &quote;SELECT MAX(age) FROM people WHERE age > 99999&quote; 

MAX(age) 
--------- 
0
No, actually, both queries return NULL, not 0. But, I can kind of see where you're going. Maybe MAX() forces that something is returned.
I just wrote what i thought/expected... Too lazy to think twice about it or give it a try :P


Anyway, i'm glad you see where i'm going. I think it's very reasonable to return NULL (thus one row) if you select the highest value when there are no values...

Posted: Fri Jun 24, 2005 10:07 am
by Swede78
Yeah, that must be the case. I guess I just expected an empty string if no records match the query. But, I guess those aggregate functions have that effect. I'll have to see if other functions do the same thing.

I'm just surprised that there are no notes on MySQL's website about this. I'll definitely have to search my past code and make sure I'm not relying on a recordset count when using MAX() in the query.

Thanks.

Posted: Sat Jun 25, 2005 7:08 am
by Weirdan
But, I guess those aggregate functions have that effect. I'll have to see if other functions do the same thing.
They do. I think this make sense, when you select nothing (no rows matching) you get nothing (0 rows), but when you select maximum/minimum/etc of nothing you get something (1 row). There is maximum, it just equals nothing (NULL in sql jargon).