[MDEV-9862] Illegal mix of collation, when comparing column with CASE expression Created: 2016-04-01  Updated: 2016-04-01  Resolved: 2016-04-01

Status: Closed
Project: MariaDB Server
Component/s: Character Sets
Affects Version/s: 5.5
Fix Version/s: 5.5.49

Type: Bug Priority: Minor
Reporter: Vladislav Vaintroub Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description   

An unexpected 'Illegal mix of collations' pops up when comparing result of CASE expression with a field (CASE expression returns either 'Y' or NULL). According to Bar, this
should not happen.

To reproduce :

MariaDB [test]> CREATE TABLE `datatypetestm` (CCHAR1 char(1));
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select CCHAR1 from datatypetestm where CASE CCHAR1 WHEN 'aaaa' THEN 'Y' WHEN 'aaaa' THEN 'Y' ELSE NULL END <> CCHAR1;
ERROR 1267 (HY000): Illegal mix of collations (cp850_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<>'

 Comments   
Comment by Alexander Barkov [ 2016-04-01 ]

The full script which demonstrates the problem:

SET NAMES cp850;
 
CREATE TABLE t1 (a CHAR(1) CHARACTER SET latin1);
 
SELECT a FROM t1 WHERE CASE a WHEN 'aaaa' THEN 'Y' WHEN 'aaaa' THEN 'Y' ELSE NULL END <> a;

Generated at Thu Feb 08 07:37:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.