1# 2# Test of like 3# 4 5--disable_warnings 6drop table if exists t1; 7--enable_warnings 8 9create table t1 (a varchar(10), key(a)); 10insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); 11explain extended select * from t1 where a like 'abc%'; 12explain extended select * from t1 where a like concat('abc','%'); 13select * from t1 where a like "abc%"; 14select * from t1 where a like concat("abc","%"); 15select * from t1 where a like "ABC%"; 16select * from t1 where a like "test%"; 17select * from t1 where a like "te_t"; 18 19# 20# The following will test the Turbo Boyer-Moore code 21# 22select * from t1 where a like "%a%"; 23select * from t1 where a like "%abcd%"; 24select * from t1 where a like "%abc\d%"; 25 26drop table t1; 27 28create table t1 (a varchar(10), key(a)); 29 30# 31# Bug #2231 32# 33insert into t1 values ('a'), ('a\\b'); 34select * from t1 where a like 'a\\%' escape '#'; 35select * from t1 where a like 'a\\%' escape '#' and a like 'a\\\\b'; 36 37# 38# Bug #4200: Prepared statement parameter as argument to ESCAPE 39# 40prepare stmt1 from 'select * from t1 where a like \'a\\%\' escape ?'; 41set @esc='#'; 42execute stmt1 using @esc; 43deallocate prepare stmt1; 44 45drop table t1; 46 47# 48# Bug #2885: like and datetime 49# 50 51create table t1 (a datetime); 52insert into t1 values ('2004-03-11 12:00:21'); 53select * from t1 where a like '2004-03-11 12:00:21'; 54drop table t1; 55 56# 57# Test like with non-default character set 58# 59 60SET NAMES koi8r; 61 62CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET koi8r); 63 64INSERT INTO t1 VALUES ('����'),('����'),('����'),('����'),('����'),('����'); 65INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 66INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 67INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 68 69SELECT * FROM t1 WHERE a LIKE '%����%'; 70SELECT * FROM t1 WHERE a LIKE '%���%'; 71SELECT * FROM t1 WHERE a LIKE '����%'; 72 73DROP TABLE t1; 74 75# Bug #2547 Strange "like" behaviour in tables with default charset=cp1250 76# Test like with non-default character set using TurboBM 77# 78SET NAMES cp1250; 79CREATE TABLE t1 (a varchar(250) NOT NULL) DEFAULT CHARACTER SET=cp1250; 80INSERT INTO t1 VALUES 81('Techni Tapes Sp. z o.o.'), 82('Pojazdy Szynowe PESA Bydgoszcz SA Holding'), 83('AKAPESTER 1 P.P.H.U.'), 84('Pojazdy Szynowe PESA Bydgoszcz S A Holding'), 85('PPUH PESKA-I Maria Struniarska'); 86 87select * from t1 where a like '%PESA%'; 88select * from t1 where a like '%PESA %'; 89select * from t1 where a like '%PES%'; 90select * from t1 where a like '%PESKA%'; 91select * from t1 where a like '%ESKA%'; 92DROP TABLE t1; 93 94# 95# LIKE crashed for binary collations in some cases 96# 97select _cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin; 98 99# 100# Check 8bit escape character 101# 102set names koi8r; 103select 'andre%' like 'andre�%' escape '�'; 104 105# Check 8bit escape character with charset conversion: 106# For "a LIKE b ESCAPE c" expressions, 107# escape character is converted into the operation character set, 108# which is result of aggregation of character sets of "a" and "b". 109# "c" itself doesn't take part in aggregation, because its collation 110# doesn't matter, escape character is always compared binary. 111# In the example below, escape character is converted from koi8r into cp1251: 112# 113select _cp1251'andre%' like convert('andre�%' using cp1251) escape '�'; 114 115 116--echo End of 4.1 tests 117 118 119--echo # 120--echo # Bug #54575: crash when joining tables with unique set column 121--echo # 122CREATE TABLE t1(a SET('a') NOT NULL, UNIQUE KEY(a)); 123CREATE TABLE t2(b INT PRIMARY KEY); 124INSERT IGNORE INTO t1 VALUES (); 125INSERT INTO t2 VALUES (1), (2), (3); 126SELECT 1 FROM t2 JOIN t1 ON 1 LIKE a GROUP BY a; 127DROP TABLE t1, t2; 128 129--echo # 130--echo # Bug#59149 valgrind warnings with "like .. escape .." function 131--echo # 132--error ER_WRONG_ARGUMENTS 133SELECT '' LIKE '1' ESCAPE COUNT(1); 134 135--echo End of 5.1 tests 136 137--echo # 138--echo # Start of 10.0 tests 139--echo # 140 141--echo # 142--echo # MDEV-5445 Server crashes in Item_func_like::fix_fields on LIKE ExtractValue(..) 143--echo # 144SELECT 'a' LIKE REPEAT('',0); 145SELECT 'a' LIKE EXTRACTVALUE('bar','qux'); 146 147--echo # 148--echo # End of 10.0 tests 149--echo # 150 151--echo # 152--echo # Start of 10.1 tests 153--echo # 154 155--echo # 156--echo # MDEV-8257 Erroneous "Impossible where" when mixing decimal comparison and LIKE 157--echo # 158CREATE TABLE t1 (a DECIMAL(8,2)); 159INSERT INTO t1 VALUES (10),(20); 160SELECT * FROM t1 WHERE a=10.0; 161SELECT * FROM t1 WHERE a LIKE 10.00; 162SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00; 163EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00; 164DROP TABLE t1; 165 166--echo # 167--echo # MDEV-8599 "WHERE varchar_field LIKE temporal_const" does not use range optimizer 168--echo # 169CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, KEY(a)) ENGINE=MyISAM; 170INSERT INTO t1 VALUES ('00:00:00'); 171INSERT INTO t1 VALUES ('00:00:01'); 172INSERT INTO t1 VALUES ('00:00:02'); 173INSERT INTO t1 VALUES ('00:00:03'); 174INSERT INTO t1 VALUES ('00:00:04'); 175INSERT INTO t1 VALUES ('00:00:05'); 176INSERT INTO t1 VALUES ('00:00:06'); 177INSERT INTO t1 VALUES ('00:00:07'); 178EXPLAIN SELECT * FROM t1 WHERE a LIKE '00:00:00'; 179EXPLAIN SELECT * FROM t1 WHERE a LIKE TIME'00:00:00'; 180SELECT * FROM t1 WHERE a LIKE '00:00:00'; 181SELECT * FROM t1 WHERE a LIKE TIME'00:00:00'; 182DROP TABLE t1; 183 184 185--echo # 186--echo # End of 10.1 tests 187--echo # 188 189# 190# Item_func_like::print() 191# 192create view v1 as select 'foo!' like 'foo!!', 'foo!' like 'foo!!' escape '!'; 193show create view v1; 194select * from v1; 195drop view v1; 196 197create table t1 (a varchar(100), 198 b int default (a like '%f\\_'), 199 c int default (a like '%f\\_' escape ''), 200 d int default (a like '%f\\_' escape '\\')); 201show create table t1; 202insert t1 (a) values ('1 f_'), ('1 f\\_'); 203set sql_mode=no_backslash_escapes; 204insert t1 (a) values ('2 f_'), ('2 f\_'); 205flush tables; 206insert t1 (a) values ('3 f_'), ('3 f\_'); 207set sql_mode=default; 208select * from t1; 209drop table t1; 210 211# 212# Item_func_like::fix_fields() 213# 214create table t1 (f int); 215insert t1 values (1),(2); 216select 1 from (select distinct * from t1) as x where f < (select 1 like 2 escape (3=1)); 217drop table t1; 218 219# 220# Item_func_like::fix_fields, ESCAPE, const_item() 221# 222create table t1(f1 int); 223insert into t1 values(1); 224--error ER_WRONG_ARGUMENTS 225update (select 1 like 2 escape (1 in (select 1 from t1))) x, t1 as d set d.f1 = 1; 226select * from (select 1 like 2 escape (1 in (select 1 from t1))) x; 227drop table t1; 228 229# 230# Item_func_like::walk 231# 232create table t1 (f int); 233insert t1 values (1),(2); 234create 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); 235drop view v1; 236drop table t1; 237 238--echo # 239--echo # MDEV-17359 - Extend expression supported by like (| & << >> || + - * / DIV MOD ^ ) 240--echo # 241 242SELECT 1 LIKE +1; 243SELECT -1 LIKE -1; 244SELECT 1 LIKE (1); 245SELECT 1 LIKE 1|2, 3 LIKE 1|2; 246SELECT 1 LIKE 3&2, 2 LIKE 3&2; 247SELECT 1 LIKE 1>>0, 1 LIKE 1>>1 , 64 LIKE 256>>2; 248SELECT 1 LIKE 1<<0, 1 LIKE 0<<2, 32 LIKE 1<<5; 249SELECT 1 LIKE 1||2, 1 LIKE 0||2; 250SELECT 2 LIKE 1+1, 2.0 LIKE 1+1.0, 2 LIKE 1+1.0, 1+1 LIKE 2, 1+1 LIKE 0+2; 251SELECT 0 LIKE 1-1, 2.0 LIKE 3-1.0, 2 LIKE 3-1.0, 2-1 LIKE 1, 3-1 LIKE 4-1; 252SELECT 1 LIKE 1*1, 2.0 LIKE 2*1.0, 2 LIKE 2*1.0, 2*1 LIKE 2, 2*3 LIKE 6*1; 253SELECT 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; 254SELECT 1 LIKE 1 DIV 1, 1 LIKE 1.0 DIV 1.0 ; 255SELECT 2 LIKE 10 MOD 8, 1.9 LIKE 10 MOD 8.1, 1.9 LIKE 10 MOD 8.10 ; 256 257SELECT 1 LIKE CAST(1 AS CHAR(10)); 258SELECT 1 LIKE CASE WHEN 1=1 THEN '1' ELSE '0' END; 259SELECT 1 LIKE COALESCE(1+0, 1); 260 261CREATE TABLE t1(c1 INTEGER, c2 INTEGER); 262INSERT INTO t1 VALUES(1,1); 263INSERT INTO t1 VALUES(1,2); 264 265SELECT c1, c2, c1|c2, 1 LIKE c1|c2 FROM t1 ORDER BY c2; 266SELECT c1, c2, c1&c2, 1 LIKE c1&c2 FROM t1 ORDER BY c2; 267SELECT c1, c2, c2>>c1, 1 LIKE c2>>c1 FROM t1 ORDER BY c2; 268SELECT c1, c2, c2<<c1, 2 LIKE c2<<c1 FROM t1 ORDER BY c2; 269SELECT c1, c2, c1||c2, 1 LIKE c1||c2 FROM t1 ORDER BY c2; 270SELECT c1, c2, c1+c2, 2 LIKE c1+c2 FROM t1 ORDER BY c2; 271SELECT c1, c2, c1-c2, -1 LIKE c1-c2 FROM t1 ORDER BY c2; 272SELECT c1, c2, c1*c2, 2 LIKE c1*c2 FROM t1 ORDER BY c2; 273SELECT c1, c2, c1/c2, 0.5000 LIKE c1/c2 FROM t1 ORDER BY c2; 274SELECT c1, c2, c1 DIV c2, 0 LIKE c1 DIV c2 FROM t1 ORDER BY c2; 275SELECT c1, c2, c1 MOD c2, 0 LIKE c1 MOD c2 FROM t1 ORDER BY c2; 276 277CREATE VIEW v1 AS 278SELECT 1 LIKE c1|c2, 1 LIKE c1&c2, 1 LIKE c2>>c1, 2 LIKE c2<<c1, 279 1 LIKE c1||c2, 2 LIKE c1+c2, -1 LIKE c1-c2, 2 LIKE c1*c2, 280 0.5000 LIKE c1/c2, 0 LIKE c1 DIV c2, 0 LIKE c1 MOD c2 281 FROM t1 ORDER BY c2; 282 283SELECT * FROM v1; 284EXPLAIN EXTENDED SELECT * FROM v1; 285DROP VIEW v1; 286DROP TABLE t1; 287