MAX() returns 1 recordset when it should be 0

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

MAX() returns 1 recordset when it should be 0

Post 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".
User avatar
nhan
Forum Commoner
Posts: 95
Joined: Sun Feb 27, 2005 8:26 pm
Contact:

Post by nhan »

Try to do the query in your mysql manually and check the results... :wink:
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

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

how about COUNT()...sounds like it would fit the bill better
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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).
Post Reply