Bug #27760 The least() function used on date local vars in a stored proc seems busted.
Submitted: 11 Apr 2007 15:50 Modified: 13 Apr 2007 22:51
Reporter: David Sahagian Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0.40-BK, 5.0.27-community-nt OS:Windows (XP, Linux)
Assigned to: CPU Architecture:Any
Tags: least()

[11 Apr 2007 15:50] David Sahagian 
Description:
Is this a bug or do I misunderstand the least() function ? 
Thanks, 
-dvs- 

========== 
DROP PROCEDURE IF EXISTS dvs_test_least ; 
DELIMITER <eoproc> 
CREATE PROCEDURE dvs_test_least () 
BEGIN 
/* 
Why are "a_then_b" and "b_then_a" DIFFERENT ? 
*/ 
declare v_a date ; 
declare v_b date ; 

set v_a = '2007-04-10' ; 
set v_b = '2007-04-11' ; 

select 
version() as vers, 
v_a as a, 
v_b as b, 
least( v_a, v_b ) as a_then_b, 
least( v_b, v_a ) as b_then_a 
; 
END 
<eoproc> 
DELIMITER ; 
==========

How to repeat:
Please create this mini proc, and then CALL it.

Suggested fix:
I dont know.
[12 Apr 2007 6:28] Valeriy Kravchuk 
Thank you for a bug report. Verified just as described with latest 5.0.40-BK on Linux:

mysql> DROP PROCEDURE IF EXISTS dvs_test_least ;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> DELIMITER <eoproc>
mysql> CREATE PROCEDURE dvs_test_least ()
    -> BEGIN
    -> /*
   /*> Why are "a_then_b" and "b_then_a" DIFFERENT ?
   /*> */
    -> declare v_a date ;
    -> declare v_b date ;
    ->
    -> set v_a = '2007-04-10' ;
    -> set v_b = '2007-04-11' ;
    ->
    -> select
    -> version() as vers,
    -> v_a as a,
    -> v_b as b,
    -> least( v_a, v_b ) as a_then_b,
    -> least( v_b, v_a ) as b_then_a
    -> ;
    -> END
    -> <eoproc>
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call dvs_test_least();
+--------+------------+------------+------------+------------+
| vers   | a          | b          | a_then_b   | b_then_a   |
+--------+------------+------------+------------+------------+
| 5.0.40 | 2007-04-10 | 2007-04-11 | 2007-04-11 | 2007-04-10 |
+--------+------------+------------+------------+------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select least('2007-04-11', '2007-04-10') a_then_b;
+------------+
| a_then_b   |
+------------+
| 2007-04-10 |
+------------+
1 row in set (0.00 sec)

mysql> select least('2007-04-10', '2007-04-11') b_then_a;
+------------+
| b_then_a   |
+------------+
| 2007-04-10 |
+------------+
1 row in set (0.01 sec)

This is a weird bug.
[13 Apr 2007 22:51] Sergei Golubchik 
duplicate of bug#27759