| Bug #2773 | Function's data type ignored in stored procedures | ||
|---|---|---|---|
| Submitted: | 13 Feb 2004 6:48 | Modified: | 9 Mar 2005 1:23 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.0-alpha-debug | OS: | Linux (SuSE 8.2) |
| Assigned to: | Antony Curtis | CPU Architecture: | Any |
[17 Feb 2004 11:42]
Dean Ellis
Verified against 5.0.1-alpha-log. (The created column name is not an error; alias the function call in the CREATE TABLE ... SELECT statement.)
[26 Sep 2004 16:31]
Per-Erik Martin
Firstly, it seems it does now detect that a NULL is return from the function. (A warning is given). Otherwise it behaves as before. However, it seems it's not specific for stored functions, as the example below shows. The same thing happens for a built-in function which presumedly returns and int. mysql> create function fu1() returns int return null; Query OK, 0 rows affected (0.00 sec) mysql> create table tu1 as select fu1(); Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+-------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------+ | Warning | 1263 | Data truncated; NULL supplied to NOT NULL column 'fu1()' at row 1 | +---------+------+-------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table tu1; +------- +-------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------- +-------------------------------------------------------------------------------------------------------+ | tu1 | CREATE TABLE `tu1` ( `fu1()` bigint(21) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +------- +-------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> drop table tu1; Query OK, 0 rows affected (0.00 sec) mysql> create table tu1 as select sign(1); Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table tu1; +------- +---------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------- +---------------------------------------------------------------------------------------------------------+ | tu1 | CREATE TABLE `tu1` ( `sign(1)` bigint(21) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +------- +---------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
[3 Oct 2004 1:38]
Antony Curtis
bk commit - 5.0 tree (antony:1.1630) BUG#2773 Date: Sun, 3 Oct 2004 02:34:19 +0100 (BST) ChangeSet 1.1630 04/10/03 02:34:17 antony@ltantony.rdg.cyberkinetica.homeunix.net +4 -0 Bug#2773 - Function's data type ignored in stored procedures
[3 Oct 2004 10:26]
Antony Curtis
bk commit - 5.0 tree (antony:1.1630) BUG#2773 Date: Sun, 3 Oct 2004 11:24:07 +0100 (BST) ChangeSet 1.1630 04/10/03 11:24:05 antony@ltantony.rdg.cyberkinetica.homeunix.net +11 -0 Bug#2773 - Function's data type ignored in stored procedures This patch makes the stored functions store more detail about the return type.
[16 Dec 2004 13:52]
Antony Curtis
Patch is stale. Updating to apply against current,
[4 Mar 2005 21:15]
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/internals/22680
[9 Mar 2005 1:23]
Antony Curtis
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: MySQL ignores what I say with the RETURNS data type clause. This means that the data type is wrong if I say "CREATE TABLE ... SELECT function()". And, since MySQL assumes incorrectly that the returned type is NOT NULL, a "RETURN NULL" won't work. The CHARACTER SET and COLLATE clauses are also ignored. How to repeat: mysql> create function fu5() returns int return null; Query OK, 0 rows affected (0.00 sec) mysql> create table tu1 as select fu1(); Query OK, 1 row affected (0.38 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table tu1; +-------+-----------------------------------------+ | Table | Create Table | +-------+-----------------------------------------+ | tu1 | CREATE TABLE `tu1` ( | | | `fu1()` bigint(21) NOT NULL default '0' | | ) TYPE=MyISAM CHARSET=latin1 | +-------+-----------------------------------------+ 1 row in set (0.00 sec) Notice that the type is BIGINT when I asked for SMALLINT. Notice that there is a NOT NULL which I didn't ask for. Incidentally -- this is probably a different bug -- notice that the function is entirely enclosed in backticks: `fu1()`. It is not possible to define such a function.