diff options
Diffstat (limited to 'mysql-test/main/ctype_latin1.test')
| -rw-r--r-- | mysql-test/main/ctype_latin1.test | 428 |
1 files changed, 428 insertions, 0 deletions
diff --git a/mysql-test/main/ctype_latin1.test b/mysql-test/main/ctype_latin1.test new file mode 100644 index 00000000000..38f147708b8 --- /dev/null +++ b/mysql-test/main/ctype_latin1.test @@ -0,0 +1,428 @@ +# +# Tests with the latin1 character set +# +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# WL 1494: Treat latin1 as cp1252 for unicode conversion +# + +SET NAMES latin1; +CREATE TABLE t1 (a char(1) character set latin1); +INSERT INTO t1 VALUES (0x00),(0x01),(0x02),(0x03),(0x04),(0x05),(0x06),(0x07); +INSERT INTO t1 VALUES (0x08),(0x09),(0x0A),(0x0B),(0x0C),(0x0D),(0x0E),(0x0F); +INSERT INTO t1 VALUES (0x10),(0x11),(0x12),(0x13),(0x14),(0x15),(0x16),(0x17); +INSERT INTO t1 VALUES (0x18),(0x19),(0x1A),(0x1B),(0x1C),(0x1D),(0x1E),(0x1F); +INSERT INTO t1 VALUES (0x20),(0x21),(0x22),(0x23),(0x24),(0x25),(0x26),(0x27); +INSERT INTO t1 VALUES (0x28),(0x29),(0x2A),(0x2B),(0x2C),(0x2D),(0x2E),(0x2F); +INSERT INTO t1 VALUES (0x30),(0x31),(0x32),(0x33),(0x34),(0x35),(0x36),(0x37); +INSERT INTO t1 VALUES (0x38),(0x39),(0x3A),(0x3B),(0x3C),(0x3D),(0x3E),(0x3F); +INSERT INTO t1 VALUES (0x40),(0x41),(0x42),(0x43),(0x44),(0x45),(0x46),(0x47); +INSERT INTO t1 VALUES (0x48),(0x49),(0x4A),(0x4B),(0x4C),(0x4D),(0x4E),(0x4F); +INSERT INTO t1 VALUES (0x50),(0x51),(0x52),(0x53),(0x54),(0x55),(0x56),(0x57); +INSERT INTO t1 VALUES (0x58),(0x59),(0x5A),(0x5B),(0x5C),(0x5D),(0x5E),(0x5F); +INSERT INTO t1 VALUES (0x60),(0x61),(0x62),(0x63),(0x64),(0x65),(0x66),(0x67); +INSERT INTO t1 VALUES (0x68),(0x69),(0x6A),(0x6B),(0x6C),(0x6D),(0x6E),(0x6F); +INSERT INTO t1 VALUES (0x70),(0x71),(0x72),(0x73),(0x74),(0x75),(0x76),(0x77); +INSERT INTO t1 VALUES (0x78),(0x79),(0x7A),(0x7B),(0x7C),(0x7D),(0x7E),(0x7F); +INSERT INTO t1 VALUES (0x80),(0x81),(0x82),(0x83),(0x84),(0x85),(0x86),(0x87); +INSERT INTO t1 VALUES (0x88),(0x89),(0x8A),(0x8B),(0x8C),(0x8D),(0x8E),(0x8F); +INSERT INTO t1 VALUES (0x90),(0x91),(0x92),(0x93),(0x94),(0x95),(0x96),(0x97); +INSERT INTO t1 VALUES (0x98),(0x99),(0x9A),(0x9B),(0x9C),(0x9D),(0x9E),(0x9F); +INSERT INTO t1 VALUES (0xA0),(0xA1),(0xA2),(0xA3),(0xA4),(0xA5),(0xA6),(0xA7); +INSERT INTO t1 VALUES (0xA8),(0xA9),(0xAA),(0xAB),(0xAC),(0xAD),(0xAE),(0xAF); +INSERT INTO t1 VALUES (0xB0),(0xB1),(0xB2),(0xB3),(0xB4),(0xB5),(0xB6),(0xB7); +INSERT INTO t1 VALUES (0xB8),(0xB9),(0xBA),(0xBB),(0xBC),(0xBD),(0xBE),(0xBF); +INSERT INTO t1 VALUES (0xC0),(0xC1),(0xC2),(0xC3),(0xC4),(0xC5),(0xC6),(0xC7); +INSERT INTO t1 VALUES (0xC8),(0xC9),(0xCA),(0xCB),(0xCC),(0xCD),(0xCE),(0xCF); +INSERT INTO t1 VALUES (0xD0),(0xD1),(0xD2),(0xD3),(0xD4),(0xD5),(0xD6),(0xD7); +INSERT INTO t1 VALUES (0xD8),(0xD9),(0xDA),(0xDB),(0xDC),(0xDD),(0xDE),(0xDF); +INSERT INTO t1 VALUES (0xE0),(0xE1),(0xE2),(0xE3),(0xE4),(0xE5),(0xE6),(0xE7); +INSERT INTO t1 VALUES (0xE8),(0xE9),(0xEA),(0xEB),(0xEC),(0xED),(0xEE),(0xEF); +INSERT INTO t1 VALUES (0xF0),(0xF1),(0xF2),(0xF3),(0xF4),(0xF5),(0xF6),(0xF7); +INSERT INTO t1 VALUES (0xF8),(0xF9),(0xFA),(0xFB),(0xFC),(0xFD),(0xFE),(0xFF); + +# +# 0x81 0x8D 0x8F 0x90 0x9D are undefined in cp1252 +# +SELECT + hex(a), + hex(@u:=convert(a using utf8)), + hex(@l:=convert(@u using latin1)), + a=@l FROM t1; +DROP TABLE t1; + +# +# Bug#13145: A table named "C-cedilla" can't be dropped. +# Accept extended cp1252 letters as valid identifiers. +# This test partially checks that "ctype" array is correct +# for cp1252 extended characters 0x80-0x9F. +# +# 0x83 0x0192 #LATIN SMALL LETTER F WITH HOOK +# 0x8A 0x0160 #LATIN CAPITAL LETTER S WITH CARON +# 0x8C 0x0152 #LATIN CAPITAL LIGATURE OE +# 0x8E 0x017D #LATIN CAPITAL LETTER Z WITH CARON +# 0x9A 0x0161 #LATIN SMALL LETTER S WITH CARON +# 0x9C 0x0153 #LATIN SMALL LIGATURE OE +# 0x9E 0x017E #LATIN SMALL LETTER Z WITH CARON +# 0x9F 0x0178 #LATIN CAPITAL LETTER Y WITH DIAERESIS +# +SELECT 1 as ƒ, 2 as Š, 3 as Œ, 4 as Ž, 5 as š, 6 as œ, 7 as ž, 8 as Ÿ; + +# +# Bug #6737: REGEXP gives wrong result with case sensitive collation +# +select 'a' regexp 'A' collate latin1_general_ci; +select 'a' regexp 'A' collate latin1_general_cs; +select 'a' regexp 'A' collate latin1_bin; + + +SET @test_character_set= 'latin1'; +SET @test_collation= 'latin1_swedish_ci'; +-- source include/ctype_common.inc + +SET collation_connection='latin1_swedish_ci'; +-- source include/ctype_filesort.inc +-- source include/ctype_like_escape.inc +-- source include/ctype_str_to_date.inc +-- source include/ctype_pad_space.inc +SET collation_connection='latin1_bin'; +-- source include/ctype_filesort.inc +-- source include/ctype_like_escape.inc +-- source include/ctype_pad_space.inc + +# +# Bug#8041 +# An unknown character (e.g. 0x84) should result in ERROR, +# It was treated like a space character earlier. +# Howerver, it should still work fine as a string part. +--error 1064 +CREATE TABLE „a (a int); +SELECT '„a' as str; + + +# +# Bug#18321: Can't store EuroSign with latin1_german1_ci and latin1_general_ci +# The problem was in latin1->utf8->latin1 round trip. +# +set @str= _latin1 'ABC €°§ß²³µ~ äöüÄÖÜ áéíóú ÀÈÌÒÙ @ abc'; +SELECT convert(@str collate latin1_bin using utf8); +SELECT convert(@str collate latin1_general_ci using utf8); +SELECT convert(@str collate latin1_german1_ci using utf8); +SELECT convert(@str collate latin1_danish_ci using utf8); +SELECT convert(@str collate latin1_spanish_ci using utf8); +SELECT convert(@str collate latin1_german2_ci using utf8); +SELECT convert(@str collate latin1_swedish_ci using utf8); + +# End of 4.1 tests + +SET NAMES latin1; +--disable_warnings +DROP TABLE IF EXISTS `abcÿdef`; +--enable_warnings +CREATE TABLE `abcÿdef` (i int); +INSERT INTO `abcÿdef` VALUES (1); +INSERT INTO abcÿdef VALUES (2); +SELECT * FROM `abcÿdef`; +SELECT * FROM abcÿdef; +DROP TABLE `abcÿdef`; +# +# Bug#29499 Converting 'del' from ascii to Unicode results in 'question mark' +# +select hex(cast(_ascii 0x7f as char(1) character set latin1)); + +--echo End of 5.0 tests + + +--echo # +--echo # Bug#58022 ... like ... escape export_set ( ... ) crashes when export_set returns warnings +--echo # +SELECT '' LIKE '' ESCAPE EXPORT_SET(1, 1, 1, 1, ''); + +--echo End of 5.1 tests + + +--echo # +--echo # Start of 5.5 tests +--echo # + +--source include/ctype_numconv.inc + +--echo # +--echo # Bug#11764503 (Bug#57341) Query in EXPLAIN EXTENDED shows wrong characters +--echo # +# Test latin1 client erroneously started with --default-character-set=utf8 +# EXPLAIN output should still be pretty readable. +# We're using 'ó' (\xC3\xB3) as a magic sequence: +# - it's "LATIN CAPITAL LETTER A WITH TILDE ABOVE + SUPERSCRIPT 3" in latin1 +# - it's "LATIN SMALL LETTER O WITH ACUTE ABOVE" in utf8. +SET NAMES utf8; +EXPLAIN EXTENDED SELECT 'abcdó', _latin1'abcdó', _utf8'abcdó'; +# Test normal latin1 +SET NAMES latin1; +EXPLAIN EXTENDED SELECT 'abcdó', _latin1'abcdó', _utf8'abcdó'; + + +--echo # +--echo # End of 5.5 tests +--echo # + + + +--echo # +--echo # Start of 5.6 tests +--echo # + +--echo # +--echo # WL#3664 WEIGHT_STRING +--echo # + +set @@collation_connection=latin1_swedish_ci; +--source include/weight_string.inc +--source include/weight_string_euro.inc +--source include/weight_string_l1.inc + +set @@collation_connection=latin1_bin; +--source include/weight_string.inc +--source include/weight_string_euro.inc +--source include/weight_string_l1.inc + +set @@collation_connection=latin1_general_cs; +--source include/weight_string.inc +--source include/weight_string_euro.inc +--source include/weight_string_l1.inc + +set @@collation_connection=binary; +--source include/weight_string.inc +--source include/weight_string_euro.inc +--source include/weight_string_l1.inc + +set names latin1; + +--echo # +--echo # End of 5.6 tests +--echo # + +--echo # +--echo # Start of 10.0 tests +--echo # + +let $ctype_unescape_combinations=selected; +--source include/ctype_unescape.inc + +SET NAMES latin1; +--source include/ctype_like_cond_propagation.inc + +SET NAMES latin1 COLLATE latin1_bin; +--source include/ctype_like_cond_propagation.inc + + +--echo # +--echo # MDEV-6752 Trailing incomplete characters are not replaced to question marks on conversion +--echo # +SET NAMES utf8, character_set_connection=latin1; +SELECT 'Â'; +SELECT HEX('Â'); +SELECT HEX(CAST('Â' AS CHAR CHARACTER SET utf8)); +SELECT HEX(CAST('Â' AS CHAR CHARACTER SET latin1)); +SELECT HEX(CONVERT('Â' USING utf8)); +SELECT HEX(CONVERT('Â' USING latin1)); +SELECT 'Âx'; +SELECT HEX('Âx'); +SELECT HEX(CAST('Âx' AS CHAR CHARACTER SET utf8)); +SELECT HEX(CAST('Âx' AS CHAR CHARACTER SET latin1)); +SELECT HEX(CONVERT('Âx' USING utf8)); +SELECT HEX(CONVERT('Âx' USING latin1)); +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT IGNORE INTO t1 VALUES ('Â'),('Â#'); +SHOW WARNINGS; +SELECT HEX(a),a FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-7629 Regression: Bit and hex string literals changed column names in 10.0.14 +--echo # +SELECT _latin1 0x7E, _latin1 X'7E', _latin1 B'01111110'; + + +--echo # +--echo # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant +--echo # + +SET NAMES latin1; +CREATE TABLE t1 (a TEXT CHARACTER SET latin1); +INSERT INTO t1 VALUES (0xC0); +SELECT a<>0xEE5D AS a FROM t1; +CREATE VIEW v1 AS SELECT a<>0xC0 AS a FROM t1; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +DROP VIEW v1; +DROP TABLE t1; + + +--echo # +--echo # End of 10.0 tests +--echo # + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-8661 Wrong result for SELECT..WHERE a='a' AND a='a' COLLATE latin1_bin +--echo # +SET NAMES latin1; +CREATE TABLE t1 (a CHAR(10)); +INSERT INTO t1 VALUES ('a'),('A'); +SELECT * FROM t1 WHERE a='a' AND a='a' COLLATE latin1_bin; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci); +INSERT INTO t1 VALUES ('a'),('A'); +SELECT * FROM t1 WHERE a='a' COLLATE latin1_bin AND a='A' COLLATE latin1_swedish_ci; +DROP TABLE t1; + +--echo # +--echo # MDEV-8679 Equal field propagation is not used for VARCHAR when it safely could +--echo # +CREATE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('10'),('11'),('12'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='10' AND IF(a='10',1,0)=1; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='10' AND CASE WHEN a='10' THEN 1 ELSE 0 END; +DROP TABLE t1; + +--echo # +--echo # MDEV-8680 Wrong result for SELECT..WHERE a IN ('a' COLLATE latin1_bin,'b') AND a='a' +--echo # +SET NAMES latin1; +CREATE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('a'),('A'); +SELECT * FROM t1 WHERE a IN ('a' COLLATE latin1_bin,'b'); +SELECT * FROM t1 WHERE a IN ('a' COLLATE latin1_bin,'b') AND a='a'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN ('a' COLLATE latin1_bin,'b') AND a='a'; +DROP TABLE t1; + +--echo # +--echo # MDEV-8698 Wrong result for SELECT..WHERE a BETWEEN 'a' AND 'c' COLLATE latin1_bin; +--echo # +SET NAMES latin1; +CREATE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('a'),('A'),('b'),('B'),('c'),('C'); +SELECT * FROM t1 WHERE a BETWEEN 'a' AND 'c' COLLATE latin1_bin; +SELECT * FROM t1 WHERE a BETWEEN 'a' AND 'c' COLLATE latin1_bin AND a='a'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a BETWEEN 'a' AND 'c' COLLATE latin1_bin AND a='a'; +DROP TABLE t1; + +--echo # +--echo # MDEV-8707 Wrong result for SELECT..WHERE varchar_column=DATE'2001-01-01' AND varchar_column='2001-01-01' +--echo # +SET NAMES latin1; +CREATE TABLE t1 (a VARCHAR(40)); +INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-01x'); +SELECT * FROM t1 WHERE a=DATE'2001-01-01' AND a='2001-01-01'; +SELECT * FROM t1 WHERE a='2001-01-01' AND a=DATE'2001-01-01'; +SELECT * FROM t1 WHERE (a,a)=('2001-01-01x',DATE'2001-01-01'); +SELECT * FROM t1 WHERE (a,a)=(DATE'2001-01-01','2001-01-01x'); +SELECT * FROM t1 WHERE (a,a)=('2001-01-01',DATE'2001-01-01'); +SELECT * FROM t1 WHERE (a,a)=(DATE'2001-01-01','2001-01-01'); +DROP TABLE t1; +CREATE TABLE t1 (a ENUM('2001-01-01','2001-01-01x')); +INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-01x'); +SELECT * FROM t1 WHERE a=DATE'2001-01-01' AND a='2001-01-01'; +SELECT * FROM t1 WHERE a='2001-01-01' AND a=DATE'2001-01-01'; +SELECT * FROM t1 WHERE (a,a)=('2001-01-01x',DATE'2001-01-01'); +SELECT * FROM t1 WHERE (a,a)=(DATE'2001-01-01','2001-01-01x'); +SELECT * FROM t1 WHERE (a,a)=('2001-01-01',DATE'2001-01-01'); +SELECT * FROM t1 WHERE (a,a)=(DATE'2001-01-01','2001-01-01'); +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(40),b VARCHAR(40)); +INSERT INTO t1 VALUES ('2001-01-01','2001-01-01x'); +SELECT * FROM t1 WHERE a=b AND a=DATE'2001-01-01'; +DROP TABLE t1; +CREATE TABLE t1 (a ENUM('2001-01-01','2001-01-01x'),b ENUM('2001-01-01','2001-01-01x')); +INSERT INTO t1 VALUES ('2001-01-01','2001-01-01x'); +SELECT * FROM t1 WHERE a=b AND a=DATE'2001-01-01'; +DROP TABLE t1; + +--echo # +--echo # MDEV-8695 Wrong result for SELECT..WHERE varchar_column='a' AND CRC32(varchar_column)=3904355907 +--echo # +CREATE TABLE t1 (a VARCHAR(10) COLLATE latin1_bin); +INSERT INTO t1 VALUES ('a'),('a '); +SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE CRC32(a)=3904355907; +SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE a='a' AND CRC32(a)=3904355907; +--echo # 'a' should not be propagated into CRC32(a) +EXPLAIN EXTENDED SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE a='a' AND CRC32(a)=3904355907; +SELECT a, HEX(a) FROM t1 WHERE HEX(a)='61'; +SELECT *,HEX(a) FROM t1 WHERE a='a' AND HEX(a)='61'; +--echo # 'a' should not be propagated into HEX(a) +EXPLAIN EXTENDED SELECT *,HEX(a) FROM t1 WHERE a='a' AND HEX(a)='61'; +SELECT * FROM t1 WHERE a='a'; +SELECT * FROM t1 WHERE LENGTH(a)=2; +SELECT * FROM t1 WHERE a='a' AND LENGTH(a)=2; +--echo # 'a' should not be propagated into LENGTH(a) +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND LENGTH(a)=2; +DROP TABLE t1; + +--echo # +--echo # MDEV-8712 Wrong result for SELECT..WHERE latin1_bin_column=_latin1'a' AND latin1_bin_column='A' +--echo # +SET NAMES latin1; +CREATE TABLE t1 (a VARCHAR(20) COLLATE latin1_bin); +INSERT INTO t1 VALUES ('a'),('b'); +SELECT * FROM t1 WHERE a='A'; +SELECT * FROM t1 WHERE a='A' AND a=_latin1'a'; +SELECT * FROM t1 WHERE a=_latin1'a' AND a='A'; +SELECT * FROM t1 WHERE a=_latin1'A'; +SELECT * FROM t1 WHERE a=_latin1'A' AND a=_latin1'a'; +SELECT * FROM t1 WHERE a=_latin1'a' AND a=_latin1'A'; +DROP TABLE t1; + +--echo # +--echo # MDEV-8253 EXPLAIN SELECT prints unexpected characters +--echo # +SET NAMES latin1; +CREATE TABLE t1 (a DECIMAL(10,1),b DECIMAL(10,1),c VARCHAR(10),d VARCHAR(10)); +INSERT INTO t1 VALUES (1.5,1.5,'1','1'),(3.5,3.5,'3','3'); +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE COALESCE(c,0)='3 ' AND COALESCE(d,0)=COALESCE(c,0); +DROP TABLE t1; + +--echo # +--echo # MDEV-15005 ASAN: stack-buffer-overflow in my_strnncollsp_simple +--echo # + +SET NAMES latin1; +SELECT CONVERT(1, CHAR) IN ('100', 10, '101'); +SELECT CONVERT(1, CHAR) IN ('100', 10, '1'); +SELECT CONVERT(1, CHAR) IN ('100', '10', '1'); + +--echo # +--echo # End of 10.1 tests +--echo # + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-9711 NO PAD Collatons +--echo # +SET character_set_connection=latin1; +let $coll='latin1_swedish_nopad_ci'; +let $coll_pad='latin1_swedish_ci'; +--source include/ctype_pad_all_engines.inc + +let $coll='latin1_nopad_bin'; +let $coll_pad='latin1_bin'; +--source include/ctype_pad_all_engines.inc + +SET NAMES latin1; +--source include/ctype_like_range_mdev14350.inc + +--echo # +--echo # End of 10.2 tests +--echo # |