Inconsistency in view column data type

When we crate views in MySQL, the data types of the view columns should be derived from the base tables. However MySQL sometimes goes “extra miles”.

USE test;

DROP TABLE IF EXISTS property;

CREATE TABLE property (user_id int not null, property_id int NOT NULL, property_value varchar(512) NOT NULL, PRIMARY KEY CLUSTERED(user_id,property_id));

INSERT property (user_id,property_id,property_value)
VALUES (1,1,’21’),(1,2,’M’),(1,3,’aaa@test.com’),
(2,1,’34’),(2,2,’F’),(2,3,’fff@test.com’),
(3,1,’28’),(3,2,’M’),(3,3,’bbb@test.com’);

— THe view is basically pivot the table and give meaningful column name
DROP VIEW IF EXISTS person;

CREATE VIEW person
AS
SELECT user_id
, MAX(CASE WHEN t.property_id=1 THEN t.property_value ELSE NULL END ) AS age
, MAX(CASE WHEN t.property_id=2 THEN t.property_value ELSE NULL END ) AS gender
, MAX(CASE WHEN t.property_id=3 THEN t.property_value ELSE NULL END ) AS email
FROM property t
GROUP BY t.user_id;

SELECT * FROM person;

SELECT * FROM information_schema.columns where table_schema=’test’ AND table_name=’person’;

From the output of the information_schema.columns, the data type of the columns (age,gender,email) in the view is varchar(512), which is same as in the base table.

If we change the data type of property_value to varchar(n), where n is bigger than 512, and re-run the script, the data type of the columns (age,gender,email) in the view will be changed to longtext, which is a big suprise. MySQL is inconsistent in defining the data types for the view columns.

Now let’s cast the data types of the view columns to what are adequate and meaningful in the view, e.g.
age: unsigned integer
gender: char(1)
email:varchar(512)

The view will be like:
DROP VIEW IF EXISTS person;

CREATE VIEW person
AS
SELECT user_id
, CAST(MAX(CASE WHEN t.property_id=1 THEN t.property_value ELSE NULL END ) AS unsigned) AS age
, CAST(MAX(CASE WHEN t.property_id=2 THEN t.property_value ELSE NULL END ) AS char(1)) AS gender
, CAST(MAX(CASE WHEN t.property_id=3 THEN t.property_value ELSE NULL END ) AS char(512)) AS email
FROM property t
GROUP BY t.user_id;

Check with information_schema.columns, the data type for age is changed to bigint, but the column type becomes ‘bigint(67) unsigned’. In one more complex view, it produced ‘bigint(12288)’. When the database is dumped and ported to another server, the script failed because 12288 is too big for display. The error message is ‘Error Code: 1439
Display width out of range for column xxxx (max = 255) 0.000 sec’

To prevent this happens, a better way is to convert to decimal, e.g. decimal(20,0):

DROP VIEW IF EXISTS person;

CREATE VIEW person
AS
SELECT user_id
, CAST(MAX(CASE WHEN t.property_id=1 THEN t.property_value ELSE NULL END ) AS decimal(20,0)) AS age
, CAST(MAX(CASE WHEN t.property_id=2 THEN t.property_value ELSE NULL END ) AS char(1)) AS gender
, CAST(MAX(CASE WHEN t.property_id=3 THEN t.property_value ELSE NULL END ) AS char(512)) AS email
FROM property t
GROUP BY t.user_id;

Check with information_schema.columns, the column type is ‘decimal(20,0)’.

The data type conversion functions (CAST and CONVERT) in MySQL only supports limited target data type. If they support all the data types that can be used in creating a table, this inconsistency should be able to avoided.

Advertisements
This entry was posted in MySQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s