[MDEV-15337] Collation handling in view changed in 10.1.29 Created: 2018-02-16  Updated: 2018-11-20  Resolved: 2018-11-20

Status: Closed
Project: MariaDB Server
Component/s: Documentation - Support, Server
Affects Version/s: 10.1.29
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Claudio Nanni Assignee: Kenneth Dyer (Inactive)
Resolution: Fixed Votes: 1
Labels: None
Environment:

RHEL 7.3

Attachments: Zip Archive mtr_suite.zip    

 Description   

There's apparently a difference in view collation handling between 10.1.28 and 10.1.29

Test case :

--source include/have_innodb.inc
 
CREATE DATABASE `example` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs */;
 
use example;
 
set names utf8;
 
CREATE TABLE `example` (
 
`username` varchar(20) COLLATE latin1_general_cs NOT NULL,
 
PRIMARY KEY (`username`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
 
 
 
insert into example values ('user_a');
 
 
 
create or replace view example_view as
 
select username
 
from example
 
where substring_index('TEST', '@', 1) <> example.username;
 
 
 
select * from example_view;
 
drop database example;

It passes in 10.1.29 but it broke before 10.1.29 : It has been noticed it on 10.1.20. It also breaks on 5.6.39, 5.7.21 and 8.0.4

mysqltest: At line 16: query 'select * from example_view' failed: 1267: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_cs,IMPLICIT) for operation '<>'

To reproduce, just run the below example or

mysql-test]$ ./mtr --suite view_bug view_bug

The way client collation influences view collation is not well documented either in MariaDB nor MySQL. Is a documentation bug report needed?

Apparently the collation used by the view is the collation of the connection that created the view. For stored procedures, it is the default database collation if not specified.

So this works for any version :

--source include/have_innodb.inc
 
CREATE DATABASE `example` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs */;
 
use example;
 
set collation_connection = latin1_general_cs;
 
CREATE TABLE `example` (
 
`username` varchar(20) COLLATE latin1_general_cs NOT NULL,
 
PRIMARY KEY (`username`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
 
 
 
insert into example values ('user_a');
 
 
 
create or replace view example_view as
 
select username
 
from example
 
where substring_index('TEST', '@', 1) <> example.username;
 
 
 
select * from example_view;
 
drop database example;



 Comments   
Comment by Elena Stepanova [ 2018-02-16 ]

The change was caused by the bugfix for MDEV-9886:

commit ca948e335e0e43538f994484938dd729b32ae286
 
Author: Alexander Barkov <bar@mariadb.org>
 
Date:   Sat Oct 7 13:42:11 2017 +0400
 
 
 
    MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant

Unless there is a suspicion that the new behavior is wrong, the most we can do is redirect it to documentation.

Comment by Arnaud Adant [ 2018-02-19 ]

Yes, the document is not very clear regarding collation and views. Maybe this is just a documentation bug.
The new behavior seems to remove an error that would not occur if the query was run without the view.

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