Hi Hartmut, On 01/10/2015 04:08 AM, Hartmut Holzgraefe wrote:

Any idea how that happened, as I can't find anything that seems to be related in the 5.5.38 changelog?

http://bugs.mysql.com/bug.php?id=68557

From a glance, the patch for "MDEV-5459 Illegal mix of collations for datetime' fixed this problem as well (see the patch below). Before this patch it erroneously added a character set converter and compared as strings: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (d DATE); INSERT INTO t1 VALUES('2013-02-28'); SET NAMES sjis; EXPLAIN EXTENDED SELECT * FROM t1 WHERE d = '2013/02/28'; SHOW WARNINGS; +-------+------+-----------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`d` AS `d` from `test`.`t1` where (convert(`test`.`t1`.`d` using sjis) = '2013/02/28') | +-------+------+-----------------------------------------------------------------------------------------------------------+ After this patch it does not add the converter and compares as dates.

=== modified file 'mysql-test/r/ctype_cp1251.result' --- mysql-test/r/ctype_cp1251.result 2014-04-28 11:56:31 +0000 +++ mysql-test/r/ctype_cp1251.result 2014-04-28 13:01:58 +0000 @@ -3282,5 +3282,15 @@ SELECT COALESCE(IF(test1=1, NULL, 1), te COALESCE(IF(test1=1, NULL, 1), test2) DROP TABLE t1; # +# MDEV-5459 Illegal mix of collations for datetime +# +SET NAMES cp1251; +CREATE TABLE t1 (dt DATETIME); +INSERT INTO t1 VALUES ('2014-01-02 10:20:30'); +SELECT date(dt) FROM t1 WHERE (CASE WHEN 1 THEN date(dt) ELSE null END >= '2013-12-01 00:00:00'); +date(dt) +2014-01-02 +DROP TABLE t1; +# # End of 5.5 tests #

=== modified file 'mysql-test/t/ctype_cp1251.test' --- mysql-test/t/ctype_cp1251.test 2011-10-19 19:45:18 +0000 +++ mysql-test/t/ctype_cp1251.test 2014-04-28 13:01:58 +0000 @@ -95,6 +95,14 @@ SELECT COALESCE(IF(test1=1, 1, NULL), te SELECT COALESCE(IF(test1=1, NULL, 1), test2) FROM t1; DROP TABLE t1;

+--echo # +--echo # MDEV-5459 Illegal mix of collations for datetime +--echo # +SET NAMES cp1251; +CREATE TABLE t1 (dt DATETIME); +INSERT INTO t1 VALUES ('2014-01-02 10:20:30'); +SELECT date(dt) FROM t1 WHERE (CASE WHEN 1 THEN date(dt) ELSE null END >= '2013-12-01 00:00:00'); +DROP TABLE t1;

--echo # --echo # End of 5.5 tests

=== modified file 'sql/item_cmpfunc.cc' --- sql/item_cmpfunc.cc 2014-04-28 11:56:31 +0000 +++ sql/item_cmpfunc.cc 2014-04-28 13:01:58 +0000 @@ -549,8 +549,8 @@ void Item_bool_func2::fix_length_and_dec */

DTCollation coll; - if (args[0]->result_type() == STRING_RESULT && - args[1]->result_type() == STRING_RESULT && + if (args[0]->cmp_type() == STRING_RESULT && + args[1]->cmp_type() == STRING_RESULT && agg_arg_charsets_for_comparison(coll, args, 2)) return;