|
DROP TABLE IF EXISTS t1;
|
SET @a='test';
|
SELECT @a;
|
Field 1: `@a`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: MEDIUM_BLOB
|
Collation: utf8_general_ci (33)
|
Length: 50331645
|
Max_length: 4
|
Decimals: 31
|
Flags:
|
+------+
|
| @a |
|
+------+
|
| test |
|
+------+
|
Notice, MEDIUM_BLOB is displayed as the data type.
Now I create a table:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 AS SELECT @a;
|
SHOW CREATE TABLE t1;
|
+-------+-----------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-----------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`@a` longtext CHARACTER SET utf8
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-----------------------------------------------------------------------------------------------+
|
Notice, a LONGTEXT column was created.
There is a mismatch:
- Either LONG_BLOB should be reported in the metadata
- Or a MEDIUMBLOB column should be created
- Or perhaps a smaller data type could be used, e.g. BLOB or TINYBLOB, with the corresponding data type displayed in metadata.
Update:
The data type should be LONGBLOB/LONGTEXT, because user variables can be modified on the fly. So the metadata should by synchronized with the data type and report LONG_BLOB.
The same problem is repeatable with Item_func_set_user_var:
CREATE OR REPLACE TABLE t1 AS SELECT @a:=1;
|
DESCRIBE t1;
|
+-------+--------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+--------+------+-----+---------+-------+
|
| @a:=1 | int(1) | NO | | 0 | |
|
+-------+--------+------+-----+---------+-------+
|
But if I start mysql --column-type-info test and do:
Field 1: `@a:=1`
|
Type: LONGLONG
|
Length: 3
|
Max_length: 1
|
Notice, result set metadata is wrong. It should report LONG rather than LONGLONG.
|