[#MDEV-12860] Out-of-range error on CREATE..SELECT with a view using MAX and EXTRACT(MINUTE

I run this script:

SET sql_mode=STRICT_ALL_TABLES;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

  id bigint(11) NOT NULL PRIMARY KEY,

  dt datetime(6)

);

INSERT INTO t1 VALUES (1,'2001-01-01 11:22:33.123456');

CREATE OR REPLACE VIEW v1 AS SELECT EXTRACT(MINUTE_MICROSECOND FROM dt) AS dt2 FROM t1;

DESCRIBE v1;

SELECT * FROM v1;

It correctly reports that the data type for EXTRACT(MINUTE_MICROSECOND) is BIGINT:

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| dt2   | bigint(11) | YES  |     | NULL    |       |

+-------+------------+------+-----+---------+-------+

and returns the correct value which is outside of the signed 32-bit range:

+------------+

| dt2        |

+------------+

| 2233123456 |

+------------+

Now I want to create a table as follows:

CREATE OR REPLACE TABLE t2 AS SELECT MAX(dt2) FROM v1;

It unexpectedly returns an error:

ERROR 1264 (22003): Out of range value for column 'MAX(dt2)' at row 2

Adding LIMIT 0 shows that it erroneously creates an INT rather than BIGINT column:

CREATE OR REPLACE TABLE t2 AS SELECT MAX(dt2) FROM v1 LIMIT 0;

DESCRIBE t2;

+----------+---------+------+-----+---------+-------+

| Field    | Type    | Null | Key | Default | Extra |

+----------+---------+------+-----+---------+-------+

| MAX(dt2) | int(11) | YES  |     | NULL    |       |

+----------+---------+------+-----+---------+-------+

The problem is in Item_sum::create_tmp_field:

  Field *create_tmp_field(bool group, TABLE *table)

    return Item::create_tmp_field(group, table, MY_INT32_NUM_DECIMAL_DIGITS);

It creates BIGINT only if max_char_length() is greater than MY_INT32_NUM_DECIMAL_DIGITS, i.e. starting from 12.

[MDEV-12860] Out-of-range error on CREATE..SELECT with a view using MAX and EXTRACT(MINUTE_MICROSECOND..) Created: 2017-05-22 Updated: 2017-05-22 Resolved: 2017-05-22
Status:	Closed
Project:	MariaDB Server
Component/s:	OTHER
Affects Version/s:	10.1, 10.2, 10.3
Fix Version/s:	10.3.1