MyBatis doesn't return all the results from the query

Question

The Problem

I have a query that returns 17 records. When I use MyBatis with a map that has an <association> it returns 6 records. Note that this doesn't happen with my other maps, I have many other maps with associations that all work fine.

Query:

    with leave_counts as 
    (
        select leave_type_id, count(llm.leave_id) as count
        from lw_leave_master llm
        group by leave_type_id
    )
    select llt.leave_type_id, llt.leave_type_abbr_tx, 
            llt.leave_type_desc_tx, lc.count as count_nm
    from lw_leave_type llt
        join leave_counts lc on lc.leave_type_id=llt.leave_type_id
    order by llt.leave_type_abbr_tx

Map:

<resultMap id="typeCountMap" type="mypackage.myclass">
    <result property="count" column="count_nm"/>
    <association property="type" resultMap="package.myMap"/>
</resultMap>


<resultMap id="myMap" type="mypackage.myclass2">
    <result property="id" column="leave_type_id"/>
    <result property="abbr" column="leave_type_abbr_tx"/>
    <result property="description" column="leave_type_desc_tx"/>
</resultMap>

The <association> in typeCountMap refers to the map myMap.

This returns 6 records every time. Grabbing the actual query run from the logger and running it manually returns 17 records.

Solutions?

There are two things I can do to get MyBatis to return all 17 records

#1

If I remove the lc.count as count_nm from my query I get all 17 records returned (just with no values associated with them)

    with leave_counts as 
    (
        select leave_type_id, count(llm.leave_id) as count
        from lw_leave_master llm
        group by leave_type_id
    )
    select llt.leave_type_id, llt.leave_type_abbr_tx, 
            llt.leave_type_desc_tx
    from lw_leave_type llt
        join leave_counts lc on lc.leave_type_id=llt.leave_type_id
    order by llt.leave_type_abbr_tx

This is obviously not a good solution, but I wanted to include this in case it would help you figure out what I'm doing wrong.

#2

If I replace the association with the contents of the other map everything works as expected.

<resultMap id="typeCountMap" type="mypackage.myclass1">
    <result property="count" column="count_nm"/>        
    <result property="type.id" column="leave_type_id"/>
    <result property="type.abbr" column="leave_type_abbr_tx"/>
    <result property="type.description" column="leave_type_desc_tx"/>
</resultMap>

This obviously is what I'll do if I don't find another solution, since this does work. It would just be nice to use the <association> like I have in other maps.

I should note that I am using MyBatis 3.1.1

Answer 1

I recently ran into this same problem. I believe the issue was related to my main resultMap with the association in it not having an id column while my association's resultMap did have an id column. My results were getting grouped by the associations id column.

To correct the issue I selected the row number in my query and added an id to my main resultMap pointing to the row number column.

Answer 2

If you're using 3.1.1 you can define just <id column="leave_type_id" /> without property attribute
If you're not using 3.1.1 you can add <result property="abbr" column="leave_type_id" /> on top of the list to include field into cache key calculation for association, later re-definition will assign correct value