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:
None 
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
Triage: D3 (Medium)

[2 Apr 2007 5:30] Ondra Zizka
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.
[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.