[#MDEV-8871] Wrong result for CREATE TABLE .. SELECT LEAST(unsigned_column,unsigned

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)

[MDEV-8871] Wrong result for CREATE TABLE .. SELECT LEAST(unsigned_column,unsigned_column) Created: 2015-09-30 Updated: 2017-04-15 Resolved: 2015-09-30
Status:	Closed
Project:	MariaDB Server
Component/s:	OTHER
Affects Version/s:	5.5, 10.0, 10.1
Fix Version/s:	10.1.8