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

Illegal mix of collations for datetime

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

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

          bar Alexander Barkov added a comment - 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'));

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

          bar Alexander Barkov added a comment - 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');

          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.

          bar Alexander Barkov added a comment - 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.

          Pushed into 5.5.

          bar Alexander Barkov added a comment - Pushed into 5.5.

          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.