Description:
Subqueries with 'limit 1' cause concat strings to report errors
How to repeat:
Here are the steps to reproduce:
mysql> create database test_db;
Query OK, 1 row affected (0.01 sec)
mysql> use test_db;
Database changed
mysql> create table t1 (
-> flowid varchar(32)
-> ) charset=gbk collate=gbk_bin;
Query OK, 0 rows affected (0.30 sec)
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`flowid` varchar(32) COLLATE gbk_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin
1 row in set (0.01 sec)
mysql> insert t1 values('xxx');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1 ;
+--------+
| flowid |
+--------+
| xxx |
+--------+
1 row in set (0.00 sec)
mysql>
mysql> select concat(abc, flowid) from (select 'yyy-' as abc, flowid from t1) as alias;
+---------------------+
| concat(abc, flowid) |
+---------------------+
| yyy-xxx |
+---------------------+
1 row in set (0.00 sec)
mysql> select concat(abc, flowid) from (select 'yyy-' as abc, flowid from t1 limit 1) as alias;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (gbk_bin,IMPLICIT) for operation 'concat'
mysql>
Suggested fix:
Expected results:
The last SQL does not report an error.
Description: Subqueries with 'limit 1' cause concat strings to report errors How to repeat: Here are the steps to reproduce: mysql> create database test_db; Query OK, 1 row affected (0.01 sec) mysql> use test_db; Database changed mysql> create table t1 ( -> flowid varchar(32) -> ) charset=gbk collate=gbk_bin; Query OK, 0 rows affected (0.30 sec) mysql> show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `flowid` varchar(32) COLLATE gbk_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin 1 row in set (0.01 sec) mysql> insert t1 values('xxx'); Query OK, 1 row affected (0.01 sec) mysql> select * from t1 ; +--------+ | flowid | +--------+ | xxx | +--------+ 1 row in set (0.00 sec) mysql> mysql> select concat(abc, flowid) from (select 'yyy-' as abc, flowid from t1) as alias; +---------------------+ | concat(abc, flowid) | +---------------------+ | yyy-xxx | +---------------------+ 1 row in set (0.00 sec) mysql> select concat(abc, flowid) from (select 'yyy-' as abc, flowid from t1 limit 1) as alias; ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (gbk_bin,IMPLICIT) for operation 'concat' mysql> Suggested fix: Expected results: The last SQL does not report an error.