| Bug #42909 | Collation binary instead of latin | ||
|---|---|---|---|
| Submitted: | 17 Feb 2009 5:00 | Modified: | 17 Feb 2009 22:23 |
| Reporter: | Jared S (Silver Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.31 | OS: | Microsoft Windows (Vista) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | BINARY, collation, latin, latin1_swedish_ci, qc | ||
[17 Feb 2009 8:30]
Sveta Smirnova
Thank you for the report. Yes, this is known behavior. But I believe for these functions this is logical: - last inserted id can be only bigint, int, smallint or tinyint. Why do you think last_insert_id() should return string with some collation? - aes_decrypt is "complement" to aes_encrypt which returns binary string. How aes_decrypt can know which collation was used for encrypted string?
[17 Feb 2009 8:59]
Jared S
I see your points. To be specific, it is CONCAT_WS screwing me up. My question is, is it really necessary for me to perform some sort of CAST or CONVERT function on the 2nd param of CONCAT_WS call.
mysql> select concat_ws('ID', last_insert_id());
Field 1: `concat_ws('ID', last_insert_id())`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 21
Max_length: 1
Decimals: 31
Flags: BINARY
+-----------------------------------+
| concat_ws('ID', last_insert_id()) |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (0.00 sec)
[17 Feb 2009 10:18]
Sveta Smirnova
Thank you for the feedback.
If instead of last_insert_id() use integer concat_ws will have same binary collation:
mysql> select concat_ws('foo', 1);
Field 1: `concat_ws('foo', 1)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 1
Max_length: 1
Decimals: 31
Flags: NOT_NULL BINARY
+---------------------+
| concat_ws('foo', 1) |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
So this still looks like not a bug for me.
[17 Feb 2009 22:12]
Jared S
2 work arounds below. In terms of S4 request. It would be nice if MySQL made integers params internally interchangeable with local charset in near future for string functions such as concat_ws.
select concat_ws(';', 'A', CONVERT(1 USING utf8));
select concat_ws(';', 'A', CONVERT(1 USING latin1));
[17 Feb 2009 22:23]
Jared S
Correct workaround below. LAST_INSERT_ID() is actually compatible with charsets.
-- proc trying to return 2 IDs into scalar call
SELECT CONCAT_WS(';', LAST_INSERT_ID(), CONVERT(1 USING latin1));
Description: Couple of funcs like last_insert_id() & aes_decrypt() are forcing my ODBC and NET connector to return bynary string. Is there some legacy Collation code in your server? See collation types below. Brought this issue up with connector guys 2 years ago and have decided to log formal server bug. How to repeat: mysql> select last_insert_id(); Field 1: `last_insert_id()` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONGLONG Collation: binary (63) Length: 21 Max_length: 1 Decimals: 0 Flags: NOT_NULL BINARY NUM +------------------+ | last_insert_id() | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) mysql> select aes_decrypt('a', 'b'); Field 1: `aes_decrypt('a', 'b')` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 1 Max_length: 0 Decimals: 31 Flags: BINARY +-----------------------+ | aes_decrypt('a', 'b') | +-----------------------+ | NULL | +-----------------------+ 1 row in set (0.01 sec)