Bug #8204 incorrect conversion results of arithmetic operation to ucs2 string
Submitted: 30 Jan 2005 19:49 Modified: 12 Aug 2010 6:20
Reporter: Oleksandr Byelkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.0 OS:Any
Assigned to: Alexander Barkov CPU Architecture:Any

[30 Jan 2005 19:49] Oleksandr Byelkin
Description:
incorrect conversion results of arithmetic operation to ucs2 string

How to repeat:
+ CREATE TABLE t1 (a varchar(64) character set ucs2);
+ INSERT INTO t1 VALUES ("1.1"), ("2.1");
+ SELECT * FROM t1;
+ a
+ 1.1
+ 2.1
+ update t1 set a= a + 0.1;
+ SELECT * FROM t1;
+ a
+ 1?
+ 2?
+ DROP TABLE t1;
[30 Jan 2005 20:43] Oleksandr Byelkin
it is script for mysql.test

Attachment: test.test (text/plain), 205 bytes.

[30 Jan 2005 20:47] Alexander Keremidarski
Simpler test case:

select convert(1.1 using ucs2);
  -> 1
[18 Feb 2005 16:51] Antony Curtis
Problem is actually quite serious and seems to affect all instances where a numerical value result is converted to a ucs2 string:

+ CREATE TABLE t1 (a varchar(64) character set ucs2);
+ INSERT INTO t1 VALUES ("1.1"), ("2.1");
+ SELECT a,hex(a) FROM t1;
+ a     hex(a)
+ 1.1   0031002E0031
+ 2.1   0032002E0031
+ UPDATE t1 SET a= a + 0.1;
+ SELECT a,hex(a) FROM t1;
+ a     hex(a)
+ 1?    00312E32
+ 2?    00322E32
+ UPDATE t1 SET a= a + 10;
+ SELECT a,hex(a) FROM t1;
+ a     hex(a)
+ ?     3131
+ ?     3132
+ DROP TABLE t1;
+ SELECT CONVERT(1.1 USING ucs2);
+ CONVERT(1.1 USING ucs2)
+ 1?
[8 Mar 2005 11:49] Sergei Golubchik
INSERT t1 VALUES (1.2);

doesn't work very good either
[9 Mar 2005 20:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22859
[27 Apr 2005 8:09] Antony Curtis
This bug won't be fixed in 4.1 and should be documented. The patch attached to this worklog is only a poor workaround and does not solve the problem in all cases.
A real solution should be developed for 5.x.
[10 May 2005 13:01] Alexander Barkov
We had discussion with Monty, PeterG and Serg yesterday about
number-to-string conversion. Decision was made to fix it in 5.1.
This problem won't be fixed before 5.1.
One needs to use explicit CAST to CHAR with CHARACTER SET ucs2
to make it work before 5.1.
[29 May 2006 13:00] MySQL Verification Team
See also bug: http://bugs.mysql.com/bug.php?id=20108
[19 Jun 2007 9:01] Alexander Barkov
See also http://bugs.mysql.com/bug.php?id=29072
[15 Oct 2007 8:38] Alexander Barkov
See also:

http://bugs.mysql.com/bug.php?id=31006
[12 Aug 2010 6:20] Alexander Barkov
This problem was fixed in MySQL-5.5 under terms of
"WL#2649 Number-to-string conversions".
http://forge.mysql.com/worklog/task.php?id=2649

mysql> DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a varchar(64) character set ucs2); INSERT INTO t1 VALUES ("1.1"), ("2.1"); SELECT * FROM t1; update t1 set a= a + 0.1; SELECT * FROM t1; SELECT CONVERT(1.1 USING ucs2);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

+------+
| a    |
+------+
| 1.1  |
| 2.1  |
+------+
2 rows in set (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

+--------------------+
| a                  |
+--------------------+
| 1.2000000000000002 |
| 2.2                |
+--------------------+
2 rows in set (0.00 sec)

+-------------------------+
| CONVERT(1.1 USING ucs2) |
+-------------------------+
| 1.1                     |
+-------------------------+
1 row in set (0.00 sec)