Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 5.5.32, 5.3.12
    • Fix Version/s: 5.5.33, 5.3.13
    • Labels:
      None
    • Environment:
      Linux
    • Global Rank:
      3356

      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.

        Activity

        Hide
        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
        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
        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
        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
        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
        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
        Alexander Barkov added a comment -

        Pushed to 5.3.13 and 5.5.33

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

          People

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

            Dates

            • Created:
              Updated:
              Resolved: