path: root/mysql-test/main/ctype_collate.test

#remove this include after fix MDEV-27871
--source include/no_view_protocol.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
--enable_warnings

CREATE TABLE t1 (
  latin1_f CHAR(32) CHARACTER SET latin1 NOT NULL
);

--error 1253
CREATE TABLE t2 (
  latin1_f CHAR(32) CHARACTER SET latin1 COLLATE koi8r_general_ci NOT NULL
);

--error 1273
CREATE TABLE t2 (
  latin1_f CHAR(32) CHARACTER SET latin1 COLLATE some_non_existing_col NOT NULL
);


INSERT INTO t1 (latin1_f) VALUES (_latin1'A');
INSERT INTO t1 (latin1_f) VALUES (_latin1'a');

INSERT INTO t1 (latin1_f) VALUES (_latin1'AD');
INSERT INTO t1 (latin1_f) VALUES (_latin1'ad');

INSERT INTO t1 (latin1_f) VALUES (_latin1'AE');
INSERT INTO t1 (latin1_f) VALUES (_latin1'ae');

INSERT INTO t1 (latin1_f) VALUES (_latin1'AF');
INSERT INTO t1 (latin1_f) VALUES (_latin1'af');

INSERT INTO t1 (latin1_f) VALUES (_latin1'Ä');
INSERT INTO t1 (latin1_f) VALUES (_latin1'ä');

INSERT INTO t1 (latin1_f) VALUES (_latin1'Å');
INSERT INTO t1 (latin1_f) VALUES (_latin1'å');

INSERT INTO t1 (latin1_f) VALUES (_latin1'B');
INSERT INTO t1 (latin1_f) VALUES (_latin1'b');

INSERT INTO t1 (latin1_f) VALUES (_latin1'U');
INSERT INTO t1 (latin1_f) VALUES (_latin1'u');

INSERT INTO t1 (latin1_f) VALUES (_latin1'UE');
INSERT INTO t1 (latin1_f) VALUES (_latin1'ue');

INSERT INTO t1 (latin1_f) VALUES (_latin1'Ü');
INSERT INTO t1 (latin1_f) VALUES (_latin1'ü');

INSERT INTO t1 (latin1_f) VALUES (_latin1'SS');
INSERT INTO t1 (latin1_f) VALUES (_latin1'ss');
INSERT INTO t1 (latin1_f) VALUES (_latin1'ß');

INSERT INTO t1 (latin1_f) VALUES (_latin1'Y');
INSERT INTO t1 (latin1_f) VALUES (_latin1'y');

INSERT INTO t1 (latin1_f) VALUES (_latin1'Z');
INSERT INTO t1 (latin1_f) VALUES (_latin1'z');


# ORDER BY

SELECT latin1_f FROM t1 ORDER BY latin1_f;
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_swedish_ci;
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_german2_ci;
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_general_ci;
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_bin;
--error 1253
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE koi8r_general_ci;

# SELECT latin1_f COLLATE koi8r FROM t1 ;

# AS + ORDER BY
SELECT latin1_f COLLATE latin1_swedish_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
SELECT latin1_f COLLATE latin1_german2_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
SELECT latin1_f COLLATE latin1_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
SELECT latin1_f COLLATE latin1_bin        AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
--error 1253
SELECT latin1_f COLLATE koi8r_general_ci  AS latin1_f_as FROM t1 ORDER BY latin1_f_as;


# GROUP BY

SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f;
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_swedish_ci;
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_german2_ci;
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_general_ci;
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_bin;
--error 1253
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE koi8r_general_ci;


# DISTINCT

SELECT DISTINCT latin1_f                           FROM t1;
SELECT DISTINCT latin1_f COLLATE latin1_swedish_ci FROM t1;
SELECT DISTINCT latin1_f COLLATE latin1_german2_ci FROM t1;
SELECT DISTINCT latin1_f COLLATE latin1_general_ci FROM t1;
SELECT DISTINCT latin1_f COLLATE latin1_bin        FROM t1;
--error 1273
SELECT DISTINCT latin1_f COLLATE koi8r             FROM t1;


# Aggregates

--disable_parsing
SELECT MAX(k COLLATE latin1_german2_ci)
FROM t1
WHERE
SELECT *
FROM t1 
WHERE (_latin1'Mu"ller' COLLATE latin1_german2_ci) = k 
HAVING
SELECT *
FROM t1 
HAVING (_latin1'Mu"ller' COLLATE latin1_german2_ci) = k;
--enable_parsing

#
# Check that SHOW displays COLLATE clause
#

SHOW CREATE TABLE t1;
SHOW FIELDS FROM  t1;
ALTER TABLE t1 CHANGE latin1_f 
latin1_f CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin;
SHOW CREATE TABLE t1;
SHOW FIELDS FROM  t1;
ALTER TABLE t1 CHARACTER SET latin1 COLLATE latin1_bin;
SHOW CREATE TABLE t1;
SHOW FIELDS FROM  t1;

#
# Check SET CHARACTER SET
#

SET CHARACTER SET 'latin1';
SHOW VARIABLES LIKE 'character_set_client';
SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';

SET CHARACTER SET koi8r;
SHOW VARIABLES LIKE 'collation_client';
SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';

--error 1115
SET CHARACTER SET 'DEFAULT';

DROP TABLE t1;

CREATE TABLE t1 
(s1 CHAR(5) COLLATE latin1_german1_ci,
 s2 CHAR(5) COLLATE latin1_swedish_ci);
--error 1267
SELECT * FROM t1 WHERE s1 = s2;
DROP TABLE t1;


CREATE TABLE t1
(s1 CHAR(5) COLLATE latin1_german1_ci,
 s2 CHAR(5) COLLATE latin1_swedish_ci,
 s3 CHAR(5) COLLATE latin1_bin);
INSERT INTO t1 VALUES ('a','A','A');
--error 1267
SELECT * FROM t1 WHERE s1 = s2;
SELECT * FROM t1 WHERE s1 = s3;
SELECT * FROM t1 WHERE s2 = s3;
DROP TABLE t1;


#
# Test that optimizer doesn't use indexes with wrong collation
#
#
# BUG#48447, Delivering too few records with indexes using collate syntax
#
create table t1 (a varchar(1) character set latin1 collate latin1_general_ci);
insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c');
select * from t1 where a > 'B' collate latin1_bin;
select * from t1 where a <> 'B' collate latin1_bin;
create index i on t1 (a);
select * from t1 where a > 'B' collate latin1_bin;
select * from t1 where a <> 'B' collate latin1_bin;
drop table t1;

SET NAMES latin1;
CREATE TABLE t1 
(s1 char(10) COLLATE latin1_german1_ci,
 s2 char(10) COLLATE latin1_swedish_ci,
 KEY(s1),
 KEY(s2));

INSERT INTO t1 VALUES ('a','a');
INSERT INTO t1 VALUES ('b','b');
INSERT INTO t1 VALUES ('c','c');
INSERT INTO t1 VALUES ('d','d');
INSERT INTO t1 VALUES ('e','e');
INSERT INTO t1 VALUES ('f','f');
INSERT INTO t1 VALUES ('g','g');
INSERT INTO t1 VALUES ('h','h');
INSERT INTO t1 VALUES ('i','i');
INSERT INTO t1 VALUES ('j','j');

EXPLAIN SELECT * FROM t1 WHERE s1='a';
EXPLAIN SELECT * FROM t1 WHERE s2='a';
EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci;
EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci;

EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;

EXPLAIN SELECT * FROM t1 WHERE s1 IN  ('a','b' COLLATE latin1_german1_ci);
EXPLAIN SELECT * FROM t1 WHERE s2 IN  ('a','b' COLLATE latin1_german1_ci);

EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci;
EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci;

DROP TABLE t1;

# End of 4.1 tests

#
# Bug#29261: Sort order of the collation wasn't used when comparing trailing
#            spaces.
#
create table t1(f1 varchar(10) character set latin2 collate latin2_hungarian_ci, key(f1));
insert into t1 set f1=0x3F3F9DC73F;
insert into t1 set f1=0x3F3F1E563F;
insert into t1 set f1=0x3F3F;
check table t1 extended;
drop table t1;

#
# Bug#29461: Sort order of the collation wasn't used when comparing characters
#            with the space character.
#
create table t1 (a varchar(2) character set latin7 collate latin7_general_ci,key(a));
insert into t1 set a=0x4c20;
insert into t1 set a=0x6c;
insert into t1 set a=0x4c98;
check table t1 extended;
drop table t1;

#
# Bug#41627 Illegal mix of collations in LEAST / GREATEST / CASE
#
select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci);
create table t1
select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) as f1;
show create table t1;
drop table t1;

select case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate
latin5_turkish_ci then 2 else 3 end;

select concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci);


--echo #
--echo # Bug#11765016 57926: ILLEGAL MIX OF COLLATIONS FOR OPERATION 'UNION' .. USING CONCAT/FUNCTION/
--echo # Not a bug: only adding coverage tests
--echo #
SET NAMES latin1 COLLATE latin1_german2_ci;
CREATE DATABASE test1 DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;
USE test1;
DELIMITER //;
--echo #
--echo # Using "COLLATE latin1_swedish_ci" as the default collation for latin1
--echo #
CREATE FUNCTION `getText`() RETURNS varchar(20) CHARSET latin1
BEGIN
  RETURN "Testtext";
END;//
DELIMITER ;//
SELECT getText(), CHARSET(getText()), COLLATION(getText()), COERCIBILITY(getText());
--error ER_CANT_AGGREGATE_NCOLLATIONS
CREATE TABLE t1 AS SELECT ' - ' AS a UNION SELECT getText();
DROP FUNCTION getText;
--echo #
--echo # Using "CHARACTER SET latin1 COLLATE latin1_german2_ci" as the database defaults
--echo #
DELIMITER //;
CREATE FUNCTION `getText`() RETURNS varchar(20)
BEGIN
  RETURN "Testtext";
END;//
DELIMITER ;//
SELECT getText(), CHARSET(getText()), COLLATION(getText()), COERCIBILITY(getText());
CREATE TABLE t1 AS SELECT ' - ' AS a UNION SELECT getText();
SHOW CREATE TABLE t1;
DROP TABLE t1;
DROP FUNCTION getText;
--echo #
--echo # Using explicit "CHARACTER SET latin1 COLLATE latin1_german2_ci"
--echo #
DELIMITER //;
CREATE FUNCTION `getText`() RETURNS varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci
BEGIN
  RETURN "Testtext";
END;//
DELIMITER ;//
SELECT getText(), CHARSET(getText()), COLLATION(getText()), COERCIBILITY(getText());
CREATE TABLE t1 AS SELECT ' - ' AS a UNION SELECT getText();
SHOW CREATE TABLE t1;
DROP TABLE t1;
DROP FUNCTION getText;
DROP DATABASE test1;
USE test;
SET NAMES latin1;

--echo #
--echo # MDEV-11320, MySQL BUG#81810: Inconsistent sort order for blob/text between InnoDB and filesort
--echo #

CREATE TABLE t1 (
  b LONGTEXT CHARACTER SET "latin1" COLLATE "latin1_bin",
  KEY b (b(32))
);
INSERT INTO t1 (b) VALUES ('a'), (_binary 0x1), (_binary 0x0), ('');


drop table t1;

CREATE TABLE t1 (
  b LONGTEXT CHARACTER SET "latin1" COLLATE "latin1_bin",
  PRIMARY KEY b (b(32))
);

INSERT INTO t1 (b) VALUES ('a'), (_binary 0x1), (_binary 0x0), ('');

explain 
select hex(b) from t1 force index (PRIMARY) where b<'zzz';
select hex(b) from t1 force index (PRIMARY) where b<'zzz';

explain
select hex(b) from t1 where b<'zzz' order by b;
select hex(b) from t1 where b<'zzz' order by b;

drop table t1;

--echo #
--echo # Start of 10.2 tests
--echo #

--echo #
--echo # MDEV-27690 Crash on `CHARACTER SET csname COLLATE DEFAULT` in column definition
--echo #

CREATE TABLE t1 (a CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT);
DROP TABLE t1;
SELECT CAST('a' AS CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT);
SELECT COLUMN_GET(COLUMN_CREATE(0, 'string'),0 AS CHAR CHARACTER SET latin1 COLLATE DEFAULT) AS c1;


--echo #
--echo # End of 10.2 tests
--echo #