MYSQL why would min be larger than max?

Question

select min(lead), max(lead)
from products 
WHERE part_line != 90 and category = 'x'

When I run the above query the min returned is larger than the max. If I remove the condition pline != 90 the problem goes away.

I was thinking at first that mysql was processing the min, max before it got to pline... but that doesn't explain why it would assign a higher min.

What gets me, when I get the avg() it calculates correctly.

What's going on?

Answer 1

Maybe your fields are treated as text so, for example 21 will be smallest than 3 (alphabetical order) If that fields are type of TEXT try to cast values to INT()

Answer 2

Here is a sql server example:

with Test_CTE (first, second, third)
as(
    select '21 ', 'b', 1
    union
    select '3  ', 'd', 2
)
select MIN(first) as Minimum, MAX(first) as Maximum 
from Test_CTE

output is

Minimum Maximum
21      3  

Note that the values are Text data with trailing spaces, hence the reason it's sorted differently from how you might expect.