CAST to CHAR should use one of the collation settings instead of the charset's default collation

character-set-client-handshake=FALSE

character-set-server=latin1

collation-server=latin1_general_ci

sql_mode=ANSI,TRADITIONAL

drop database if exists latin;

create database latin;

use latin;

show session variables where variable_name like '%coll%' or variable_name like '%char%' or variable_name like 'sql_mode';

create table if not exists num (

	a int,

	b int

);

create table if not exists str (

	a char(4),

	b char(4)

);

create view combine as

select cast(a as CHAR) || '.' || cast(b as CHAR) as cat

from num

union

select a || '.' || b as cat

from str;

create view cond as

select num.a as na, num.b as nb, str.a as sa, str.b as sb

from num

join str

on str.a = cast(num.a as CHAR);

show table status;

Variable_name   Value

character_set_client    latin1

character_set_connection        latin1

character_set_database  latin1

character_set_filesystem        binary

character_set_results   latin1

character_set_server    latin1

character_set_system    utf8

character_sets_dir      /usr/share/mysql/charsets/

collation_connection    latin1_general_ci

collation_database      latin1_general_ci

collation_server        latin1_general_ci

sql_mode        REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Name    Engine  Version Row_format      Rows    Avg_row_length  Data_length     Max_data_length Index_length    Data_free       Auto_increment  Create_time     Update_time     Check_time      Collation       Checksum        Create_options       Comment

combine NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Illegal mix of collations for operation 'UNION'

cond    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

num     InnoDB  10      Compact 0       0       16384   0       0       0       NULL    2016-09-13 18:05:27     NULL    NULL    latin1_general_ci       NULL

str     InnoDB  10      Compact 0       0       16384   0       0       0       NULL    2016-09-13 18:05:27     NULL    NULL    latin1_general_ci       NULL