1#
2# Start of 5.5 tests
3#
4#
5# MDEV-7649 wrong result when comparing utf8 column with an invalid literal
6#
7SET NAMES utf8 COLLATE utf8_unicode_ci;
8#
9# Start of ctype_utf8_ilseq.inc
10#
11CREATE TABLE t1 ENGINE=InnoDB AS SELECT REPEAT(' ', 60) AS ch LIMIT 0;;
12ALTER TABLE t1
13ADD id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
14ADD KEY(ch);
15SHOW CREATE TABLE t1;
16Table	Create Table
17t1	CREATE TABLE `t1` (
18  `ch` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
19  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
20  PRIMARY KEY (`id`),
21  KEY `ch` (`ch`)
22) ENGINE=InnoDB DEFAULT CHARSET=latin1
23INSERT INTO t1 (ch) VALUES ('admin'),('admin1');
24SELECT ch FROM t1 WHERE ch='admin��';
25ch
26EXPLAIN SELECT ch FROM t1 WHERE ch='admin��';
27id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
281	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
29SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin��';
30ch
31SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin��';
32ch
33DELETE FROM t1;
34INSERT INTO t1 (ch) VALUES ('a'), ('a?'), ('a??'), ('a???'), ('a????');
35INSERT INTO t1 (ch) VALUES ('ab'),('a?b'),('a??b'),('a???b'),('a????b');
36INSERT INTO t1 (ch) VALUES ('az'),('a?z'),('a??z'),('a???z'),('a????z');
37INSERT INTO t1 (ch) VALUES ('z');
38INSERT INTO t1 (ch) VALUES (_utf8 0x61D080);
39INSERT INTO t1 (ch) VALUES (_utf8 0x61D680);
40SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a��' ORDER BY ch;
41ch
42SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a��b' ORDER BY ch;
43ch
44EXPLAIN
45SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a��' ORDER BY ch;
46id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
471	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	#	Impossible WHERE noticed after reading const tables
48SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a��' ORDER BY ch;
49ch
50EXPLAIN
51SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a��b' ORDER BY ch;
52id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
531	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	#	Impossible WHERE noticed after reading const tables
54SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a��b' ORDER BY ch;
55ch
56SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a��' ORDER BY ch;
57ch
58a
59a?
60a??
61a???
62a????
63a????b
64a????z
65a???b
66a???z
67a??b
68a??z
69a?b
70a?z
71ab
72az
73aЀ
74aր
75SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a��b' ORDER BY ch;
76ch
77a
78a?
79a??
80a???
81a????
82a????b
83a????z
84a???b
85a???z
86a??b
87a??z
88a?b
89a?z
90ab
91az
92aЀ
93aր
94EXPLAIN
95SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a��' ORDER BY ch;
96id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
971	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
98SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a��' ORDER BY ch;
99ch
100a
101a?
102a??
103a???
104a????
105a????b
106a????z
107a???b
108a???z
109a??b
110a??z
111a?b
112a?z
113ab
114az
115aЀ
116aր
117EXPLAIN
118SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a��b' ORDER BY ch;
119id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1201	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
121SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a��b' ORDER BY ch;
122ch
123a
124a?
125a??
126a???
127a????
128a????b
129a????z
130a???b
131a???z
132a??b
133a??z
134a?b
135a?z
136ab
137az
138aЀ
139aր
140SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a��' ORDER BY ch;
141ch
142z
143SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a��b' ORDER BY ch;
144ch
145z
146EXPLAIN
147SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a��' ORDER BY ch;
148id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1491	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
150SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a��' ORDER BY ch;
151ch
152z
153EXPLAIN
154SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a��b' ORDER BY ch;
155id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1561	SIMPLE	t1	index	ch	ch	183	NULL	#	Using where; Using index
157SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a��b' ORDER BY ch;
158ch
159z
160ALTER TABLE t1 DROP KEY ch;
161# 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence
162SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'''');
163PREPARE stmt FROM @query;
164EXECUTE stmt;
165ch
166SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b''');
167PREPARE stmt FROM @query;
168EXECUTE stmt;
169ch
170SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch');
171PREPARE stmt FROM @query;
172EXECUTE stmt;
173ch
174a
175a?
176a??
177a???
178a????
179a????b
180a????z
181a???b
182a???z
183a??b
184a??z
185a?b
186a?z
187ab
188az
189aЀ
190aր
191SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch');
192PREPARE stmt FROM @query;
193EXECUTE stmt;
194ch
195z
196# 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence
197SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch');
198PREPARE stmt FROM @query;
199EXECUTE stmt;
200ch
201SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch');
202PREPARE stmt FROM @query;
203EXECUTE stmt;
204ch
205# 0x8F is a bad byte sequence (an mb2tail without mb2head)
206SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch');
207PREPARE stmt FROM @query;
208EXECUTE stmt;
209ch
210SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch');
211PREPARE stmt FROM @query;
212EXECUTE stmt;
213ch
214# 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times)
215SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch');
216PREPARE stmt FROM @query;
217EXECUTE stmt;
218ch
219SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch');
220PREPARE stmt FROM @query;
221EXECUTE stmt;
222ch
223DROP TABLE t1;
224#
225# End of ctype_utf8_ilseq.inc
226#
227#
228# End of 5.5 tests
229#
230