Do not understand why MariaDB rejected REGEXP with 42000 You have an error in your SQL syntax - Source

Revision: 93398
User: James Cobban
Date: 2020-02-19 15:48

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

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Do not understand why MariaDB rejected REGEXP with 42000 You have an error in your SQL syntax - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB