MySQL Bugs: #29350: charset() and collation() return 'binary' incorrectly

Bug #29350	charset() and collation() return 'binary' incorrectly
Submitted:	26 Jun 2007 4:20	Modified:	5 Sep 2007 1:09
Reporter:	Yoshiaki Tajika (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S2 (Serious)
Version:	5.0.46-BK, 5.0.37	OS:	Any
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
charset() and collation() return 'binary' incorrectly

How to repeat:
The procedure below is quoted from the manual "19.2. CREATE VIEW Syntax".

  mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));
  mysql> SET NAMES 'latin1';
  mysql> SELECT * FROM v;
  +-------------------+---------------------+
  | CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
  +-------------------+---------------------+
  | latin1            | latin1_swedish_ci   |
  +-------------------+---------------------+
  mysql> SET NAMES 'utf8';
  mysql> SELECT * FROM v;
  +-------------------+---------------------+
  | CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
  +-------------------+---------------------+
  | utf8              | utf8_general_ci     |
  +-------------------+---------------------+

I followed this example. But in my 5.0.38(windows) and 5.0.37(linux), 
they always return 'binary' as each field value. 
On the contrary, 4.1.23(windows) and 4.1.22(linux) worked as expected.

Suggested fix:
N/A

Thank you for a bug report. Verified just as described both with 5.0.37 on Windows and with latest 5.0.46-BK on Linux:

openxs@linux:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));
Query OK, 0 rows affected (0.01 sec)

mysql> set names 'latin1';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v;
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| binary            | binary              |
+-------------------+---------------------+
1 row in set (0.00 sec)

And what is the reason? Let's check:

mysql> select charset('A');
+--------------+
| charset('A') |
+--------------+
| latin1       |
+--------------+
1 row in set (0.01 sec)

mysql> select charset(65);
+-------------+
| charset(65) |
+-------------+
| binary      |
+-------------+
1 row in set (0.00 sec)

mysql> select charset(char(65)), char(65);
+-------------------+----------+
| charset(char(65)) | char(65) |
+-------------------+----------+
| binary            | A        |
+-------------------+----------+
1 row in set (0.00 sec)

So, looks like charset (and collation) is just not determined correctly now for char(X). char(X) should return a char in current character set, not binary!

The problem is that the manual is not up to date.
Some times ago, a new syntax was introduced:

CHAR(x USING csname) - to return a string with the given character set.

At the same time, a change was made that CHAR(x) without the USING
clause returns always a binary string.

This manual section should be fixed.

To doc team: Setting the bug into "documenting" state. Please takeover this 
bug.

CHAR() no longer makes a good example here because it returns binary strings. I replaced the example wiith one that modifies the SQL mode and thus changes the view result:

mysql> CREATE VIEW v (mycol) AS SELECT 'abc'; 
Query OK, 0 rows affected (0.01 sec)

mysql> SET sql_mode = ''; 
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v; 
+-------+
| mycol |
+-------+
| mycol |
+-------+
1 row in set (0.01 sec)

mysql> SET sql_mode = 'ANSI_QUOTES'; 
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v; 
+-------+
| mycol |
+-------+
| abc   |
+-------+
1 row in set (0.00 sec)