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 2021 9:52] MySQL Verification Team
Bug #103155 marked as duplicate of this one

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.