jooq binding within regexp pattern (MySQL)

I have a small Java service connected to a MySQL DB which stores documents. Each document has a name, and when a document is duplicated, the new document gets the name <previous name> (#). The documents table looks something like this:

I have a working MySQL query which selects a document and all of its duplicates, which I am attempting to translate into jooq.

select * from documents
where name regexp '(TITLE_GOES_HERE|TITLE_GOES_HERE \\([:digit:]+\\))$';

-- For instance, to select rows 1, 2, and 3 of the above table,
-- I would replace `TITLE_GOES_HERE` with `A Document`, resulting
-- in the following MySQL query:
select * from documents
where name regexp '(A Document|A Document \\([:digit:]+\\))$';

> Note: If you would like a working regex tester, you can find it here. It's slightly different than the MySQL version, but it suffices for demonstration purposes.

In order for this query to work programmatically, I have to replace "TITLE_GOES_HERE" in the regular expression with the name of the document for which I want to find. However, that throws an unexpected error in jooq.

public List<Document> getOriginalAndDuplicates(String docName) {
  SelectQuery<DocumentRecord> select = getDSLContext().selectQuery(DOCUMENT);

  select.addCondition(
    DSL.condition(
      DSL.field(
        // Binding for both the document AND the name
        // Notice that the name binding is inside the regular expression
        "{0} regexp '({1} \\\\([:digit:]+\\\\))'",
        Boolean.class,
        DOCUMENT.NAME,
        docName)));

  Result<DocumentRecord> records = select.fetch();
  List<Document> docs = records.stream().map(documentConverter::convertTo).toList();
  return docs;
}

When I run this, I get the following error:

Caused by: org.jooq.exception.DataAccessException: SQL [select document.id, document.name from document where (document.name regexp '({1} \\([:digit:]+\\))')]; Syntax error in regular expression on line 1, character 2.
	at org.jooq_3.14.16.MYSQL.debug(Unknown Source)
	at org.jooq.impl.Tools.translate(Tools.java:2903)
	at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:757)
	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:389)
	... 27 common frames omitted
Caused by: java.sql.SQLException: Syntax error in regular expression on line 1, character 2.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:555)
	at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:339)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:214)
	at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4217)
	at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:283)
	at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:375)
	... 27 common frames omitted

The error is occurring because the first binding ({1}) is not being replaced with the document name.

Has anyone ever encountered anything like this?

> Additional info: When I replace the {1} with a hardcoded value, such as A Document, the query succeeds without any problems. It only causes problems when I try to bind the {1} to a variable value.

Why your plain SQL template didn't work

A jOOQ plain SQL template will not make any substitutions in string literals or comments, and other things. As per the documentation:

> ## Parsing rules
> When processing these plain SQL templates, a mini parser is run that handles things like
> - String literals
> - Quoted names
> - Comments
> - JDBC escape sequences
> - Indexed (?) or named (:identifier) bind variable placeholders
>
> The above are recognised by the templating engine and contents inside of them are ignored when replacing numbered placeholders and/or bind variables.

Your template has to be written like this:

DSL.field(
    "{0} regexp concat('(', {1}, '\\\\([:digit:]+\\\\))'",
    Boolean.class,
    DOCUMENT.NAME,
    docName
)

Note the number of \\ needed depends on the NO_BACKSLASH_ESCAPES setting, which you can also configure in jOOQ. jOOQ handles this automatically if you're not using a plain SQL template.

Using jOOQ API instead

But why bother using a template? jOOQ has native support for this operator using Field.likeRegex():

DOCUMENT.NAME.likeRegex(concat(val("("), val(docName), val("\\([:digit:]+\\))")))