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

Out-of-range error on CREATE..SELECT with a view using MAX and EXTRACT(MINUTE_MICROSECOND..)

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1, 10.2, 10.3
    • 10.3.1
    • OTHER

    Description

      I run this script:

      SET sql_mode=STRICT_ALL_TABLES;
      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (
        id bigint(11) NOT NULL PRIMARY KEY,
        dt datetime(6)
      );
      INSERT INTO t1 VALUES (1,'2001-01-01 11:22:33.123456');
      CREATE OR REPLACE VIEW v1 AS SELECT EXTRACT(MINUTE_MICROSECOND FROM dt) AS dt2 FROM t1;
      DESCRIBE v1;
      SELECT * FROM v1;
      

      It correctly reports that the data type for EXTRACT(MINUTE_MICROSECOND) is BIGINT:

      +-------+------------+------+-----+---------+-------+
      | Field | Type       | Null | Key | Default | Extra |
      +-------+------------+------+-----+---------+-------+
      | dt2   | bigint(11) | YES  |     | NULL    |       |
      +-------+------------+------+-----+---------+-------+
      

      and returns the correct value which is outside of the signed 32-bit range:

      +------------+
      | dt2        |
      +------------+
      | 2233123456 |
      +------------+
      

      Now I want to create a table as follows:

      CREATE OR REPLACE TABLE t2 AS SELECT MAX(dt2) FROM v1;
      

      It unexpectedly returns an error:

      ERROR 1264 (22003): Out of range value for column 'MAX(dt2)' at row 2
      

      Adding LIMIT 0 shows that it erroneously creates an INT rather than BIGINT column:

      CREATE OR REPLACE TABLE t2 AS SELECT MAX(dt2) FROM v1 LIMIT 0;
      DESCRIBE t2;
      

      +----------+---------+------+-----+---------+-------+
      | Field    | Type    | Null | Key | Default | Extra |
      +----------+---------+------+-----+---------+-------+
      | MAX(dt2) | int(11) | YES  |     | NULL    |       |
      +----------+---------+------+-----+---------+-------+
      

      The problem is in Item_sum::create_tmp_field:

        Field *create_tmp_field(bool group, TABLE *table)
        {
          return Item::create_tmp_field(group, table, MY_INT32_NUM_DECIMAL_DIGITS);
        }
      

      It creates BIGINT only if max_char_length() is greater than MY_INT32_NUM_DECIMAL_DIGITS, i.e. starting from 12.

      Attachments

        Issue Links

          Activity

            People

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