[MDEV-13349] ERROR 1267, Illegal mix of collations by date functions Created: 2017-07-19  Updated: 2018-01-10  Resolved: 2018-01-10

Status: Closed
Project: MariaDB Server
Component/s: Character Sets
Affects Version/s: 5.5.54, 5.5.55, 5.5.56, 5.5.57, 5.5.58
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sven Herschke Assignee: Alexander Barkov
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

FreeBSD 10.3-STABLE

Sprint: 5.5.59

 Description    

select greatest(20170718 + interval 1 day, curDate())

fail with

Illegal mix of collations (latin1_german1_ci,NUMERIC) and (latin1_swedish_ci,NUMERIC) for operation 'greatest'

Without date function (date_add or curDate) operation greatest works.

Only MariaDB-Server and not MySQl-Server seems to be affected.
MariaDB-Version prior 5.5.54 are not affected..

additional information:

show variables like 'collation%';
 
+----------------------+-------------------+
 
| Variable_name        | Value             |
 
+----------------------+-------------------+
 
| collation_connection | latin1_german1_ci |
 
| collation_database   | latin1_german1_ci |
 
| collation_server     | latin1_german1_ci |
 
+----------------------+-------------------+
 
 
 
show variables like 'character%';
 
+--------------------------+----------------------------------+
 
| Variable_name            | Value                            |
 
+--------------------------+----------------------------------+
 
| character_set_client     | latin1                           |
 
| character_set_connection | latin1                           |
 
| character_set_database   | latin1                           |
 
| character_set_filesystem | binary                           |
 
| character_set_results    | latin1                           |
 
| character_set_server     | latin1                           |
 
| character_set_system     | utf8                             |                                                                                                                                                                              
| character_sets_dir       | /usr/local/share/mysql/charsets/ |                                                                                                                                                                              
+--------------------------+----------------------------------+



 Comments   
Comment by Alice Sherepa [ 2017-07-19 ]

An error appears after this commit 2f6fede8d5f7e98319b4b7b557bd565fdb42fac3
no error in 10.0, 10.1, 10.2

MariaDB [(none)]> show variables like 'collation_connection';
 
+----------------------+-----------------+
 
| Variable_name        | Value           |
 
+----------------------+-----------------+
 
| collation_connection | utf8_general_ci |
 
+----------------------+-----------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> select greatest(20170718 + interval 1 day, curDate());
 
+------------------------------------------------+
 
| greatest(20170718 + interval 1 day, curDate()) |
 
+------------------------------------------------+
 
| 2017-07-19                                     |
 
+------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> set collation_connection = latin1_german1_ci ;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> select greatest(20170718 + interval 1 day, curDate());
 
ERROR 1267 (HY000): Illegal mix of collations (latin1_german1_ci,NUMERIC) and (latin1_swedish_ci,NUMERIC) for operation 'greatest'
 
 
 
MariaDB [(none)]> select version();
 
+----------------------+
 
| version()            |
 
+----------------------+
 
| 5.5.57-MariaDB-debug |
 
+----------------------+
 
1 row in set (0.00 sec)

Comment by Alexander Barkov [ 2018-01-10 ]

This problem is fixed in 10.0. Please consider upgrading.
We won't fix this in 5.5.

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