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

Wrong value and data type of "SELECT MAX(time_column) + 1 FROM t1"

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.0.3, 5.5.32, 5.3.12
    • 5.5.35, 10.0.7
    • None
    • None

    Description

      DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (t0 TIME);
      INSERT INTO t1 VALUES ('10:10:10'); 
      CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1; SELECT * FROM t2; SHOW COLUMNS FROM t2;

      produces this output in MariaDB-5.5 and MariaDB-10.0:

      +-------------+
      | MAX(t0)+1   |
      +-------------+
      | 36610000001 |
      +-------------+
      +-----------+---------------+------+-----+---------+-------+
      | Field     | Type          | Null | Key | Default | Extra |
      +-----------+---------------+------+-----+---------+-------+
      | MAX(t0)+1 | decimal(11,0) | YES  |     | NULL    |       |
      +-----------+---------------+------+-----+---------+-------+

      36610000001 is a wrong value. The expected value is 101011.

      DECIMAL(11,0) is a wrong type. The expected type is INT(9).

      Note, MariaDB-5.3 returns 11 instead of 36610000001, which is also wrong.

      Similar problem is observed with a DATETIME column.
      This script:

      CREATE TABLE t1 (t0 DATETIME);
      INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
      CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
      SELECT * FROM t2;
      SHOW COLUMNS FROM t2;

      produces this output:

      +-------------------+
      | MAX(t0)+1         |
      +-------------------+
      | 71923630210000001 |
      +-------------------+
      +-----------+---------------+------+-----+---------+-------+
      | Field     | Type          | Null | Key | Default | Extra |
      +-----------+---------------+------+-----+---------+-------+
      | MAX(t0)+1 | decimal(20,0) | YES  |     | NULL    |       |
      +-----------+---------------+------+-----+---------+-------+

      Attachments

        Activity

          People

            bar Alexander Barkov
            bar Alexander Barkov
            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.