| Bug #10504 | Character set does not support traditional mode | ||
|---|---|---|---|
| Submitted: | 10 May 2005 11:42 | Modified: | 19 Sep 2005 19:40 |
| Reporter: | Disha | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.5-beta-nt | OS: | Windows (window xp) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[10 May 2005 16:24]
MySQL Verification Team
Verified on Linux.
[28 Jul 2005 4:47]
Alexander Barkov
Comments from PeterG: The ASCII() function is non-standard but DB2 and Oracle and SQL Server have it. Here is a reference to what SQL Server does: http://www.mcse.ms/archive81-2005-4-1564429.html Bart Duncan's message on this thread says that ASCII() is for ASCII, you would use the UNICODE() function for Unicode strings. The MySQL Reference Manual says: " ASCII(str) Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for characters with numeric values from 0 to 255. " To me, that means: yes, it returns the value of the character, but only if the character is in an 8-bit character set. Therefore I believe we should not change ASCII(). However, CHAR() is indeed getting bad values in. Alternatives: 1. Error (or warning if not strict). The result of CHAR() is in the connection character set, so it should be possible to test if 2557 is out of range. 2. Since 2557 is in hexadecimal 0x09fd, treat INSERT INTO t VALUES (CHAR(2557)); as INSERT INTO t VALUES (0x09fd); That is, there are multiple bytes here. 3. Convert invalid values to 255. I am neutral about which alternative is best.
[28 Jul 2005 4:52]
Alexander Barkov
Sticking to the second way.
[5 Sep 2005 13:26]
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/29324
[16 Sep 2005 5:29]
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/29971
[19 Sep 2005 4:52]
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/30034
[19 Sep 2005 4:56]
Alexander Barkov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Fixed in 5.0.14
[19 Sep 2005 19:40]
Paul DuBois
Noted in 5.0.14 changelog, and in description of CHAR() function.
Description: In traditional mode invalid range of characters are accepted in tradional and strict mode How to repeat: 1. execute the following command. set @@sql_mode='traditional'// Create table t1 (c1 CHAR(255) CHARACTER SET utf8)// 2. Execute the following insert statment insert into t1 (c1) values (char('1'))// insert into t1 (c1) values (char('195'))// insert into t1 (c1) values (char('196'))// insert into t1 (c1) values (char('2557'))// 3. execute the following select commands select ascii(c1) from t1// Expected Result: it should reject char('2557') value Actual Result: +-----------+ | ascii(c1) | +-----------+ | 1 | | 195 | | 195 | | 195 | +-----------+ 4 rows in set (0.00 sec) Suggested fix: In traditional mode if range is 1 to 195 then it should not accept invalid range of characters and give error in traditional mode.