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

Wrong result for CREATE TABLE .. SELECT LEAST(unsigned_column,unsigned_column)

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 10.1.8
    • OTHER
    • 10.1.8-3, 10.1.8-4

    Description

      If I run this query:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (b INT UNSIGNED);
      INSERT INTO t1 VALUES (4294967295);
      SELECT LEAST(b,b) FROM t1;

      it returns a correct result set:

      +------------+
      | LEAST(b,b) |
      +------------+
      | 4294967295 |
      +------------+

      Now if I do CREATE TABLE .. SELECT with the same expression:

      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 AS SELECT LEAST(b,b) FROM t1;
      SHOW WARNINGS;

      it returns a warning:

      +---------+------+-----------------------------------------------------+
      | Level   | Code | Message                                             |
      +---------+------+-----------------------------------------------------+
      | Warning | 1264 | Out of range value for column 'LEAST(b,b)' at row 1 |
      +---------+------+-----------------------------------------------------

      Now this script:

      SELECT * FROM t2;
      SHOW CREATE TABLE t2;

      reveals that it erroneously created a SIGNED INT column, and the value was truncated to maximum SIGNED INT range:

      +------------+
      | LEAST(b,b) |
      +------------+
      | 2147483647 |
      +------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SHOW CREATE TABLE t2;
      +-------+------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                   |
      +-------+------------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `LEAST(b,b)` int(11) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      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.