1drop table if exists t1;
2create table t1 (a varchar(10), key(a));
3insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
4explain extended select * from t1 where a like 'abc%';
5id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
61	SIMPLE	t1	index	a	a	13	NULL	5	20.00	Using where; Using index
7Warnings:
8Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like 'abc%')
9explain extended select * from t1 where a like concat('abc','%');
10id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
111	SIMPLE	t1	index	a	a	13	NULL	5	20.00	Using where; Using index
12Warnings:
13Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like <cache>(concat('abc','%')))
14select * from t1 where a like "abc%";
15a
16abc
17abcd
18select * from t1 where a like concat("abc","%");
19a
20abc
21abcd
22select * from t1 where a like "ABC%";
23a
24abc
25abcd
26select * from t1 where a like "test%";
27a
28test
29select * from t1 where a like "te_t";
30a
31test
32select * from t1 where a like "%a%";
33a
34a
35abc
36abcd
37select * from t1 where a like "%abcd%";
38a
39abcd
40select * from t1 where a like "%abc\d%";
41a
42abcd
43drop table t1;
44create table t1 (a varchar(10), key(a));
45insert into t1 values ('a'), ('a\\b');
46select * from t1 where a like 'a\\%' escape '#';
47a
48a\b
49select * from t1 where a like 'a\\%' escape '#' and a like 'a\\\\b';
50a
51a\b
52prepare stmt1 from 'select * from t1 where a like \'a\\%\' escape ?';
53set @esc='#';
54execute stmt1 using @esc;
55a
56a\b
57deallocate prepare stmt1;
58drop table t1;
59create table t1 (a datetime);
60insert into t1 values ('2004-03-11 12:00:21');
61select * from t1 where a like '2004-03-11 12:00:21';
62a
632004-03-11 12:00:21
64drop table t1;
65SET NAMES koi8r;
66CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET koi8r);
67INSERT INTO t1 VALUES ('����'),('����'),('����'),('����'),('����'),('����');
68INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
69INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
70INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������');
71SELECT * FROM t1 WHERE a LIKE '%����%';
72a
73����
74����
75����
76����
77����
78����
79����������
80����������
81����������
82����������
83����������
84����������
85����������
86����������
87����������
88����������
89����������
90����������
91SELECT * FROM t1 WHERE a LIKE '%���%';
92a
93����
94����
95����
96����
97����
98����
99����������
100����������
101����������
102����������
103����������
104����������
105����������
106����������
107����������
108����������
109����������
110����������
111SELECT * FROM t1 WHERE a LIKE '����%';
112a
113����
114����
115����
116����
117����
118����
119����������
120����������
121����������
122����������
123����������
124����������
125����������
126����������
127����������
128����������
129����������
130����������
131DROP TABLE t1;
132SET NAMES cp1250;
133CREATE TABLE t1 (a varchar(250) NOT NULL) DEFAULT CHARACTER SET=cp1250;
134INSERT INTO t1 VALUES
135('Techni Tapes Sp. z o.o.'),
136('Pojazdy Szynowe PESA Bydgoszcz SA Holding'),
137('AKAPESTER 1 P.P.H.U.'),
138('Pojazdy Szynowe PESA Bydgoszcz S A Holding'),
139('PPUH PESKA-I Maria Struniarska');
140select * from t1 where a like '%PESA%';
141a
142Pojazdy Szynowe PESA Bydgoszcz SA Holding
143Pojazdy Szynowe PESA Bydgoszcz S A Holding
144select * from t1 where a like '%PESA %';
145a
146Pojazdy Szynowe PESA Bydgoszcz SA Holding
147Pojazdy Szynowe PESA Bydgoszcz S A Holding
148select * from t1 where a like '%PES%';
149a
150Techni Tapes Sp. z o.o.
151Pojazdy Szynowe PESA Bydgoszcz SA Holding
152AKAPESTER 1 P.P.H.U.
153Pojazdy Szynowe PESA Bydgoszcz S A Holding
154PPUH PESKA-I Maria Struniarska
155select * from t1 where a like '%PESKA%';
156a
157PPUH PESKA-I Maria Struniarska
158select * from t1 where a like '%ESKA%';
159a
160PPUH PESKA-I Maria Struniarska
161DROP TABLE t1;
162select _cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin;
163_cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin
1641
165set names koi8r;
166select 'andre%' like 'andre�%' escape '�';
167'andre%' like 'andre�%' escape '�'
1681
169select _cp1251'andre%' like convert('andre�%' using cp1251)  escape '�';
170_cp1251'andre%' like convert('andre�%' using cp1251)  escape '�'
1711
172End of 4.1 tests
173#
174# Bug #54575: crash when joining tables with unique set column
175#
176CREATE TABLE t1(a SET('a') NOT NULL, UNIQUE KEY(a));
177CREATE TABLE t2(b INT PRIMARY KEY);
178INSERT INTO t1 VALUES ();
179Warnings:
180Warning	1364	Field 'a' doesn't have a default value
181INSERT INTO t2 VALUES (1), (2), (3);
182SELECT 1 FROM t2 JOIN t1 ON 1 LIKE a GROUP BY a;
1831
184DROP TABLE t1, t2;
185#
186# Bug#59149 valgrind warnings with "like .. escape .." function
187#
188SELECT '' LIKE '1' ESCAPE COUNT(1);
189ERROR HY000: Incorrect arguments to ESCAPE
190End of 5.1 tests
191