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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.1, 10.0
    • Fix Version/s: 10.1.8
    • Component/s: OTHER
    • Labels:
    • Sprint:
      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

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

                Dates

                Created:
                Updated:
                Resolved: