1drop table if exists t1; 2set names latin1; 3drop table if exists t1; 4create table t1 as 5select repeat(' ', 64) as s1, repeat(' ',64) as s2 6union 7select null, null; 8show create table t1; 9Table Create Table 10t1 CREATE TABLE `t1` ( 11 `s1` varchar(64) DEFAULT NULL, 12 `s2` varchar(64) DEFAULT NULL 13) ENGINE=MyISAM DEFAULT CHARSET=latin1 14delete from t1; 15insert into t1 values('aaa','aaa'); 16insert into t1 values('aaa|qqq','qqq'); 17insert into t1 values('gheis','^[^a-dXYZ]+$'); 18insert into t1 values('aab','^aa?b'); 19insert into t1 values('Baaan','^Ba*n'); 20insert into t1 values('aaa','qqq|aaa'); 21insert into t1 values('qqq','qqq|aaa'); 22insert into t1 values('bbb','qqq|aaa'); 23insert into t1 values('bbb','qqq'); 24insert into t1 values('aaa','aba'); 25insert into t1 values(null,'abc'); 26insert into t1 values('def',null); 27insert into t1 values(null,null); 28insert into t1 values('ghi','ghi['); 29select HIGH_PRIORITY s1 regexp s2 from t1; 30s1 regexp s2 310 320 330 341 351 361 371 381 391 401 41NULL 42NULL 43NULL 44NULL 45drop table t1; 46create table t1 (xxx char(128)); 47insert into t1 (xxx) values('this is a test of some long text to see what happens'); 48select * from t1 where xxx regexp('is a test of some long text to'); 49xxx 50this is a test of some long text to see what happens 51explain extended select * from t1 where xxx regexp('is a test of some long text to'); 52id select_type table type possible_keys key key_len ref rows filtered Extra 531 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 54Warnings: 55Note 1003 select 'this is a test of some long text to see what happens' AS `xxx` from dual where 1 56select * from t1 where xxx regexp('is a test of some long text to '); 57xxx 58this is a test of some long text to see what happens 59select * from t1 where xxx regexp('is a test of some long text to s'); 60xxx 61this is a test of some long text to see what happens 62select * from t1 where xxx regexp('is a test of some long text to se'); 63xxx 64this is a test of some long text to see what happens 65drop table t1; 66create table t1 (xxx char(128)); 67insert into t1 (xxx) values('this is some text: to test - out.reg exp (22/45)'); 68select * from t1 where xxx REGEXP '^this is some text: to test - out\\.reg exp [[(][0-9]+[/\\][0-9]+[])][ ]*$'; 69xxx 70this is some text: to test - out.reg exp (22/45) 71drop table t1; 72select _latin1 0xFF regexp _latin1 '[[:lower:]]' COLLATE latin1_bin; 73_latin1 0xFF regexp _latin1 '[[:lower:]]' COLLATE latin1_bin 741 75select _koi8r 0xFF regexp _koi8r '[[:lower:]]' COLLATE koi8r_bin; 76_koi8r 0xFF regexp _koi8r '[[:lower:]]' COLLATE koi8r_bin 770 78select _latin1 0xFF regexp _latin1 '[[:upper:]]' COLLATE latin1_bin; 79_latin1 0xFF regexp _latin1 '[[:upper:]]' COLLATE latin1_bin 800 81select _koi8r 0xFF regexp _koi8r '[[:upper:]]' COLLATE koi8r_bin; 82_koi8r 0xFF regexp _koi8r '[[:upper:]]' COLLATE koi8r_bin 831 84select _latin1 0xF7 regexp _latin1 '[[:alpha:]]'; 85_latin1 0xF7 regexp _latin1 '[[:alpha:]]' 860 87select _koi8r 0xF7 regexp _koi8r '[[:alpha:]]'; 88_koi8r 0xF7 regexp _koi8r '[[:alpha:]]' 891 90select _latin1'a' regexp _latin1'A' collate latin1_general_ci; 91_latin1'a' regexp _latin1'A' collate latin1_general_ci 921 93select _latin1'a' regexp _latin1'A' collate latin1_bin; 94_latin1'a' regexp _latin1'A' collate latin1_bin 950 96create table t1 (a varchar(40)); 97insert into t1 values ('C1'),('C2'),('R1'),('C3'),('R2'),('R3'); 98prepare stmt1 from 'select a from t1 where a rlike ? order by a'; 99set @a="^C.*"; 100execute stmt1 using @a; 101a 102C1 103C2 104C3 105set @a="^R.*"; 106execute stmt1 using @a; 107a 108R1 109R2 110R3 111deallocate prepare stmt1; 112drop table t1; 113End of 4.1 tests 114SELECT 1 REGEXP NULL; 1151 REGEXP NULL 116NULL 117SELECT '' REGEXP BINARY NULL; 118'' REGEXP BINARY NULL 119NULL 120SELECT NULL REGEXP BINARY NULL; 121NULL REGEXP BINARY NULL 122NULL 123SELECT 'A' REGEXP BINARY NULL; 124'A' REGEXP BINARY NULL 125NULL 126SELECT "ABC" REGEXP BINARY NULL; 127"ABC" REGEXP BINARY NULL 128NULL 129End of 5.0 tests 130CREATE TABLE t1(a INT, b CHAR(4)); 131INSERT INTO t1 VALUES (1, '6.1'), (1, '7.0'), (1, '8.0'); 132PREPARE stmt1 FROM "SELECT a FROM t1 WHERE a=1 AND '7.0' REGEXP b LIMIT 1"; 133EXECUTE stmt1; 134a 1351 136EXECUTE stmt1; 137a 1381 139EXECUTE stmt1; 140a 1411 142EXECUTE stmt1; 143a 1441 145DEALLOCATE PREPARE stmt1; 146DROP TABLE t1; 147End of 5.1 tests 148SELECT ' ' REGEXP '[[:blank:]]'; 149' ' REGEXP '[[:blank:]]' 1501 151SELECT '\t' REGEXP '[[:blank:]]'; 152'\t' REGEXP '[[:blank:]]' 1531 154SELECT ' ' REGEXP '[[:space:]]'; 155' ' REGEXP '[[:space:]]' 1561 157SELECT '\t' REGEXP '[[:space:]]'; 158'\t' REGEXP '[[:space:]]' 1591 160# 161# MDEV-13967 Parameter data type control for Item_long_func 162# 163SELECT REGEXP_INSTR('111222333',2); 164REGEXP_INSTR('111222333',2) 1654 166