'LIMIT 1 FOR UPDATE' is automatically replaced with 'FOR UPDATE LIMIT 1' in version 3.5.16

[zhuwenbing]

MyBatis version

3.5.16

Database vendor and version

MySQL [5.7.26]

Test case or example project

    @Select({
      "<script>",
      "SELECT * FROM tms_fin_price",
      "WHERE",
      "<if test=\"id != null\">",
          "id &lt;&gt; #{id}",
        "AND",
      "</if>",
          "state = 1",
        "AND",
          "pid = #{productId}",
        "AND",
          "said = #{startAddrId}",
        "AND",
          "eaid = #{endAddrId}",
        "AND",
        "<if test=\"startTime == endTime\">",
          "stime &lt;= #{startTime} AND etime &gt;= #{startTime}",
        "</if>",
        "<if test=\"startTime != endTime\">",
          "(",
            "(stime &lt;= #{startTime} AND etime &gt;= #{startTime})",
              "OR",
            "(stime &lt;= #{endTime} AND etime &gt;= #{endTime})",
          ")",
        "</if>",
        "AND",
          "dflag = '0'",
        "LIMIT 1 FOR UPDATE",
        "</script>"
    })
    FinancialPrice getValidFinancialPrice(FinancialPrice financialPrice) throws Exception;

Steps to reproduce

I've tested it already; using the @select annotation or writing the SQL in XML yields the same result.
By the way, in version 3.5.9, executing the same SQL was fine, but when I upgraded to version 3.5.16, issues arose. I don't know why it would modify the SQL statement.

Expected result

Execute "LIMIT 1 FOR UPDATE" in the end of the SQL.

Actual result

Execute "FOR UPDATE LIMIT 1" in the end of the SQL.
You can see in the error log that it indeed modified the SQL statement executed in the end, as follows:

 ### Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1 ### The error may exist in com/example/tms/mapper/FinancialPriceMapper.java (best guess) ### The error may involve com.example.tms.mapper.FinancialPriceMapper.getValidFinancialPrice-Inline ### The error occurred while setting parameters ### SQL: SELECT * FROM tms_fin_price WHERE id <> ? AND state = 1 AND pid = ? AND said = ? AND eaid = ? AND ((stime <= ? AND etime >= ?) OR (stime <= ? AND etime >= ?)) AND dflag = '0' FOR UPDATE LIMIT 1 ### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1

[harawata]

Hello @zhuwenbing ,

MyBatis never rewrites SQL like that.
Aren't you using some kind of third party library like mybatis-plus or common-mapper?

[zhuwenbing]

@harawata Yes, I'm using MyBatis-Plus. Sorry, I got a bit confused about where the issue originated. Initially, I followed the usual practices in MyBatis with @select annotation and XML. I apologize again. I'll redirect this issue to MyBatis-Plus shortly. Thank you for your prompt assistance!

[zhuwenbing]

Found a similar issue in the issue list of the MyBatis-Plus repository. It appears to be a bug related to JSqlParser, not related to MyBatis or MyBatis-Plus. There's no workaround until the bug is fixed.