MySQL Select with Where on a View calculated column results in Illegal mix of collations

20200514

I have been being to create a lookup table for woocommerce orders states. So I decided to implement it as a view:

Before adding in ‘collate utf8_general_ci’ to each record in the view below I was getting an error when consuming the view:

Error Code: 1267. Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT)

create view vRefOrderStates
 as
 -- 20200513 LSR
 -- Initial version
 select 'wc-cancelled' collate utf8_general_ci as pstatus, 0 as complete, 1 as failed, 0 as open
 union
 select 'wc-completed' collate utf8_general_ci as pstatus, 1 as complete, 0 as failed, 0 as open
 union
 select 'wc-failed' collate utf8_general_ci as pstatus, 0 as complete, 1 as failed, 0 as open
 union
 select 'wc-on-hold' collate utf8_general_ci as pstatus, 0 as complete, 0 as failed, 1 as open
 union
 select 'wc-pending' collate utf8_general_ci as pstatus, 0 as complete, 0 as failed, 1 as open
 union
 select 'wc-processing' collate utf8_general_ci as pstatus, 0 as complete, 0 as failed, 1 as open
 union
 select 'wc-refunded' collate utf8_general_ci as pstatus, 0 as complete, 1 as failed, 0 as open;

Then for speed of excution (an assumption) we turned the above view into an indexed table and had to specify collation as well to prevent similar errors:

CREATE TABLE m4m_refOrderStates
     (
     post_status char(20)
     ,complete bool not null
     ,failed bool not null
     ,incomplete bool not null
     ,KEY iPostState (post_status) USING BTREE
 )
 COLLATE utf8mb4_unicode_ci;
 insert into m4m_refOrderStates values('wc-cancelled', 0, 1, 0);
 insert into m4m_refOrderStates values('wc-completed', 1, 0, 0);
 insert into m4m_refOrderStates values('wc-failed', 0, 1, 0);
 insert into m4m_refOrderStates values('wc-on-hold', 0, 0, 1);
 insert into m4m_refOrderStates values('wc-pending', 0, 0, 1);
 insert into m4m_refOrderStates values('wc-processing', 0, 0, 1);
 insert into m4m_refOrderStates values('wc-refunded', 0, 1, 0);

---

201903 Woocommerce, creating a view that wraps HTML around post_status, so post_status is now calculated column (yes stupid wordpress is ignoring newlines in the pasted SQL snippet)

,concat(
     case
         when coalesce(mecha_note, '') <> ''
             then '
'             else '' end,     case         when post_status like '%cancel%' or post_status like '%refund%' then             concat('', post_status, '')         when post_status like '%completed%' then             concat('', post_status, '
')         else post_status end,     case         when coalesce(mecha_note, '') <> ''             then concat('Fulfillment note:

', mecha_note, '
')
             else '' end
         ) as post_status

PHP -> SQL filtering by status errors:

select * from vOrdersList
      where post_status <> 'wc-completed'
      and post_status <> 'wc-cancelled'
      and post_status <> 'wc-refunded';

Error Code: 1267. Illegal mix of collations (utf8mb4_bin,NONE) and (utf8_general_ci,COERCIBLE) for operation 'like'    0.235 sec

What is the collation of the original posts table source column:

select COLLATION(post_status) as post_status, 
     COLLATION(user()) as user_func, 
     COLLATION(substring_index(user(), '@', 1)) as substr_func
         from  wp1_posts;

post_status, user_func, substr_func
 'utf8mb4_unicode_ci', 'utf8_general_ci', 'utf8_general_ci'

What about the calculated column in the view?

select COLLATION(post_status) as post_status, 
     COLLATION(user()) as user_func, 
     COLLATION(substring_index(user(), '@', 1)) as substr_func
         from  vOrdersList;

post_status, user_func, substr_func
 'utf8mb4_bin', 'utf8_general_ci', 'utf8_general_ci'

My solution: set collation for the calculated column in the view to match the source column, revised view for that column (see last line)
(yes stupid wordpress is ignoring newlines in the pasted SQL snippet) :

 ,concat(
     case
         when coalesce(mecha_note, '') <> ''
             then '
'             else '' end,     case         when post_status like '%cancel%' or post_status like '%refund%' then             concat('', post_status, '')         when post_status like '%completed%' then             concat('', post_status, '
')         else post_status end,     case         when coalesce(mecha_note, '') <> ''             then concat('Fulfillment note:

', mecha_note, '
')
             else '' end
         ) collate utf8mb4_unicode_ci as post_status