diff options
Diffstat (limited to 'mysql-test/main/func_regexp_pcre.result')
| -rw-r--r-- | mysql-test/main/func_regexp_pcre.result | 897 |
1 files changed, 897 insertions, 0 deletions
diff --git a/mysql-test/main/func_regexp_pcre.result b/mysql-test/main/func_regexp_pcre.result new file mode 100644 index 00000000..9e51ced9 --- /dev/null +++ b/mysql-test/main/func_regexp_pcre.result @@ -0,0 +1,897 @@ +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 utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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')` +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +CREATE TABLE t1 AS SELECT REGEXP_REPLACE('abc','b','x')+0; +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'axc' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `REGEXP_REPLACE('abc','b','x')+0` double NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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 utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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')` +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +CREATE TABLE t1 AS SELECT REGEXP_SUBSTR('abc','b')+0; +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'b' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `REGEXP_SUBSTR('abc','b')+0` double NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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 +# +# MDEV-6027 RLIKE: "." no longer matching new line +# +SELECT 'cat and\ndog' RLIKE 'cat.*dog'; +'cat and\ndog' RLIKE 'cat.*dog' +0 +SELECT 'cat and\r\ndog' RLIKE 'cat.*dog'; +'cat and\r\ndog' RLIKE 'cat.*dog' +0 +SELECT 'a\nb' RLIKE 'a.b'; +'a\nb' RLIKE 'a.b' +0 +SELECT 'a\nb' RLIKE '(?-s)a.b'; +'a\nb' RLIKE '(?-s)a.b' +0 +SET default_regex_flags='DOTALL'; +SELECT @@default_regex_flags; +@@default_regex_flags +DOTALL +SELECT 'cat and\ndog' RLIKE 'cat.*dog'; +'cat and\ndog' RLIKE 'cat.*dog' +1 +SELECT 'cat and\r\ndog' RLIKE 'cat.*dog'; +'cat and\r\ndog' RLIKE 'cat.*dog' +1 +SELECT 'a\nb' RLIKE 'a.b'; +'a\nb' RLIKE 'a.b' +1 +SELECT 'a\nb' RLIKE '(?-s)a.b'; +'a\nb' RLIKE '(?-s)a.b' +0 +SET default_regex_flags=DEFAULT; +SELECT REGEXP_SUBSTR('Monday Mon','^((?<DN>Mon|Fri|Sun)day|(?<DN>Tue)sday).*(?P=DN)$'); +ERROR 42000: Regex error 'two named subpatterns have the same name (PCRE2_DUPNAMES not set) at offset 30' +SET default_regex_flags='DUPNAMES'; +SELECT REGEXP_SUBSTR('Monday Mon','^((?<DN>Mon|Fri|Sun)day|(?<DN>Tue)sday).*(?P=DN)$'); +REGEXP_SUBSTR('Monday Mon','^((?<DN>Mon|Fri|Sun)day|(?<DN>Tue)sday).*(?P=DN)$') +Monday Mon +SELECT REGEXP_SUBSTR('Tuesday Tue','^((?<DN>Mon|Fri|Sun)day|(?<DN>Tue)sday).*(?P=DN)$'); +REGEXP_SUBSTR('Tuesday Tue','^((?<DN>Mon|Fri|Sun)day|(?<DN>Tue)sday).*(?P=DN)$') +Tuesday Tue +SET default_regex_flags=DEFAULT; +SELECT 'AB' RLIKE 'A B'; +'AB' RLIKE 'A B' +0 +SELECT 'AB' RLIKE 'A# this is a comment\nB'; +'AB' RLIKE 'A# this is a comment\nB' +0 +SET default_regex_flags='EXTENDED'; +SELECT 'AB' RLIKE 'A B'; +'AB' RLIKE 'A B' +1 +SELECT 'AB' RLIKE 'A# this is a comment\nB'; +'AB' RLIKE 'A# this is a comment\nB' +1 +SET default_regex_flags=DEFAULT; +SELECT 'Aq' RLIKE 'A\\q'; +ERROR 42000: Regex error 'unrecognized character follows \ at offset 2' +SET default_regex_flags='EXTRA'; +SELECT 'A' RLIKE 'B'; +'A' RLIKE 'B' +0 +Warnings: +Warning 1105 PCRE2 doesn't support the EXTRA flag. Ignored. +SET default_regex_flags=DEFAULT; +SELECT 'a\nb\nc' RLIKE '^b$'; +'a\nb\nc' RLIKE '^b$' +0 +SET default_regex_flags='MULTILINE'; +SELECT 'a\nb\nc' RLIKE '^b$'; +'a\nb\nc' RLIKE '^b$' +1 +SET default_regex_flags=DEFAULT; +SELECT REGEXP_SUBSTR('abc','.+'); +REGEXP_SUBSTR('abc','.+') +abc +SELECT REGEXP_REPLACE('abc','^(.*)(.*)$','\\1/\\2'); +REGEXP_REPLACE('abc','^(.*)(.*)$','\\1/\\2') +abc/ +SET default_regex_flags='UNGREEDY'; +SELECT REGEXP_SUBSTR('abc','.+'); +REGEXP_SUBSTR('abc','.+') +a +SELECT REGEXP_REPLACE('abc','^(.*)(.*)$','\\1/\\2'); +REGEXP_REPLACE('abc','^(.*)(.*)$','\\1/\\2') +/abc +SET default_regex_flags=DEFAULT; +# +# MDEV-6965 non-captured group \2 in regexp_replace +# +SELECT REGEXP_REPLACE('1 foo and bar', '(\\d+) foo and (\\d+ )?bar', '\\1 this and \\2that'); +REGEXP_REPLACE('1 foo and bar', '(\\d+) foo and (\\d+ )?bar', '\\1 this and \\2that') +1 this and that +# +# MDEV-8102 REGEXP function fails to match hex values when expression is stored as a variable +# +# Testing a warning +SET NAMES latin1; +SET @regCheck= '\\xE0\\x01'; +SELECT 0xE001 REGEXP @regCheck; +0xE001 REGEXP @regCheck +0 +Warnings: +Warning 1139 Regex error 'UTF-8 error: 1 byte missing at end' +# Testing workaround N1: This makes the pattern to be a binary string: +SET NAMES latin1; +SET @regCheck= X'E001'; +SELECT 0xE001 REGEXP @regCheck; +0xE001 REGEXP @regCheck +1 +# Testing workaround N2: This also makes the pattern to be a binary string, using a different syntax: +SET NAMES latin1; +SET @regCheck= _binary '\\xE0\\x01'; +SELECT 0xE001 REGEXP @regCheck; +0xE001 REGEXP @regCheck +1 +# Testing workarond N3: This makes derivation of the subject string stronger (IMLICIT instead of COERCIBLE) +SET NAMES latin1; +SET @regCheck= '\\xE0\\x01'; +SELECT CAST(0xE001 AS BINARY) REGEXP @regCheck; +CAST(0xE001 AS BINARY) REGEXP @regCheck +1 +# MDEV-12420: Testing recursion overflow +SELECT 1 FROM dual WHERE ('Alpha,Bravo,Charlie,Delta,Echo,Foxtrot,StrataCentral,Golf,Hotel,India,Juliet,Kilo,Lima,Mike,StrataL3,November,Oscar,StrataL2,Sand,P3,P4SwitchTest,Arsys,Poppa,ExtensionMgr,Arp,Quebec,Romeo,StrataApiV2,PtReyes,Sierra,SandAcl,Arrow,Artools,BridgeTest,Tango,SandT,PAlaska,Namespace,Agent,Qos,PatchPanel,ProjectReport,Ark,Gimp,Agent,SliceAgent,Arnet,Bgp,Ale,Tommy,Central,AsicPktTestLib,Hsc,SandL3,Abuild,Pca9555,Standby,ControllerDut,CalSys,SandLib,Sb820,PointV2,BfnLib,Evpn,BfnSdk,Sflow,ManagementActive,AutoTest,GatedTest,Bgp,Sand,xinetd,BfnAgentLib,bf-utils,Hello,BfnState,Eos,Artest,Qos,Scd,ThermoMgr,Uniform,EosUtils,Eb,FanController,Central,BfnL3,BfnL2,tcp_wrappers,Victor,Environment,Route,Failover,Whiskey,Xray,Gimp,BfnFixed,Strata,SoCal,XApi,Msrp,XpProfile,tcpdump,PatchPanel,ArosTest,FhTest,Arbus,XpAcl,MacConc,XpApi,telnet,QosTest,Alpha2,BfnVlan,Stp,VxlanControllerTest,MplsAgent,Bravo2,Lanz,BfnMbb,Intf,XCtrl,Unicast,SandTunnel,L3Unicast,Ipsec,MplsTest,Rsvp,EthIntf,StageMgr,Sol,MplsUtils,Nat,Ira,P4NamespaceDut,Counters,Charlie2,Aqlc,Mlag,Power,OpenFlow,Lag,RestApi,BfdTest,strongs,Sfa,CEosUtils,Adt746,MaintenanceMode,MlagDut,EosImage,IpEth,MultiProtocol,Launcher,Max3179,Snmp,Acl,IpEthTest,PhyEee,bf-syslibs,tacc,XpL2,p4-ar-switch,p4-bf-switch,LdpTest,BfnPhy,Mirroring,Phy6,Ptp' REGEXP '^((?!\b(Strata|StrataApi|StrataApiV2)\b).)*$'); +SELECT REGEXP_INSTR('a_kollision', 'oll'); +REGEXP_INSTR('a_kollision', 'oll') +4 +SELECT REGEXP_INSTR('a_kollision', '(oll)'); +REGEXP_INSTR('a_kollision', '(oll)') +4 +SELECT REGEXP_INSTR('a_kollision', 'o([lm])\\1'); +REGEXP_INSTR('a_kollision', 'o([lm])\\1') +4 +SELECT a FROM (SELECT "aa" a) t WHERE a REGEXP '[0-9]'; +a |