A set of bugs, related to a wrong result set metadata, were fixed recently:
MDEV-4838, MDEV-4848, MDEV-8865, MDEV-8867, MDEV-8873, MDEV-8875, MDEV-8910, MDEV-8912, MDEV-12546

The same problem exists for INT result functions:

SELECT LENGTH('a');

Field   1:  `LENGTH('a')`

Type:       LONGLONG

Collation:  binary (63)

Length:     10

Max_length: 1

Decimals:   0

Flags:      NOT_NULL BINARY NUM 

CREATE OR REPLACE TABLE t1 AS SELECT LENGTH('a');

DESCRIBE t1;

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

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

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

| LENGTH('a') | int(10) | NO   |     | 0       |       |

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

Notice, LENGTH() reports itself as LONGLONG data in the result set metadata, however in fact create an INT(10) column in CREATE..SELECT.

Under terms of this tasks will fix the majority of Item_func_int descendants, so the result set metadata matches CREATE..SELECT.

This will also positively affect on aggregation for UNION or CASE and CASE abbreviations, as the current way of data type detection using max_length
is not precise in some cases, especially when max_length is 10 (it can result in both INT and BIGINT).

Only functions creating a BIGINT column in CREATE..SELECT will report LONGLONG in metadata.
Other non-hybrid INT functions will return LONG for now.

Note, later we'll possibly change LONG to more precise type codes (e.g. TINY, SHORT, INT24). But this will be done in a separate task.

The intent for now is to make type_handler() return a correct pointer, to make create_tmp_field() and create_field_for_create_select() call tmp_table_field_from_field_type(), to get rid of Item::create_tmp_field() soon.