[HIBERNATE 5.4] Space is not allowed after parameter prefix ':'

i am getting the following error when using the user variable used by mysql in the orm.xml file.

TXT
Caused by: org.hibernate.HibernateException: Errors in named queries:
  failed because of: org.hibernate.QueryException: Space is not allowed after parameter prefix ':' [SELECT ... omitted]

• Space is not allowed after parameter prefix ':'

in the xml file, we use the user variable as shown below, but there is a conflict between the colon in the syntax to assign a value to the variable (:=) and the syntax used by hibernate to bind the value to the query statement.

SQL
    INNER JOIN (SELECT @n := 1, @sbj := -1) var (mysql syntax)
    WHERE p.deleted = 'N'
    GROUP BY p.subject, p.seq
    HAVING rnum <= :topK (hiberate syntax)

the colon after the user variable should be recognized as a regular character, but hiberate interprets it as a named parameter and throws an error

Solution

if you google it, you'll find a lot of articles that suggest putting two backspaces after the colon you assign to the user variable.

so what you should do is

SQL
    INNER JOIN (SELECT @n\\:= 1, @sbj\\:= -1) var (two backspaces before the colon)
    WHERE p.deleted = 'N'
    GROUP BY p.subject, p.seq
    HAVING rnum <= :topK (don't tack here)

when typing a query directly into Java code, you need to add an extra backspace to recognize \:, so most documentation says to add two backspaces.

however, since the current query is written in xml, we only need to put one backspace.

SQL
    INNER JOIN (SELECT @n\:= 1, @sbj\:= -1) var (one backspace before colon)
    WHERE p.deleted = 'N'
    GROUP BY p.subject, p.seq
    HAVING rnum <= :topK (don't append here)

Conclusion

if you wrote your query in Java code, you'll have two queries, and if you wrote it in XML, you'll have one