|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, b INT);
|
INSERT INTO t1 VALUES ('a',1);
|
SELECT CONCAT(a, IF(b>10, _utf8 X'61', _utf8 X'61')) FROM t1;
|
SELECT CONCAT(a, IF(b>10, _utf8 X'61', _utf8 B'01100001')) FROM t1;
|
returns correct output for the first SELECT:
MariaDB [test]> SELECT CONCAT(a, IF(b>10, _utf8 X'61', _utf8 X'61')) FROM t1;
|
+-----------------------------------------------+
|
| CONCAT(a, IF(b>10, _utf8 X'61', _utf8 X'61')) |
|
+-----------------------------------------------+
|
| aa |
|
+-----------------------------------------------+
|
1 row in set (0.00 sec
|
but returns an error for the second SELECT:
MariaDB [test]> SELECT CONCAT(a, IF(b>10, _utf8 X'61', _utf8 B'01100001')) FROM t1;
|
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
|
The second query should be fixed to return a result,
because _utf8 X'61' and _utf8 B'01100001' are actually
the same values representing Latin small letter 'a':
MariaDB [test]> SELECT _utf8 X'61', _utf8 B'01100001';
|
+-------------+-------------------+
|
| _utf8 X'61' | _utf8 B'01100001' |
|
+-------------+-------------------+
|
| a | a |
|
+-------------+-------------------+
|
1 row in set (0.00 sec)
|
|