[MDEV-5459] Illegal mix of collations for datetime Created: 2013-12-17  Updated: 2014-04-28  Resolved: 2014-04-28

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.33a, 5.5.34
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Ekaterina Shemaeva Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
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');



 Comments   
Comment by Alexander Barkov [ 2013-12-17 ]

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'));

Comment by Alexander Barkov [ 2013-12-17 ]

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');

Comment by Alexander Barkov [ 2013-12-17 ]

The problem is not repeatable in 5.3.
The problem is not repeatable in the current bzr sources for 10.0.7
due to "MDEV-5298 Illegal mix of collations on timestamp" fixed earlier.

The patch for MDEV-5298 should be partially backported to 5.5.

Comment by Alexander Barkov [ 2014-04-28 ]

Pushed into 5.5.

Generated at Thu Feb 08 07:04:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.