'create table ... select' creates text column where varchar would suffice
Hello everyone :)
I have noticed some unexpected behaviour from the MyISAM engine, while using the 'create table ... select' syntax.
The problem is that I concat two VARCHAR(255) columns and an INT(11) column with some extra chars to create a column in the to-be-created-table.
This concat, which looks like:
CONCAT( first_column, ' - ', second_column, ' (', third_column, ')') AS x
where first_column and second_column are both VARCHAR(255) and third_column is an INT(11), causes the 'x' column in the created table to have the TEXT datatype.
The 'x' column should be able to fit into a VARCHAR(...) datatype.
Take, for example, the following concat-statement:
CONCAT( first_column, ' (', third_column, ')') AS y
which creates a VARCHAR(269) column as expected.
I am wondering why MyISAM decides to move to a TEXT datatype for the created column instead of a VARCHAR(...) datatype.
Defining the datatype for the column in the 'create table ... select' statement, before the select-keyword (as explained here:
http://dev.mysql.com/doc/refman/5.6/en/create-table-select.html), is a workaround/solution, but I would prefer to understand why this happens, to prevent future issues.
Thanks in advance to anyone who might shed some light on this for me.