MySQL Bugs: #70643: COERCIBILITY behaves not as described in manual

Bug #70643	COERCIBILITY behaves not as described in manual
Submitted:	17 Oct 2013 7:17	Modified:	14 Nov 2013 19:59
Reporter:	Olag Ulga	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.6.14,5.1.40, 5.1.71, 5.5.35, 5.0.97, 5.6.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	COERCIBILITY CONCAT CONCAT_WS

Description:
In the manual the chapter:
10.1.7.5. Collation of Expressions (http://dev.mysql.com/doc/refman/5.6/en/charset-collation-expressions.html)
describes as the second point that: The concatenation of two strings with different collations has a coercibility of 1.

This statement is wrong. In 5.6.14 and 5.1.40 the COERCIBILITY of a concatenated string is always, as far as we have observed, the lowest COERCIBILITY of all strings.

To concat strings we use CONCAT or CONCAT_WS

How to repeat:
select
COERCIBILITY('x') as x_coe,
COLLATION('x') as x_col,
COERCIBILITY('y' COLLATE latin1_general_ci) as y_coe,
COLLATION('y' COLLATE latin1_general_ci) as y_col,
COERCIBILITY(CONCAT('x', 'y' COLLATE latin1_general_ci)) as concat_coe,
COLLATION(CONCAT('x', 'y' COLLATE latin1_general_ci)) as concat_col

results in:
x_coe x_col              y_coe y_col             concat_coe concat_col
4     latin1_swedish_ci  0     latin1_general_ci 0          latin1_general_ci

The coercibility of a string with explicit COLLATE is 0. The same behavior is also reproducible, if you take a text column of a table instead of:'y' COLLATE latin1_general_ci'.
The only difference is that a textcolumn has a COERCIBILITY of 2 instead of 4.

select
COERCIBILITY('x') as x_coe,
COLLATION('x') as x_col,
COERCIBILITY(mytextcol) as y_coe,
COLLATION(mytextcol) as y_col,
COERCIBILITY(CONCAT('x', mytextcol)) as concat_coe,
COLLATION(CONCAT('x', mytextcol)) as concat_col
from mytable

results in:
x_coe x_col              y_coe y_col             concat_coe concat_col
4     latin1_swedish_ci  2     latin1_general_ci 2          latin1_general_ci

Suggested fix:
In my opinion the behavior of the server and the CONCAT-function makes sense. The documentation should be corrected.

Thank you for the bug report. According your last comment is Documentation bug right?. Thanks.

In my opinion it is a documentation bug yes. But I'm not a coercibility specialist. I think this needs to be juged by a developer who knows the internals and concepts of the coercibility.

Thank you for the feedback.

Verified as described. I verify it as a server bug.