Bug #5720 Coercibility of CONVERT function is 2, should be 3

• Submitted: 23 Sep 2004 14:26
• Modified: 1 Oct 2004 15:27
• Reporter: Alexander M. Turek
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.5
• OS: Linux (Linux 2.6.8.1)
• Assigned to: Peter Gulutzan
• CPU Architecture: Any

[23 Sep 2004 14:26] Alexander M. Turek

Description:
When using the CONVERT function on a string literal, I get a string of the default collation of the selected character set and the coercibility value of 2 which is according to the docs (http://dev.mysql.com/doc/mysql/en/Charset-collate-tricky.html) the coercibility of column collations.
Other functions (I tried a few) result in a coercibility of 3 which is - in my humble opinion - the correct value.

How to repeat:
mysql> SELECT COERCIBILITY('foo');
+---------------------+
| COERCIBILITY('foo') |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT COERCIBILITY(CONVERT('foo' USING 'utf8'));
+---------------------------------------------------+
| COERCIBILITY(CONVERT('foo' USING 'utf8')) |
+---------------------------------------------------+
|                                                 2 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT COERCIBILITY(MD5('foo'));
+--------------------------+
| COERCIBILITY(MD5('foo')) |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)

Suggested fix:
CONVERT should have a coercibility of 2 in order to be consistant.

[26 Sep 2004 21:33] Alexander M. Turek

Oops, a small but fatal typo: Of course I meant that CONVERT should have a coercibility of _3_ in order to be consistant.

[1 Oct 2004 15:27] Peter Gulutzan

It's sensible to ask for consistency, but in the case of CONVERT we felt obliged to follow 
the SQL-99 standard document, which says: 
 
"The result [of conversion] has the Implicit coercibility characteristic ...". 
 
We use (2) as a code for Implicit. It's the same coercibility as for columns. So I believe (2) 
is okay. 
 
The manual section actually say "It's complex, but the following rules take care of most 
situations: ...". This happens to be one of the complex exceptions.