| Bug #10025 | Misleading error with COLLATE mediumtext and UNION | ||
|---|---|---|---|
| Submitted: | 20 Apr 2005 9:32 | Modified: | 31 May 2005 2:34 |
| Reporter: | Alexander Keremidarski | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.11+ | OS: | Any (any) |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[5 May 2005 11:22]
Alexander Barkov
This problem doesn't seem to be related to character sets/collations.
This test case reproduced the same problem:
drop table if exists t1, t2;
CREATE TABLE t1 (a mediumtext);
CREATE TABLE t2 (b varchar(20));
INSERT INTO t1 VALUES ('a'),('b');
SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;
I'm reassigning the bug to Sanja, he is the best person to check this problem.
[23 May 2005 13:51]
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/25178
[23 May 2005 14:09]
Oleksandr Byelkin
ChangeSet 1.2288 05/05/23 16:50:45 bell@sanja.is.com.ua +3 -0 fixed problem with long string results of expressions in UNIONS (BUG#10025) sql/item.cc 1.203 05/05/23 16:50:42 bell@sanja.is.com.ua +5 -3 set HA_OPTION_PACK_RECORD and change type to MYSQL_TYPE_STRING, to allow +correct field creation in case of length more then 255 (creation STRING field +with length more then 255) mysql-test/t/union.test 1.84 05/05/23 16:50:42 bell@sanja.is.com.ua +9 -0 test for Bug #10025 mysql-test/r/union.result 1.90 05/05/23 16:50:42 bell@sanja.is.com.ua +8 -0 test for Bug #10025
[27 May 2005 19:06]
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/25343
[30 May 2005 12:14]
Oleksandr Byelkin
pushed to 4.1.13
[31 May 2005 2:34]
Paul DuBois
Noted in 4.1.13 changelog.
[26 Sep 2006 10:17]
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/12512 ChangeSet@1.2326, 2006-09-26 13:19:25+03:00, jani@a88-112-41-254.elisa-laajakaista.fi +2 -0 Fix for bug#20208 A better fix for bug#10025. Fixed test case plus added new tests. After fixing Bug#20208 "Blobs greater than 8K are being truncated to 8K" the fix to bug#10025 "Misleading error with COLLATE mediumtext and UNION" became more accurate. Earlier mediumtext got converted to longtext, although mediumtext was enough to contain the results. Now it converts correctly to mediumtext, if the length does not exceed that and if none of the original fields were type longtext. Type longtext still converts correctly to type longtext, as the extra tests prove.
Description: Under some circumstances the following UNION SELECT a COLLATE utf8_bin FROM c1 UNION SELECT b FROM c2; fails with strange perror 140 MySQL error code 140: Wrong create options * Selected columns are with different collation * Happens with both MyISAM and InnoDB * Happens only if there is at least one NULL and one non-NULL value in column `a` * Happens with column of MEDIUMTEXT or LONGTEXT type. TINYTEXT and LONGTEXT do not expose the problem! Tested against mysql-4.1 BK tree ChangeSet@1.2197, 2005-04-19 11:17:32+02:00, joreland@mysql.com Does not exist in 4.1.10 How to repeat: CREATE TABLE `c1` ( `a` mediumtext collate utf8_unicode_ci ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `c2` ( `b` varchar(20) character set utf8 collate utf8_bin default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO c1 VALUES ('a'), (NULL); mysql> SELECT a COLLATE utf8_bin FROM c1 UNION SELECT b FROM c2; ERROR 1030 (HY000): Got error 140 from storage engine