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

Wrong character set of ADDTIME() and DATE_ADD()

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.4
    • Fix Version/s: 10.0.5
    • Component/s: None
    • Labels:
      None

      Description

      ADDTIME(expr1,expr2) is a hybrid function.

      • In case when expr1 is DATETIME, the result type is DATETIME.
      • In case when expr1 is TIME, the result type is TIME.
      • Otherwise, the result type is VARCHAR, and the choice between
        DATETIME or TIME format is done per-row.

      In case of VARCHAR result, the function must follow the standard
      MySQL/MariaDB convention:
      character set and collation of a string function returning a result
      from numeric or temporal input should be set according to
      @@character_set_connection and @@collation_connection
      respectively.

      http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

      In MySQL 5.6, implicit conversion of a numeric or temporal value to string produces a value that has a character set and collation determined by the character_set_connection and collation_connection system variables.

      Consider this SQL script:

      set character_set_connection=ucs2;
      select charset(addtime(_latin1'10:01:01',_latin1'10:00:00'));
      drop table if exists t1;
      create table t1 as select addtime(_latin1'10:01:01',_latin1'10:00:00') as a;
      show create table t1;
      

      MySQL-5.6 does follow this rule, and the output is:

      +-------------------------------------------------------+
      | charset(addtime(_latin1'10:01:01',_latin1'10:00:00')) |
      +-------------------------------------------------------+
      | ucs2                                                  |
      +-------------------------------------------------------+
      1
      +-------+--------------------------------------------------------------------------------------------
      
      ...
      
      ------------------+
      | Table | Create Table                                                                                                 |
      +-------+--------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` varchar(29) CHARACTER SET ucs2 DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+--------------------------------------------------------------------------------------------------------------+
      

      Notice: ucs2 is consistently reported in both tables in the output.

      MariaDB does not follow this rule, and the output is:

      +-------------------------------------------------------+
      | charset(addtime(_latin1'10:01:01',_latin1'10:00:00')) |
      +-------------------------------------------------------+
      | binary                                                |
      +-------------------------------------------------------+
      
      ...
      
      +-------+-------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                              |
      +-------+-------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` varchar(26) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------
      

      Moreover, the result of CHARSET() and of "SHOW CREATE" do not
      match to each over (binary vs latin1).

      The same problem is observed with another temporal hybrid function DATE_ADD.
      The script:

      set character_set_connection=ucs2;
      select charset(date_add(_latin1'10:01:01',interval 10 second));
      drop table if exists t1;
      create table t1 as select date_add(_latin1'10:01:01',interval 10 second) as a;
      show create table t1;
      

      Results from MySQL-5.6 are consistently correct:

      +---------------------------------------------------------+
      | charset(date_add(_latin1'10:01:01',interval 10 second)) |
      +---------------------------------------------------------+
      | ucs2                                                    |
      +---------------------------------------------------------+
      +-------+--------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                 |
      +-------+--------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` varchar(29) CHARACTER SET ucs2 DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+--------------------------------------------------------------------------------------------------------------+
      

      Result from MariaDB-10.0 are wrong and non-consistent:

      +---------------------------------------------------------+
      | charset(date_add(_latin1'10:01:01',interval 10 second)) |
      +---------------------------------------------------------+
      | binary                                                  |
      +---------------------------------------------------------+
      +-------+-------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                              |
      +-------+-------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `a` varchar(26) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------+
      
      

        Gliffy Diagrams

          Activity

          Hide
          bar Alexander Barkov added a comment -

          Pushed into 10.0.5

          Show
          bar Alexander Barkov added a comment - Pushed into 10.0.5

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 hour
                1h