Bug #28974 Incorrect collation returned when SELECTing CHAR(nn)BINARY field
| Submitted: | 8 Jun 2007 11:54 | Modified: | 19 Jun 2007 8:52 |
|---|---|---|---|
| Reporter: | Tonci Grgin | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.0.44BK, ChangeSet@1.2516 | OS: | Windows (XP Pro SP2) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[8 Jun 2007 11:54]
Tonci Grgin
[8 Jun 2007 12:14]
MySQL Verification Team
Tonci, I was not able to repeat with Windows source server built with the below changeset: ChangeSet@1.2515, 2007-06-01 20:05:34+04:00, kostja@bodhi.(none) +2 -0 Fix more mysqltest warnings. ChangeSet@1.2514, 2007-06-01 18:06:40+04:00, kostja@bodhi.(none) +1 -0 Do not ignore mysqltest language interpreter warnings. ChangeSet@1.2513, 2007-06-01 18:04:25+04:00, kostja@bodhi.(none) +2 -0 Fix a typo in the test case. ChangeSet@1.2512, 2007-06-01 17:49:01+04:00, kostja@bodhi.(none) +7 -0 c:\build\5.0>bin\mysql -uroot -T test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.44-nt Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT * FROM `appointment0`; Field 1: `GGUID` Catalog: `def` Database: `test` Table: `appointment0` Org_table: `appointment0` Type: STRING Collation: binary (63) Length: 16 Max_length: 0 Decimals: 0 Flags: NOT_NULL PRI_KEY BINARY NO_DEFAULT_VALUE PART_KEY Field 2: `Keyword` Catalog: `def` Database: `test` Table: `appointment0` Org_table: `appointment0` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 40 Max_length: 0 Decimals: 0 Flags: MULTIPLE_KEY PART_KEY 0 rows in set (0.00 sec)
[8 Jun 2007 12:21]
Tonci Grgin
Win MySQL server started with: - character-set-server=utf8 & collation-server=utf8_general_ci
[8 Jun 2007 12:34]
Tonci Grgin
This is what happens when one does not take care of different environments...
On SLES test table was freshly created while on Win I used table created with 5.0.38BK...
When table is freshly created with 5.0.44BK there is no problem on Win:
C:\mysql507\bin>mysql -uroot -T test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44-max-nt-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE IF EXISTS `appointment0`;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE `appointment0` (
-> `GGUID` binary(16) NOT NULL,
-> `Keyword` varchar(40) default NULL,
-> PRIMARY KEY (`GGUID`),
-> KEY `IDX_Keyword41` (`Keyword`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.13 sec)
mysql> SELECT * FROM appointment0;
Field 1: `GGUID`
Catalog: `def`
Database: `test`
Table: `appointment0`
Org_table: `appointment0`
Type: STRING
Collation: binary (63)
Length: 16
Max_length: 0
Decimals: 0
Flags: NOT_NULL PRI_KEY BINARY NO_DEFAULT_VALUE PART_KEY
Field 2: `Keyword`
Catalog: `def`
Database: `test`
Table: `appointment0`
Org_table: `appointment0`
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 40
Max_length: 0
Decimals: 0
Flags: MULTIPLE_KEY PART_KEY
0 rows in set (0.00 sec)
Now the question remains why does "old" table give wrong collation?
[11 Jun 2007 12:41]
Tonci Grgin
I am sorry but I burned out during this discussion and I missed the change in test case I requested from Valeriy. It was NOT GGUID BINARY(16) but GGUID CHAR(16) BINARY... Changing the synopsis accordingly.
DDL:
DROP TABLE IF EXISTS `appointment0`;
CREATE TABLE `appointment0` (
`GGUID` CHAR(16) binary NOT NULL,
`Keyword` varchar(40) default NULL,
PRIMARY KEY (`GGUID`),
KEY `IDX_Keyword41` (`Keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SELECT * FROM appointment0;
Output:
C:\mysql507\bin>mysql -uroot -T test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.0.44-max-nt-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE IF EXISTS `appointment0`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `appointment0` (
-> `GGUID` CHAR(16) binary NOT NULL,
-> `Keyword` varchar(40) default NULL,
-> PRIMARY KEY (`GGUID`),
-> KEY `IDX_Keyword41` (`Keyword`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT * FROM appointment0;
Field 1: `GGUID`
Catalog: `def`
Database: `test`
Table: `appointment0`
Org_table: `appointment0`
Type: STRING
Collation: latin1_swedish_ci (8)
Length: 16
Max_length: 0
Decimals: 0
Flags: NOT_NULL PRI_KEY BINARY NO_DEFAULT_VALUE PART_KEY
Field 2: `Keyword`
Catalog: `def`
Database: `test`
Table: `appointment0`
Org_table: `appointment0`
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 40
Max_length: 0
Decimals: 0
Flags: MULTIPLE_KEY PART_KEY
In my opinion, having Collation: latin1_swedish_ci (8) for field GGUID is obviously wrong, it should have been latin1_swedish_bin and we are again at the beginning. We need stronger typing as I don't see the way to tell CHAR BINARY from BINARY column (well when this bug is fixed there would be no way)...
[19 Jun 2007 8:52]
Alexander Barkov
This is not a bug. Client-server protocol converts all text results to the current @@character_set_results value. Obviously, it is latin1 in your case. If you need the columns to be returned in their original character set, you must set character_set_results to NULL: mysql> set character_set_results=null; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM appointment0; Field 1: `GGUID` Catalog: `def` Database: `test` Table: `appointment0` Org_table: `appointment0` Type: STRING Collation: utf8_bin (83) Length: 48 Max_length: 0 Decimals: 0 Flags: NOT_NULL PRI_KEY BINARY NO_DEFAULT_VALUE PART_KEY Field 2: `Keyword` Catalog: `def` Database: `test` Table: `appointment0` Org_table: `appointment0` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 120 Max_length: 0 Decimals: 0 Flags: MULTIPLE_KEY PART_KEY 0 rows in set (0.00 sec) I'm closing the report as "not a bug". If you think that this behavior is not well convered in the manual, feel free to get in touch with the doc team.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.063 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.