Invalid combination of sorts (utf8_unicode_ci, COERCIBLE) and (utf8_general_ci, COERCIBLE) for operation '='

Question

When I run the following query:

CREATE ALGORITHM = UNDEFINED VIEW d_view_galerias AS ( SELECT id, titulo, 'foto' AS tipo, '' AS embed FROM d_galeria_fotos ) UNION ( SELECT id, titulo, 'video' AS tipo, embed FROM d_galeria_videos ) 

I get an error message:

Invalid combination of sorts (utf8_unicode_ci, COERCIBLE) and (utf8_general_ci, COERCIBLE) for operation '='

"tipo" gets like utf8_unicode, but other fields like utf8_general ... how to do translation, convert?

Answer 1

The error message is rather confusing as it indicates operation = - this is not obvious from the request you sent, but is called by a UNION request that selects only individual values. Thus, using implicit equality matching.

In either case, you can always force the column to be sorted using the COLLATE . Here is an example if you want to change the sorting of the tipo column:

 SELECT id, titulo, 'foto' COLLATE utf8_general_ci AS tipo ... UNION SELECT id, titulo, 'video' COLLATE utf8_general_ci AS tipo, ... 

Answer 2

I had a similar problem. I did this to isolate the comparison in the where clause causing the error. I ran the CONVERT function for the value that I mapped to table mapping.

 `field` = CONVERT(value USING charset_of_table) 

See this post for more details and examples of using CONVERT.