[MDEV-4804] Date comparing false result Created: 2013-07-22  Updated: 2013-08-22  Resolved: 2013-08-22

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.32, 5.3.12
Fix Version/s: 5.5.33, 5.3.13

Type: Bug Priority: Critical
Reporter: bulepage Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux


 Description    

set @h0="20111107";
 
set @h1="0";
 
select
 
COALESCE(date(@h0),date("1901-01-01")) as h0d,
 
COALESCE(date(@h1),date(now())) as h1d,
 
COALESCE(date(@h0),date("1901-01-01"))>COALESCE(date(@h1),date(now())) as compare_h0_gt_h1;

Date comparing is true in 5.5.32 but in 5.5.30 is false. False is the good result.

 Comments   
Comment by Elena Stepanova [ 2013-07-23 ]

The problem came in two steps.

revno: 3636
revision-id: sergii@pisem.net-20130317064122-vc1e8ytrrs5jku3g
fixes bug: https://mariadb.atlassian.net/browse/MDEV-4281
branch nick: 5.3
MDEV-4281 Assertion `maybe_null && item->null_value' fails in make_sortkey on CASE with different return types, GROUP_CONCAT, GROUP BY

revno: 3665
revision-id: sergii@pisem.net-20130703074620-23eo2cve9hvm0xi8
fixes bug: https://mariadb.atlassian.net/browse/MDEV-4667
committer: Sergei Golubchik <sergii@pisem.net>
branch nick: 5.3
MDEV-4667 DATE('string') incompability between mysql and mariadb

Until the revision 3636, it worked all right – date("0") returned NULL, h1d was set to date(now()), and comparison returned false:

h0d h1d compare_h0_gt_h1
2011-11-07 2013-07-23 0
Warnings:
Warning 1292 Incorrect datetime value: '0'
Warning 1292 Incorrect datetime value: '0'
select date("0");
date("0")
NULL

Since revno 3636 and until revision 3635, date("0") returned "0000-00-00", and consequently the comparison returned true:

h0d h1d compare_h0_gt_h1
2011-11-07 0000-00-00 1
Warnings:
Warning 1292 Incorrect datetime value: '0'
Warning 1292 Incorrect datetime value: '0'
select date("0");
date("0")
0000-00-00

Starting with revno 3665, date("0") returns NULL again, and h1d is date(now()) again, but comparison result remains true:

h0d h1d compare_h0_gt_h1
2011-11-07 2013-07-23 1
Warnings:
Warning 1292 Incorrect datetime value: '0'
Warning 1292 Incorrect datetime value: '0'
select date("0");
date("0")
NULL

Comment by Alexander Barkov [ 2013-08-20 ]

A smaller test case demonstrating the problem:

MariaDB [test]> SELECT DATE('20011107'),DATE('0'),COALESCE(DATE('0'),CURRENT_DATE) AS d1, DATE('20011107')>COALESCE(DATE('0'),CURRENT_DATE) AS cmp;
-----------------------------------------+

DATE('20011107') DATE('0') d1 cmp

-----------------------------------------+

2001-11-07 NULL 2013-08-20 1

-----------------------------------------+
1 row in set, 3 warnings (0.00 sec)

Comment by Alexander Barkov [ 2013-08-20 ]

IFNULL has the same problem:

MariaDB [test]> SELECT DATE('20011107'),DATE('0'),IFNULL(DATE('0'),CURRENT_DATE) AS d1, DATE('20011107')>IFNULL(DATE('0'),CURRENT_DATE) AS cmp;
-----------------------------------------+

DATE('20011107') DATE('0') d1 cmp

-----------------------------------------+

2001-11-07 NULL 2013-08-20 1

-----------------------------------------+
1 row in set, 3 warnings (0.00 sec)

Comment by Alexander Barkov [ 2013-08-22 ]

Pushed to 5.3.13 and 5.5.33

Generated at Thu Feb 08 06:59:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.