Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4804

Date comparing false result

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Critical
        • Resolution: Fixed
        • Affects Version/s: 5.5.32, 5.3.12
        • Fix Version/s: 5.5.33, 5.3.13
        • Component/s: None
        • 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.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                elenst Elena Stepanova added a comment - - edited

                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

                Show
                elenst Elena Stepanova added a comment - - edited 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
                Hide
                Permalink
                bar Alexander Barkov added a comment -

                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)

                Show
                bar Alexander Barkov added a comment - 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)
                Hide
                Permalink
                bar Alexander Barkov added a comment -

                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)

                Show
                bar Alexander Barkov added a comment - 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)
                Hide
                Permalink
                bar Alexander Barkov added a comment -

                Pushed to 5.3.13 and 5.5.33

                Show
                bar Alexander Barkov added a comment - Pushed to 5.3.13 and 5.5.33

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bulepage bulepage
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    4 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: