Bug #28550 Potential bugs related to the return type of the CHAR function

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)

Thank you for the report.

Verified as described.

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

Same for 5.1.23-beta.

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.

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().

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().

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

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.