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: | |
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
[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)