"Why aren't all of my results being returned?" - Optimization!

"Why aren't all of my results being returned?" - Optimization!

  
Take the following scenario:

1. Place a query in the Preparation of a Screen.
2. Bind a ListRecords or TableRecords Widget to the query. Give that widget a linecount property bound to a local variable (let's initialize it at 10 for the time being).
3. Add a "View More" link to the screen.
4. Bind the "View More" link to an action that increases the linecount variable by 10, then refreshes the Widget.

Expectation: the initial query pulls all records (let's say 50 match it) and on each click of "View more", an additional 10 records are displayed.

Actual result: the initial query pulls 11 records. The first view shows 10 records, clicking "View more" shows 11 records total.

To make it more confusing... using "Test Query" in Service Studio shows the expected number of results!

Here's what's happening:

OPTIMIZATION

You see, the OutSystems Platform is INSANELY smart. Smarter than probably 95% of developers are, and certainly more consistent than 99% of developers at following well-thought-out best practices.

In this case, the platform sees that the query is bound to a list, and limits it *automatically* to linecount + 1 (I suspect the + 1 allows it to make some smart decisions about pagination and such if needed, though I am not 100% sure). You don't realize this unless you pop SQL Server Profiler on the SQL Server and watch for the query.

This is an excellent optimization probably 99% of the time, but in the very rare scenario (like the one described above), it doesn't make sense.

The workaround, should you need to use one, is to set the "Max Records" of the query to linecount + 1, and on each "View More", increment the line count by the desired amount, then refresh the query.

J.Ja