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

ADD_MONTHS with a non-integer argument gives a different result on Oracle compared to MariaDB

Details

    Description

      MariaDB:

      SELECT ADD_MONTHS('1994-07-07', 2);
      +-----------------------------+
      | ADD_MONTHS('1994-07-07', 2) |
      +-----------------------------+
      | 1994-09-07                  |
      +-----------------------------+
       
      SELECT ADD_MONTHS('1994-07-07', 2.6);
      +-------------------------------+
      | ADD_MONTHS('1994-07-07', 2.6) |
      +-------------------------------+
      | 1994-10-07                    |
      +-------------------------------+
      

      Oracle:

      SELECT ADD_MONTHS('07-JUL-1994',2) FROM DUAL
      ADD_MONTHS('07-JUL-1994',2.6)
      07-SEP-94
      	
      SELECT ADD_MONTHS('07-JUL-1994',2.6) FROM DUAL	
      ADD_MONTHS('07-JUL-1994',2)
      07-SEP-94
      

      Is this intentional? Also, no warnings.

      Attachments

        Issue Links

          Activity

            Not intentional

            serg Sergei Golubchik added a comment - Not intentional
            Gosselin Dave Gosselin added a comment - - edited

            The grammar support for ADD_MONTHS allows expr for both arguments, which results in an Item_decimal getting created for the second argument for rational numbers, in this case 2.6. When Func_handler_date_add_interval::add is invoked, it calls get_interval_value which relies on the item argument's val_int() implementation to do the right thing. In the case of Item_decimal, val_int() will return a value rounded to the nearest integer because it relies on my_decimal which in turn relies on my_decimal2int and decimal_round to handle rounding according to a policy flag. That policy flag is set to HALF_UP by default because my_decimal doesn't supply an alternative parameter to my_decimal2int. In the present bug, that causes 2.6 to be rounded to 3 which is returned by Item_decimal::val_int() during get_interval_value, thus resulting in 1994-10-07 instead of the expected 1994-09-07 as otherwise expected.

            Gosselin Dave Gosselin added a comment - - edited The grammar support for ADD_MONTHS allows expr for both arguments, which results in an Item_decimal getting created for the second argument for rational numbers, in this case 2.6. When Func_handler_date_add_interval::add is invoked, it calls get_interval_value which relies on the item argument's val_int() implementation to do the right thing. In the case of Item_decimal, val_int() will return a value rounded to the nearest integer because it relies on my_decimal which in turn relies on my_decimal2int and decimal_round to handle rounding according to a policy flag. That policy flag is set to HALF_UP by default because my_decimal doesn't supply an alternative parameter to my_decimal2int. In the present bug, that causes 2.6 to be rounded to 3 which is returned by Item_decimal::val_int() during get_interval_value, thus resulting in 1994-10-07 instead of the expected 1994-09-07 as otherwise expected.
            Gosselin Dave Gosselin added a comment -

            After a conversation with serg we decided to document the current behavior and close this as not-a-bug.

            Gosselin Dave Gosselin added a comment - After a conversation with serg we decided to document the current behavior and close this as not-a-bug.
            Gosselin Dave Gosselin added a comment -

            Updated documentation at https://mariadb.com/kb/en/add_months/

            Gosselin Dave Gosselin added a comment - Updated documentation at https://mariadb.com/kb/en/add_months/

            People

              Gosselin Dave Gosselin
              greenman Ian Gilfillan
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.