MySQL Bugs: #28974: Incorrect collation returned when SELECTing CHAR(nn)BINARY field

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:	None
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

Description:
Incorrect collation returned when SELECTing (VAR)BINARY field on Win. Manual states collation for (VAR)BINARY field would be _bin one but it isn't.

Same problem (with same sources) not present on SLES10.

munja:/home/Tonci/bkwork/mysql-5.0 # bk changes|head
ChangeSet@1.2516, 2007-06-05 23:04:40+02:00, tsmith@quadxeon.mysql.com +10 -0
  Merge tsmith@bk-internal.mysql.com:/home/bk/mysql-5.0
  into  quadxeon.mysql.com:/benchmarks/ext3/TOSAVE/tsmith/bk/maint/jun05/50
  MERGE: 1.2493.2.18

ChangeSet@1.2514, 2007-06-05 17:28:49+02:00, tsmith@quadxeon.mysql.com +7 -0
  Merge quadxeon.mysql.com:/benchmarks/ext3/TOSAVE/tsmith/bk/50
  into  quadxeon.mysql.com:/benchmarks/ext3/TOSAVE/tsmith/bk/maint/jun05/50
  MERGE: 1.2493.7.14

This is continuation of Bug#28577 (and probably many other reports).

How to repeat:
Linux server test:
C:\mysql507\bin>mysql -uroot -T -hmunja -p test
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.44 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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.06 sec)

mysql> SELECT GGUID FROM appointment0 UNION SELECT GGUID FROM appointment0;
Field   1:  `GGUID`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     16
Max_length: 0
Decimals:   0
Flags:      NOT_NULL BINARY

0 rows in set (0.03 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)

Field type is STRING, collation is BINARY and 1st field has BINARY flag set which is correct according to manual.

-----
Win test (server built from same sources, using same cl client):

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> 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

0 rows in set (0.09 sec)

Collation for the first field is obviously wrong, both logically and according to manual.

Suggested fix:
- Please fix this
- Please rename "binary" collation to something else
- Please change *type* system and rules for implicit type conversion

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)

Win MySQL server started with:
 - character-set-server=utf8 & collation-server=utf8_general_ci

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?

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)...

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.