Link to home
Create AccountLog in
Avatar of dolan2go
dolan2goFlag for United States of America

asked on

Bug with min() in query ??

This one's a real stumper (for me).

The pictures included below should help to define the problem. Num is an integer, primary key, auto increment.

First, the data being polled.
User generated image
Using the query:
SELECT min(Num), empNum, lastName, travelerName, runTime, fltNum, depCity, timeZone, text, textNum FROM Check WHERE runTime BETWEEN TIMESTAMPADD ( MINUTE, 0, NOW() ) AND TIMESTAMPADD ( MINUTE, 15, NOW() ) AND accomp = 'YES'

Open in new window

in both phpmyadmin and from within mysql_query ( ), this query returns,
User generated imageUsing max(Num), returns the following,
 User generated imageThe pictures paint a glaring anomoly. Or, am I using the wrong function to return one row?

min() seems to be grabbing the minimum empNum (also an int), while max() seems to get the correct data.

All comments welcome. Please help.
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Try removing the min function and sorting by the Num field to see what is the lowest value returned.  Based on what you have sown 8386 looks to be the min.
Avatar of dolan2go

ASKER

@lowfatspread,

Thank you for the suggestion. That seems to remedy the problem. Can you explain why my original query doesn't work with MIN(Num)?

I'd like to gain some understanding on this issue. I'd like also to try to ensure that this doesn't cause some other problem. Obviously, the situation and database are much deeper than this question. The original query was returning the lowest Num as long as there weren't duplicates in other fields. This behavior doesn't make sense to me.

@jocasio123,

Thank you. Your suggestion sort of begs for the processing to be done by the script. I'm attempting to 'clean up' the code and let mysql pick the record that is within the time constraints AND has the lowest Num value. It seems that should be easily within a mysql query.

Am I following your suggestion or does it sound like I've missed your point?
Still my question is asking if the demonstrated behavior with MIN (Num) is a bug??
It is as if the records are being crossed with MIN but not with MAX.

??
Apologies for the number of posts.

To clarify my confusion. It's as if the Num and the rest of the query's data fields are being crossed for MIN but are correct with MAX.
sorry haven't used mysql much recently....

which version are you using...

however your basic problem is that you are using the Min() function without a specific group by clause
which can/will lead to an arbitrary result being returned....

to obtain a MIN or MAX "row"  from a table a self join or exists sub query is usually better (and standard sql across db platforms)

hth

@lowfatspread,

The server is MySQL 5.0.91.

to obtain a MIN or MAX "row"  from a table a self join or exists sub query is usually better (and standard sql across db platforms)
Is this the reason for your reason for the query you gave in your first 12:49pm post? Is this the sub query?

and Num = (SELECT MIN(Num)  FROM Check
WHERE runTime
BETWEEN TIMESTAMPADD ( MINUTE, 0, NOW() )
AND TIMESTAMPADD ( MINUTE, 15, NOW() )
AND accomp = 'YES')

Open in new window


Is the redundancy of BETWEEN TIMESPAMPADD .... in 2 places required? Can I omit the first WHERE clause to have the query be like

SELECT Num, empNum, lastName, travelerName, runTime, fltNum, depCity, timeZone, text, textNum
FROM Check
WHERE Num = (SELECT MIN(Num)  FROM Check
WHERE runTime
BETWEEN TIMESTAMPADD ( MINUTE, 0, NOW() )
AND TIMESTAMPADD ( MINUTE, 15, NOW() )
AND accomp = 'YES')

Open in new window

Just tested the query,

SELECT Num, empNum, lastName, travelerName, runTime, fltNum, 
depCity, timeZone, text, textNum
FROM Check
WHERE Num = (SELECT MIN(Num) FROM Check
WHERE runTime
BETWEEN TIMESTAMPADD ( MINUTE, 0, NOW() )
AND TIMESTAMPADD ( MINUTE, 15, NOW() )
AND accomp = 'YES')

Open in new window


and it seems to be responding in the desired manner.

If anyone can express a reason not to leave the query like this, please do.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account