Bug #9129 CHARSET(), COLLATION(), COERCIBILITY() not always correct for NULL values
Submitted: 11 Mar 2005 16:02 Modified: 16 Mar 2005 19:27
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1, 5.0 OS:
Assigned to: Alexander Barkov

[11 Mar 2005 16:02] Paul Dubois
Description:
The COERCIBILITY() function almost always returns a non-NULL value, even for
NULL arguments.  For example, if there is no default database, the
DATABASE() function returns NULL, but the coercibility is SYSCONST:

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> select coercibility(database());
+--------------------------+
| coercibility(database()) |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)

However, the character set and collation are NULL:

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

mysql> select collation(database());
+-----------------------+
| collation(database()) |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set (0.00 sec)

Presumably the character set should be utf8, and the collation
should be utf_general_ci?

CHARSET() and COLLATION() also return NULL in other contexts:

- For column values that are NULL (should be column charset/collation)
- For user variables that are NULL (should be ???)

COERCIBILITY() does return NULL in one instance:

mysql> select coercibility(NULL);
+--------------------+
| coercibility(NULL) |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)

This should be fixed to return IGNORABLE.

How to repeat:
See above.
[16 Mar 2005 12:59] Alexander Barkov
Fixed in 4.1.11.
[16 Mar 2005 19:27] Paul Dubois
Noted in 4.1.11 changelog.

Also added IGNORABLE to the description
of COERCIBILITY().