Bug #20550 Stored function: wrong RETURN type metadata when used in a VIEW
Submitted: 19 Jun 2006 22:22 Modified: 24 Oct 2007 18:47
Reporter: Mauricio Orizola Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.22 OS:Windows (Windows XP)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[19 Jun 2006 22:22] Mauricio Orizola 
Description:
When a function that returns varchar(65535) is used in a view, the field that refers to the function in the view appears as longtext in the information_schema.columns. The same problem for a function that returns tinyint, this appears as bigint.

How to repeat:
Create any funtion that returns a varchar(65535) and other that returns tinyint, then create a view that has fields refering these functions, fanally check the DATA_TYPE field in the information_schema.columns for these fields.
[20 Jun 2006 10:12] Sveta Smirnova 
Thank you for the bug report.

Verified as described by reporter on:
Linux 2.6.16-1.2108_FC4smp
MySQL 5.0.23-debug, bk build

Test case:

CREATE FUNCTION foo() RETURNS VARCHAR(65525) RETURN 'Hello';
CREATE VIEW v1 AS SELECT foo();
SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='v1';

CREATE FUNCTION bar() RETURNS TINYINT RETURN 1;
CREATE VIEW v2 AS SELECT bar();
SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='v2';
[5 Oct 2007 10:31] Konstantin Osipov 
Can cause bad data.
[5 Oct 2007 12:31] Alexander Nozdrin 
BUG#13716 was marked as a duplicate of this bug.
[5 Oct 2007 12:34] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34967

ChangeSet@1.2619, 2007-10-05 16:35:01+04:00, anozdrin@station. +4 -0
  Fix for BUG#20550: Stored function: wrong RETURN type metadata
  when used in a VIEW.
  
  The problem was that wrong function (create_tmp_from_item())
  was used to create a temporary field for Item_func_sp.
  
  The fix is to use create_tmp_from_field().
[6 Oct 2007 13:14] Alexander Nozdrin 
Pushed into 5.1-runtime.
[19 Oct 2007 18:52] Bugs System 
Pushed into 5.1.23-beta
[24 Oct 2007 18:47] Paul DuBois 
Noted in 5.1.23 changelog.

If a column selected by a view referred to a stored function, the
data type reported for the column in INFORMATION_SCHEMA.COLUMNS could
be incorrect.