From: Steven Staples
Date: June 1 2010 3:15pm
Subject: Strange GREATEST() result in 5.0.32
List-Archive: http://lists.mysql.com/mysql/221772
Message-Id: <037d01cb019d$3c7f4ca0$b57de5e0$@net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hello all

I have a stored procedure that probably does a lot more than it should, but
it works fine on my test server (which is running 5.0.67).   When I moved it
over to the production server, (which is running 5.0.32 and I never thought
to check that the versions were the same before) it works almost perfectly.

The issue is when this runs:
GREATEST(d_UPDATE_Time, d_START_Time)

The value is '2010-05-21 20:26:18' and not '2010-05-26 21:49:51' even though
it is greater.


Both of these variables are declared in the top of the procedure:
DECLARE d_UPDATE_Time DATETIME;
DECLARE d_START_Time DATETIME;

When I put this in, to see the values of the variables, I get the right
data:
SELECT d_UPDATE_Time, d_START_Time;

d_UPDATE_Time        d_START_Time  
-------------------  -------------------
2010-05-26 21:49:51  2010-05-21 20:26:18


So if I ran this from the command line:
SELECT GREATEST('2010-05-26 21:49:51', '2010-05-21 20:26:18');

I get:
RESULT
-------------------
2010-05-26 21:49:51


So it works perfectly fine when it runs outside of the stored procedure, but
inside the procedure, it returns the wrong date :(

Is there something I am doing wrong?   I can't seem to see anything wrong.
Also, upgrading from 5.0.32 would require upgrading both the master and
slave, and right now, that is not a good idea (we are discussing it though)


Steven Staples