What does Coercibility mean? MySQL User Variables

Question

All user variables have an implicit coercibility value

what does that mean? does that have something to do with ...

mysql> SET @a = 1; 
mysql> SET @A = @a; 
mysql> SELECT @a, @A;
mysql> SELECT @a, @A;
+------+------+
| @a    | @A |
+------+------+
| 1 | 1 |
+------+------+
mysql> SET @a = 2;
mysql> SELECT @a, @A;
mysql> SELECT @a, @A;
+------+------+
| @a    | @A |
+------+------+
| 2 | 2 |
+------+------+

where @A is assigned 2 maybe because it "references" @a?

Answer 1

SET @test = 'test';
SELECT COERCIBILITY(@test), COERCIBILITY('test');

---  ---
2    4

From the documentation:

COERCIBILITY(str)

The return values have the meanings shown in the following table. Lower values have higher precedence.

Coercibility  Meaning   Example
0             Explicit collation    Value with COLLATE clause
1             No collation          Concatenation of strings with different collations
2             Implicit collation    Column value
3             System constant       USER() return value
4             Coercible             Literal string
5             Ignorable             NULL or an expression derived from NULL

Coercibility defines what will be converted to what in case of collation conflict.

An expression with higher coercibility will be converted to the collation of the expression with lower coercibility.

This function is useful to troubleshoot collation problems. For instance, these two queries return the results in different order.

This one:

SELECT  col
FROM    (
        SELECT  DATABASE() AS col
        UNION ALL
        SELECT  'X'
        ) q
ORDER BY
        col;

----
'test'
'X'

And this one:

SET @t := 'X' COLLATE UTF8_BIN;
SELECT  col
FROM    (
        SELECT  DATABASE() AS col
        UNION ALL
        SELECT  @t
        ) q
ORDER BY
        col;

----
'X'
'test'

Why so?

DATABASE() is a system function whose return values have coercibility of 3 and default database collation of UTF8_GENERAL_CI.

'X' in the first query is a string literal with coercibility of 4.

The result of the UNION will always have the least coercibility of all values (that is, 3) and the collation of the expression with the least coersibility:

SELECT  col, COERCIBILITY(col), COLLATION(col)
FROM    (
        SELECT  DATABASE() AS col
        UNION ALL
        SELECT  'X'
        ) q
ORDER BY
        col;

--------
'test',  3, 'utf8_general_ci'
'X',     3, 'utf8_general_ci'

In the second query, @t is a variable that holds string value with collation UTF8_BIN. Since its coercibility is lower than that of the system function, it's the variable's collation that is used in the resultset.

Coercibility of the variable is 2, so the result's coercibility is that of the variable, as well as the collation:

SET @t := 'X' COLLATE UTF8_BIN;
SELECT  col, COERCIBILITY(col), COLLATION(col)
FROM    (
        SELECT  DATABASE() AS col
        UNION ALL
        SELECT  @t
        ) q
ORDER BY
        col;

--------
'X',     2, 'utf8_bin'
'test',  2, 'utf8_bin'