Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 5.5.33a, 5.5.34
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Environment:CentOS release 6.4 - 5.5.34-MariaDB-log
or
Fedora release 19 - 5.5.33a-MariaDB
Description
To reproduce the problem:
DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`t1_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=cp1251;
INSERT INTO t1 (t1_date) VALUES (NOW());
Set character and collation to cp1251:
CREATE DATABASE `test_cp1251` DEFAULT CHARACTER SET cp1251 ;
Query:
SELECT date(t1_date) FROM t1 WHERE (CASE WHEN 1 THEN date(t1_date) ELSE null END >= '2013-12-01 00:00:00');
return the error:
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,NUMERIC) and (cp1251_general_ci,COERCIBLE) for operation '>='
If I return configuration to UTF8, all work fine:
SET NAMES utf8 COLLATE utf8_unicode_ci;
SELECT date(t1_date) FROM t1 WHERE (CASE WHEN 1 THEN date(t1_date) ELSE null END >= '2013-12-01 00:00:00');
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
This is a script to repeat the problem:
SET NAMES cp1251;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (dt DATETIME);
INSERT INTO t1 VALUES (NOW());
SELECT date(dt) FROM t1 WHERE (CASE WHEN 1 THEN date(dt) ELSE null END >= '2013-12-01 00:00:00');
Pushed into 5.5.
Please use this query as a workaround:
SELECT date(t1_date) FROM t1 WHERE (CASE WHEN 1 THEN date(t1_date) ELSE null END >= TIMESTAMP('2013-12-01 00:00:00'));