Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5459

Illegal mix of collations for datetime

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • 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');

        Attachments

          Activity

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              ksh770 Ekaterina Shemaeva
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: