Problem with MySQL LEAST() function not returning results

I've been recently using LEAST(tb1, tb2, tb3) AS name within my queries to fetch minimal values across multiple columns and check whether they were not equal to a default value of 0.00

So far it was working correctly and as intended for me, as well, the version of the script on a remote server is working fine.

However, after changing column defaults and NULL status (I was attempting to see if i could use NULL as opposed to 0.00, which after reading the LEAST documentation would not have been what I wanted to do.) and then afterwards re-importing the table I was originally using. It still will not work as it did before, for no apparent reason, my queries that use LEAST() return nothing at all, empty arrays, and no visible error messages.

I have restarted MySQL, Flushed the tables, pulled the remote script to test on my dev server, and nothing seems to work.

Running the same query on the table via phpmyadmin on both my dev and remote server returns opposite results, locally it returns zero rows, remotely it returns rows, with the same EXPLAIN result on both(aside from #rows in the table).

Removing the LEAST() functions from the queries, doe开发者_StackOverflow社区s return data on my dev server.

Here is an example query:

SELECT id, make, model, LEAST(r24ch10k, r36ch10k, r24ch15k, r36ch15k, r24ch20k, r36ch20k) AS lowest 
    FROM xml 
    WHERE LEAST(r24ch10k, r36ch10k, r24ch15k, r36ch15k, r24ch20k, r36ch20k) != 0.00         
    ORDER BY lowest LIMIT 5

My Local Server Information:

Server Version: 5.1.51-community

Apache/2.2.16 (Win32) PHP/5.3.3

MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 300533 $

PHP extension: mysqli

My Remote Server Information:

Server version: 5.0.51a-24+lenny5

Apache/2.2.16 PHP/5.3.3

MySQL client version: 5.0.51a

PHP extension: mysql

Help in resolving this issue would be appreciated.

Don't know if this helps, LEAST()

Before MySQL 5.0.13, LEAST() returns NULL only if all arguments are NULL. As of 5.0.13, it returns NULL if any argument is NULL.

I figured the answer out with some help at the MySQL forums.

Since what I was looking for was the lowest value, setting the default value to be the lowest possible value is where I err'ed.

I have set the default value for the columns to a high value (9999999999.99), the values added to these columns will never exceed the default value.

So instead of

WHERE LEAST(col1, col2, col3) != 0.00

I would use

WHERE LEAST(col1, col2, col3) != 99999999999.99