| Bug #77382 | Get unexpected "ERROR 1267" for a normal select query | ||
|---|---|---|---|
| Submitted: | 17 Jun 2015 8:35 | Modified: | 26 Nov 2019 21:28 |
| Reporter: | Su Dylan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.5/5.6/5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Jun 2015 13:20]
MySQL Verification Team
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.26 Source distribution PULL 2015/06/05
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.6 > use test
Database changed
mysql 5.6 > drop table if exists t1; create table t1 (a bigint primary key, b timestamp);
Query OK, 0 rows affected (0.17 sec)
Query OK, 0 rows affected (0.19 sec)
mysql 5.6 > select * from t1 WHERE ('2015-01-01' <> b) AND (b <=> '2015-01-01');
ERROR 1267 (HY000): Illegal mix of collations (cp850_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<>'
mysql 5.6 > select * from t1 WHERE (b <> '2015-01-01') AND (b <=> '2015-01-01');
Empty set (0.00 sec)
mysql 5.6 > exit
Bye
C:\dbs>net start mysqld51
The MySQLD51 service is starting.
The MySQLD51 service was started successfully.
C:\dbs>51
C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.1 > use test
Database changed
mysql 5.1 > drop table if exists t1; create table t1 (a bigint primary key, b timestamp);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
mysql 5.1 > select * from t1 WHERE ('2015-01-01' <> b) AND (b <=> '2015-01-01');
Empty set (0.00 sec)
mysql 5.1 > select * from t1 WHERE (b <> '2015-01-01') AND (b <=> '2015-01-01');
Empty set (0.00 sec)
[18 Jun 2015 13:24]
MySQL Verification Team
Thank you for the bug report.
C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.45-log Source distribution PULL: 2015/06/05
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.5 > use test
Database changed
mysql 5.5 > drop table if exists t1; create table t1 (a bigint primary key, b timestamp);
Query OK, 0 rows affected (0.09 sec)
Query OK, 0 rows affected (0.08 sec)
mysql 5.5 > select * from t1 WHERE ('2015-01-01' <> b) AND (b <=> '2015-01-01');
ERROR 1267 (HY000): Illegal mix of collations (cp850_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<>'
mysql 5.5 > select * from t1 WHERE (b <> '2015-01-01') AND (b <=> '2015-01-01');
Empty set (0.00 sec)
mysql 5.5 > exit
Bye
C:\dbs>net start mysqld57
The MySQLD57 service is starting...
The MySQLD57 service was started successfully.
C:\dbs>57
C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc Source distribution PULL 2015/06/05
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > use test
Database changed
mysql 5.7 > drop table if exists t1; create table t1 (a bigint primary key, b timestamp);
Query OK, 0 rows affected (0.16 sec)
Query OK, 0 rows affected (0.16 sec)
mysql 5.7 > select * from t1 WHERE ('2015-01-01' <> b) AND (b <=> '2015-01-01');
ERROR 1267 (HY000): Illegal mix of collations (cp850_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<>'
mysql 5.7 > select * from t1 WHERE (b <> '2015-01-01') AND (b <=> '2015-01-01');
Empty set (0.00 sec)
[16 Jul 2015 5:54]
Tor Didriksen
Posted by developer:
mtr test case:
set names utf8mb4;
create table t1 (a bigint primary key, b timestamp) engine=innodb;
select * from t1 WHERE ('2015-01-01' <> b) AND (b <=> '2015-01-01');
[5 Sep 2017 15:53]
Alexey Kopytov
Not reproducible with 8.0.2.
[26 Nov 2019 21:28]
Roy Lyseng
Posted by developer: Fixed in 5.7 and 8.0
Description: mysql> drop table if exists t1; create table t1 (a bigint primary key, b timestamp); Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 WHERE ('2015-01-01' <> b) AND (b <=> '2015-01-01'); ERROR 1267 (HY000): Illegal mix of collations (utf8_bin,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<>' mysql> select * from t1 WHERE (b <> '2015-01-01') AND (b <=> '2015-01-01'); Empty set (0.00 sec) mysql> select version(); +------------+ | version() | +------------+ | 5.6.22-log | +------------+ 1 row in set (0.00 sec) How to repeat: mysql> drop table if exists t1; create table t1 (a bigint primary key, b timestamp); Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 WHERE ('2015-01-01' <> b) AND (b <=> '2015-01-01'); ERROR 1267 (HY000): Illegal mix of collations (utf8_bin,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<>' mysql> select * from t1 WHERE (b <> '2015-01-01') AND (b <=> '2015-01-01'); Empty set (0.00 sec) mysql> select version(); +------------+ | version() | +------------+ | 5.6.22-log | +------------+ 1 row in set (0.00 sec) Suggested fix: Expected: The query succeeds.