COERCIBILITY

Syntax

COERCIBILITY(str)

Purpose

COERCIBILITY() returns the collation coercibility value of the str argument for multilingual linguistic sorting.

Two variables of the same character set are compared based on their collations. However, when two variables of different character sets are compared, the precedence of their collations must be determined. Take the following statement as an example:

obclient> SELECT c1 FROM t WHERE c1 = 'Y';

If the column c1 and the string literal 'Y' use the same collation, no ambiguity exists. If a different collation is specified for the t table in table_option or a different collation is specified for the system parameter collation_connection, the collation used in the comparison depends on the precedence of the two collations.

The following table describes the precedence of different collations. A smaller value indicates higher precedence.

OceanBase Database uses coercibility values and the following rules to eliminate ambiguities:

• A smaller coercibility value has higher precedence.

• If the collations of the two compared items have the same coercibility value, the following supplementary rules are used:

  • If both or none of the two character sets are Unicode, and different collations are used, an error is returned.

  • The item that uses a Unicode character set has higher precedence over the one that does not. For example, the following statement does not return an error:

      obclient> SELECT CONCAT(t1.utf8_column, t2.gbk_column) FROM t1,t2;
    

  • If the two compared items use the same character set, but their collations are _bin and a_ci or _cs, the_bin collation is used.

Examples

obclient> SELECT COERCIBILITY('oceanbase' COLLATE utf8mb4_bin);
+-----------------------------------------------+
| COERCIBILITY('oceanbase' COLLATE utf8mb4_bin) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set

obclient> SELECT COERCIBILITY('oceanbase');
+---------------------------+
| COERCIBILITY('oceanbase') |
+---------------------------+
|                         4 |
+---------------------------+
1 row in set

obclient> SELECT COERCIBILITY(USER());
+----------------------+
| COERCIBILITY(USER()) |
+----------------------+
|                    3 |
+----------------------+
1 row in set

obclient> SELECT COERCIBILITY(1000);
+--------------------+
| COERCIBILITY(1000) |
+--------------------+
|                  5 |
+--------------------+
1 row in set