Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.29
-
None
-
RHEL 7.3
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;
|