[GROOVY-6665] maxRows in Groovy.sql.Sql goes one too far

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 2.2.2
• Fix Version/s: 2.3.0-beta-2, 2.2.3
• Labels: performance sql
• Assignee: Paul King
• Reporter: Erik Tennant
• Created: 04/Apr/14 12:18
• Updated: 10/Apr/14 17:16
• Resolved: 05/Apr/14 00:44

Description

Groovy sql supports a parameter of "maxRows" that will only iterate and return N rows from the result set. The code ends up calling the result set N+1 times though:

(line 1247 of Groovy.sql.Sql)

 while (groovyRS.next() && (maxRows <= 0 || i++ < maxRows)) {
                rowClosure.call(groovyRS);
            }

should be:

 while ((maxRows <= 0 || i++ < maxRows) && groovyRS.next() ) {
                rowClosure.call(groovyRS);
            }

By flipping the conditions in the conditional statement, the result set next() method will not be called after maxrows has been reached.

This is not critical, but there are a couple of situations where the current behavior is not optimal:
-Attempting to match "maxRows" and jdbcFetchSize for performance reasons. (you will end up with one unnecessary network call to the DB)
-Any query using row locking (i.e. "for update" in oracle) will cause an additional batch of records to be locked unnecessarily.