<foreach> does not properly close IN-clause if the collection is empty.

[gitolathe]

The <foreach> tag changed behavior between version 3.2.7 and 3.2.8. Having a <select> like below in a mapper file:

<select id="getReports" parameterType="list" resultMap="reportResultMap">
    select * from REPORTS where reportId IN
    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
        #{item.id}
    </foreach>
</select>

the IN-clause is rendered invalid with version 3.2.8 but works just fine with 3.2.7.

It seems quite likely that the changed behavior is caused by the fix for #195 which issued the if-statement

    if (!iterable.iterator().hasNext()) {
      return true;
    }

to be added to ForEachSqlNode.apply() in commit abf2ef3616 at row 55.

3.2.7
The IN clause is properly closed with an empty collection:

select * from REPORTS where REPORTID IN ( ) 

which is valid SQL.

3.2.8
The open and close attributes are not applied thus the SQL is not valid since there are no opening and ending parenthesis following the IN:

### Error querying database.  Cause: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT * FROM REPORTS WHERE REPORTID IN [*]"; expected "("; SQL statement:

[harawata]

Hi Ola,

Thank you for the report, but I must say that your statement relied on the incorrect behavior, unfortunately.
You may have to add <if /> or <choose /> elements to check the list parameter.

By the way, I have tested your query generated by 3.2.7 using http://sqlfiddle.com/ and it worked only on SQLite.

Regards,
Iwao

[harawata]

...or just enclose the foreach element with the parenthesis.

<select id="getReports" parameterType="list" resultMap="reportResultMap">
    select * from REPORTS where reportId IN (
    <foreach item="item" index="index" collection="list" separator=",">
        #{item.id}
    </foreach>
    )
</select>

[gitolathe]

Hi Harawata,

Thanks for the response and it seems like you're correct with respect to the SQL syntax. Apparently at least one value expression must be provided (Second Informal Review Draft) ISO/IEC 9075:1992, Database
Language SQL- July 30, 1992 so I've learned something but on the other hand I must also say that from a general point of view it's a bit odd that the change in #195 was accepted adding a special case to the <foreach> behavior.

Generally an empty list is also a list and having attributes that open and close the list should apply also to the empty case. It would be more reasonable to use the <if> statement to avoid unwanted behavior as the one described in #195 especially since it changes the behavior of <foreach> between 3.2.7 and 3.2.8 regardless of it actually rendering invalid SQL in this particular case.

8.4  <in predicate>

Function

Specify a quantified comparison.

Format

<in predicate> ::=
    <row value constructor>
      [ NOT ] IN <in predicate value>

<in predicate value> ::=
      <table subquery>
    | <left paren> <in value list> <right paren>

<in value list> ::=
    <value expression> { <comma> <value expression> }...

[harawata]

Thank you for the reference, Ola!

If you follow the link in #195 summary, you will find that the old behavior is inconsistent with iBATIS 2.x and two developers reported it as a regression.
And no one posted any objection, so I would consider it was a community's decision.

Regards,
Iwao