Description:
For the view and table created from SELECT statement, column
is created from the SELECT_LEX field items. But in following
case, column metadata of view and table created from similar
SELECT statement is not same.
5.7 Behavior:
============================================
CREATE TABLE t (a int(11) NOT NULL);
CREATE FUNCTION f1() RETURNS TEXT return "Hello!";
CREATE TABLE t1 AS SELECT LENGTH(a), f1() FROM t;
CREATE VIEW v1 AS SELECT LENGTH(a), f1() FROM t;
DESC t1;
Field Type Null Key Default Extra
LENGTH(a) int(10) NO 0
f1() longtext YES NULL
DESC v1;
Field Type Null Key Default Extra
LENGTH(a) bigint(10) NO 0
f1() text YES NULL
8.0 Behavior:
============================================
CREATE FUNCTION f1() RETURNS TEXT return "Hello!";
CREATE TABLE t1 AS SELECT LENGTH(a), f1() FROM t;
CREATE VIEW v1 AS SELECT LENGTH(a), f1() FROM t;
DESC t1;
Field Type Null Key Default Extra
LENGTH(a) int(10) NO 0
f1() longtext YES NULL
DESC v1;
Field Type Null Key Default Extra
LENGTH(a) bigint(10) NO 0
f1() text YES NULL
From the stored function definition, type of f1() in view
looks correct. But column metadata of other column differs.
How to repeat:
CREATE TABLE t (a int(11) NOT NULL);
CREATE FUNCTION f1() RETURNS TEXT return "Hello!";
CREATE TABLE t1 AS SELECT LENGTH(a), f1() FROM t;
CREATE VIEW v1 AS SELECT LENGTH(a), f1() FROM t;
DESC t1;
DESC v1;
DROP TABLE t, t1;
DROP VIEW v1;
DROP FUNCTION f1;
Suggested fix:
Column metadata view and table created from
similar SELECT should be same.
