diff options
| author | Alexander Barkov <bar@mnogosearch.org> | 2013-10-15 10:26:08 +0400 |
|---|---|---|
| committer | Alexander Barkov <bar@mnogosearch.org> | 2013-10-15 10:26:08 +0400 |
| commit | a9240dce9e2f32941611b4f1a52e3b30a1508fc2 (patch) | |
| tree | ab7a7ec71341e29676d9a6bb039aeface4f88ce6 /mysql-test | |
| parent | 2c0a073970cf5f1dc679b34bb13e7fc55109dfd0 (diff) | |
| parent | eb2c6f451392396ef2ca74f1dba761fc4459d171 (diff) | |
| download | mariadb-git-a9240dce9e2f32941611b4f1a52e3b30a1508fc2.tar.gz | |
Merge 10.0-base -> 10.0
Diffstat (limited to 'mysql-test')
25 files changed, 1578 insertions, 95 deletions
diff --git a/mysql-test/include/ctype_utf8mb4.inc b/mysql-test/include/ctype_utf8mb4.inc index d1cb64705cd..af3a4564026 100644 --- a/mysql-test/include/ctype_utf8mb4.inc +++ b/mysql-test/include/ctype_utf8mb4.inc @@ -234,15 +234,15 @@ set names utf8mb4; set names utf8mb4; # This should return TRUE -select 'вася' rlike '[[:<:]]вася[[:>:]]'; -select 'вася ' rlike '[[:<:]]вася[[:>:]]'; -select ' вася' rlike '[[:<:]]вася[[:>:]]'; -select ' вася ' rlike '[[:<:]]вася[[:>:]]'; +select 'вася' rlike '\\bвася\\b'; +select 'вася ' rlike '\\bвася\\b'; +select ' вася' rlike '\\bвася\\b'; +select ' вася ' rlike '\\bвася\\b'; # This should return FALSE -select 'васяz' rlike '[[:<:]]вася[[:>:]]'; -select 'zвася' rlike '[[:<:]]вася[[:>:]]'; -select 'zвасяz' rlike '[[:<:]]вася[[:>:]]'; +select 'васяz' rlike '\\bвася\\b'; +select 'zвася' rlike '\\bвася\\b'; +select 'zвасяz' rlike '\\bвася\\b'; # # Bug #4555 diff --git a/mysql-test/include/show_slave_status.inc b/mysql-test/include/show_slave_status.inc index 41b5ee17ecd..55eb83c25e5 100644 --- a/mysql-test/include/show_slave_status.inc +++ b/mysql-test/include/show_slave_status.inc @@ -94,7 +94,7 @@ while ($_show_slave_status_items) --replace_regex /\.[\\\/]master/master/ --let $_show_slave_status_value= query_get_value(SHOW SLAVE STATUS, $_show_slave_status_name, 1) - --let $_slave_field_result_replace= /[\\]/\// $slave_field_result_replace + --let $_slave_field_result_replace= /[\\\\]/\// $slave_field_result_replace --replace_regex $_slave_field_result_replace --let $_show_slave_status_value= `SELECT REPLACE("$_show_slave_status_value", '$MYSQL_TEST_DIR', 'MYSQL_TEST_DIR')` --echo $_show_slave_status_name = '$_show_slave_status_value' diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 71cdcff8c14..42d10ddeef5 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -316,26 +316,26 @@ NULL drop table t1; set names utf8; set names utf8; -select 'вася' rlike '[[:<:]]вася[[:>:]]'; -'вася' rlike '[[:<:]]вася[[:>:]]' +select 'вася' rlike '\\bвася\\b'; +'вася' rlike '\\bвася\\b' 1 -select 'вася ' rlike '[[:<:]]вася[[:>:]]'; -'вася ' rlike '[[:<:]]вася[[:>:]]' +select 'вася ' rlike '\\bвася\\b'; +'вася ' rlike '\\bвася\\b' 1 -select ' вася' rlike '[[:<:]]вася[[:>:]]'; -' вася' rlike '[[:<:]]вася[[:>:]]' +select ' вася' rlike '\\bвася\\b'; +' вася' rlike '\\bвася\\b' 1 -select ' вася ' rlike '[[:<:]]вася[[:>:]]'; -' вася ' rlike '[[:<:]]вася[[:>:]]' +select ' вася ' rlike '\\bвася\\b'; +' вася ' rlike '\\bвася\\b' 1 -select 'васяz' rlike '[[:<:]]вася[[:>:]]'; -'васяz' rlike '[[:<:]]вася[[:>:]]' +select 'васяz' rlike '\\bвася\\b'; +'васяz' rlike '\\bвася\\b' 0 -select 'zвася' rlike '[[:<:]]вася[[:>:]]'; -'zвася' rlike '[[:<:]]вася[[:>:]]' +select 'zвася' rlike '\\bвася\\b'; +'zвася' rlike '\\bвася\\b' 0 -select 'zвасяz' rlike '[[:<:]]вася[[:>:]]'; -'zвасяz' rlike '[[:<:]]вася[[:>:]]' +select 'zвасяz' rlike '\\bвася\\b'; +'zвасяz' rlike '\\bвася\\b' 0 CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8_unicode_ci); ALTER TABLE t1 ADD COLUMN b CHAR(20); diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result index f4be208e0f7..c713ce39e41 100644 --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@ -316,26 +316,26 @@ NULL drop table t1; set names utf8mb4; set names utf8mb4; -select 'вася' rlike '[[:<:]]вася[[:>:]]'; -'вася' rlike '[[:<:]]вася[[:>:]]' +select 'вася' rlike '\\bвася\\b'; +'вася' rlike '\\bвася\\b' 1 -select 'вася ' rlike '[[:<:]]вася[[:>:]]'; -'вася ' rlike '[[:<:]]вася[[:>:]]' +select 'вася ' rlike '\\bвася\\b'; +'вася ' rlike '\\bвася\\b' 1 -select ' вася' rlike '[[:<:]]вася[[:>:]]'; -' вася' rlike '[[:<:]]вася[[:>:]]' +select ' вася' rlike '\\bвася\\b'; +' вася' rlike '\\bвася\\b' 1 -select ' вася ' rlike '[[:<:]]вася[[:>:]]'; -' вася ' rlike '[[:<:]]вася[[:>:]]' +select ' вася ' rlike '\\bвася\\b'; +' вася ' rlike '\\bвася\\b' 1 -select 'васяz' rlike '[[:<:]]вася[[:>:]]'; -'васяz' rlike '[[:<:]]вася[[:>:]]' +select 'васяz' rlike '\\bвася\\b'; +'васяz' rlike '\\bвася\\b' 0 -select 'zвася' rlike '[[:<:]]вася[[:>:]]'; -'zвася' rlike '[[:<:]]вася[[:>:]]' +select 'zвася' rlike '\\bвася\\b'; +'zвася' rlike '\\bвася\\b' 0 -select 'zвасяz' rlike '[[:<:]]вася[[:>:]]'; -'zвасяz' rlike '[[:<:]]вася[[:>:]]' +select 'zвасяz' rlike '\\bвася\\b'; +'zвасяz' rlike '\\bвася\\b' 0 CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8mb4_unicode_ci); ALTER TABLE t1 ADD COLUMN b CHAR(20); diff --git a/mysql-test/r/ctype_utf8mb4_heap.result b/mysql-test/r/ctype_utf8mb4_heap.result index 94ea59c1a0c..1649e7afeaf 100644 --- a/mysql-test/r/ctype_utf8mb4_heap.result +++ b/mysql-test/r/ctype_utf8mb4_heap.result @@ -306,26 +306,26 @@ NULL drop table t1; set names utf8mb4; set names utf8mb4; -select 'вася' rlike '[[:<:]]вася[[:>:]]'; -'вася' rlike '[[:<:]]вася[[:>:]]' +select 'вася' rlike '\\bвася\\b'; +'вася' rlike '\\bвася\\b' 1 -select 'вася ' rlike '[[:<:]]вася[[:>:]]'; -'вася ' rlike '[[:<:]]вася[[:>:]]' +select 'вася ' rlike '\\bвася\\b'; +'вася ' rlike '\\bвася\\b' 1 -select ' вася' rlike '[[:<:]]вася[[:>:]]'; -' вася' rlike '[[:<:]]вася[[:>:]]' +select ' вася' rlike '\\bвася\\b'; +' вася' rlike '\\bвася\\b' 1 -select ' вася ' rlike '[[:<:]]вася[[:>:]]'; -' вася ' rlike '[[:<:]]вася[[:>:]]' +select ' вася ' rlike '\\bвася\\b'; +' вася ' rlike '\\bвася\\b' 1 -select 'васяz' rlike '[[:<:]]вася[[:>:]]'; -'васяz' rlike '[[:<:]]вася[[:>:]]' +select 'васяz' rlike '\\bвася\\b'; +'васяz' rlike '\\bвася\\b' 0 -select 'zвася' rlike '[[:<:]]вася[[:>:]]'; -'zвася' rlike '[[:<:]]вася[[:>:]]' +select 'zвася' rlike '\\bвася\\b'; +'zвася' rlike '\\bвася\\b' 0 -select 'zвасяz' rlike '[[:<:]]вася[[:>:]]'; -'zвасяz' rlike '[[:<:]]вася[[:>:]]' +select 'zвасяz' rlike '\\bвася\\b'; +'zвасяz' rlike '\\bвася\\b' 0 CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8mb4_unicode_ci) ENGINE heap; ALTER TABLE t1 ADD COLUMN b CHAR(20); diff --git a/mysql-test/r/ctype_utf8mb4_innodb.result b/mysql-test/r/ctype_utf8mb4_innodb.result index b0e5bcef176..a0c334834ba 100644 --- a/mysql-test/r/ctype_utf8mb4_innodb.result +++ b/mysql-test/r/ctype_utf8mb4_innodb.result @@ -316,26 +316,26 @@ NULL drop table t1; set names utf8mb4; set names utf8mb4; -select 'вася' rlike '[[:<:]]вася[[:>:]]'; -'вася' rlike '[[:<:]]вася[[:>:]]' +select 'вася' rlike '\\bвася\\b'; +'вася' rlike '\\bвася\\b' 1 -select 'вася ' rlike '[[:<:]]вася[[:>:]]'; -'вася ' rlike '[[:<:]]вася[[:>:]]' +select 'вася ' rlike '\\bвася\\b'; +'вася ' rlike '\\bвася\\b' 1 -select ' вася' rlike '[[:<:]]вася[[:>:]]'; -' вася' rlike '[[:<:]]вася[[:>:]]' +select ' вася' rlike '\\bвася\\b'; +' вася' rlike '\\bвася\\b' 1 -select ' вася ' rlike '[[:<:]]вася[[:>:]]'; -' вася ' rlike '[[:<:]]вася[[:>:]]' +select ' вася ' rlike '\\bвася\\b'; +' вася ' rlike '\\bвася\\b' 1 -select 'васяz' rlike '[[:<:]]вася[[:>:]]'; -'васяz' rlike '[[:<:]]вася[[:>:]]' +select 'васяz' rlike '\\bвася\\b'; +'васяz' rlike '\\bвася\\b' 0 -select 'zвася' rlike '[[:<:]]вася[[:>:]]'; -'zвася' rlike '[[:<:]]вася[[:>:]]' +select 'zвася' rlike '\\bвася\\b'; +'zвася' rlike '\\bвася\\b' 0 -select 'zвасяz' rlike '[[:<:]]вася[[:>:]]'; -'zвасяz' rlike '[[:<:]]вася[[:>:]]' +select 'zвасяz' rlike '\\bвася\\b'; +'zвасяz' rlike '\\bвася\\b' 0 CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8mb4_unicode_ci) ENGINE InnoDB; ALTER TABLE t1 ADD COLUMN b CHAR(20); diff --git a/mysql-test/r/ctype_utf8mb4_myisam.result b/mysql-test/r/ctype_utf8mb4_myisam.result index 6f5d79ff6df..3b40d646b61 100644 --- a/mysql-test/r/ctype_utf8mb4_myisam.result +++ b/mysql-test/r/ctype_utf8mb4_myisam.result @@ -316,26 +316,26 @@ NULL drop table t1; set names utf8mb4; set names utf8mb4; -select 'вася' rlike '[[:<:]]вася[[:>:]]'; -'вася' rlike '[[:<:]]вася[[:>:]]' +select 'вася' rlike '\\bвася\\b'; +'вася' rlike '\\bвася\\b' 1 -select 'вася ' rlike '[[:<:]]вася[[:>:]]'; -'вася ' rlike '[[:<:]]вася[[:>:]]' +select 'вася ' rlike '\\bвася\\b'; +'вася ' rlike '\\bвася\\b' 1 -select ' вася' rlike '[[:<:]]вася[[:>:]]'; -' вася' rlike '[[:<:]]вася[[:>:]]' +select ' вася' rlike '\\bвася\\b'; +' вася' rlike '\\bвася\\b' 1 -select ' вася ' rlike '[[:<:]]вася[[:>:]]'; -' вася ' rlike '[[:<:]]вася[[:>:]]' +select ' вася ' rlike '\\bвася\\b'; +' вася ' rlike '\\bвася\\b' 1 -select 'васяz' rlike '[[:<:]]вася[[:>:]]'; -'васяz' rlike '[[:<:]]вася[[:>:]]' +select 'васяz' rlike '\\bвася\\b'; +'васяz' rlike '\\bвася\\b' 0 -select 'zвася' rlike '[[:<:]]вася[[:>:]]'; -'zвася' rlike '[[:<:]]вася[[:>:]]' +select 'zвася' rlike '\\bвася\\b'; +'zвася' rlike '\\bвася\\b' 0 -select 'zвасяz' rlike '[[:<:]]вася[[:>:]]'; -'zвасяz' rlike '[[:<:]]вася[[:>:]]' +select 'zвасяz' rlike '\\bвася\\b'; +'zвасяz' rlike '\\bвася\\b' 0 CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8mb4_unicode_ci) ENGINE MyISAM; ALTER TABLE t1 ADD COLUMN b CHAR(20); diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 40420a15c25..3438579d97e 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2215,6 +2215,35 @@ DROP VIEW v1; DROP TABLE t1,t2; set optimizer_switch=@save_optimizer_switch; # +# mdev-5078: sum over a view/derived table +# +CREATE TABLE t1 (a int); +INSERT INTO t1 (a) VALUES (1), (2); +CREATE TABLE t2 (b int(11)); +INSERT INTO t2 (b) VALUES (1), (2); +CREATE VIEW v AS SELECT b as c FROM t2; +SELECT a, (SELECT SUM(a + c) FROM v) FROM t1; +a (SELECT SUM(a + c) FROM v) +1 5 +2 7 +SELECT a, (SELECT SUM(a + c) FROM (SELECT b as c FROM t2) AS v1) FROM t1; +a (SELECT SUM(a + c) FROM (SELECT b as c FROM t2) AS v1) +1 5 +2 7 +DROP VIEW v; +DROP TABLE t1,t2; +# +# mdev-5105: memory overwrite in multi-table update +# using natuaral join with a view +# +create table t1(a int,b tinyint,c tinyint)engine=myisam; +create table t2(a tinyint,b float,c int, d int, e int, f int, key (b), key(c), key(d), key(e), key(f))engine=myisam; +create table t3(a int,b int,c int, d int, e int, f int, key(a), key(b), key(c), key(d), key(e), key(f))engine=myisam; +create view v1 as select t2.b a, t1.b b, t2.c c, t2.d d, t2.e e, t2.f f from t1,t2 where t1.a=t2.a; +update t3 natural join v1 set a:=1; +drop view v1; +drop table t1,t2,t3; +# # end of 5.3 tests # set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index d23a6706e04..019099bde14 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -879,6 +879,116 @@ NULL 7 drop view v; drop table t1, t2; +CREATE TABLE t1 ( +id int, i1 int, i2 int DEFAULT 0, +d1 date DEFAULT '2000-01-01', d2 date DEFAULT '2000-01-01', +t1 time DEFAULT '00:00:00', t2 time DEFAULT '00:00:00', +dt1 datetime NOT NULL DEFAULT '2000-01-01 00:00:00', +dt2 datetime NOT NULL DEFAULT '2000-01-01 00:00:00', +c1 varchar(1) NOT NULL, c2 varchar(1) NOT NULL +) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 (id,i1,c1,c2) VALUES +(1,7,'t','t'),(2,4,'k','k'),(3,2,'e','e'),(4,0,'i','i'),(5,1,'t','t'),(6,91,'m','m'), +(7,6,'z','z'),(8,3,'c','c'),(9,6,'i','i'),(10,8,'v','v'), (11,1,'l','l'),(12,4,'j','j'), +(13,5,'w','w'),(14,0,'r','r'),(15,7,'o','o'),(16,9,'o','o'),(17,7,'u','u'),(18,6,'f','f'), +(19,0,'l','l'),(20,6,'g','g'),(21,1,'e','e'),(22,7,'y','y'),(23,0,'p','p'),(24,6,'v','v'), +(25,5,'d','d'),(26,9,'i','i'),(27,5,'z','z'),(28,2,'q','q'),(29,4,'j','j'),(30,9,'m','m'), +(31,8,'d','d'),(32,5,'r','r'),(33,1,'r','r'),(34,1,'k','k'),(35,4,'p','p'),(36,2,'x','x'), +(37,5,'w','w'),(38,0,'k','k'),(39,7,'y','y'),(40,4,'p','p'),(41,9,'l','l'),(42,2,'u','u'), +(43,3,'r','r'),(44,5,'y','y'),(45,3,'u','u'),(46,9,'t','t'),(47,8,'f','f'),(48,2,'f','f'), +(49,2,'q','q'),(50,6,'v','v'),(51,6,'u','u'),(52,0,'b','b'),(53,1,'n','n'),(54,2,'p','p'), +(55,0,'y','y'),(56,1,'l','l'),(57,1,'c','c'),(58,0,'d','d'),(59,2,'y','y'),(60,7,'l','l'), +(61,6,'m','m'),(62,9,'q','q'),(63,0,'j','j'),(64,3,'u','u'),(65,4,'w','w'),(66,5,'p','p'), +(67,8,'z','z'),(68,5,'u','u'),(69,7,'b','b'),(70,0,'f','f'),(71,6,'u','u'),(72,1,'i','i'), +(73,9,'s','s'),(74,3,'y','y'),(75,5,'s','s'),(76,8,'x','x'),(77,3,'s','s'),(78,3,'l','l'), +(79,8,'b','b'),(80,0,'p','p'),(81,9,'m','m'),(82,5,'k','k'),(83,7,'u','u'),(84,0,'y','y'), +(85,2,'x','x'),(86,5,'h','h'),(87,5,'j','j'),(88,5,'o','o'),(89,9,'o','o'),(90,1,'c','c'), +(91,7,'k','k'),(92,9,'t','t'),(93,3,'h','h'),(94,6,'g','g'),(95,9,'r','r'),(96,2,'i','i'); +CREATE TABLE t2 (i INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (7),(8); +SELECT STRAIGHT_JOIN COUNT(DISTINCT t1.id) FROM +t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; +COUNT(DISTINCT t1.id) +18 +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN DISTINCT t1.id FROM +t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 96 100.00 Using where; Using temporary +1 PRIMARY <derived2> ref key0 key0 5 test.t1.i1 9 100.00 Using where; Distinct +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Distinct; Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 96 100.00 +Warnings: +Note 1003 select straight_join distinct `test`.`t1`.`id` AS `id` from `test`.`t1` join `test`.`v1` join `test`.`t2` where ((`test`.`t2`.`i` = `v1`.`id`) and (`v1`.`i1` = `test`.`t1`.`i1`) and (`v1`.`id` <> 3)) +set join_buffer_size=1024; +SELECT STRAIGHT_JOIN DISTINCT t1.id FROM +t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; +id +7 +8 +9 +18 +20 +24 +43 +45 +50 +51 +61 +64 +71 +74 +77 +78 +93 +94 +set join_buffer_size=1024*16; +SELECT STRAIGHT_JOIN DISTINCT t1.id FROM +t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; +id +7 +9 +18 +20 +24 +8 +50 +51 +61 +43 +45 +71 +64 +74 +77 +78 +94 +93 +set join_buffer_size=default; +SELECT STRAIGHT_JOIN DISTINCT t1.id FROM +t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; +id +7 +9 +18 +20 +24 +50 +51 +61 +71 +94 +8 +43 +45 +64 +74 +77 +78 +93 +DROP VIEW v1; +DROP TABLE t1,t2; # # Bug #11744875: 4082: integer lengths cause truncation with distinct concat and innodb # diff --git a/mysql-test/r/func_regexp_pcre.result b/mysql-test/r/func_regexp_pcre.result new file mode 100644 index 00000000000..ebbdedc6999 --- /dev/null +++ b/mysql-test/r/func_regexp_pcre.result @@ -0,0 +1,756 @@ +SET NAMES utf8; +# +# MDEV-4425 REGEXP enhancements +# +# +# Checking RLIKE +# +SELECT 'à' RLIKE '^.$'; +'à' RLIKE '^.$' +1 +SELECT 'à' RLIKE '\\x{00E0}'; +'à' RLIKE '\\x{00E0}' +1 +SELECT 'À' RLIKE '\\x{00E0}'; +'À' RLIKE '\\x{00E0}' +1 +SELECT 'à' RLIKE '\\x{00C0}'; +'à' RLIKE '\\x{00C0}' +1 +SELECT 'À' RLIKE '\\x{00C0}'; +'À' RLIKE '\\x{00C0}' +1 +SELECT 'à' RLIKE '\\x{00E0}' COLLATE utf8_bin; +'à' RLIKE '\\x{00E0}' COLLATE utf8_bin +1 +SELECT 'À' RLIKE '\\x{00E0}' COLLATE utf8_bin; +'À' RLIKE '\\x{00E0}' COLLATE utf8_bin +0 +SELECT 'à' RLIKE '\\x{00C0}' COLLATE utf8_bin; +'à' RLIKE '\\x{00C0}' COLLATE utf8_bin +0 +SELECT 'À' RLIKE '\\x{00C0}' COLLATE utf8_bin; +'À' RLIKE '\\x{00C0}' COLLATE utf8_bin +1 +CREATE TABLE t1 (s VARCHAR(10) CHARACTER SET utf8); +INSERT INTO t1 VALUES ('a'),('A'); +CREATE TABLE t2 (p VARCHAR(10) CHARACTER SET utf8); +INSERT INTO t2 VALUES ('a'),('(?i)a'),('(?-i)a'),('A'),('(?i)A'),('(?-i)A'); +SELECT s,p,s RLIKE p, s COLLATE utf8_bin RLIKE p FROM t1,t2 ORDER BY BINARY s, BINARY p; +s p s RLIKE p s COLLATE utf8_bin RLIKE p +A (?-i)A 1 1 +A (?-i)a 0 0 +A (?i)A 1 1 +A (?i)a 1 1 +A A 1 1 +A a 1 0 +a (?-i)A 0 0 +a (?-i)a 1 1 +a (?i)A 1 1 +a (?i)a 1 1 +a A 1 0 +a a 1 1 +DROP TABLE t1,t2; +CREATE TABLE t1 (ch VARCHAR(22)) CHARACTER SET utf8; +CREATE TABLE t2 (class VARCHAR(32)) CHARACTER SET utf8; +INSERT INTO t1 VALUES ('Я'),('Σ'),('A'),('À'); +INSERT INTO t1 VALUES ('я'),('σ'),('a'),('à'); +INSERT INTO t1 VALUES ('㐗'),('갷'),('ප'); +INSERT INTO t1 VALUES ('1'),('௨'); +INSERT INTO t2 VALUES ('\\p{Cyrillic}'),('\\p{Greek}'),('\\p{Latin}'); +INSERT INTO t2 VALUES ('\\p{Han}'),('\\p{Hangul}'); +INSERT INTO t2 VALUES ('\\p{Sinhala}'), ('\\p{Tamil}'); +INSERT INTO t2 VALUES ('\\p{L}'),('\\p{Ll}'),('\\p{Lu}'),('\\p{L&}'); +INSERT INTO t2 VALUES ('[[:alpha:]]'),('[[:digit:]]'); +SELECT class, ch, ch RLIKE class FROM t1, t2 ORDER BY class, BINARY ch; +class ch ch RLIKE class +[[:alpha:]] 1 0 +[[:alpha:]] A 1 +[[:alpha:]] a 1 +[[:alpha:]] À 1 +[[:alpha:]] à 1 +[[:alpha:]] Σ 1 +[[:alpha:]] σ 1 +[[:alpha:]] Я 1 +[[:alpha:]] я 1 +[[:alpha:]] ௨ 0 +[[:alpha:]] ප 1 +[[:alpha:]] 㐗 1 +[[:alpha:]] 갷 1 +[[:digit:]] 1 1 +[[:digit:]] A 0 +[[:digit:]] a 0 +[[:digit:]] À 0 +[[:digit:]] à 0 +[[:digit:]] Σ 0 +[[:digit:]] σ 0 +[[:digit:]] Я 0 +[[:digit:]] я 0 +[[:digit:]] ௨ 1 +[[:digit:]] ප 0 +[[:digit:]] 㐗 0 +[[:digit:]] 갷 0 +\p{Cyrillic} 1 0 +\p{Cyrillic} A 0 +\p{Cyrillic} a 0 +\p{Cyrillic} À 0 +\p{Cyrillic} à 0 +\p{Cyrillic} Σ 0 +\p{Cyrillic} σ 0 +\p{Cyrillic} Я 1 +\p{Cyrillic} я 1 +\p{Cyrillic} ௨ 0 +\p{Cyrillic} ප 0 +\p{Cyrillic} 㐗 0 +\p{Cyrillic} 갷 0 +\p{Greek} 1 0 +\p{Greek} A 0 +\p{Greek} a 0 +\p{Greek} À 0 +\p{Greek} à 0 +\p{Greek} Σ 1 +\p{Greek} σ 1 +\p{Greek} Я 0 +\p{Greek} я 0 +\p{Greek} ௨ 0 +\p{Greek} ප 0 +\p{Greek} 㐗 0 +\p{Greek} 갷 0 +\p{Hangul} 1 0 +\p{Hangul} A 0 +\p{Hangul} a 0 +\p{Hangul} À 0 +\p{Hangul} à 0 +\p{Hangul} Σ 0 +\p{Hangul} σ 0 +\p{Hangul} Я 0 +\p{Hangul} я 0 +\p{Hangul} ௨ 0 +\p{Hangul} ප 0 +\p{Hangul} 㐗 0 +\p{Hangul} 갷 1 +\p{Han} 1 0 +\p{Han} A 0 +\p{Han} a 0 +\p{Han} À 0 +\p{Han} à 0 +\p{Han} Σ 0 +\p{Han} σ 0 +\p{Han} Я 0 +\p{Han} я 0 +\p{Han} ௨ 0 +\p{Han} ප 0 +\p{Han} 㐗 1 +\p{Han} 갷 0 +\p{L&} 1 0 +\p{L&} A 1 +\p{L&} a 1 +\p{L&} À 1 +\p{L&} à 1 +\p{L&} Σ 1 +\p{L&} σ 1 +\p{L&} Я 1 +\p{L&} я 1 +\p{L&} ௨ 0 +\p{L&} ප 0 +\p{L&} 㐗 0 +\p{L&} 갷 0 +\p{Latin} 1 0 +\p{Latin} A 1 +\p{Latin} a 1 +\p{Latin} À 1 +\p{Latin} à 1 +\p{Latin} Σ 0 +\p{Latin} σ 0 +\p{Latin} Я 0 +\p{Latin} я 0 +\p{Latin} ௨ 0 +\p{Latin} ප 0 +\p{Latin} 㐗 0 +\p{Latin} 갷 0 +\p{Ll} 1 0 +\p{Ll} A 0 +\p{Ll} a 1 +\p{Ll} À 0 +\p{Ll} à 1 +\p{Ll} Σ 0 +\p{Ll} σ 1 +\p{Ll} Я 0 +\p{Ll} я 1 +\p{Ll} ௨ 0 +\p{Ll} ප 0 +\p{Ll} 㐗 0 +\p{Ll} 갷 0 +\p{Lu} 1 0 +\p{Lu} A 1 +\p{Lu} a 0 +\p{Lu} À 1 +\p{Lu} à 0 +\p{Lu} Σ 1 +\p{Lu} σ 0 +\p{Lu} Я 1 +\p{Lu} я 0 +\p{Lu} ௨ 0 +\p{Lu} ප 0 +\p{Lu} 㐗 0 +\p{Lu} 갷 0 +\p{L} 1 0 +\p{L} A 1 +\p{L} a 1 +\p{L} À 1 +\p{L} à 1 +\p{L} Σ 1 +\p{L} σ 1 +\p{L} Я 1 +\p{L} я 1 +\p{L} ௨ 0 +\p{L} ප 1 +\p{L} 㐗 1 +\p{L} 갷 1 +\p{Sinhala} 1 0 +\p{Sinhala} A 0 +\p{Sinhala} a 0 +\p{Sinhala} À 0 +\p{Sinhala} à 0 +\p{Sinhala} Σ 0 +\p{Sinhala} σ 0 +\p{Sinhala} Я 0 +\p{Sinhala} я 0 +\p{Sinhala} ௨ 0 +\p{Sinhala} ප 1 +\p{Sinhala} 㐗 0 +\p{Sinhala} 갷 0 +\p{Tamil} 1 0 +\p{Tamil} A 0 +\p{Tamil} a 0 +\p{Tamil} À 0 +\p{Tamil} à 0 +\p{Tamil} Σ 0 +\p{Tamil} σ 0 +\p{Tamil} Я 0 +\p{Tamil} я 0 +\p{Tamil} ௨ 1 +\p{Tamil} ප 0 +\p{Tamil} 㐗 0 +\p{Tamil} 갷 0 +DROP TABLE t1, t2; +SELECT 0xFF RLIKE '\\w'; +0xFF RLIKE '\\w' +0 +SELECT 0xFF RLIKE '(*UCP)\\w'; +0xFF RLIKE '(*UCP)\\w' +1 +SELECT '\n' RLIKE '(*CR)'; +'\n' RLIKE '(*CR)' +1 +SELECT '\n' RLIKE '(*LF)'; +'\n' RLIKE '(*LF)' +1 +SELECT '\n' RLIKE '(*CRLF)'; +'\n' RLIKE '(*CRLF)' +1 +SELECT '\n' RLIKE '(*ANYCRLF)'; +'\n' RLIKE '(*ANYCRLF)' +1 +SELECT '\n' RLIKE '(*ANY)'; +'\n' RLIKE '(*ANY)' +1 +SELECT 'a\nb' RLIKE '(*LF)(?m)^a$'; +'a\nb' RLIKE '(*LF)(?m)^a$' +1 +SELECT 'a\nb' RLIKE '(*CR)(?m)^a$'; +'a\nb' RLIKE '(*CR)(?m)^a$' +0 +SELECT 'a\nb' RLIKE '(*CRLF)(?m)^a$'; +'a\nb' RLIKE '(*CRLF)(?m)^a$' +0 +SELECT 'a\nb' RLIKE '(*ANYCRLF)(?m)^a$'; +'a\nb' RLIKE '(*ANYCRLF)(?m)^a$' +1 +SELECT 'a\rb' RLIKE '(*LF)(?m)^a$'; +'a\rb' RLIKE '(*LF)(?m)^a$' +0 +SELECT 'a\rb' RLIKE '(*CR)(?m)^a$'; +'a\rb' RLIKE '(*CR)(?m)^a$' +1 +SELECT 'a\rb' RLIKE '(*CRLF)(?m)^a$'; +'a\rb' RLIKE '(*CRLF)(?m)^a$' +0 +SELECT 'a\rb' RLIKE '(*ANYCRLF)(?m)^a$'; +'a\rb' RLIKE '(*ANYCRLF)(?m)^a$' +1 +SELECT 'a\r\nb' RLIKE '(*LF)(?m)^a$'; +'a\r\nb' RLIKE '(*LF)(?m)^a$' +0 +SELECT 'a\r\nb' RLIKE '(*CR)(?m)^a$'; +'a\r\nb' RLIKE '(*CR)(?m)^a$' +1 +SELECT 'a\r\nb' RLIKE '(*CRLF)(?m)^a$'; +'a\r\nb' RLIKE '(*CRLF)(?m)^a$' +1 +SELECT 'a\r\nb' RLIKE '(*ANYCRLF)(?m)^a$'; +'a\r\nb' RLIKE '(*ANYCRLF)(?m)^a$' +1 +SELECT 'aa' RLIKE '(a)\\g1'; +'aa' RLIKE '(a)\\g1' +1 +SELECT 'aa bb' RLIKE '(a)\\g1 (b)\\g2'; +'aa bb' RLIKE '(a)\\g1 (b)\\g2' +1 +SELECT 'aaaaa' RLIKE 'a{0,5}'; +'aaaaa' RLIKE 'a{0,5}' +1 +SELECT 'aaaaa' RLIKE 'a{1,3}'; +'aaaaa' RLIKE 'a{1,3}' +1 +SELECT 'aaaaa' RLIKE 'a{0,}'; +'aaaaa' RLIKE 'a{0,}' +1 +SELECT 'aaaaa' RLIKE 'a{10,20}'; +'aaaaa' RLIKE 'a{10,20}' +0 +SELECT 'aabb' RLIKE 'a(?R)?b'; +'aabb' RLIKE 'a(?R)?b' +1 +SELECT 'aabb' RLIKE 'aa(?R)?bb'; +'aabb' RLIKE 'aa(?R)?bb' +1 +SELECT 'abcc' RLIKE 'a(?>bc|b)c'; +'abcc' RLIKE 'a(?>bc|b)c' +1 +SELECT 'abc' RLIKE 'a(?>bc|b)c'; +'abc' RLIKE 'a(?>bc|b)c' +0 +SELECT 'ab' RLIKE 'a(?!b)'; +'ab' RLIKE 'a(?!b)' +0 +SELECT 'ac' RLIKE 'a(?!b)'; +'ac' RLIKE 'a(?!b)' +1 +SELECT 'ab' RLIKE 'a(?=b)'; +'ab' RLIKE 'a(?=b)' +1 +SELECT 'ac' RLIKE 'a(?=b)'; +'ac' RLIKE 'a(?=b)' +0 +SELECT 'ab' RLIKE '(?<!a)b'; +'ab' RLIKE '(?<!a)b' +0 +SELECT 'cb' RLIKE '(?<!a)b'; +'cb' RLIKE '(?<!a)b' +1 +SELECT 'ab' RLIKE '(?<=a)b'; +'ab' RLIKE '(?<=a)b' +1 +SELECT 'cb' RLIKE '(?<=a)b'; +'cb' RLIKE '(?<=a)b' +0 +SELECT 'aa' RLIKE '(?P<pattern>a)(?P=pattern)'; +'aa' RLIKE '(?P<pattern>a)(?P=pattern)' +1 +SELECT 'aba' RLIKE '(?P<pattern>a)b(?P=pattern)'; +'aba' RLIKE '(?P<pattern>a)b(?P=pattern)' +1 +SELECT 'a' RLIKE 'a(?#comment)'; +'a' RLIKE 'a(?#comment)' +1 +SELECT 'aa' RLIKE 'a(?#comment)a'; +'aa' RLIKE 'a(?#comment)a' +1 +SELECT 'aba' RLIKE 'a(?#b)a'; +'aba' RLIKE 'a(?#b)a' +0 +SELECT 'aaa' RLIKE '\\W\\W\\W'; +'aaa' RLIKE '\\W\\W\\W' +0 +SELECT '%' RLIKE '\\W'; +'%' RLIKE '\\W' +1 +SELECT '%a$' RLIKE '\\W.\\W'; +'%a$' RLIKE '\\W.\\W' +1 +SELECT '123' RLIKE '\\d\\d\\d'; +'123' RLIKE '\\d\\d\\d' +1 +SELECT 'aaa' RLIKE '\\d\\d\\d'; +'aaa' RLIKE '\\d\\d\\d' +0 +SELECT '1a3' RLIKE '\\d.\\d'; +'1a3' RLIKE '\\d.\\d' +1 +SELECT 'a1b' RLIKE '\\d.\\d'; +'a1b' RLIKE '\\d.\\d' +0 +SELECT '8' RLIKE '\\D'; +'8' RLIKE '\\D' +0 +SELECT 'a' RLIKE '\\D'; +'a' RLIKE '\\D' +1 +SELECT '%' RLIKE '\\D'; +'%' RLIKE '\\D' +1 +SELECT 'a1' RLIKE '\\D\\d'; +'a1' RLIKE '\\D\\d' +1 +SELECT 'a1' RLIKE '\\d\\D'; +'a1' RLIKE '\\d\\D' +0 +SELECT '\t' RLIKE '\\s'; +'\t' RLIKE '\\s' +1 +SELECT '\r' RLIKE '\\s'; +'\r' RLIKE '\\s' +1 +SELECT '\n' RLIKE '\\s'; +'\n' RLIKE '\\s' +1 +SELECT '\v' RLIKE '\\s'; +'\v' RLIKE '\\s' +0 +SELECT 'a' RLIKE '\\S'; +'a' RLIKE '\\S' +1 +SELECT '1' RLIKE '\\S'; +'1' RLIKE '\\S' +1 +SELECT '!' RLIKE '\\S'; +'!' RLIKE '\\S' +1 +SELECT '.' RLIKE '\\S'; +'.' RLIKE '\\S' +1 +SELECT 'abc\0def' REGEXP 'def'; +'abc\0def' REGEXP 'def' +1 +SELECT 'abc\0def' REGEXP 'abc\\x{00}def'; +'abc\0def' REGEXP 'abc\\x{00}def' +1 +SELECT HEX(REGEXP_SUBSTR('abc\0def','abc\\x{00}def')); +HEX(REGEXP_SUBSTR('abc\0def','abc\\x{00}def')) +61626300646566 +# +# Checking REGEXP_REPLACE +# +CREATE TABLE t1 AS SELECT REGEXP_REPLACE('abc','b','x'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `REGEXP_REPLACE('abc','b','x')` longtext CHARACTER SET utf8 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +EXPLAIN EXTENDED SELECT REGEXP_REPLACE('abc','b','x'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select regexp_replace('abc','b','x') AS `REGEXP_REPLACE('abc','b','x')` +CREATE TABLE t1 AS SELECT REGEXP_REPLACE('abc','b','x')+0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `REGEXP_REPLACE('abc','b','x')+0` double NOT NULL DEFAULT '0' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +SELECT REGEXP_REPLACE(NULL,'b','c'); +REGEXP_REPLACE(NULL,'b','c') +NULL +SELECT REGEXP_REPLACE('a',NULL,'c'); +REGEXP_REPLACE('a',NULL,'c') +NULL +SELECT REGEXP_REPLACE('a','b',NULL); +REGEXP_REPLACE('a','b',NULL) +NULL +SELECT REGEXP_REPLACE('a','x','b'); +REGEXP_REPLACE('a','x','b') +a +SELECT REGEXP_REPLACE('a','','b'); +REGEXP_REPLACE('a','','b') +a +SELECT REGEXP_REPLACE('a5b ab a5b','(?<=a)5*(?=b)','x'); +REGEXP_REPLACE('a5b ab a5b','(?<=a)5*(?=b)','x') +axb ab a5b +SELECT REGEXP_REPLACE('a5b a5b a5b','(?<=a)5*(?=b)','x'); +REGEXP_REPLACE('a5b a5b a5b','(?<=a)5*(?=b)','x') +axb axb axb +SELECT REGEXP_REPLACE('A','a','b'); +REGEXP_REPLACE('A','a','b') +b +SELECT REGEXP_REPLACE('a','A','b'); +REGEXP_REPLACE('a','A','b') +b +SELECT REGEXP_REPLACE('A' COLLATE utf8_bin,'a','b'); +REGEXP_REPLACE('A' COLLATE utf8_bin,'a','b') +A +SELECT REGEXP_REPLACE('a' COLLATE utf8_bin,'A','b'); +REGEXP_REPLACE('a' COLLATE utf8_bin,'A','b') +a +SELECT REGEXP_REPLACE('James Bond', '(.*) (.*)', '\\2, \\1 \\2'); +REGEXP_REPLACE('James Bond', '(.*) (.*)', '\\2, \\1 \\2') +Bond, James Bond +SELECT REGEXP_REPLACE('абвгд','в','ц'); +REGEXP_REPLACE('абвгд','в','ц') +абцгд +SELECT REGEXP_REPLACE('г',0xB3,0xB4); +REGEXP_REPLACE('г',0xB3,0xB4) +д +SELECT REGEXP_REPLACE('aaaa','a','b'); +REGEXP_REPLACE('aaaa','a','b') +bbbb +SELECT REGEXP_REPLACE('aaaa','(?<=.)a','b'); +REGEXP_REPLACE('aaaa','(?<=.)a','b') +abbb +SELECT REGEXP_REPLACE('aaaa','a(?=.)','b'); +REGEXP_REPLACE('aaaa','a(?=.)','b') +bbba +SELECT REGEXP_REPLACE('aaaa','(?<=.)a(?=.)','b'); +REGEXP_REPLACE('aaaa','(?<=.)a(?=.)','b') +abba +SELECT REGEXP_REPLACE('a\nb','(*LF)(?m)^a$','c'); +REGEXP_REPLACE('a\nb','(*LF)(?m)^a$','c') +c +b +SELECT REGEXP_REPLACE('a\nb','(*CR)(?m)^a$','c'); +REGEXP_REPLACE('a\nb','(*CR)(?m)^a$','c') +a +b +SELECT REGEXP_REPLACE('a\nb','(*CRLF)(?m)^a$','c'); +REGEXP_REPLACE('a\nb','(*CRLF)(?m)^a$','c') +a +b +SELECT REGEXP_REPLACE('a\nb','(*ANYCRLF)(?m)^a$','c'); +REGEXP_REPLACE('a\nb','(*ANYCRLF)(?m)^a$','c') +c +b +SELECT REGEXP_REPLACE('a\rb','(*LF)(?m)^a$','c'); +REGEXP_REPLACE('a\rb','(*LF)(?m)^a$','c') +a
b +SELECT REGEXP_REPLACE('a\rb','(*CR)(?m)^a$','c'); +REGEXP_REPLACE('a\rb','(*CR)(?m)^a$','c') +c
b +SELECT REGEXP_REPLACE('a\rb','(*CRLF)(?m)^a$','c'); +REGEXP_REPLACE('a\rb','(*CRLF)(?m)^a$','c') +a
b +SELECT REGEXP_REPLACE('a\rb','(*ANYCRLF)(?m)^a$','c'); +REGEXP_REPLACE('a\rb','(*ANYCRLF)(?m)^a$','c') +c
b +SELECT REGEXP_REPLACE('a\r\nb','(*LF)(?m)^a$','c'); +REGEXP_REPLACE('a\r\nb','(*LF)(?m)^a$','c') +a
+b +SELECT REGEXP_REPLACE('a\r\nb','(*CR)(?m)^a$','c'); +REGEXP_REPLACE('a\r\nb','(*CR)(?m)^a$','c') +c
+b +SELECT REGEXP_REPLACE('a\r\nb','(*CRLF)(?m)^a$','c'); +REGEXP_REPLACE('a\r\nb','(*CRLF)(?m)^a$','c') +c
+b +SELECT REGEXP_REPLACE('a\r\nb','(*ANYCRLF)(?m)^a$','c'); +REGEXP_REPLACE('a\r\nb','(*ANYCRLF)(?m)^a$','c') +c
+b +SELECT REGEXP_REPLACE('aa','(a)\\g1','b'); +REGEXP_REPLACE('aa','(a)\\g1','b') +b +SELECT REGEXP_REPLACE('aa bb','(a)\\g1 (b)\\g2','c'); +REGEXP_REPLACE('aa bb','(a)\\g1 (b)\\g2','c') +c +SELECT REGEXP_REPLACE('aaaaa','a{1,3}','b'); +REGEXP_REPLACE('aaaaa','a{1,3}','b') +bb +SELECT REGEXP_REPLACE('aaaaa','a{10,20}','b'); +REGEXP_REPLACE('aaaaa','a{10,20}','b') +aaaaa +SELECT REGEXP_REPLACE('daabbd','a(?R)?b','c'); +REGEXP_REPLACE('daabbd','a(?R)?b','c') +dcd +SELECT REGEXP_REPLACE('daabbd','aa(?R)?bb','c'); +REGEXP_REPLACE('daabbd','aa(?R)?bb','c') +dcd +SELECT REGEXP_REPLACE('dabccd','a(?>bc|b)c','e'); +REGEXP_REPLACE('dabccd','a(?>bc|b)c','e') +ded +SELECT REGEXP_REPLACE('dabcd','a(?>bc|b)c','e'); +REGEXP_REPLACE('dabcd','a(?>bc|b)c','e') +dabcd +SELECT REGEXP_REPLACE('ab','a(?!b)','e'); +REGEXP_REPLACE('ab','a(?!b)','e') +ab +SELECT REGEXP_REPLACE('ac','a(?!b)','e'); +REGEXP_REPLACE('ac','a(?!b)','e') +ec +SELECT REGEXP_REPLACE('ab','a(?=b)','e'); +REGEXP_REPLACE('ab','a(?=b)','e') +eb +SELECT REGEXP_REPLACE('ac','a(?=b)','e'); +REGEXP_REPLACE('ac','a(?=b)','e') +ac +SELECT REGEXP_REPLACE('ab','(?<!a)b','e'); +REGEXP_REPLACE('ab','(?<!a)b','e') +ab +SELECT REGEXP_REPLACE('cb','(?<!a)b','e'); +REGEXP_REPLACE('cb','(?<!a)b','e') +ce +SELECT REGEXP_REPLACE('ab','(?<=a)b','e'); +REGEXP_REPLACE('ab','(?<=a)b','e') +ae +SELECT REGEXP_REPLACE('cb','(?<=a)b','e'); +REGEXP_REPLACE('cb','(?<=a)b','e') +cb +SELECT REGEXP_REPLACE('aa','(?P<pattern>a)(?P=pattern)','b'); +REGEXP_REPLACE('aa','(?P<pattern>a)(?P=pattern)','b') +b +SELECT REGEXP_REPLACE('aba','(?P<pattern>a)b(?P=pattern)','c'); +REGEXP_REPLACE('aba','(?P<pattern>a)b(?P=pattern)','c') +c +SELECT REGEXP_REPLACE('a','a(?#comment)','e'); +REGEXP_REPLACE('a','a(?#comment)','e') +e +SELECT REGEXP_REPLACE('aa','a(?#comment)a','e'); +REGEXP_REPLACE('aa','a(?#comment)a','e') +e +SELECT REGEXP_REPLACE('aba','a(?#b)a','e'); +REGEXP_REPLACE('aba','a(?#b)a','e') +aba +SELECT REGEXP_REPLACE('ddd<ab>cc</ab>eee','<.+?>','*'); +REGEXP_REPLACE('ddd<ab>cc</ab>eee','<.+?>','*') +ddd*cc*eee +SELECT REGEXP_REPLACE('aaa','\\W\\W\\W','e'); +REGEXP_REPLACE('aaa','\\W\\W\\W','e') +aaa +SELECT REGEXP_REPLACE('aaa','\\w\\w\\w','e'); +REGEXP_REPLACE('aaa','\\w\\w\\w','e') +e +SELECT REGEXP_REPLACE('%','\\W','e'); +REGEXP_REPLACE('%','\\W','e') +e +SELECT REGEXP_REPLACE('%a$','\\W.\\W','e'); +REGEXP_REPLACE('%a$','\\W.\\W','e') +e +SELECT REGEXP_REPLACE('%a$','\\W\\w\\W','e'); +REGEXP_REPLACE('%a$','\\W\\w\\W','e') +e +SELECT REGEXP_REPLACE('123','\\d\\d\\d\\d\\d\\d','e'); +REGEXP_REPLACE('123','\\d\\d\\d\\d\\d\\d','e') +123 +SELECT REGEXP_REPLACE('123','\\d\\d\\d','e'); +REGEXP_REPLACE('123','\\d\\d\\d','e') +e +SELECT REGEXP_REPLACE('aaa','\\d\\d\\d','e'); +REGEXP_REPLACE('aaa','\\d\\d\\d','e') +aaa +SELECT REGEXP_REPLACE('1a3','\\d.\\d\\d.\\d','e'); +REGEXP_REPLACE('1a3','\\d.\\d\\d.\\d','e') +1a3 +SELECT REGEXP_REPLACE('1a3','\\d.\\d','e'); +REGEXP_REPLACE('1a3','\\d.\\d','e') +e +SELECT REGEXP_REPLACE('a1b','\\d.\\d','e'); +REGEXP_REPLACE('a1b','\\d.\\d','e') +a1b +SELECT REGEXP_REPLACE('8','\\D','e'); +REGEXP_REPLACE('8','\\D','e') +8 +SELECT REGEXP_REPLACE('a','\\D','e'); +REGEXP_REPLACE('a','\\D','e') +e +SELECT REGEXP_REPLACE('%','\\D','e'); +REGEXP_REPLACE('%','\\D','e') +e +SELECT REGEXP_REPLACE('a1','\\D\\d','e'); +REGEXP_REPLACE('a1','\\D\\d','e') +e +SELECT REGEXP_REPLACE('a1','\\d\\D','e'); +REGEXP_REPLACE('a1','\\d\\D','e') +a1 +SELECT REGEXP_REPLACE('\t','\\s','e'); +REGEXP_REPLACE('\t','\\s','e') +e +SELECT REGEXP_REPLACE('\r','\\s','e'); +REGEXP_REPLACE('\r','\\s','e') +e +SELECT REGEXP_REPLACE('\n','\\s','e'); +REGEXP_REPLACE('\n','\\s','e') +e +SELECT REGEXP_REPLACE('a','\\S','e'); +REGEXP_REPLACE('a','\\S','e') +e +SELECT REGEXP_REPLACE('1','\\S','e'); +REGEXP_REPLACE('1','\\S','e') +e +SELECT REGEXP_REPLACE('!','\\S','e'); +REGEXP_REPLACE('!','\\S','e') +e +SELECT REGEXP_REPLACE('.','\\S','e'); +REGEXP_REPLACE('.','\\S','e') +e +# +# Checking REGEXP_INSTR +# +SELECT REGEXP_INSTR('abcd','X'); +REGEXP_INSTR('abcd','X') +0 +SELECT REGEXP_INSTR('abcd','a'); +REGEXP_INSTR('abcd','a') +1 +SELECT REGEXP_INSTR('abcd','b'); +REGEXP_INSTR('abcd','b') +2 +SELECT REGEXP_INSTR('abcd','c'); +REGEXP_INSTR('abcd','c') +3 +SELECT REGEXP_INSTR('abcd','d'); +REGEXP_INSTR('abcd','d') +4 +SELECT REGEXP_INSTR('aaaa','(?<=a)a'); +REGEXP_INSTR('aaaa','(?<=a)a') +2 +SELECT REGEXP_INSTR('вася','в'); +REGEXP_INSTR('вася','в') +1 +SELECT REGEXP_INSTR('вася','а'); +REGEXP_INSTR('вася','а') +2 +SELECT REGEXP_INSTR('вася','с'); +REGEXP_INSTR('вася','с') +3 +SELECT REGEXP_INSTR('вася','я'); +REGEXP_INSTR('вася','я') +4 +SELECT REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('в' USING koi8r)); +REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('в' USING koi8r)) +1 +SELECT REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('а' USING koi8r)); +REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('а' USING koi8r)) +2 +SELECT REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('с' USING koi8r)); +REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('с' USING koi8r)) +3 +SELECT REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('я' USING koi8r)); +REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('я' USING koi8r)) +4 +# +# Checking REGEXP_SUBSTR +# +CREATE TABLE t1 AS SELECT REGEXP_SUBSTR('abc','b'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `REGEXP_SUBSTR('abc','b')` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +EXPLAIN EXTENDED SELECT REGEXP_SUBSTR('abc','b'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select regexp_substr('abc','b') AS `REGEXP_SUBSTR('abc','b')` +CREATE TABLE t1 AS SELECT REGEXP_SUBSTR('abc','b')+0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `REGEXP_SUBSTR('abc','b')+0` double NOT NULL DEFAULT '0' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +SELECT REGEXP_SUBSTR('See https://mariadb.org/en/foundation/ for details', 'https?://[^/]*'); +REGEXP_SUBSTR('See https://mariadb.org/en/foundation/ for details', 'https?://[^/]*') +https://mariadb.org diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 836de3d5842..ce233e0db23 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -373,3 +373,12 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); dt DROP TABLE t1; +# +# Bug mdev-5132: crash when exeicuting a join query +# with IS NULL and IS NOT NULL in where +# +CREATE TABLE t1 (a DATE, b INT, c INT, KEY(a), KEY(b), KEY(c)) ENGINE=MyISAM; +CREATE TABLE t2 (d DATE) ENGINE=MyISAM; +SELECT * FROM t1,t2 WHERE 1 IS NOT NULL AND t1.b IS NULL; +a b c d +DROP TABLE t1,t2; diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 4f94f5a704c..d87514923c4 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -1094,6 +1094,21 @@ COUNT(*) 3724 set optimizer_prune_level=@tmp_951283; DROP TABLE t1,t2; +# +# Bug mdev-5135: crash on semijoin with nested outer joins +# +CREATE TABLE t1 (i1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (i2 int, INDEX(i2)) ENGINE=MyISAM; +CREATE TABLE t3 (i3 int, c varchar(1), INDEX(i3), INDEX(c)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3,'x'),(4,'y'); +SELECT * FROM t1 WHERE ( 1, 1 ) IN ( +SELECT i2, i2 FROM t2 LEFT OUTER JOIN ( +t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) +) ON ( t3a.c = t3b.c ) +); +i1 +DROP TABLE t1,t2,t3; DROP TABLE IF EXISTS t1,t2,t3,t4; # # MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index e0c5864f1b3..00e393635a2 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -1109,6 +1109,21 @@ COUNT(*) 3724 set optimizer_prune_level=@tmp_951283; DROP TABLE t1,t2; +# +# Bug mdev-5135: crash on semijoin with nested outer joins +# +CREATE TABLE t1 (i1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (i2 int, INDEX(i2)) ENGINE=MyISAM; +CREATE TABLE t3 (i3 int, c varchar(1), INDEX(i3), INDEX(c)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3,'x'),(4,'y'); +SELECT * FROM t1 WHERE ( 1, 1 ) IN ( +SELECT i2, i2 FROM t2 LEFT OUTER JOIN ( +t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) +) ON ( t3a.c = t3b.c ) +); +i1 +DROP TABLE t1,t2,t3; DROP TABLE IF EXISTS t1,t2,t3,t4; # # MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 74fea8957a9..2306f19606c 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1096,6 +1096,21 @@ COUNT(*) 3724 set optimizer_prune_level=@tmp_951283; DROP TABLE t1,t2; +# +# Bug mdev-5135: crash on semijoin with nested outer joins +# +CREATE TABLE t1 (i1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (i2 int, INDEX(i2)) ENGINE=MyISAM; +CREATE TABLE t3 (i3 int, c varchar(1), INDEX(i3), INDEX(c)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3,'x'),(4,'y'); +SELECT * FROM t1 WHERE ( 1, 1 ) IN ( +SELECT i2, i2 FROM t2 LEFT OUTER JOIN ( +t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) +) ON ( t3a.c = t3b.c ) +); +i1 +DROP TABLE t1,t2,t3; DROP TABLE IF EXISTS t1,t2,t3,t4; # # MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin diff --git a/mysql-test/suite/innodb/t/innodb-create-options.test b/mysql-test/suite/innodb/t/innodb-create-options.test index c09d707ab11..aeb22514bf6 100644 --- a/mysql-test/suite/innodb/t/innodb-create-options.test +++ b/mysql-test/suite/innodb/t/innodb-create-options.test @@ -80,8 +80,10 @@ SET SESSION innodb_strict_mode = ON; DROP TABLE IF EXISTS t1; --echo # 'FIXED' is sent to InnoDB since it is used by MyISAM. --echo # But it is an invalid mode in InnoDB +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) ROW_FORMAT=FIXED; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=0; SHOW WARNINGS; @@ -108,14 +110,20 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE --echo # Test 2) StrictMode=ON, CREATE with each ROW_FORMAT & a valid non-zero KEY_BLOCK_SIZE --echo # KEY_BLOCK_SIZE is incompatible with COMPACT, REDUNDANT, & DYNAMIC DROP TABLE IF EXISTS t1; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=2; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; SHOW WARNINGS; @@ -214,8 +222,10 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE --echo # Test 6) StrictMode=ON, CREATE with an invalid KEY_BLOCK_SIZE. DROP TABLE IF EXISTS t1; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=9; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; --echo # Test 7) StrictMode=ON, Make sure ROW_FORMAT= COMPRESSED & DYNAMIC and @@ -223,14 +233,20 @@ SHOW WARNINGS; --echo # and that they can be set to default values during strict mode. SET GLOBAL innodb_file_format=Antelope; DROP TABLE IF EXISTS t1; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=4; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; SHOW WARNINGS; @@ -270,14 +286,20 @@ SET GLOBAL innodb_file_format=Barracuda; --echo # values during strict mode. SET GLOBAL innodb_file_per_table=OFF; DROP TABLE IF EXISTS t1; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE CREATE TABLE t1 ( i INT ) ROW_FORMAT=DYNAMIC; +--replace_regex / - .*[0-9]*[)]/)/ SHOW WARNINGS; CREATE TABLE t1 ( i INT ) ROW_FORMAT=REDUNDANT; SHOW WARNINGS; diff --git a/mysql-test/suite/innodb/t/innodb-zip.test b/mysql-test/suite/innodb/t/innodb-zip.test index 4a72169d5ec..1c7c7c8c419 100644 --- a/mysql-test/suite/innodb/t/innodb-zip.test +++ b/mysql-test/suite/innodb/t/innodb-zip.test @@ -175,8 +175,10 @@ set innodb_strict_mode = on; create table t1 (id int primary key) engine = innodb key_block_size = 0; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; @@ -202,19 +204,25 @@ drop table t1, t3, t4, t5, t8, t9, t10, t11; create table t1 (id int primary key) engine = innodb key_block_size = 4 row_format = compressed; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 4 row_format = redundant; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t3 (id int primary key) engine = innodb key_block_size = 4 row_format = compact; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t4 (id int primary key) engine = innodb key_block_size = 4 row_format = dynamic; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; create table t5 (id int primary key) engine = innodb @@ -224,19 +232,25 @@ key_block_size = 4 row_format = default; drop table t1, t5; #test multiple errors +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 9 row_format = redundant; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = compact; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 9 row_format = dynamic; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; --eval $query_i_s @@ -244,12 +258,17 @@ show warnings; #test valid values with innodb_file_per_table unset set global innodb_file_per_table = off; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 1; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 2; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t3 (id int primary key) engine = innodb key_block_size = 4; show warnings; @@ -259,9 +278,12 @@ show warnings; --error ER_CANT_CREATE_TABLE create table t6 (id int primary key) engine = innodb row_format = compressed; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t7 (id int primary key) engine = innodb row_format = dynamic; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; @@ -274,12 +296,17 @@ drop table t8, t9; set global innodb_file_per_table = on; set global innodb_file_format = `0`; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t1 (id int primary key) engine = innodb key_block_size = 1; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t2 (id int primary key) engine = innodb key_block_size = 2; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t3 (id int primary key) engine = innodb key_block_size = 4; show warnings; @@ -289,9 +316,12 @@ show warnings; --error ER_CANT_CREATE_TABLE create table t6 (id int primary key) engine = innodb row_format = compressed; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; +--replace_regex / - .*[0-9]*[)]/)/ --error ER_CANT_CREATE_TABLE create table t7 (id int primary key) engine = innodb row_format = dynamic; +--replace_regex / - .*[0-9]*[)]/)/ show warnings; create table t8 (id int primary key) engine = innodb row_format = compact; create table t9 (id int primary key) engine = innodb row_format = redundant; diff --git a/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test b/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test index bf21db1c7dc..5baff405d28 100644 --- a/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test +++ b/mysql-test/suite/sys_vars/t/character_sets_dir_basic.test @@ -8,7 +8,7 @@ # TODO: fix with a proper comparison in mysqltest let $rcd= `SELECT REPLACE('$MYSQL_CHARSETSDIR', '\\\\\', '.')`; let $rcd= `SELECT REPLACE('$rcd', '/', '.')`; -let $regex_charsetdir= `SELECT '/$rcd[\\\\\/\\\\\]/MYSQL_CHARSETSDIR/'`; +let $regex_charsetdir= `SELECT '/$rcd[[:punct:]]/MYSQL_CHARSETSDIR/'`; --replace_regex $regex_charsetdir select @@global.character_sets_dir; diff --git a/mysql-test/suite/unit/suite.pm b/mysql-test/suite/unit/suite.pm index 78d82ccb31d..966fd278a52 100644 --- a/mysql-test/suite/unit/suite.pm +++ b/mysql-test/suite/unit/suite.pm @@ -10,14 +10,23 @@ sub list_cases { sub start_test { my ($self, $tinfo)= @_; - my $args=[ ]; + my $args; + my $path; + my $cmd = $self->{ctests}->{$tinfo->{shortname}}; + + if ($cmd =~ /[ "'><%!*?]/) { + ($path, $args) = ('/bin/sh', [ '-c', $cmd ]) + } else { + ($path, $args) = ($cmd, , [ ]) + } + my $oldpwd=getcwd(); chdir $::opt_vardir; my $proc=My::SafeProcess->new ( name => $tinfo->{shortname}, - path => $self->{ctests}->{$tinfo->{shortname}}, + path => $path, args => \$args, append => 1, output => $::path_current_testlog, diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 0b90f222593..176d7e58c77 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -209,15 +209,15 @@ set names utf8; set names utf8; # This should return TRUE -select 'вася' rlike '[[:<:]]вася[[:>:]]'; -select 'вася ' rlike '[[:<:]]вася[[:>:]]'; -select ' вася' rlike '[[:<:]]вася[[:>:]]'; -select ' вася ' rlike '[[:<:]]вася[[:>:]]'; +select 'вася' rlike '\\bвася\\b'; +select 'вася ' rlike '\\bвася\\b'; +select ' вася' rlike '\\bвася\\b'; +select ' вася ' rlike '\\bвася\\b'; # This should return FALSE -select 'васяz' rlike '[[:<:]]вася[[:>:]]'; -select 'zвася' rlike '[[:<:]]вася[[:>:]]'; -select 'zвасяz' rlike '[[:<:]]вася[[:>:]]'; +select 'васяz' rlike '\\bвася\\b'; +select 'zвася' rlike '\\bвася\\b'; +select 'zвасяz' rlike '\\bвася\\b'; # # Bug #4555 diff --git a/mysql-test/t/ctype_utf8mb4.test b/mysql-test/t/ctype_utf8mb4.test index e4067245622..e5ede872075 100644 --- a/mysql-test/t/ctype_utf8mb4.test +++ b/mysql-test/t/ctype_utf8mb4.test @@ -207,15 +207,15 @@ set names utf8mb4; set names utf8mb4; # This should return TRUE -select 'вася' rlike '[[:<:]]вася[[:>:]]'; -select 'вася ' rlike '[[:<:]]вася[[:>:]]'; -select ' вася' rlike '[[:<:]]вася[[:>:]]'; -select ' вася ' rlike '[[:<:]]вася[[:>:]]'; +select 'вася' rlike '\\bвася\\b'; +select 'вася ' rlike '\\bвася\\b'; +select ' вася' rlike '\\bвася\\b'; +select ' вася ' rlike '\\bвася\\b'; # This should return FALSE -select 'васяz' rlike '[[:<:]]вася[[:>:]]'; -select 'zвася' rlike '[[:<:]]вася[[:>:]]'; -select 'zвасяz' rlike '[[:<:]]вася[[:>:]]'; +select 'васяz' rlike '\\bвася\\b'; +select 'zвася' rlike '\\bвася\\b'; +select 'zвасяz' rlike '\\bвася\\b'; # # Bug #4555 diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 4b7e76e11ca..8d1b3109d20 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1546,6 +1546,39 @@ set optimizer_switch=@save_optimizer_switch; --echo # +--echo # mdev-5078: sum over a view/derived table +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 (a) VALUES (1), (2); + +CREATE TABLE t2 (b int(11)); +INSERT INTO t2 (b) VALUES (1), (2); + +CREATE VIEW v AS SELECT b as c FROM t2; + +SELECT a, (SELECT SUM(a + c) FROM v) FROM t1; + +SELECT a, (SELECT SUM(a + c) FROM (SELECT b as c FROM t2) AS v1) FROM t1; + +DROP VIEW v; +DROP TABLE t1,t2; + +--echo # +--echo # mdev-5105: memory overwrite in multi-table update +--echo # using natuaral join with a view +--echo # + +create table t1(a int,b tinyint,c tinyint)engine=myisam; +create table t2(a tinyint,b float,c int, d int, e int, f int, key (b), key(c), key(d), key(e), key(f))engine=myisam; +create table t3(a int,b int,c int, d int, e int, f int, key(a), key(b), key(c), key(d), key(e), key(f))engine=myisam; +create view v1 as select t2.b a, t1.b b, t2.c c, t2.d d, t2.e e, t2.f f from t1,t2 where t1.a=t2.a; + +update t3 natural join v1 set a:=1; +drop view v1; +drop table t1,t2,t3; + +--echo # --echo # end of 5.3 tests --echo # diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index f1c120a313d..71643a25c5a 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -683,6 +683,64 @@ select distinct (select max(a) from t1 where alias.b = a) as field1 from t2 as a drop view v; drop table t1, t2; +# +# Bug mdev-5028: invalid distinct optimization when join buffer is used +# + +CREATE TABLE t1 ( + id int, i1 int, i2 int DEFAULT 0, + d1 date DEFAULT '2000-01-01', d2 date DEFAULT '2000-01-01', + t1 time DEFAULT '00:00:00', t2 time DEFAULT '00:00:00', + dt1 datetime NOT NULL DEFAULT '2000-01-01 00:00:00', + dt2 datetime NOT NULL DEFAULT '2000-01-01 00:00:00', + c1 varchar(1) NOT NULL, c2 varchar(1) NOT NULL +) ENGINE=MyISAM; + +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +INSERT INTO t1 (id,i1,c1,c2) VALUES +(1,7,'t','t'),(2,4,'k','k'),(3,2,'e','e'),(4,0,'i','i'),(5,1,'t','t'),(6,91,'m','m'), +(7,6,'z','z'),(8,3,'c','c'),(9,6,'i','i'),(10,8,'v','v'), (11,1,'l','l'),(12,4,'j','j'), +(13,5,'w','w'),(14,0,'r','r'),(15,7,'o','o'),(16,9,'o','o'),(17,7,'u','u'),(18,6,'f','f'), +(19,0,'l','l'),(20,6,'g','g'),(21,1,'e','e'),(22,7,'y','y'),(23,0,'p','p'),(24,6,'v','v'), +(25,5,'d','d'),(26,9,'i','i'),(27,5,'z','z'),(28,2,'q','q'),(29,4,'j','j'),(30,9,'m','m'), +(31,8,'d','d'),(32,5,'r','r'),(33,1,'r','r'),(34,1,'k','k'),(35,4,'p','p'),(36,2,'x','x'), +(37,5,'w','w'),(38,0,'k','k'),(39,7,'y','y'),(40,4,'p','p'),(41,9,'l','l'),(42,2,'u','u'), +(43,3,'r','r'),(44,5,'y','y'),(45,3,'u','u'),(46,9,'t','t'),(47,8,'f','f'),(48,2,'f','f'), +(49,2,'q','q'),(50,6,'v','v'),(51,6,'u','u'),(52,0,'b','b'),(53,1,'n','n'),(54,2,'p','p'), +(55,0,'y','y'),(56,1,'l','l'),(57,1,'c','c'),(58,0,'d','d'),(59,2,'y','y'),(60,7,'l','l'), +(61,6,'m','m'),(62,9,'q','q'),(63,0,'j','j'),(64,3,'u','u'),(65,4,'w','w'),(66,5,'p','p'), +(67,8,'z','z'),(68,5,'u','u'),(69,7,'b','b'),(70,0,'f','f'),(71,6,'u','u'),(72,1,'i','i'), +(73,9,'s','s'),(74,3,'y','y'),(75,5,'s','s'),(76,8,'x','x'),(77,3,'s','s'),(78,3,'l','l'), +(79,8,'b','b'),(80,0,'p','p'),(81,9,'m','m'),(82,5,'k','k'),(83,7,'u','u'),(84,0,'y','y'), +(85,2,'x','x'),(86,5,'h','h'),(87,5,'j','j'),(88,5,'o','o'),(89,9,'o','o'),(90,1,'c','c'), +(91,7,'k','k'),(92,9,'t','t'),(93,3,'h','h'),(94,6,'g','g'),(95,9,'r','r'),(96,2,'i','i'); + +CREATE TABLE t2 (i INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (7),(8); + +SELECT STRAIGHT_JOIN COUNT(DISTINCT t1.id) FROM + t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; + +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN DISTINCT t1.id FROM + t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; + +set join_buffer_size=1024; +SELECT STRAIGHT_JOIN DISTINCT t1.id FROM + t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; + +set join_buffer_size=1024*16; +SELECT STRAIGHT_JOIN DISTINCT t1.id FROM + t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; + +set join_buffer_size=default; +SELECT STRAIGHT_JOIN DISTINCT t1.id FROM + t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3; + +DROP VIEW v1; +DROP TABLE t1,t2; + --echo # --echo # Bug #11744875: 4082: integer lengths cause truncation with distinct concat and innodb --echo # diff --git a/mysql-test/t/func_regexp_pcre.test b/mysql-test/t/func_regexp_pcre.test new file mode 100644 index 00000000000..4892d9931da --- /dev/null +++ b/mysql-test/t/func_regexp_pcre.test @@ -0,0 +1,351 @@ + +SET NAMES utf8; + +--echo # +--echo # MDEV-4425 REGEXP enhancements +--echo # + +--echo # +--echo # Checking RLIKE +--echo # + +# Checking that à is a single character +SELECT 'à' RLIKE '^.$'; + +# Checking \x{FFFF} syntax and case sensitivity +SELECT 'à' RLIKE '\\x{00E0}'; +SELECT 'À' RLIKE '\\x{00E0}'; +SELECT 'à' RLIKE '\\x{00C0}'; +SELECT 'À' RLIKE '\\x{00C0}'; +SELECT 'à' RLIKE '\\x{00E0}' COLLATE utf8_bin; +SELECT 'À' RLIKE '\\x{00E0}' COLLATE utf8_bin; +SELECT 'à' RLIKE '\\x{00C0}' COLLATE utf8_bin; +SELECT 'À' RLIKE '\\x{00C0}' COLLATE utf8_bin; + +# Checking how (?i) and (?-i) affect case sensitivity +CREATE TABLE t1 (s VARCHAR(10) CHARACTER SET utf8); +INSERT INTO t1 VALUES ('a'),('A'); +CREATE TABLE t2 (p VARCHAR(10) CHARACTER SET utf8); +INSERT INTO t2 VALUES ('a'),('(?i)a'),('(?-i)a'),('A'),('(?i)A'),('(?-i)A'); +SELECT s,p,s RLIKE p, s COLLATE utf8_bin RLIKE p FROM t1,t2 ORDER BY BINARY s, BINARY p; +DROP TABLE t1,t2; + + +# Checking Unicode character classes +CREATE TABLE t1 (ch VARCHAR(22)) CHARACTER SET utf8; +CREATE TABLE t2 (class VARCHAR(32)) CHARACTER SET utf8; +INSERT INTO t1 VALUES ('Я'),('Σ'),('A'),('À'); +INSERT INTO t1 VALUES ('я'),('σ'),('a'),('à'); +INSERT INTO t1 VALUES ('㐗'),('갷'),('ප'); +INSERT INTO t1 VALUES ('1'),('௨'); +INSERT INTO t2 VALUES ('\\p{Cyrillic}'),('\\p{Greek}'),('\\p{Latin}'); +INSERT INTO t2 VALUES ('\\p{Han}'),('\\p{Hangul}'); +INSERT INTO t2 VALUES ('\\p{Sinhala}'), ('\\p{Tamil}'); +INSERT INTO t2 VALUES ('\\p{L}'),('\\p{Ll}'),('\\p{Lu}'),('\\p{L&}'); +INSERT INTO t2 VALUES ('[[:alpha:]]'),('[[:digit:]]'); +SELECT class, ch, ch RLIKE class FROM t1, t2 ORDER BY class, BINARY ch; +DROP TABLE t1, t2; + +# Checking that UCP is disabled by default for binary data +SELECT 0xFF RLIKE '\\w'; +SELECT 0xFF RLIKE '(*UCP)\\w'; + +# newline character +SELECT '\n' RLIKE '(*CR)'; +SELECT '\n' RLIKE '(*LF)'; +SELECT '\n' RLIKE '(*CRLF)'; +SELECT '\n' RLIKE '(*ANYCRLF)'; +SELECT '\n' RLIKE '(*ANY)'; + +SELECT 'a\nb' RLIKE '(*LF)(?m)^a$'; +SELECT 'a\nb' RLIKE '(*CR)(?m)^a$'; +SELECT 'a\nb' RLIKE '(*CRLF)(?m)^a$'; +SELECT 'a\nb' RLIKE '(*ANYCRLF)(?m)^a$'; + +SELECT 'a\rb' RLIKE '(*LF)(?m)^a$'; +SELECT 'a\rb' RLIKE '(*CR)(?m)^a$'; +SELECT 'a\rb' RLIKE '(*CRLF)(?m)^a$'; +SELECT 'a\rb' RLIKE '(*ANYCRLF)(?m)^a$'; + +SELECT 'a\r\nb' RLIKE '(*LF)(?m)^a$'; +SELECT 'a\r\nb' RLIKE '(*CR)(?m)^a$'; +SELECT 'a\r\nb' RLIKE '(*CRLF)(?m)^a$'; +SELECT 'a\r\nb' RLIKE '(*ANYCRLF)(?m)^a$'; + +#backreference +SELECT 'aa' RLIKE '(a)\\g1'; +SELECT 'aa bb' RLIKE '(a)\\g1 (b)\\g2'; + +#repitition +SELECT 'aaaaa' RLIKE 'a{0,5}'; +SELECT 'aaaaa' RLIKE 'a{1,3}'; +SELECT 'aaaaa' RLIKE 'a{0,}'; +SELECT 'aaaaa' RLIKE 'a{10,20}'; + +#Recursion +SELECT 'aabb' RLIKE 'a(?R)?b'; +SELECT 'aabb' RLIKE 'aa(?R)?bb'; + +#subroutine +#SELECT 'abbbc' RLIKE '(a(b|(?1))*c)'; +#SELECT 'abca' RLIKE '([abc])(?1){3}'; + +#Atomic grouping +SELECT 'abcc' RLIKE 'a(?>bc|b)c'; +SELECT 'abc' RLIKE 'a(?>bc|b)c'; + +#lookahead - negative +SELECT 'ab' RLIKE 'a(?!b)'; +SELECT 'ac' RLIKE 'a(?!b)'; + +#lookahead - positive +SELECT 'ab' RLIKE 'a(?=b)'; +SELECT 'ac' RLIKE 'a(?=b)'; + +#lookbehind - negative +SELECT 'ab' RLIKE '(?<!a)b'; +SELECT 'cb' RLIKE '(?<!a)b'; + +#lookbehind - positive +SELECT 'ab' RLIKE '(?<=a)b'; +SELECT 'cb' RLIKE '(?<=a)b'; + +# named subpatterns +SELECT 'aa' RLIKE '(?P<pattern>a)(?P=pattern)'; +SELECT 'aba' RLIKE '(?P<pattern>a)b(?P=pattern)'; + +#comments +SELECT 'a' RLIKE 'a(?#comment)'; +SELECT 'aa' RLIKE 'a(?#comment)a'; +SELECT 'aba' RLIKE 'a(?#b)a'; + +#ungreedy maching +#SELECT 'ddd <ab>cc</ab> eee' RLIKE '<.+?>'; + +#Extended character classes +SELECT 'aaa' RLIKE '\\W\\W\\W'; +SELECT '%' RLIKE '\\W'; +SELECT '%a$' RLIKE '\\W.\\W'; + +SELECT '123' RLIKE '\\d\\d\\d'; +SELECT 'aaa' RLIKE '\\d\\d\\d'; +SELECT '1a3' RLIKE '\\d.\\d'; +SELECT 'a1b' RLIKE '\\d.\\d'; + +SELECT '8' RLIKE '\\D'; +SELECT 'a' RLIKE '\\D'; +SELECT '%' RLIKE '\\D'; +SELECT 'a1' RLIKE '\\D\\d'; +SELECT 'a1' RLIKE '\\d\\D'; + +SELECT '\t' RLIKE '\\s'; +SELECT '\r' RLIKE '\\s'; +SELECT '\n' RLIKE '\\s'; +SELECT '\v' RLIKE '\\s'; + +SELECT 'a' RLIKE '\\S'; +SELECT '1' RLIKE '\\S'; +SELECT '!' RLIKE '\\S'; +SELECT '.' RLIKE '\\S'; + +# checking 0x00 bytes +# Bug#70470 REGEXP fails to find matches after NUL character +SELECT 'abc\0def' REGEXP 'def'; +SELECT 'abc\0def' REGEXP 'abc\\x{00}def'; +SELECT HEX(REGEXP_SUBSTR('abc\0def','abc\\x{00}def')); + + +--echo # +--echo # Checking REGEXP_REPLACE +--echo # + +# Check data type +CREATE TABLE t1 AS SELECT REGEXP_REPLACE('abc','b','x'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# Check print() +EXPLAIN EXTENDED SELECT REGEXP_REPLACE('abc','b','x'); + +# Check decimals +CREATE TABLE t1 AS SELECT REGEXP_REPLACE('abc','b','x')+0; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# Return NULL if any of the arguments are NULL +SELECT REGEXP_REPLACE(NULL,'b','c'); +SELECT REGEXP_REPLACE('a',NULL,'c'); +SELECT REGEXP_REPLACE('a','b',NULL); + +# Return the original string if no match +SELECT REGEXP_REPLACE('a','x','b'); + +# Return the original string for an empty pattern +SELECT REGEXP_REPLACE('a','','b'); + +# Check that replace stops on the first empty match +# 'a5b' matches the pattern and '5' is replaced to 'x' +# then 'ab' matches the pattern, but the match '5*' is empty, +# so replacing stops here. +SELECT REGEXP_REPLACE('a5b ab a5b','(?<=a)5*(?=b)','x'); + +# A modified version of the previous example, +# to check that all matches are replaced if no empty match is met. +SELECT REGEXP_REPLACE('a5b a5b a5b','(?<=a)5*(?=b)','x'); + + +# Check that case sensitiviry respects the collation +SELECT REGEXP_REPLACE('A','a','b'); +SELECT REGEXP_REPLACE('a','A','b'); +SELECT REGEXP_REPLACE('A' COLLATE utf8_bin,'a','b'); +SELECT REGEXP_REPLACE('a' COLLATE utf8_bin,'A','b'); + +# Pattern references in the "replace" string +SELECT REGEXP_REPLACE('James Bond', '(.*) (.*)', '\\2, \\1 \\2'); + +# Checking with UTF8 +SELECT REGEXP_REPLACE('абвгд','в','ц'); + +# Check that it does not treat binary strings as UTF8 +SELECT REGEXP_REPLACE('г',0xB3,0xB4); + +# Check that it replaces all matches by default +SELECT REGEXP_REPLACE('aaaa','a','b'); + +# Replace all matches except the first letter +SELECT REGEXP_REPLACE('aaaa','(?<=.)a','b'); + +# Replace all matches except the last letter +SELECT REGEXP_REPLACE('aaaa','a(?=.)','b'); + +# Replace all matches except the first and the last letter +SELECT REGEXP_REPLACE('aaaa','(?<=.)a(?=.)','b'); + +# newline character +SELECT REGEXP_REPLACE('a\nb','(*LF)(?m)^a$','c'); +SELECT REGEXP_REPLACE('a\nb','(*CR)(?m)^a$','c'); +SELECT REGEXP_REPLACE('a\nb','(*CRLF)(?m)^a$','c'); +SELECT REGEXP_REPLACE('a\nb','(*ANYCRLF)(?m)^a$','c'); + +SELECT REGEXP_REPLACE('a\rb','(*LF)(?m)^a$','c'); +SELECT REGEXP_REPLACE('a\rb','(*CR)(?m)^a$','c'); +SELECT REGEXP_REPLACE('a\rb','(*CRLF)(?m)^a$','c'); +SELECT REGEXP_REPLACE('a\rb','(*ANYCRLF)(?m)^a$','c'); + +SELECT REGEXP_REPLACE('a\r\nb','(*LF)(?m)^a$','c'); +SELECT REGEXP_REPLACE('a\r\nb','(*CR)(?m)^a$','c'); +SELECT REGEXP_REPLACE('a\r\nb','(*CRLF)(?m)^a$','c'); +SELECT REGEXP_REPLACE('a\r\nb','(*ANYCRLF)(?m)^a$','c'); + +#backreference +SELECT REGEXP_REPLACE('aa','(a)\\g1','b'); +SELECT REGEXP_REPLACE('aa bb','(a)\\g1 (b)\\g2','c'); + +#repitition +SELECT REGEXP_REPLACE('aaaaa','a{1,3}','b'); +SELECT REGEXP_REPLACE('aaaaa','a{10,20}','b'); + +#Recursion +SELECT REGEXP_REPLACE('daabbd','a(?R)?b','c'); +SELECT REGEXP_REPLACE('daabbd','aa(?R)?bb','c'); + +#Atomic grouping +SELECT REGEXP_REPLACE('dabccd','a(?>bc|b)c','e'); +SELECT REGEXP_REPLACE('dabcd','a(?>bc|b)c','e'); + +#lookahead - negative +SELECT REGEXP_REPLACE('ab','a(?!b)','e'); +SELECT REGEXP_REPLACE('ac','a(?!b)','e'); + +#lookahead - positive +SELECT REGEXP_REPLACE('ab','a(?=b)','e'); +SELECT REGEXP_REPLACE('ac','a(?=b)','e'); + +#lookbehind - negative +SELECT REGEXP_REPLACE('ab','(?<!a)b','e'); +SELECT REGEXP_REPLACE('cb','(?<!a)b','e'); + +#lookbehind - positive +SELECT REGEXP_REPLACE('ab','(?<=a)b','e'); +SELECT REGEXP_REPLACE('cb','(?<=a)b','e'); + +# named subpatterns +SELECT REGEXP_REPLACE('aa','(?P<pattern>a)(?P=pattern)','b'); +SELECT REGEXP_REPLACE('aba','(?P<pattern>a)b(?P=pattern)','c'); + +#comments +SELECT REGEXP_REPLACE('a','a(?#comment)','e'); +SELECT REGEXP_REPLACE('aa','a(?#comment)a','e'); +SELECT REGEXP_REPLACE('aba','a(?#b)a','e'); + +#ungreedy maching +SELECT REGEXP_REPLACE('ddd<ab>cc</ab>eee','<.+?>','*'); + +#Extended character classes +SELECT REGEXP_REPLACE('aaa','\\W\\W\\W','e'); +SELECT REGEXP_REPLACE('aaa','\\w\\w\\w','e'); +SELECT REGEXP_REPLACE('%','\\W','e'); +SELECT REGEXP_REPLACE('%a$','\\W.\\W','e'); +SELECT REGEXP_REPLACE('%a$','\\W\\w\\W','e'); + +SELECT REGEXP_REPLACE('123','\\d\\d\\d\\d\\d\\d','e'); +SELECT REGEXP_REPLACE('123','\\d\\d\\d','e'); +SELECT REGEXP_REPLACE('aaa','\\d\\d\\d','e'); +SELECT REGEXP_REPLACE('1a3','\\d.\\d\\d.\\d','e'); +SELECT REGEXP_REPLACE('1a3','\\d.\\d','e'); +SELECT REGEXP_REPLACE('a1b','\\d.\\d','e'); + +SELECT REGEXP_REPLACE('8','\\D','e'); +SELECT REGEXP_REPLACE('a','\\D','e'); +SELECT REGEXP_REPLACE('%','\\D','e'); +SELECT REGEXP_REPLACE('a1','\\D\\d','e'); +SELECT REGEXP_REPLACE('a1','\\d\\D','e'); + +SELECT REGEXP_REPLACE('\t','\\s','e'); +SELECT REGEXP_REPLACE('\r','\\s','e'); +SELECT REGEXP_REPLACE('\n','\\s','e'); + +SELECT REGEXP_REPLACE('a','\\S','e'); +SELECT REGEXP_REPLACE('1','\\S','e'); +SELECT REGEXP_REPLACE('!','\\S','e'); +SELECT REGEXP_REPLACE('.','\\S','e'); + +--echo # +--echo # Checking REGEXP_INSTR +--echo # +SELECT REGEXP_INSTR('abcd','X'); +SELECT REGEXP_INSTR('abcd','a'); +SELECT REGEXP_INSTR('abcd','b'); +SELECT REGEXP_INSTR('abcd','c'); +SELECT REGEXP_INSTR('abcd','d'); +SELECT REGEXP_INSTR('aaaa','(?<=a)a'); + +SELECT REGEXP_INSTR('вася','в'); +SELECT REGEXP_INSTR('вася','а'); +SELECT REGEXP_INSTR('вася','с'); +SELECT REGEXP_INSTR('вася','я'); +SELECT REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('в' USING koi8r)); +SELECT REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('а' USING koi8r)); +SELECT REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('с' USING koi8r)); +SELECT REGEXP_INSTR(CONVERT('вася' USING koi8r), CONVERT('я' USING koi8r)); + + +--echo # +--echo # Checking REGEXP_SUBSTR +--echo # + +# Check data type +CREATE TABLE t1 AS SELECT REGEXP_SUBSTR('abc','b'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# Check print() +EXPLAIN EXTENDED SELECT REGEXP_SUBSTR('abc','b'); + +# Check decimals +CREATE TABLE t1 AS SELECT REGEXP_SUBSTR('abc','b')+0; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +SELECT REGEXP_SUBSTR('See https://mariadb.org/en/foundation/ for details', 'https?://[^/]*'); diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 4a45240ec68..81951a9ce68 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -283,3 +283,15 @@ SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); DROP TABLE t1; + +--echo # +--echo # Bug mdev-5132: crash when exeicuting a join query +--echo # with IS NULL and IS NOT NULL in where +--echo # + +CREATE TABLE t1 (a DATE, b INT, c INT, KEY(a), KEY(b), KEY(c)) ENGINE=MyISAM; +CREATE TABLE t2 (d DATE) ENGINE=MyISAM; + +SELECT * FROM t1,t2 WHERE 1 IS NOT NULL AND t1.b IS NULL; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index 0f701cf9624..18221c90bc0 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -1225,6 +1225,25 @@ WHERE alias3.d IN ( set optimizer_prune_level=@tmp_951283; DROP TABLE t1,t2; +--echo # +--echo # Bug mdev-5135: crash on semijoin with nested outer joins +--echo # + +CREATE TABLE t1 (i1 int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (i2 int, INDEX(i2)) ENGINE=MyISAM; + +CREATE TABLE t3 (i3 int, c varchar(1), INDEX(i3), INDEX(c)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3,'x'),(4,'y'); + +SELECT * FROM t1 WHERE ( 1, 1 ) IN ( + SELECT i2, i2 FROM t2 LEFT OUTER JOIN ( + t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) + ) ON ( t3a.c = t3b.c ) +); + +DROP TABLE t1,t2,t3; --source include/have_innodb.inc |