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

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

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.1, 10.0, 5.5
        • 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)

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                bar Alexander Barkov added a comment -

                Repeatable with MySQL-5.7.8

                Show
                bar Alexander Barkov added a comment - Repeatable with MySQL-5.7.8

                  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:

                      Agile