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 partitions type possible_keys key key_len ref rows filtered Extra 61 SIMPLE t1 NULL range a a 13 NULL 1 100.00 Using where; Using index 7Warnings: 8Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 9Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like 'abc%') 10explain extended select * from t1 where a like concat('abc','%'); 11id select_type table partitions type possible_keys key key_len ref rows filtered Extra 121 SIMPLE t1 NULL range a a 13 NULL 1 100.00 Using where; Using index 13Warnings: 14Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 15Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like <cache>(concat('abc','%'))) 16select * from t1 where a like "abc%"; 17a 18abc 19abcd 20select * from t1 where a like concat("abc","%"); 21a 22abc 23abcd 24select * from t1 where a like "ABC%"; 25a 26abc 27abcd 28select * from t1 where a like "test%"; 29a 30test 31select * from t1 where a like "te_t"; 32a 33test 34select * from t1 where a like "%a%"; 35a 36a 37abc 38abcd 39select * from t1 where a like "%abcd%"; 40a 41abcd 42select * from t1 where a like "%abc\d%"; 43a 44abcd 45drop table t1; 46create table t1 (a varchar(10), key(a)); 47insert into t1 values ('a'), ('a\\b'); 48select * from t1 where a like 'a\\%' escape '#'; 49a 50a\b 51select * from t1 where a like 'a\\%' escape '#' and a like 'a\\\\b'; 52a 53a\b 54prepare stmt1 from 'select * from t1 where a like \'a\\%\' escape ?'; 55set @esc='#'; 56execute stmt1 using @esc; 57a 58a\b 59deallocate prepare stmt1; 60drop table t1; 61create table t1 (a datetime); 62insert into t1 values ('2004-03-11 12:00:21'); 63select * from t1 where a like '2004-03-11 12:00:21'; 64a 652004-03-11 12:00:21 66drop table t1; 67SET NAMES koi8r; 68CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET koi8r); 69INSERT INTO t1 VALUES ('����'),('����'),('����'),('����'),('����'),('����'); 70INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 71INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 72INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 73SELECT * FROM t1 WHERE a LIKE '%����%'; 74a 75���� 76���� 77���� 78���� 79���� 80���� 81���������� 82���������� 83���������� 84���������� 85���������� 86���������� 87���������� 88���������� 89���������� 90���������� 91���������� 92���������� 93SELECT * FROM t1 WHERE a LIKE '%���%'; 94a 95���� 96���� 97���� 98���� 99���� 100���� 101���������� 102���������� 103���������� 104���������� 105���������� 106���������� 107���������� 108���������� 109���������� 110���������� 111���������� 112���������� 113SELECT * FROM t1 WHERE a LIKE '����%'; 114a 115���� 116���� 117���� 118���� 119���� 120���� 121���������� 122���������� 123���������� 124���������� 125���������� 126���������� 127���������� 128���������� 129���������� 130���������� 131���������� 132���������� 133DROP TABLE t1; 134SET NAMES cp1250; 135CREATE TABLE t1 (a varchar(250) NOT NULL) DEFAULT CHARACTER SET=cp1250; 136INSERT INTO t1 VALUES 137('Techni Tapes Sp. z o.o.'), 138('Pojazdy Szynowe PESA Bydgoszcz SA Holding'), 139('AKAPESTER 1 P.P.H.U.'), 140('Pojazdy Szynowe PESA Bydgoszcz S A Holding'), 141('PPUH PESKA-I Maria Struniarska'); 142select * from t1 where a like '%PESA%'; 143a 144Pojazdy Szynowe PESA Bydgoszcz SA Holding 145Pojazdy Szynowe PESA Bydgoszcz S A Holding 146select * from t1 where a like '%PESA %'; 147a 148Pojazdy Szynowe PESA Bydgoszcz SA Holding 149Pojazdy Szynowe PESA Bydgoszcz S A Holding 150select * from t1 where a like '%PES%'; 151a 152Techni Tapes Sp. z o.o. 153Pojazdy Szynowe PESA Bydgoszcz SA Holding 154AKAPESTER 1 P.P.H.U. 155Pojazdy Szynowe PESA Bydgoszcz S A Holding 156PPUH PESKA-I Maria Struniarska 157select * from t1 where a like '%PESKA%'; 158a 159PPUH PESKA-I Maria Struniarska 160select * from t1 where a like '%ESKA%'; 161a 162PPUH PESKA-I Maria Struniarska 163DROP TABLE t1; 164select _cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin; 165_cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin 1661 167set names koi8r; 168select 'andre%' like 'andre�%' escape '�'; 169'andre%' like 'andre�%' escape '�' 1701 171select _cp1251'andre%' like convert('andre�%' using cp1251) escape '�'; 172_cp1251'andre%' like convert('andre�%' using cp1251) escape '�' 1731 174End of 4.1 tests 175# 176# Bug #54575: crash when joining tables with unique set column 177# 178CREATE TABLE t1(a SET('a') NOT NULL, UNIQUE KEY(a)); 179CREATE TABLE t2(b INT PRIMARY KEY); 180INSERT IGNORE INTO t1 VALUES (); 181Warnings: 182Warning 1364 Field 'a' doesn't have a default value 183INSERT INTO t2 VALUES (1), (2), (3); 184SELECT 1 FROM t2 JOIN t1 ON 1 LIKE a GROUP BY a; 1851 186DROP TABLE t1, t2; 187# 188# Bug#59149 valgrind warnings with "like .. escape .." function 189# 190SELECT '' LIKE '1' ESCAPE COUNT(1); 191ERROR HY000: Incorrect arguments to ESCAPE 192End of 5.1 tests 193# 194# Bug #18114294 CRASH IN ITEM_FUNC_LIKE::BM_MATCHES 195# 196select 0x0000000001020003F03F40408484040ADDE40 like 0x256F3B38312A7725; 1970x0000000001020003F03F40408484040ADDE40 like 0x256F3B38312A7725 1980 199select 0x003c8793403032 like '%-112%'; 2000x003c8793403032 like '%-112%' 2010 202select 0x903f645a8c507dd79178 like '%-128%'; 2030x903f645a8c507dd79178 like '%-128%' 2040 205select 0xac14aa84f000d276d66ed9 like '%-107%'; 2060xac14aa84f000d276d66ed9 like '%-107%' 2070 208select 0xf0be117400d02a20b8e049da3e74 like '%-123%'; 2090xf0be117400d02a20b8e049da3e74 like '%-123%' 2100 211select 0x961838f6fc3c7f9ec17b5d900410d8aa like '%-113%'; 2120x961838f6fc3c7f9ec17b5d900410d8aa like '%-113%' 2130 214select 0x6a8473fc1c64ce4f2684c05a400c5e7ca4a01a like '%emailin%'; 2150x6a8473fc1c64ce4f2684c05a400c5e7ca4a01a like '%emailin%' 2160 217select 0x00b25278956e0044683dfc180cd886aeff2f5bc3fc18 like '%-122%'; 2180x00b25278956e0044683dfc180cd886aeff2f5bc3fc18 like '%-122%' 2190 220select 0xbc24421ce6194ab5c260e80af647ae58fdbfca18a19dc8411424 like '%-106%'; 2210xbc24421ce6194ab5c260e80af647ae58fdbfca18a19dc8411424 like '%-106%' 2220 223# 224# Bug#19931126 VALGRIND REPORTS USE OF UNINITIALIZED VALUE IN 225# MY_WILDCMP_BIN_IMPL 226# 227CREATE TABLE t1(x CHAR(1)) ENGINE=InnoDB; 228SELECT ('a%b' LIKE 'a\%b' ESCAPE (SELECT x FROM t1)); 229('a%b' LIKE 'a\%b' ESCAPE (SELECT x FROM t1)) 2301 231SELECT ('a%b' LIKE 'ax%b' ESCAPE (SELECT x FROM t1)); 232('a%b' LIKE 'ax%b' ESCAPE (SELECT x FROM t1)) 2330 234INSERT INTO t1 VALUES ('x'); 235SELECT ('a%b' LIKE 'a\%b' ESCAPE (SELECT x FROM t1)); 236('a%b' LIKE 'a\%b' ESCAPE (SELECT x FROM t1)) 2370 238SELECT ('a%b' LIKE 'ax%b' ESCAPE (SELECT x FROM t1)); 239('a%b' LIKE 'ax%b' ESCAPE (SELECT x FROM t1)) 2401 241SELECT ('a%b' LIKE 'ax%b' ESCAPE (SELECT 'xy' FROM t1)); 242ERROR HY000: Incorrect arguments to ESCAPE 243INSERT INTO t1 VALUES ('y'); 244SELECT ('a%b' LIKE 'ax%b' ESCAPE (SELECT x FROM t1)); 245ERROR 21000: Subquery returns more than 1 row 246DELETE FROM t1 WHERE x = 'y'; 247SELECT ('a%b' LIKE 'ax%b' ESCAPE (SELECT x, x FROM t1)); 248ERROR 21000: Operand should contain 1 column(s) 249SELECT ('a%b' LIKE 'ax%b' ESCAPE ('x', 'y')); 250ERROR 21000: Operand should contain 1 column(s) 251SELECT ('a%b' LIKE 'ax%b' ESCAPE x) FROM t1; 252ERROR HY000: Incorrect arguments to ESCAPE 253CREATE TABLE t2(x int, y varchar(100)) ENGINE=InnoDB; 254CREATE INDEX idx ON t2(y); 255INSERT INTO t2 VALUES (1, 'abcd'), (2, 'ab%cde'); 256ANALYZE TABLE t2; 257Table Op Msg_type Msg_text 258test.t2 analyze status OK 259EXPLAIN SELECT * FROM t2 WHERE y LIKE 'abc%%' ESCAPE (SELECT 'c' FROM t1) ORDER BY y; 260id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2611 PRIMARY t2 NULL ALL idx NULL NULL NULL 2 50.00 Using where; Using filesort 2622 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL 263Warnings: 264Note 1003 /* select#1 */ select `test`.`t2`.`x` AS `x`,`test`.`t2`.`y` AS `y` from `test`.`t2` where (`test`.`t2`.`y` like 'abc%%') order by `test`.`t2`.`y` 265SELECT * FROM t2 WHERE y LIKE 'abc%%' ESCAPE (SELECT 'c' FROM t1) ORDER BY y; 266x y 2672 ab%cde 268DROP TABLE t1, t2; 269# Bug#20035071: Out of range error in subselect lead to assertion failed 270CREATE TABLE t1(a INTEGER) engine=innodb; 271SELECT 1 FROM t1 HAVING (SELECT 1 FROM t1) LIKE EXP(NOW()); 272ERROR 22003: DOUBLE value is out of range in 'exp(now())' 273DROP TABLE t1; 274