Bug #97038 Column metadata of a table and a view created from similar SELECT is not same
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.
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.