search

hbwf / mybatis

Automatically exported from code.google.com/p/mybatis
0 stars 0 forks source link

call the same sql twice,but return different values #805

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
I want to list users by property 'ownersystemId' with paging.
And I create a general form of select sql as follows:
SELECT
      [it omits field names here]
        FROM
        user
        LEFT JOIN
        user_userGroup ON user.id=user_userGroup.userId
        LEFT JOIN
        userGroup ON userGroup.id=user_userGroup.userGroupId
        LEFT JOIN
        action_userGroup ON action_userGroup.userGroupId=userGroup.id
        LEFT JOIN
        action ON action_userGroup.actionId=action.id
        LEFT JOIN
        address ON address.userId=user.id
        WHERE user.id IN
        (
        SELECT
        id
        FROM
        (
        SELECT
        id
        FROM
        user
        <where>
            <foreach collection="list" item="item">
                AND ${item.key}=#{item.value}
            </foreach>
        </where>
        ORDER BY user.id DESC
        <if test="start!=null and limit!=null">
            LIMIT #{start},#{limit}
        </if>
        ) AS newTable
        )
        ORDER BY
        user.id DESC,userGroup.id DESC,action.id DESC

And I send a list which has a key/value pair,here is 'ownersystem=1'.Then start 
is set 0 and limit is set 10.

The expected data number is 4;
but when i call the select sql more than once,the return data number often 
changes.
It confused me very much.
Who could help me?
Thank you!

Original issue reported on code.google.com by DSL...@gmail.com on 9 Apr 2013 at 1:42

Attachments:

GoogleCodeExporter commented 9 years ago 
First, enable logging to see what is being sent and got to and from the DB.

Then try to reproduce that behaviour with a test:

https://code.google.com/p/mybatis/wiki/Test

Original comment by eduardo.macarron on 9 Apr 2013 at 6:49

GoogleCodeExporter commented 9 years ago 
No reply.

Original comment by eduardo.macarron on 16 Apr 2013 at 6:21