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

[8 Jun 2007 11:54] Tonci Grgin
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
[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.