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

            Hide
            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
            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
            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
            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: