[MDEV-23246] Illegal mix of collations Created: 2020-07-21  Updated: 2020-07-22

Status: Open
Project: MariaDB Server
Component/s: Character Sets, Variables
Affects Version/s: 10.5
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Todd Michael Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows 10 Pro 64 bit

Issue Links:
Relates
relates to MDEV-23250 User variable loses its character set Open
relates to MDEV-21815 Incorrect character set / collation f... Closed

 Description   

Inconsistent collation treatment for some function for user variables when comparing values.

Receiving error Error Code: 1267. Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' for some operations that previously worked on 10.3.23, but now fails on 10.5 (10.5.1 ... 10.5.4).

Comparisons for functions tested so far that fail with the above error:

SET @x:='dummy';
 
SELECT @x:= 123456789, @x = BIN(@x);
 
SELECT @x:= 123456789, @x = FORMAT(@x, 4);
 
SELECT @x:= 123456789, @x = JSON_QUOTE(@x);
 
SELECT @x:= 123456789, @x = MD5(@x);
 
SELECT @x:= 123456789, @x = SHA1(@x);
 
SELECT @x:= 123456789, @x = SHA2(@x, 512);
 
SELECT @x:= 123456789, @x = FORMAT(@x,'#,###,###.##');

Importantly, this will work if the first line is SET @x:=1;. Also, if @x = is removed, they also work.

I suspect that this has to do with the collation of user variables acting differently in v10.5, and may be related to MDEV-21815 [#https://jira.mariadb.org/browse/MDEV-21815].

 Comments   
Comment by Alexander Barkov [ 2020-07-22 ]

Note, in a statement like this:

SELECT @x:= 123456789, @x = BIN(@x);

the data type for @x (in the right part) is detected by the value of @x as of start of the entire query.
It does take into account that the data type is changed inside the statement by this part:

@x:= 123456789

So for example, in this script:

SET @x:='dummy';
 
SELECT @x:= 123456789, @x = BIN(@x);

The data type of @x in @x=BIN(@x) is considered to be VARCHAR, as of the start of the statement.
The fact that it's changed to INT inside the statement is not taken into account.
So the comparison is performed in string format, between VARCHAR and VARCHAR.

It's also true the other way around:

SET @x:=123;
 
SELECT @x:= '123456789', @x = BIN(@x);

The data type of @x in @x=BIN(@x) is considered as INT, as of the start of the statement.
The fact that it is changed to VARCHAR inside the statement is not taken into account.
So the comparison is done in DOUBLE format (between INT and VARCHAR).

We cannot fix it in the way so it takes into account variable data type change inside the query, like in the assignment to the left of the comparison in this example. The current implementation to use the variable data type as of start of the entire statement is intentional.

We can only try to fix the illegal mix of collations error because the result of BIN() contains only ASCII characters and therefore can be converted to latin1 without a data loss.

But please analyse your script. It might perform the comparison in the way that you don't expect.

Comment by Todd Michael [ 2020-07-22 ]

Thanks, but this does not really answer the problem at hand. These statements used to work in 10.3, and now don't in 10.5, despite your explanation.

Also, if the variable is completely uninitiated in the query, then it also fails in 10.5, but works fine in 10.3:

SELECT @x:= 123456789, @x = FORMAT(@x, 4);

The dummy value was just used in the example to cause it to always fail (ie just in case your session was already using an initialised @x value).

The pattern works for some functions, and not others in no consistent manner in 10.5, but have no problems in 10.3.

Generated at Thu Feb 08 09:20:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.