I'm trying to create a view for my database.
CREATE VIEW `latest` AS
SELECT r.id as recheck_id, f.id as failure_id, r.`when`,
case
when r.description like _utf8'dummy' then f.description
else r.description
end as description,
case
when r.technician_id is null then f.technician_id
else r.technician_id
end as technician_id,
case
when r.technician_id is null then f.tname
else r.name
end as tech_name,
case
when r.technician_id is null then f.tsurname
else r.surname
end as tech_surname,
f.customer_id, f.name as cust_name, f.surname as cust_surname, f.area,
case
when r.telephone is null then f.telephone
else r.telephone
end as telephone, mobile,
f.appliance_id, f.type, f.model, f.brand,
f.budget, f.advance_payment, f.final_price
FROM rechecks_with_info r
left join failures_with_info f on f.id = r.failure_id
where `when` =
(select max(r2.`when`)
from rechecks r2
where r.failure_id = r2.failure_id)
order by `when` desc;
Everything works fine, until I try to search for a string inside the description
column
I get an Illegal mix of collations (utf8_bin,NONE) and (utf8_general_ci,COERCIBLE) for operation 'like'
error.
I checked the collation of the specific column and it is indeed set to utf8_bin
by default. All other columns are in utf8_general_ci
.
Is there a way I can specify the collation I want for the specific column in the view?
I have tried adding a specific collation after the description
column definition and I got and error.
I figured out the problem a while ago and thought of sharing.
It turns out that the table columns the two descriptions where read from were not of the same collation. One was utf8_general_ci
and the other utf8_unicode_ci
. Their mixing produced a utf8_bin
collated column.
Using the same collation for the two tables produced the correct results.