MySQL Bugs: #37559: Update doesn't work for character set binary and char columns.

Bug #37559	Update doesn't work for character set binary and char columns.
Submitted:	20 Jun 2008 22:29	Modified:	11 Jul 2008 9:27
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	mysql-6.0-backup	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
1) I create Database and tables with binary character set.
2) I insert some values to the table.
3) Then I update some contents in the table. This does not work and the entries are not updated int he table especially if the column is of CHAR type.

create database y character set binary;
set names binary;
use y;
CREATE TABLE cap( id SMALLINT, country CHAR(20), city VARCHAR(20), population BIGINT, capital CHAR(20)) character set binary;
INSERT INTO cap VALUES (1,'IND','chn',87874646468,'deli'),(2,'US','Austin',-6466547,'DC'), (3,'Russia','moscow',76487623235682,'moscow'),(4,'IND','bang',-83875477,'deli');
select * from cap;
UPDATE cap SET capital='Delhi' WHERE country='IND';
select * from cap;

How to repeat:
mysql> create database y character set binary;
Query OK, 1 row affected (0.03 sec)

mysql> set names binary;
Query OK, 0 rows affected (0.00 sec)

mysql> use y;
Database changed
mysql> CREATE TABLE cap( id SMALLINT, country CHAR(20), city VARCHAR(20), population BIGINT, capital CHAR(20)) character set binary;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO cap VALUES (1,'IND','chn',87874646468,'deli'),(2,'US','Austin',-6466547,'DC'), (3,'Russia','moscow',76487623235682,'moscow'),(4,'IND','bang',-83875477,'deli');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from cap;
+------+----------------------+--------+----------------+----------------------+
| id   | country              | city   | population     | capital              |
+------+----------------------+--------+----------------+----------------------+
|    1 | IND                  | chn    |    87874646468 | deli                 |
|    2 | US                   | Austin |       -6466547 | DC                   |
|    3 | Russia               | moscow | 76487623235682 | moscow               |
|    4 | IND                  | bang   |      -83875477 | deli                 |
+------+----------------------+--------+----------------+----------------------+
4 rows in set (0.00 sec)

mysql> UPDATE cap SET capital='Delhi' WHERE country='IND';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from cap;
+------+----------------------+--------+----------------+----------------------+
| id   | country              | city   | population     | capital              |
+------+----------------------+--------+----------------+----------------------+
|    1 | IND                  | chn    |    87874646468 | deli                 |
|    2 | US                   | Austin |       -6466547 | DC                   |
|    3 | Russia               | moscow | 76487623235682 | moscow               |
|    4 | IND                  | bang   |      -83875477 | deli                 |
+------+----------------------+--------+----------------+----------------------+
4 rows in set (0.00 sec)

If you see the above table, we can notice that values are not updated.This happens specifically if we try to update the CHAR columns.

Documentation perhaps needs to be more clearer.

I think this is not a bug.

Examining the structure of the table that is created yields this:

--------------
show create table cap
--------------

*************************** 1. row ***************************
       Table: cap
Create Table: CREATE TABLE `cap` (
  `id` smallint(6) DEFAULT NULL,
  `country` binary(20) DEFAULT NULL,
  `city` varbinary(20) DEFAULT NULL,
  `population` bigint(20) DEFAULT NULL,
  `capital` binary(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=binary
1 row in set (0.00 sec)

That is, you have country as a BINARY data type, which is thus zero-padded. So the UPDATE statement compares a zero-padded value with a non-zero-padded value.  Changing it as follows yields the desired result:

mysql> UPDATE cap SET capital='Delhi' WHERE country=CAST('IND' AS BINARY(20));
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from cap;
+------+----------------------+--------+----------------+----------------------+
| id   | country              | city   | population     | capital              |
+------+----------------------+--------+----------------+----------------------+
|    1 | IND                  | chn    |    87874646468 | Delhi                | 
|    2 | US                   | Austin |       -6466547 | DC                   | 
|    3 | Russia               | moscow | 76487623235682 | moscow               | 
|    4 | IND                  | bang   |      -83875477 | Delhi                | 
+------+----------------------+--------+----------------+----------------------+
4 rows in set (0.00 sec)

Conversion of data types when you specify a character set of binary appears not to be documented. I will add it here:

http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

Zero-padding of BINARY is documented here:

http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html

Description of data type conversion to be added to manual:

Specifying the CHARACTER SET binary attribute for a
character data type causes the column to be created
as the corresponding binary data type: CHAR becomes
BINARY, VARCHAR becomes VARBINARY, and TEXT becomes
BLOB. For the ENUM and SET data types, this does not 
occur; they are created as declared. Suppose that
you specify a table using this definition:

CREATE TABLE t
(
  c1 VARCHAR(10) CHARACTER SET binary,
  c2 TEXT CHARACTER SET binary,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

The resulting table has this definition:

CREATE TABLE t
(
  c1 VARBINARY(10),
  c2 BLOB,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

As sveta mentioned, this bug report can be categorized to documentation team.

Thank you for the feedback.

Reclassified as documentation bug and marked as closed as documentation was updated.