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 40.00 Using where; Using index 7Warnings: 8Note 1003 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 40.00 Using where; Using index 12Warnings: 13Note 1003 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 IGNORE 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# 192# Start of 10.0 tests 193# 194# 195# MDEV-5445 Server crashes in Item_func_like::fix_fields on LIKE ExtractValue(..) 196# 197SELECT 'a' LIKE REPEAT('',0); 198'a' LIKE REPEAT('',0) 1990 200SELECT 'a' LIKE EXTRACTVALUE('bar','qux'); 201'a' LIKE EXTRACTVALUE('bar','qux') 2020 203# 204# End of 10.0 tests 205# 206# 207# Start of 10.1 tests 208# 209# 210# MDEV-8257 Erroneous "Impossible where" when mixing decimal comparison and LIKE 211# 212CREATE TABLE t1 (a DECIMAL(8,2)); 213INSERT INTO t1 VALUES (10),(20); 214SELECT * FROM t1 WHERE a=10.0; 215a 21610.00 217SELECT * FROM t1 WHERE a LIKE 10.00; 218a 21910.00 220SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00; 221a 22210.00 223EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00; 224id select_type table type possible_keys key key_len ref rows filtered Extra 2251 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 226Warnings: 227Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 10.0 and `test`.`t1`.`a` like 10.00 228DROP TABLE t1; 229# 230# MDEV-8599 "WHERE varchar_field LIKE temporal_const" does not use range optimizer 231# 232CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, KEY(a)) ENGINE=MyISAM; 233INSERT INTO t1 VALUES ('00:00:00'); 234INSERT INTO t1 VALUES ('00:00:01'); 235INSERT INTO t1 VALUES ('00:00:02'); 236INSERT INTO t1 VALUES ('00:00:03'); 237INSERT INTO t1 VALUES ('00:00:04'); 238INSERT INTO t1 VALUES ('00:00:05'); 239INSERT INTO t1 VALUES ('00:00:06'); 240INSERT INTO t1 VALUES ('00:00:07'); 241EXPLAIN SELECT * FROM t1 WHERE a LIKE '00:00:00'; 242id select_type table type possible_keys key key_len ref rows Extra 2431 SIMPLE t1 range a a 13 NULL 1 Using where; Using index 244EXPLAIN SELECT * FROM t1 WHERE a LIKE TIME'00:00:00'; 245id select_type table type possible_keys key key_len ref rows Extra 2461 SIMPLE t1 range a a 13 NULL 1 Using where; Using index 247SELECT * FROM t1 WHERE a LIKE '00:00:00'; 248a 24900:00:00 250SELECT * FROM t1 WHERE a LIKE TIME'00:00:00'; 251a 25200:00:00 253DROP TABLE t1; 254# 255# End of 10.1 tests 256# 257create view v1 as select 'foo!' like 'foo!!', 'foo!' like 'foo!!' escape '!'; 258show create view v1; 259View Create View character_set_client collation_connection 260v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 'foo!' like 'foo!!' AS `'foo!' like 'foo!!'`,'foo!' like 'foo!!' escape '!' AS `'foo!' like 'foo!!' escape '!'` koi8r koi8r_general_ci 261select * from v1; 262'foo!' like 'foo!!' 'foo!' like 'foo!!' escape '!' 2630 1 264drop view v1; 265create table t1 (a varchar(100), 266b int default (a like '%f\\_'), 267c int default (a like '%f\\_' escape ''), 268d int default (a like '%f\\_' escape '\\')); 269show create table t1; 270Table Create Table 271t1 CREATE TABLE `t1` ( 272 `a` varchar(100) DEFAULT NULL, 273 `b` int(11) DEFAULT (`a` like '%f\\_'), 274 `c` int(11) DEFAULT (`a` like '%f\\_' escape ''), 275 `d` int(11) DEFAULT (`a` like '%f\\_' escape '\\') 276) ENGINE=MyISAM DEFAULT CHARSET=latin1 277insert t1 (a) values ('1 f_'), ('1 f\\_'); 278set sql_mode=no_backslash_escapes; 279insert t1 (a) values ('2 f_'), ('2 f\_'); 280flush tables; 281insert t1 (a) values ('3 f_'), ('3 f\_'); 282set sql_mode=default; 283select * from t1; 284a b c d 2851 f_ 1 0 1 2861 f\_ 0 1 0 2872 f_ 1 0 1 2882 f\_ 0 1 0 2893 f_ 1 0 1 2903 f\_ 0 1 0 291drop table t1; 292create table t1 (f int); 293insert t1 values (1),(2); 294select 1 from (select distinct * from t1) as x where f < (select 1 like 2 escape (3=1)); 2951 296drop table t1; 297create table t1(f1 int); 298insert into t1 values(1); 299update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1; 300ERROR HY000: Incorrect arguments to ESCAPE 301select * from (select 1 like 2 escape (1 in (select 1 from t1))) x; 3021 like 2 escape (1 in (select 1 from t1)) 3030 304drop table t1; 305create table t1 (f int); 306insert t1 values (1),(2); 307create view v1 as select * from t1 where (1 like 2 escape (3 in (('h', 'b') in (select 'k', 'k' union select 'g', 'j'))) and f >= 0); 308drop view v1; 309drop table t1; 310# 311# MDEV-17359 - Extend expression supported by like (| & << >> || + - * / DIV MOD ^ ) 312# 313SELECT 1 LIKE +1; 3141 LIKE +1 3151 316SELECT -1 LIKE -1; 317-1 LIKE -1 3181 319SELECT 1 LIKE (1); 3201 LIKE (1) 3211 322SELECT 1 LIKE 1|2, 3 LIKE 1|2; 3231 LIKE 1|2 3 LIKE 1|2 3240 1 325SELECT 1 LIKE 3&2, 2 LIKE 3&2; 3261 LIKE 3&2 2 LIKE 3&2 3270 1 328SELECT 1 LIKE 1>>0, 1 LIKE 1>>1 , 64 LIKE 256>>2; 3291 LIKE 1>>0 1 LIKE 1>>1 64 LIKE 256>>2 3301 0 1 331SELECT 1 LIKE 1<<0, 1 LIKE 0<<2, 32 LIKE 1<<5; 3321 LIKE 1<<0 1 LIKE 0<<2 32 LIKE 1<<5 3331 0 1 334SELECT 1 LIKE 1||2, 1 LIKE 0||2; 3351 LIKE 1||2 1 LIKE 0||2 3361 1 337SELECT 2 LIKE 1+1, 2.0 LIKE 1+1.0, 2 LIKE 1+1.0, 1+1 LIKE 2, 1+1 LIKE 0+2; 3382 LIKE 1+1 2.0 LIKE 1+1.0 2 LIKE 1+1.0 1+1 LIKE 2 1+1 LIKE 0+2 3391 1 0 1 1 340SELECT 0 LIKE 1-1, 2.0 LIKE 3-1.0, 2 LIKE 3-1.0, 2-1 LIKE 1, 3-1 LIKE 4-1; 3410 LIKE 1-1 2.0 LIKE 3-1.0 2 LIKE 3-1.0 2-1 LIKE 1 3-1 LIKE 4-1 3421 1 0 1 0 343SELECT 1 LIKE 1*1, 2.0 LIKE 2*1.0, 2 LIKE 2*1.0, 2*1 LIKE 2, 2*3 LIKE 6*1; 3441 LIKE 1*1 2.0 LIKE 2*1.0 2 LIKE 2*1.0 2*1 LIKE 2 2*3 LIKE 6*1 3451 1 0 1 1 346SELECT 1 LIKE 1/1, 1.0000 LIKE 1/1, 1.0000 LIKE 1/1.000000, 1.000000 LIKE 1.0/1.000000, 1/1 like 1/1; 3471 LIKE 1/1 1.0000 LIKE 1/1 1.0000 LIKE 1/1.000000 1.000000 LIKE 1.0/1.000000 1/1 like 1/1 3480 1 1 0 1 349SELECT 1 LIKE 1 DIV 1, 1 LIKE 1.0 DIV 1.0 ; 3501 LIKE 1 DIV 1 1 LIKE 1.0 DIV 1.0 3511 1 352SELECT 2 LIKE 10 MOD 8, 1.9 LIKE 10 MOD 8.1, 1.9 LIKE 10 MOD 8.10 ; 3532 LIKE 10 MOD 8 1.9 LIKE 10 MOD 8.1 1.9 LIKE 10 MOD 8.10 3541 1 0 355SELECT 1 LIKE CAST(1 AS CHAR(10)); 3561 LIKE CAST(1 AS CHAR(10)) 3571 358SELECT 1 LIKE CASE WHEN 1=1 THEN '1' ELSE '0' END; 3591 LIKE CASE WHEN 1=1 THEN '1' ELSE '0' END 3601 361SELECT 1 LIKE COALESCE(1+0, 1); 3621 LIKE COALESCE(1+0, 1) 3631 364CREATE TABLE t1(c1 INTEGER, c2 INTEGER); 365INSERT INTO t1 VALUES(1,1); 366INSERT INTO t1 VALUES(1,2); 367SELECT c1, c2, c1|c2, 1 LIKE c1|c2 FROM t1 ORDER BY c2; 368c1 c2 c1|c2 1 LIKE c1|c2 3691 1 1 1 3701 2 3 0 371SELECT c1, c2, c1&c2, 1 LIKE c1&c2 FROM t1 ORDER BY c2; 372c1 c2 c1&c2 1 LIKE c1&c2 3731 1 1 1 3741 2 0 0 375SELECT c1, c2, c2>>c1, 1 LIKE c2>>c1 FROM t1 ORDER BY c2; 376c1 c2 c2>>c1 1 LIKE c2>>c1 3771 1 0 0 3781 2 1 1 379SELECT c1, c2, c2<<c1, 2 LIKE c2<<c1 FROM t1 ORDER BY c2; 380c1 c2 c2<<c1 2 LIKE c2<<c1 3811 1 2 1 3821 2 4 0 383SELECT c1, c2, c1||c2, 1 LIKE c1||c2 FROM t1 ORDER BY c2; 384c1 c2 c1||c2 1 LIKE c1||c2 3851 1 1 1 3861 2 1 1 387SELECT c1, c2, c1+c2, 2 LIKE c1+c2 FROM t1 ORDER BY c2; 388c1 c2 c1+c2 2 LIKE c1+c2 3891 1 2 1 3901 2 3 0 391SELECT c1, c2, c1-c2, -1 LIKE c1-c2 FROM t1 ORDER BY c2; 392c1 c2 c1-c2 -1 LIKE c1-c2 3931 1 0 0 3941 2 -1 1 395SELECT c1, c2, c1*c2, 2 LIKE c1*c2 FROM t1 ORDER BY c2; 396c1 c2 c1*c2 2 LIKE c1*c2 3971 1 1 0 3981 2 2 1 399SELECT c1, c2, c1/c2, 0.5000 LIKE c1/c2 FROM t1 ORDER BY c2; 400c1 c2 c1/c2 0.5000 LIKE c1/c2 4011 1 1.0000 0 4021 2 0.5000 1 403SELECT c1, c2, c1 DIV c2, 0 LIKE c1 DIV c2 FROM t1 ORDER BY c2; 404c1 c2 c1 DIV c2 0 LIKE c1 DIV c2 4051 1 1 0 4061 2 0 1 407SELECT c1, c2, c1 MOD c2, 0 LIKE c1 MOD c2 FROM t1 ORDER BY c2; 408c1 c2 c1 MOD c2 0 LIKE c1 MOD c2 4091 1 0 1 4101 2 1 0 411CREATE VIEW v1 AS 412SELECT 1 LIKE c1|c2, 1 LIKE c1&c2, 1 LIKE c2>>c1, 2 LIKE c2<<c1, 4131 LIKE c1||c2, 2 LIKE c1+c2, -1 LIKE c1-c2, 2 LIKE c1*c2, 4140.5000 LIKE c1/c2, 0 LIKE c1 DIV c2, 0 LIKE c1 MOD c2 415FROM t1 ORDER BY c2; 416SELECT * FROM v1; 4171 LIKE c1|c2 1 LIKE c1&c2 1 LIKE c2>>c1 2 LIKE c2<<c1 1 LIKE c1||c2 2 LIKE c1+c2 -1 LIKE c1-c2 2 LIKE c1*c2 0.5000 LIKE c1/c2 0 LIKE c1 DIV c2 0 LIKE c1 MOD c2 4181 1 0 1 1 1 0 0 0 0 1 4190 0 1 0 1 0 1 1 1 1 0 420EXPLAIN EXTENDED SELECT * FROM v1; 421id select_type table type possible_keys key key_len ref rows filtered Extra 4221 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort 423Warnings: 424Note 1003 select 1 like `test`.`t1`.`c1` | `test`.`t1`.`c2` AS `1 LIKE c1|c2`,1 like `test`.`t1`.`c1` & `test`.`t1`.`c2` AS `1 LIKE c1&c2`,1 like `test`.`t1`.`c2` >> `test`.`t1`.`c1` AS `1 LIKE c2>>c1`,2 like `test`.`t1`.`c2` << `test`.`t1`.`c1` AS `2 LIKE c2<<c1`,1 like `test`.`t1`.`c1` or `test`.`t1`.`c2` <> 0 AS `1 LIKE c1||c2`,2 like `test`.`t1`.`c1` + `test`.`t1`.`c2` AS `2 LIKE c1+c2`,-1 like `test`.`t1`.`c1` - `test`.`t1`.`c2` AS `-1 LIKE c1-c2`,2 like `test`.`t1`.`c1` * `test`.`t1`.`c2` AS `2 LIKE c1*c2`,0.5000 like `test`.`t1`.`c1` / `test`.`t1`.`c2` AS `0.5000 LIKE c1/c2`,0 like `test`.`t1`.`c1` DIV `test`.`t1`.`c2` AS `0 LIKE c1 DIV c2`,0 like `test`.`t1`.`c1` MOD `test`.`t1`.`c2` AS `0 LIKE c1 MOD c2` from `test`.`t1` order by `test`.`t1`.`c2` 425DROP VIEW v1; 426DROP TABLE t1; 427