[MDEV-18523] REGEXP not properly handling \s* Created: 2019-02-08  Updated: 2019-02-09  Resolved: 2019-02-09

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.3.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Bob Smith Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

The string

{"fooa": "2", "foob": "2","fooc":"1"}

is stored in a column named "test". The statement 

select test from table1 WHERE test REGEXP '(\s*\"[^\"]+\"\s*\:\s*\"\s*2\s*\"\s*,?)';

does not match the string, and no result set is returned.

However, removing the FIRST blank space 

{"fooa":"2", "foob": "2","fooc":"1"}

DOES allow the match to occur.

Checking against other PCRE-compliant engines (eg. Regex101.com) show that BOTH strings should match, yet for some reason, the MariaDB regex engine is getting tripped up on first space. If I'm missing something regarding notation, please do let me know.

Thanks

 Comments   
Comment by Elena Stepanova [ 2019-02-09 ]

https://mariadb.com/kb/en/library/pcre/#special-characters

the backslash characters <...> must be escaped with another backslash, unless you're using the SQL_MODE NO_BACKSLASH_ESCAPES.

MariaDB [test]> select f from t where f REGEXP '(\\s*\\"[^\\"]+\\"\\s*\\:\\s*\\"\\s*2\\s*\\"\\s*,?)';
 
+---------------------------------------+
 
| f                                     |
 
+---------------------------------------+
 
| {"fooa":"2", "foob": "2","fooc":"1"}  |
 
| {"fooa": "2", "foob": "2","fooc":"1"} |
 
+---------------------------------------+
 
2 rows in set (0.002 sec)
 
 
 
MariaDB [test]> set sql_mode='NO_BACKSLASH_ESCAPES';
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> select f from t where f REGEXP '(\s*\"[^\"]+\"\s*\:\s*\"\s*2\s*\"\s*,?)';
 
+---------------------------------------+
 
| f                                     |
 
+---------------------------------------+
 
| {"fooa":"2", "foob": "2","fooc":"1"}  |
 
| {"fooa": "2", "foob": "2","fooc":"1"} |
 
+---------------------------------------+
 
2 rows in set (0.002 sec)

Generated at Thu Feb 08 08:44:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.