Do not understand why MariaDB rejected REGEXP with 42000 You have an error in your SQL syntax
When I issue the following prepared statement:
SELECT COUNT(*) FROM MethodistBaptisms WHERE LOCATE(:district1, `district`) > 0 AND `area` REGEXP :area1
with the following parameters:
Array ( 'district1' => 'Oxford & Middlesex', 'area1' => 'Nissouri Twp.E&W')
I get:
Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'REGEXP 'Nissouri Twp.E&W'' at line 1 )
However if I manually perform the insertions and issue the query:
SELECT COUNT(*) FROM MethodistBaptisms WHERE LOCATE('Oxford & Middlesex', `district`) > 0 AND `area` REGEXP 'Nissouri Twp.E&W'
it works. If I change the query to:
SELECT COUNT(*) FROM MethodistBaptisms WHERE district=:district1 AND `area` = :area1
with the same parameters, it also works. So there is something specific to the REGEXP which I do not understand. I also note that I am not receiving the form of the error message that I would get if there was an error in the regular expression.
I have tested this against MariaDB 10.3.20-MariaDB-0ubuntu0.19.10.1 and MySQL 5.7.28-0ubuntu0.18.04.4 and I get the same resu
Answer Answered by Diego Dupin in this comment.
It would be nice to create an issue https://jira.mariadb.org/projects/MDEV/issues/ with trace from wireshark, and php version.
I failed to reproduce the issue with latest php and according pdo driver, the only way to identify the issue is having wireshark log to identify if this is a server issue, connector issue or otherwise