Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1
-
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
- is duplicated by
-
-
- Closed
-