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

Illegal mix of collations for datetime

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.5.33a, 5.5.34
    • None
    • None
    • None
    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.