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: | Windows (Vista) |
Assigned to: | CPU Architecture: | Any | |
Tags: | BINARY, collation, latin, latin1_swedish_ci, qc |
[17 Feb 2009 5:00]
Jared S
[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));