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

[17 Oct 2013 7:17] Olag Ulga
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.
[17 Oct 2013 11:58] MySQL Verification Team
Thank you for the bug report. According your last comment is Documentation bug right?. Thanks.
[22 Oct 2013 14:48] Olag Ulga
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.
[14 Nov 2013 19:58] Sveta Smirnova
Thank you for the feedback.

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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.