Bug #28550 Potential bugs related to the return type of the CHAR function
Submitted: 21 May 2007 6:54 Modified: 31 Oct 2007 1:28
Reporter: Asuka Kenji Siu Ching Pong (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.18-beta, 5.0 OS:Any
Assigned to: Alexey Kopytov CPU Architecture:Any

[21 May 2007 6:54] Asuka Kenji Siu Ching Pong
Description:
It seems that some code assume that the return type of the 'CHAR' function to be 'CHAR(1)', while other code assume the length could be more than one. This inconsistency in assumption may propagate and cause more serious bugs.

In the 'How to repeat' session below, only the first character is stored in the view, which is not the behavior expected by most SQL programmers.

How to repeat:
mysql> CREATE TABLE test.temp SELECT CHAR(0x414243) AS ColA;
ERROR 1406 (22001): Data too long for column 'ColA' at row 1

mysql> CREATE VIEW test.temp AS SELECT CHAR(0x414243) AS ColA;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test.temp;
+------+
| ColA |
+------+
| A    |
+------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(ColA) FROM test.temp;
+--------------+
| LENGTH(ColA) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(CHAR(0x414243)) AS ColA;
+------+
| ColA |
+------+
|    3 |
+------+
1 row in set (0.01 sec)
[21 May 2007 8:33] Sveta Smirnova
Thank you for the report.

Verified as described.
[4 Sep 2007 8:02] Alexey Kopytov
5.0.50 behaves differently:

mysql>  CREATE TABLE test.temp SELECT CHAR(0x414243) AS ColA;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'ColA' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test.temp;
+------+
| ColA |
+------+
| A    |
+------+
1 row in set (0.01 sec)

mysql> select hex(ColA) from test.temp;
+-----------+
| hex(ColA) |
+-----------+
| 41        |
+-----------+
1 row in set (0.00 sec)

mysql> drop table test.temp;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW test.temp AS SELECT CHAR(0x414243) AS ColA;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.temp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '???) AS `ColA`' at line 1
mysql> select hex(ColA) from test.temp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '???) AS `ColA`' at line 1
[4 Sep 2007 8:24] Alexey Kopytov
Same for 5.1.23-beta.
[4 Sep 2007 14:13] Alexey Kopytov
The syntax errors with a VIEW created as CREATE VIEW ... AS SELECT CHAR(0x...) is actually a regression introduced by the patch for bug #21513.
[4 Sep 2007 15:50] 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/commits/33645

ChangeSet@1.2522, 2007-09-04 19:49:07+04:00, kaa@polly.(none) +5 -0
  Bug #28550 "Potential bugs related to the return type of the CHAR function".
  
  Since, as of MySQL 5.0.15, CHAR() arguments larger than 255 are converted into multiple result bytes, a single CHAR() argument can now take up to 4 bytes. This patch fixes Item_func_char::fix_length_and_dec() to take this into account.
  
  This patch also fixes a regression introduced by the patch for bug21513. As now we do not always have the 'name' member of Item set for Item_hex_string and Item_bin_string, an own print() method has been added to Item_hex_string so that it could correctly be printed by Item_func::print_args().
[21 Oct 2007 17:45] 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/commits/35998

ChangeSet@1.2546, 2007-10-21 21:45:31+04:00, kaa@polly.(none) +5 -0
  Bug #28550 "Potential bugs related to the return type of the CHAR function".
    
  Since, as of MySQL 5.0.15, CHAR() arguments larger than 255 are converted into multiple result bytes, a single CHAR() argument can now take up to 4 bytes. This patch fixes Item_func_char::fix_length_and_dec() to take this into account.
    
  This patch also fixes a regression introduced by the patch for bug21513. As now we do not always have the 'name' member of Item set for Item_hex_string and Item_bin_string, an own print() method has been added to Item_hex_string so that it could correctly be printed by Item_func::print_args().
[29 Oct 2007 8:43] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:46] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:50] Bugs System
Pushed into 6.0.4-alpha
[31 Oct 2007 1:28] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

The result from CHAR() was incorrectly assumed in some contexts to
return a single-byte result.