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

MIN(timestamp_column) returns a wrong result in a GROUP BY query

    XMLWordPrintable

Details

    Description

      I run this script using two timestamp values near DST change:

      SET time_zone='Europe/Moscow';
      CREATE OR REPLACE TABLE t1 (i INT, d TIMESTAMP);
      SET timestamp=1288477526  /* this is summer time */ ;
      INSERT INTO t1 VALUES (3,NULL);
      SET timestamp=1288477526+3599 /* this is winter time*/ ;
      INSERT INTO t1 VALUES (3,NULL);
      SELECT i, d, UNIX_TIMESTAMP(d) FROM t1 ORDER BY d;
      

      +------+---------------------+-------------------+
      | i    | d                   | UNIX_TIMESTAMP(d) |
      +------+---------------------+-------------------+
      |    3 | 2010-10-31 02:25:26 |        1288477526 |
      |    3 | 2010-10-31 02:25:25 |        1288481125 |
      +------+---------------------+-------------------+
      

      The record with Unix timestamp value of 1288477526 returned first, because it is smaller.

      Now I run a GROUP BY query with MIN:

      SELECT i, MIN(d) FROM t1 GROUP BY i;
      

      +------+---------------------+
      | i    | MIN(d)              |
      +------+---------------------+
      |    3 | 2010-10-31 02:25:25 |
      +------+---------------------+
      

      It erroneously chose a wrong record.
      This happens because TIMESTAMP in this piece of code is handled in striing format (which uses DATETIME rather than TIMESTAMP representation):

      void Item_sum_hybrid::update_field()
      {
        DBUG_ENTER("Item_sum_hybrid::update_field");
        Item *UNINIT_VAR(tmp_item);
        if (unlikely(direct_added))
        {
          tmp_item= args[0];
          args[0]= direct_item;
        }
        switch (result_type()) {
        case STRING_RESULT:
          min_max_update_str_field();
          break;
        case INT_RESULT:
          min_max_update_int_field();
          break;
        case DECIMAL_RESULT:
          min_max_update_decimal_field();
          break;
        default:
          min_max_update_real_field();
        }
        if (unlikely(direct_added))
        {
          direct_added= FALSE;
          args[0]= tmp_item;
        }
        DBUG_VOID_RETURN;
      }
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.