MySQL Bugs: #65681: "character_set_results" and "character_set

Bug #65681	"character_set_results" and "character_set_connection" poorly documented
Submitted:	20 Jun 2012 8:21	Modified:	6 Sep 2018 16:19
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
(Also see http://bugs.mysql.com/bug.php?id=65676)

http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html:

A SET NAMES 'x' statement is equivalent to these three statements:
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

Setting character_set_connection to x also implicitly sets collation_connection to the default collation for x. It is unnecessary to set that collation explicitly. To specify a particular collation, use the optional COLLATE clause:
SET NAMES 'charset_name' COLLATE 'collation_name'

SET CHARACTER SET charset_name

SET CHARACTER SET is similar to SET NAMES but sets character_set_connection and collation_connection to character_set_database and collation_database. A SET CHARACTER SET x statement is equivalent to these three statements:
SET character_set_client = x;
SET character_set_results = x;
SET collation_connection = @@collation_database;

Now what does character_set_results do exactly that character_set_connection does not?

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_character_set_c...:

character_set_connection: The character set used for literals that do not have a character set introducer and for number-to-string conversion.
character_set_results: The character set used for returning query results such as result sets or error messages to the client.

What is a "character set introducer"? I find no definition of that term anywhere

How to repeat:
The bottomline is that nobody really understand what the 2 variables will do precisely and what is the effect of SETting them. People simply SET NAMES and that is it.

But I do remember a few reports here where setting character_set_connection alone actually has an effect (I think I remember one where the output from a query using REGEXP on utf8 data had an effect. I think this is still a non-concluded bug report here and it could be a bug). I never observed any effect of SETting character_set_results (but maybe did not find a test case where there is a visible effect).

Suggested fix:
1) Document in an understandable way (for users without knowledge of server internals) how things work.

2) Consider (as requested in http://bugs.mysql.com/bug.php?id=65676) to make as many of the internal character set conversions transparent for user. Loic has a valid point that what really matters is *character set used for storage* and *character set used in the client*. Everything else are intermediate conversions.

(I posted in 'charsets' category as I have to choose one. But it is also valid for 'docs' category.)

Character set introducer is documented elsewhere, http://dev.mysql.com/doc/refman/5.5/en/charset-literal.html:

"The _charset_name expression is formally called an introducer. It tells the parser, “the string that is about to follow uses character set X.” Because this has confused people in the past, we emphasize that an introducer does not change the string to the introducer character set like CONVERT() would do. It does not change the string's value, although padding may occur. The introducer is just a signal."

and so on.

I do agree that link to definition may help, as well as some examples explaining what is character_set_connection used for.

As regards docs I suggest a diagram explaning how data (stored string data, data from number-to-string conversion, string literals in queries, whatever) are stepwise being transformed/encoded before sent to the client (or stored).  This should tell in what stages c_s_result and c_s_connection have effect. I actually have tried myself to create such diagram for my own understanding but was not able to complete it.

(and if the term "character set introducer" is explained elsewhere please link to it).

Posted by developer:
 
"
Now what does character_set_results do exactly that character_set_connection
does not?
"

It's as described at
https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html#charset-connection-system-...,
i.e., it's used for returning query results:

The character_set_results system variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, result metadata such as column names, and error messages.

That section describes the contexts in which the three connection charset variables apply.

"
What is a "character set introducer"? I find no definition of that term
anywhere
"

I've added a link to the section that explains introducers:
https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html

"
The bottomline is that nobody really understand what the 2 variables will do
precisely and what is the effect of SETting them. People simply SET NAMES and
that is it.
"

That's generally the correct action (or specifying --default-character-set
at connect time), so nothing else is needed.