Bug #97038 Column metadata of a table and a view created from similar SELECT is not same
Submitted: 26 Sep 2019 13:58
Reporter: Praveenkumar Hulakund Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any

[26 Sep 2019 13:58] Praveenkumar Hulakund
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.
[19 Apr 9:52] MySQL Verification Team
Bug #103155 marked as duplicate of this one