It seems we need a new method in Type_handler, like this:
The logic for the traditional Item types is hard coded in Item::create_tmp_field() in the switch by cmp_type(). It performs conversion to a super-type which has the same cmp_type(), with a special code for GEOMETRY.
Note, conversion to a super-type can often be excessive. For example, for INT-alike types we create Field_long or Field_longlong. But under certain circumstances smaller types should be enough (e.g. when the result is not exposed as a field of a permanent table). An additional "what_for" parameter will possibly be needed for tmp_table_handler().
For example, if data type of "this" is MYSQL_TYPE_SHORT, and the field is needed only for internal purposes and is know not to overflow, we could create Field_short instead of Field_long.
In order to do this, the code needs to be fixed first to make sure that type_handler()/field_type() are always in sync with max_length for all Item types, which now is not always true.
After this commit:
this code was moved from Item::create_tmp_field() to Item_sum::create_tmp_field().
So this task becomes lower priority, because only numeric data types are now affected.
Adding string-alike data types like INET6 and UUID do not seem to need this change.
As of 2018-06-19, the code of the subject looks like this:
There is no special GEOMETRY code any more.
There is only a piece of special code for REAL_RESULT.