| Bug #27580 | SPACE() function collation bug? | ||
|---|---|---|---|
| Submitted: | 2 Apr 2007 5:30 | Modified: | 30 Mar 2008 8:31 |
| Reporter: | Ondra Zizka | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.0.37, 4.1, 5.1, 5.2 | OS: | Any (Linix) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
| Tags: | space function collation | ||
[2 Apr 2007 5:34]
Ondra Zizka
Just for the case some sample data should be needed: INSERT INTO fstree VALUES (2, 0, 'C:/', 'dir', NULL, 0), (4, 2, 'treeinfo.wc', 'file', 305090, 1), (6, 2, 'cpp', 'dir', , 1), (8, 6, 'AntiAliasingWithTransparency.zip', 'file', 6621875, 2);
[2 Apr 2007 5:53]
Ondra Zizka
Note that it's the same with REPEAT(' ', level) and REPEAT(' ', 2) .
[2 Apr 2007 11:48]
Sveta Smirnova
Thank you for the report. Verified as described.
[2 Apr 2007 17:09]
Marc ALFF
Not sure if the root cause is the same, but it could be related: please see bug#23637
[8 Oct 2007 4:15]
Alexander Barkov
A simplified test, demonstrating the same problem: mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.22 | +-----------+ 1 row in set (0.00 sec) mysql> create table t1 (name varchar(10) character set cp1250 not null, level smallint unsigned) character set cp1250; Query OK, 0 rows affected (0.01 sec) mysql> select concat(name,space(level)) from t1; ERROR 1267 (HY000): Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
[8 Oct 2007 4:32]
Alexander Barkov
Similar error with REPEAT was fixed in 5.0.48.
Old version:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.22 |
+-----------+
1 row in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 (name varchar(10) character set cp1250 not null, level smallint unsigned) character set cp1250;
Query OK, 0 rows affected (0.01 sec)
mysql> select concat(name,repeat(' ',level)) from t1;
ERROR 1267 (HY000): Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
Current version:
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.50-debug |
+--------------+
1 row in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 (name varchar(10) character set cp1250 not null, level smallint unsigned) character set cp1250;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values ('name',5);
Query OK, 1 row affected (0.00 sec)
mysql> select concat(name,repeat(' ',level)) from t1;
+--------------------------------+
| concat(name,repeat(' ',level)) |
+--------------------------------+
| name |
+--------------------------------+
1 row in set (0.00 sec)
[8 Oct 2007 7:50]
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/35071 ChangeSet@1.2541, 2007-10-08 12:46:38+05:00, bar@mysql.com +11 -0 Bug#27580 SPACE() function collation bug? Problem: when character_set_connection=utf8, mixing SPACE() with a non-Unicode column (e.g. for concat) produced "illegal mix of collations" error. Fix: Item_string() corresponding to space character is now created using "ASCII" repertoire. Previously it was incorrectly created using "UNICODE" repertoure, which didn't allow to convert results of SPACE() to a non-Unicode character set.
[8 Oct 2007 11:39]
Sergey Vojtovich
Ok to push.
[15 Oct 2007 9:37]
Ramil Kalimullin
The patch itself is ok, but I think it's worth to list all similar functions and check them one by one.
[6 Mar 2008 6:03]
Alexander Barkov
Pushed into 5.0.58-engines.
[27 Mar 2008 11:18]
Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 11:21]
Bugs System
Pushed into 5.0.60
[27 Mar 2008 17:50]
Bugs System
Pushed into 6.0.5-alpha
[30 Mar 2008 8:31]
Jon Stephens
Documented bugfix as follows in the 5.0.60, 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs:
When utf8 was set as the connection character set, using SPACE() with a
non-Unicode column produced an error.
Description: SPACE() throws a COLLATION error under certain conditions, and I guess it should not. How to repeat: Use Query Browser. I did not test elsewhere... DROP TABLE IF EXISTS `test`.`fstree`; CREATE TABLE `test`.`fstree` ( `id` int(10) unsigned NOT NULL auto_increment, `pod` int(10) unsigned NOT NULL, `name` varchar(255) NOT NULL, `type` enum('dir','file') NOT NULL, `size` bigint(20) unsigned default NULL, `level` smallint(5) unsigned default NULL, PRIMARY KEY (`id`), KEY `level` (`level`) ) ENGINE=MyISAM AUTO_INCREMENT=557793 DEFAULT CHARSET=cp1250; SET CHARACTER SET utf8; -- Like Query Browser does - if not using it SELECT *, CONCAT( SPACE(2), name ) FROM fstree f LIMIT 200; -- Works fine SELECT *, CONCAT( SPACE(level), name ) FROM fstree f LIMIT 200; -- Throws error 1267: -- Illegal mix of collations (utf8_general_ci,COERCIBLE) and (cp1250_general_ci,IMPLICIT) for operation 'concat' Suggested fix: As far as in both situations SPACE() is given an integer, it should behave the same.