Mix of cursor + stream + intermediate operation seems to be broken

[mjeanroy]

MyBatis version

3.4.6

Database vendor and version

Not relevant.

Test case or example project

https://github.com/mjeanroy/mybatis-issue

Steps to reproduce

Checkout the project, then run unit tests in TeamDaoRepositoryTest: one test is green, the other is here to show the bug.

Expected result

Cursor should work with stream when handling SQL nested results.

Actual result

Well, it does not work (see below for the full analysis).

Analysis

Hi everyone,

I found a bug with cursor when the cursor result is translated to a stream and when intermediate operation such as map transform the input to a new output.

Here is the scenario:

• Suppose two tables: a team table and a member table (OneToMany relationship).
• Suppose this simple query to fetch all teams with members: SELECT team.id as id, team.name as name, member.id as member_id, member.name as member_name FROM team LEFT OUTER JOIN member ON member.team_id = team.id.
• Suppose this query returns the following results:

id	name	member_id	member_name
1	The Avengers	1	Iron Man
1	The Avengers	2	Hulk
1	The Avengers	3	Thor

• This result set is mapped to two classes: Team which contains a list of Member. The mybatis mapper is defined according to this mapping.

• Now suppose this code to get all results:

try (SqlSession session = sqlSessionFactory.openSession()) {
    Cursor<TeamEntity> cursor = session.selectCursor("com.github.mjeanroy.mybatisissue.TeamEntity.findAll");
    return StreamSupport.stream(cursor.spliterator(), false).collect(Collectors.toList());
}

• So far so good, everything is ok and the Team instance contains all members in database (check this unit test).

• Now, suppose that for some reason, you apply an intermediate operation and you want to map each result to a new object:

try (SqlSession session = sqlSessionFactory.openSession()) {
    Cursor<TeamEntity> cursor = session.selectCursor("com.github.mjeanroy.mybatisissue.TeamEntity.findAll");
    return StreamSupport.stream(cursor.spliterator(), false)
        .map(team -> new TeamDto(team))
        .collect(Collectors.toList());
}

• Now, when the map operation is executed, the team only contains one member.

What I understand

I checked the source code of mybatis and here is my understanding of this bug:

• When the cursor.spliterator() is executed, an iterator from DefaultCursor cursor is created.
• The map operation is registered into the stream pipeline.
• When the collect operation is executed, the stream will start to execute each operation one by one:
  1- The iterator#hasNext operation is executed.
  2- The DefaultCursor#Iterator#fetchNextUsingRowBound method is executed and retrieved the first row available in the cursor.
  3- The map operation is executed: at this point the team instance only contains one member, and it seems logical since only the first cursor row has been fetched.
  4- The result of the map operation is added to the final list created by Collectors.toList, the one that will be returned by the collect operation.
  5- The stream check for next results in the cursor, so call again the iterator#hasNext method.
  6- Since there is other results available in the cursor, the DefaultCursor#Iterator#fetchNextUsingRowBound method is executed again, other members are merged into the previous team instance and added to the Team#members list.

So, at the end of the operation, we lost the all nested results, since each operations of the stream is executed against the first cursor row only.

A fix could be to read the nested results the first time the fetchNextUsingRowBound is called (and not wait for the next call to hasNext to merge it into the previous result), of course: easier to said than to implement, and I don't know what could be all the impacts.

I hope I'm clear in my explanation, please let me know if not.

I created a very simple reproduction of this bug, you can checkout the repository here: https://github.com/mjeanroy/mybatis-issue (just run unit tests to reproduce the error).

[kazuki43zoo]

Probably, this issue can resolve to specify the resultOrdered="true" on <select> element.
About details of the Cursor feature, please see #437 (comment).

[mjeanroy]

@kazuki43zoo Awesome, it looks like this parameter fix evertything, sorry I missed this one ;)