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

I don't know if this is the problem, but it's something to try. I have to post here because I don't have enough rep to leave a comment.

Try adding a column attribute to your association and marking the ID column in the second map:

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

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