MySQL Bugs: #27759: least() incorrectness with date args

Bug #27759	least() incorrectness with date args
Submitted:	11 Apr 2007 15:16	Modified:	8 May 2007 19:11
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.16-beta, 5.0.37	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	qc

Description:
The docs say least() compares its args as ints, reals or strings depending on context. The docs do not mention date args, with which least() returns inconsistent results. If you change date args to char, least() returns the expected results. Whether this is a coding bug or a documentation bug, it ought to be corrected. 

How to repeat:
DROP PROCEDURE IF EXISTS test_least ;
DELIMITER |
CREATE PROCEDURE test_least()
BEGIN
declare v_a date default '2007-4-10';
declare v_b date default '2007-4-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;
|
DELIMITER ;
+-----------------+------------+------------+------------+------------+
| vers            | a          | b          | a_then_b   | b_then_a   |
+-----------------+------------+------------+------------+------------+
| 5.1.16-beta-log | 2007-04-10 | 2007-04-11 | 2007-04-11 | 2007-04-10 |
+-----------------+------------+------------+------------+------------+

Verified as described.
It seems that the problem arises only within stored procedures.
See the example below:

drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

create table t1 (a date,b date);
Query OK, 0 rows affected (0.01 sec)

insert into t1 values ('2007-4-10', '2007-4-11');
Query OK, 1 row affected (0.00 sec)

select a,b, least(a,b), least(b,a) from t1;
+------------+------------+------------+------------+
| a          | b          | least(a,b) | least(b,a) |
+------------+------------+------------+------------+
| 2007-04-10 | 2007-04-11 | 2007-04-10 | 2007-04-10 | 
+------------+------------+------------+------------+
1 row in set (0.00 sec)

# this works as expected

DROP PROCEDURE IF EXISTS p1 ;
Query OK, 0 rows affected (0.00 sec)

DELIMITER |
CREATE PROCEDURE p1()
    -> deterministic
    -> BEGIN
    ->     declare a date default '2007-4-10';
    ->     declare b date default '2007-4-11';
    ->     select 
    ->         version() as vers, a,b,
    ->         least( cast(a as date), 
    ->            cast(b as date) ) as cast_a_then_cast_b,
    ->         least( a,b )  as a_then_b,
    ->         least( b, a ) as b_then_a;
    -> END;
    -> |
Query OK, 0 rows affected (0.00 sec)

DELIMITER ;

call p1();
+------------+------------+------------+--------------------+------------+------------+
| vers       | a          | b          | cast_a_then_cast_b | a_then_b   | b_then_a   |
+------------+------------+------------+--------------------+------------+------------+
| 5.0.37-log | 2007-04-10 | 2007-04-11 | 2007-04-10         | 2007-04-11 | 2007-04-10 | 
+------------+------------+------------+--------------------+------------+------------+
1 row in set (0.00 sec)

# notice that the cast values return the expected result, 
# while the bare ones don't

How to repeat:

drop table if exists t1;
create table t1 (a date,b date);
insert into t1 values ('2007-4-10', '2007-4-11');
select a,b, least(a,b), least(b,a) from t1;

DROP PROCEDURE IF EXISTS p1 ;
DELIMITER |
CREATE PROCEDURE p1()
deterministic
BEGIN
    declare a date default '2007-4-10';
    declare b date default '2007-4-11';
    select 
        version() as vers, a,b,
        least( cast(a as date), 
           cast(b as date) ) as cast_a_then_cast_b,
        least( a,b )  as a_then_b,
        least( b, a ) as b_then_a;
END;
|
DELIMITER ;

call p1();

Bug #27760 was marked as a duplicate of this one.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26046

ChangeSet@1.2470, 2007-05-03 22:57:59+04:00, evgen@moonbone.local +9 -0
  Bug#27759: Wrong DATE/DATETIME comparison in LEAST()/GREATEST() functions.
  
  The LEAST/GREATEST functions were comparing DATE/DATETIME values as
  strings which in some cases can lead to a wrong result.
  
  The new member function called cmp_datetimes() is added to the
  Item_func_min_max class. It compares arguments in the DATETIME context
  and returns index of the least/greatest argument.
  The Item_func_min_max::fix_length_and_dec() function now detects when
  arguments should be compared in hte DATETIME context and set the newly
  added flag compare_as_dates. It indicates that the cmp_datetimes() function
  should be called to get a correct result.
  Item_func_min_max::val_xxx() methods are corrected to call the
  cmp_datetimes() function when needed.
  Objects of the Item_splocal class now stores and reports correct original
  field type.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26111

ChangeSet@1.2470, 2007-05-04 18:57:10+04:00, evgen@moonbone.local +9 -0
  Bug#27759: Wrong DATE/DATETIME comparison in LEAST()/GREATEST() functions.
  
  The LEAST/GREATEST functions compared DATE/DATETIME values as
  strings which in some cases could lead to a wrong result.
  
  A new member function called cmp_datetimes() is added to the
  Item_func_min_max class. It compares arguments in DATETIME context
  and returns index of the least/greatest argument.
  The Item_func_min_max::fix_length_and_dec() function now detects when
  arguments should be compared in DATETIME context and sets the newly
  added flag compare_as_dates. It indicates that the cmp_datetimes() function
  should be called to get a correct result.
  Item_func_min_max::val_xxx() methods are corrected to call the
  cmp_datetimes() function when needed.
  Objects of the Item_splocal class now stores and reports correct original
  field type.

Pushed into 5.1.18-beta

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26249

ChangeSet@1.2476, 2007-05-08 00:08:00+04:00, evgen@moonbone.local +2 -0
  Additional fix for the bug#27759: Wrong DATE/DATETIME comparison in LEAST()/GREATEST() functions.

Noted in 5.1.18 changelog.

The LEAST() and GREATEST() functions compared DATE and DATETIME
values as strings, which in some cases could lead to an incorrect
result.

Pushed into 5.1.19-beta

Pushed into 5.0.42