MyBatis Issue with <where> tag not filtering the first AND or OR when immediately followed by a left parenthesis

Dear MyBatis Team,

I am encountering an issue with the <where> tag in MyBatis, specifically when it is used in a scenario where the first condition starts with an AND or OR immediately followed by a left parenthesis (. In such cases, the <where> tag does not seem to properly filter out this initial logical operator, which can lead to unexpected SQL queries and potential runtime errors.

Here is a concise example to illustrate the problem:

<select id="testAndWithParentheses" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"/>
    from cm_user
    <where>
        AND(
            1 OR NULL
        )
    </where>
</select>

In this example, the generated SQL query would include the leading AND, which is incorrect:

SELECT * FROM cm_user WHERE AND ( 1 OR NULL )

The correct approach is to add a space after the first AND or the first OR in the <where> tag

<select id="testAndWithParentheses" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"/>
    from cm_user
    <where>
        AND (
            1 OR NULL
        )
    </where>
</select>

In this example, the generated SQL query would't have the leading AND, which is correct:

SELECT * FROM cm_user WHERE  ( 1 OR NULL )

I believe this is a bug in the MyBatis <where> tag's implementation and should be addressed to ensure that SQL queries are generated correctly and consistently.

Could you please investigate this issue and consider making a fix in a future release of MyBatis? Thank you for your attention to this matter.

Best regards

Comment From: harawata

Hello @slySoap ,

A space is required after the AND. The string substitution logic is pretty simple and we should keep it simple.

In SQL, if you add ( after some word, it could be treated as a function (e.g. RAND(), so you should always add a space after AND (or OR).

Comment From: slySoap

Hi @harawata , Your points are spot on, I agree. Thanks for your answer.