| Bug #20765 | Subselect causes datatype to be changed erroneously | ||
|---|---|---|---|
| Submitted: | 29 Jun 2006 5:57 | Modified: | 7 May 2008 15:38 |
| Reporter: | Morgan Tocker | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 4.1,5.0,5.1 | OS: | Any (ALL) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[29 Jun 2006 11:28]
Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.0.23-BK on Linux.
[30 Jun 2006 1:08]
Peter Laursen
let me add that this bug reports originates as a support request from a third-party vendor (of which I am a part). Here a query with a Subselect returned the type VARCHAR when querying a BLOB or a LONGBLOB. Schema: ======= SET NAMES utf8; SET SQL_MODE=''; create database if not exists `test`; USE `test`; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; /*Table structure for table `product` */ DROP TABLE IF EXISTS `product`; CREATE TABLE `product` ( `UID` int(10) unsigned NOT NULL auto_increment, `Code` varchar(10) NOT NULL default '', PRIMARY KEY (`UID`), UNIQUE KEY `Code` (`Code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*Data for the table `product` */ insert into `product` (`UID`,`Code`) values (1,'xyz'); /*Table structure for table `product_photographs` */ DROP TABLE IF EXISTS `product_photographs`; CREATE TABLE `product_photographs` ( `UID` int(10) unsigned NOT NULL auto_increment, `UID_Product` int(10) unsigned NOT NULL default '0', `SmallPhoto` blob, PRIMARY KEY (`UID`), KEY `UID_Product` (`UID_Product`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*Data for the table `product_photographs` */ insert into `product_photographs` (`UID`,`UID_Product`,`SmallPhoto`) values (1,1,'abc'); SET SQL_MODE=@OLD_SQL_MODE; queries: ======== -- does not work SELECT Code, (SELECT SmallPhoto FROM product_photographs WHERE UID_Product=product.UID ) AS SmallPhoto FROM product WHERE UID=1; -- does work SELECT CODE, SmallPhoto FROM product, product_photographs WHERE UID_Product=product.UID; -- does work Create table testtable SELECT Code, (SELECT SmallPhoto FROM product_photographs WHERE UID_Product=product.UID ) AS SmallPhoto FROM product WHERE UID=1; SELECT * FROM testtable; ============== I add this as I want to be sure that every aspect of type changes with subqueries on BLOB types is covered!
[30 Jun 2006 1:10]
Peter Laursen
I meant SUBSELECTS not SUBQUERIES!
[13 Jun 2007 10:01]
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/28636 ChangeSet@1.2498, 2007-06-13 12:02:30+03:00, mhansson@linux-st28.site +5 -0 bug#20765: Subselect causes datatype to be changed erroneously When blob fields are created from subselects, they are always created with the biggest max length available, regardless of the max length of the field they originate from. Fixed by taking into account the original max length when creating fields from subselects. There is also a check whether the field was a blob field originally.
[25 Jun 2007 14:41]
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/29509 ChangeSet@1.2498, 2007-06-25 16:38:03+03:00, mhansson@linux-st28.site +14 -0 Bug #20765: Subselect causes datatype to be changed erroneously When blob fields are created from functions results and other fields as in CREATE TABLE ... SELECT ..., they are always created with the greatest possible display length, regardless of the display length of the field they originate from. Furhtermore, the functions REPEAT and CONCAT were found to truncate their display length which made it impossible to retrieve the original length. Fixed by taking into account the original max length when creating fields from existing Item's and by not truncating the length in REPEAT and CONCAT
[26 Jun 2007 7:07]
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/29572 ChangeSet@1.2498, 2007-06-26 09:03:02+03:00, mhansson@linux-st28.site +14 -0 Bug #20765: Subselect causes datatype to be changed erroneously When blob fields are created from functions results and other fields as in CREATE TABLE ... SELECT ..., they are always created with the greatest possible length, regardless of the length of the field they originate from. Furhtermore, the functions REPEAT and CONCAT were truncating the length of their results, which made it impossible to retrieve the original length. Fixed by taking into account the original length when creating fields from existing Item's and by not truncating the length in REPEAT and CONCAT
[18 Dec 2007 9:28]
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/40125 ChangeSet@1.2498, 2007-12-18 10:30:45+01:00, mhansson@linux-st28.site +12 -0 Bug #20765: Subselect causes datatype to be changed erroneously When blob fields are created from functions results and other fields as in CREATE TABLE ... SELECT ..., they are always created with the greatest possible length, regardless of the length of the field they originate from. Fixed by taking into account the original length when creating fields from existing Item's.
[25 Jan 2008 17:16]
Sergei Golubchik
Morgan, please verify whether the test case corresponds to the issue originally described in the support request
[26 Feb 2008 0:00]
Bugs System
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".
[5 Mar 2008 13:35]
Susanne Ebrecht
Morgan, feedback is still needed.
[5 Apr 2008 23:00]
Bugs System
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".
[7 May 2008 23:00]
Bugs System
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".
Description: A subselect can cause a field to be changed from mediumblob to longblob. Verified in 5.0.22, 5.1.9 and 4.1.20. How to repeat: CREATE TABLE a ( id int NOT NULL PRIMARY KEY auto_increment, a mediumblob ); INSERT into a (a) VALUES ('123'); CREATE TABLE b ( id int NOT NULL PRIMARY KEY auto_increment ); INSERT INTO b VALUES (NULL); CREATE TEMPORARY TABLE t1 AS SELECT id, (SELECT a FROM a WHERE id=id) as a FROM b WHERE id=1; CREATE TEMPORARY TABLE t2 AS SELECT b.id, a.a FROM a, b WHERE b.id=a.id AND b.id=1; mysql> DESC t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | a | longblob | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> desc t2; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | a | mediumblob | YES | | NULL | | +-------+------------+------+-----+---------+-------+ Suggested fix: Tables t1 and t2 should be the same (in theory).