Description:
mysql> create table sb(c1 timestamp(6) NULL DEFAULT NULL) DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into sb values("2010-01-01 11:11:11");
Query OK, 1 row affected (0.00 sec)
mysql> select * from sb where ( ('2008-01-01' <= c1 ) AND ( c1 <=> "2010-01-01 11:11:11"));
ERROR 1267 (HY000): Illegal mix of collations (utf8_bin,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<='
IMHO, this error is so unfriendly. Why ? Let's dig into this:
mysql> select * from sb where ('2008-01-01' <= c1 );
+----------------------------+
| 2010-01-01 11:11:11.000000 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select * from sb where ( c1 <=> "2010-01-01 11:11:11");
+----------------------------+
| c1 |
+----------------------------+
| 2010-01-01 11:11:11.000000 |
+----------------------------+
1 row in set (0.00 sec)
Yeah, both return a record when the condition is used separately.
In the meantime, we can see that :
mysql> select '2008-01-01' <= c1 and c1 <=> "2010-01-01 11:11:11" from sb;
+-----------------------------------------------------+
| '2008-01-01' <= c1 and c1 <=> "2010-01-01 11:11:11" |
+-----------------------------------------------------+
| 1 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
Yeah, also succeed.
How to repeat:
create table sb(c1 timestamp(6) NULL DEFAULT NULL) DEFAULT CHARSET=utf8mb4;
insert into sb values("2010-01-01 11:11:11");
select * from sb where ( ('2008-01-01' <= c1 ) AND ( c1 <=> "2010-01-01 11:11:11"));
select * from sb where ('2008-01-01' <= c1 );
select * from sb where ( c1 <=> "2010-01-01 11:11:11");
select '2008-01-01' <= c1 and c1 <=> "2010-01-01 11:11:11" from sb;
Suggested fix:
select * from sb where ( ('2008-01-01' <= c1 ) AND ( c1 <=> "2010-01-01 11:11:11"));
succeed and a record is returned
OR
select '2008-01-01' <= c1 and c1 <=> "2010-01-01 11:11:11" from sb; failed with an error
Description: mysql> create table sb(c1 timestamp(6) NULL DEFAULT NULL) DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.01 sec) mysql> insert into sb values("2010-01-01 11:11:11"); Query OK, 1 row affected (0.00 sec) mysql> select * from sb where ( ('2008-01-01' <= c1 ) AND ( c1 <=> "2010-01-01 11:11:11")); ERROR 1267 (HY000): Illegal mix of collations (utf8_bin,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '<=' IMHO, this error is so unfriendly. Why ? Let's dig into this: mysql> select * from sb where ('2008-01-01' <= c1 ); +----------------------------+ | 2010-01-01 11:11:11.000000 | +----------------------------+ 1 row in set (0.00 sec) mysql> select * from sb where ( c1 <=> "2010-01-01 11:11:11"); +----------------------------+ | c1 | +----------------------------+ | 2010-01-01 11:11:11.000000 | +----------------------------+ 1 row in set (0.00 sec) Yeah, both return a record when the condition is used separately. In the meantime, we can see that : mysql> select '2008-01-01' <= c1 and c1 <=> "2010-01-01 11:11:11" from sb; +-----------------------------------------------------+ | '2008-01-01' <= c1 and c1 <=> "2010-01-01 11:11:11" | +-----------------------------------------------------+ | 1 | +-----------------------------------------------------+ 1 row in set (0.00 sec) Yeah, also succeed. How to repeat: create table sb(c1 timestamp(6) NULL DEFAULT NULL) DEFAULT CHARSET=utf8mb4; insert into sb values("2010-01-01 11:11:11"); select * from sb where ( ('2008-01-01' <= c1 ) AND ( c1 <=> "2010-01-01 11:11:11")); select * from sb where ('2008-01-01' <= c1 ); select * from sb where ( c1 <=> "2010-01-01 11:11:11"); select '2008-01-01' <= c1 and c1 <=> "2010-01-01 11:11:11" from sb; Suggested fix: select * from sb where ( ('2008-01-01' <= c1 ) AND ( c1 <=> "2010-01-01 11:11:11")); succeed and a record is returned OR select '2008-01-01' <= c1 and c1 <=> "2010-01-01 11:11:11" from sb; failed with an error