COERCIBILITY

Syntax

COERCIBILITY(str)

Purpose

Returns the priority of the collation of the string str during multibyte sorting.

When comparing two variables with the same character set, the collation is used as the rule. However, when comparing two variables with different character sets, the priority between different character sets needs to be specified. For example, the following statement:

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

If c1 and 'Y' use the same collation, there is no ambiguity. However, if the table t specifies a different collation in table_option or the system variable collation_connection uses a different collation, the collation used during comparison depends on the priority between the two collations.

The priorities of different collations are shown in the table below, with smaller values indicating higher priority.

OceanBase Database resolves ambiguities using the coercibility values and the following rules:

• A smaller coercibility value indicates a higher priority.

• If the collations of the two operands have the same coercibility, the following additional rules are used:

  • If both character sets are Unicode or neither is Unicode, and the collations are different, an error is returned.

  • If one character set is Unicode and the other is not, the Unicode character set has higher priority. For example, the following statement does not return an error:

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

  • For the same character set, if the 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