Bug #17262 Collate doesn't work on windows

• Submitted: 9 Feb 2006 4:42
• Modified: 7 Jun 2006 3:04
• Reporter: Ralf Leidinger
• Status: Not a Bug
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 5.0.19-BK, 5.0.18-nt-max
• OS: Linux (Linux, windows xp sp2)
• Assigned to: Alexander Barkov
• CPU Architecture: Any

[9 Feb 2006 4:42] Ralf Leidinger

Description:
Example using 'collate' as shown in http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html doesn't work on windows system.

Configuration:
mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.18, for Win32 (ia32)

Server version:         5.0.18-nt-max
Protocol version:       10
Connection:             2cv via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306

mysql> show variables like 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

As shown in the example the following statement should return 1 but returns zero for all collations.

select 'ä' = 'ae' collate latin1_german2_ci;

All tests to get different sort orders using collate latin1_german1_ci or collate latin1_german2_ci failed. This also applies on databases/tables build with latin_german2_ci as default collation as also when switching all server variables 'collation_%' to collation latin1_german2_ci;

How to repeat:
mysql> select 'ä' like 'ae' collate latin1_german2_ci;
+-----------------------------------------+
| 'ä' like 'ae' collate latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select 'ä' = 'ae' collate latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' collate latin1_german2_ci |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
no idea. Maybe i'am wrong.

[10 Feb 2006 13:19] Valeriy Kravchuk

Thank you for a problem report. Verified just as described. Does not work for me on 5.0.19-BK (ChangeSet@1.2045, 2006-02-08 17:17:27+01:00, [e-mail]) on Linux also.

[6 Jun 2006 17:55] Alexander Barkov

Which MySQL client are you using?
Is it "mysql.exe", or some GUI tool?

What does this query return for you:

select HEX('ä');

?

[6 Jun 2006 23:32] Ralf Leidinger

Version of MySQL Client is mysql.exe from MySQL 5.0.18 binary distribution for Windows. Calling with -V option shows 

T:\mysql\bug17262>mysql -V
mysql  Ver 14.12 Distrib 5.0.18, for Win32 (ia32)

Query select HEX('ä') returns

mysql> select HEX('ä');
+----------+
| HEX('ä') |
+----------+
| 84       |
+----------+
1 row in set (0.00 sec)

Configuration still same as posted in first mail. See output of command
"mysql> \s" and command "mysql> show variables like 'coll%';" down there.

[7 Jun 2006 3:04] Alexander Barkov

mysql.exe is a DOS-alike application, which uses cp850, not latin1.
When you type 'ä', mysql.exe sends 0x81, whch is a correct code
for  LATIN SMALL LETTER U WITH DIAERESIS in cp850, but
it corresponds to another character in latin1.

You need to execute this query:

set character_set_client=cp850, character_set_results=cp850, character_set_connection=latin1;

Then try this query again:
select 'ä' like 'ae' collate latin1_german2_ci;

Please consult our manual for further information
about client-server character set conversion in MySQL.

I'm closing the report as not a bug.