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