Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (latin1_swedish

I have a query like this:

SELECT MOV.id, MOV.idProd, MOV.merce, MOV.move AS qta, MOV.prezzo, MOV.seriale, MOV.lotto, MOV.SNLTdett, MOV.scadenza, MOV.typeMove, MOV.typeDoc, MOV.tabDoc, MOV.tabIdDoc, MOV.ragSoc, MOV.rifDoc, MOV.dataDoc, MOV.rifMove, MOV.dataMove, MOV.dataModifica, MOV.usrModifica, MOV.note, P.codProdotto, P.nomeProdotto, P.composto, CSL.ita AS causale 
    FROM PArticoli AS P, varCausaliDoc AS CSL, magMovimenti AS MOV 
        INNER JOIN(
            SELECT rifDoc, 'bollaOut' AS tabDoc FROM bollaOut WHERE idRagSoc=7 AND typRagSoc='cliente' UNION 
            SELECT rifDoc, 'schApp' AS tabDoc FROM schApp WHERE idCliente=7 UNION 
            SELECT rifDoc, 'schAut' AS tabDoc FROM schAut WHERE idCliente=7 UNION 
            SELECT rifDoc, 'schOrd' AS tabDoc FROM schOrd WHERE idCliente=7 UNION 
            SELECT rifDoc, 'schLoc' AS tabDoc FROM schLoc WHERE idCliente=7 
        ) DOCOUT ON DOCOUT.tabDoc=MOV.tabDoc AND DOCOUT.rifDoc=MOV.rifDoc  
    WHERE MOV.idProd=P.id AND MOV.typeDoc=CSL.ident AND MOV.idProd=2595

the problem is here DOCOUT.tabDoc=MOV.tabDoc because it's a "latin1_swedish_ci"

I have tried solution as:

... (DOCOUT.tabDoc COLLATE latin1_swedish_ci) = MOV.tabDoc ... but I have an error as: COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8mb4'
... DOCOUT.tabDoc = (MOV.tabDoc COLLATE utf8mb4_unicode_ci) ... but I have an error as: COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'latin1'

I wouldn't like to change the charset on my dbase because I'm afraid to broke something in some other parts in my code... any suggestions?

CodePudding user response：

Okay let's make a simpler test case:

create table DOCOUT (
  tabDoc varchar(10)
) character set utf8mb4;

create table MOV (
  tabDoc varchar(10), 
  key(tabDoc)
) character set latin1 collate latin1_swedish_ci;

A JOIN between these two tables can't use the index, because of the incompatible character set & collation. It's going to do a table-scan or index-scan on MOV instead of index lookups.

mysql> explain SELECT * FROM DOCOUT JOIN MOV ON DOCOUT.tabDoc = MOV.tabDoc;
 ---- ------------- -------- ------------ ------- --------------- -------- --------- ------ ------ ---------- ----------------------------------------------------------------- 
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                                           |
 ---- ------------- -------- ------------ ------- --------------- -------- --------- ------ ------ ---------- ----------------------------------------------------------------- 
|  1 | SIMPLE      | DOCOUT | NULL       | ALL   | NULL          | NULL   | NULL    | NULL |    1 |   100.00 | NULL                                                            |
|  1 | SIMPLE      | MOV    | NULL       | index | NULL          | tabDoc | 13      | NULL |    1 |   100.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
 ---- ------------- -------- ------------ ------- --------------- -------- --------- ------ ------ ---------- -----------------------------------------------------------------

We can use MySQL's CONVERT() function to coerce the column of DOCOUT to a compatible character set row by row. This is not greatly more costly, because the rows of DOCOUT have already been read, since it's the first table in the join.

Then the optimization can use the index on MOV:

mysql> explain SELECT * FROM DOCOUT JOIN MOV ON CONVERT(DOCOUT.tabDoc USING latin1) = MOV.tabDoc;
 ---- ------------- -------- ------------ ------ --------------- -------- --------- ------ ------ ---------- -------------------------- 
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref  | rows | filtered | Extra                    |
 ---- ------------- -------- ------------ ------ --------------- -------- --------- ------ ------ ---------- -------------------------- 
|  1 | SIMPLE      | DOCOUT | NULL       | ALL  | NULL          | NULL   | NULL    | NULL |    1 |   100.00 | NULL                     |
|  1 | SIMPLE      | MOV    | NULL       | ref  | tabDoc        | tabDoc | 13      | func |    1 |   100.00 | Using where; Using index |
 ---- ------------- -------- ------------ ------ --------------- -------- --------- ------ ------ ---------- --------------------------