MySQL Bugs: #46463: column in view that is generated by a function gets wrong data type

Bug #46463	column in view that is generated by a function gets wrong data type
Submitted:	29 Jul 2009 22:29	Modified:	17 Sep 2009 9:15
Reporter:	Rahul Nabar	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	Ver 14.12 Distrib 5.0.45	OS:	Linux (Red Hat Enterprise)
Assigned to:		CPU Architecture:	Any
Tags:	datatype, FUNCTION, Views

Description:
Make a view in which at least one column is returned as the result of a FUNCTION. FUNCTION tries to return ENUM datatype. 

VIEW description will return type as VARCHAR instead of ENUM

How to repeat:
CREATE TABLE  table1
(
        m1 varchar(6),
        x float,
        y float
);

INSERT INTO table1 values
('A',1,1),
('C',1,0),
('E',0,1);

CREATE FUNCTION prediction(x DOUBLE ) RETURNS ENUM('Overlayer','Sandwich')
BEGIN

IF  (x < 0)  THEN
RETURN 'Overlayer';
ELSEIF   (x > 0 )  THEN
RETURN 'Sandwich';
ELSE
RETURN NULL;
END IF;

END;

CREATE VIEW view1 AS
(
SELECT m1,
prediction( x) AS Prediction
FROM table1
);

SHOW COLUMNS FROM view1 LIKE 'Prediction';
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| Prediction | varchar(9) | YES  |     | NULL    |       | 
+------------+------------+------+-----+---------+-------+

Suggested fix:
No fix known

Thank you for the bug report. Are you verified starting the mysql client with the option -T which kind of data type the function returns?. Thanks in advance.

Hmmm...not sure what you mean. 

mysql -T and then what? pipe in the "SHOW COLUMNS FROM view1 LIKE 'Prediction';"

echo "use test; SHOW COLUMNS FROM view1 LIKE 'Prediction'" | mysql -T -u root -p 
Enter password: 
Field   Type    Null    Key     Default Extra
Prediction      varchar(9)      YES             NULL

User time 0.00, System time 0.00
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 623, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 15, Involuntary context switches 0

What I meant is:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > " -T r5
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.83-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > select prediction (2);
Field   1:  `prediction (2)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  latin1_swedish_ci (8)
Length:     9
Max_length: 8
Decimals:   0
Flags:      ENUM

+----------------+
| prediction (2) |
+----------------+
| Sandwich       |
+----------------+
1 row in set (0.00 sec)

mysql 5.0 >

Thanks. Got it. THe function returns ENUM.

mysql> select prediction (2);
Field   1:  `prediction (2)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  latin1_swedish_ci (8)
Length:     9
Max_length: 8
Decimals:   0
Flags:      ENUM 

+----------------+
| prediction (2) |
+----------------+
| Sandwich       | 
+----------------+

I sniffed up the old bug reports. 

Do you think this is the bug I am facing:

http://bugs.mysql.com/bug.php?id=29604

MySQL 5.0.45 is really old.

You think your problem is related to bug #29604.

This bug is fixed since 5.0.48 and since 5.1.21.

Actual version are:
5.0.84 and 5.1.37

Please try one of these and let us know if your issue has gone away.

You can download the actual packages here:
http://dev.mysql.com/downloads/

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".