Incorrect results from mybatis union query when primary key is included in result set！

[cenlm]

The following data tables and data are available:

SET FOREIGN_KEY_CHECKS=0;

---

-- Table structure for users

---

DROP TABLE IF EXISTS users;
CREATE TABLE users (
user_id varchar(64) NOT NULL COMMENT '用户id',
username varchar(50) NOT NULL COMMENT '用户名',
password varchar(128) NOT NULL COMMENT '用户密码',
user_alias varchar(50) DEFAULT NULL COMMENT '用户别名',
is_enable int(1) NOT NULL DEFAULT '1' COMMENT '账号是否可用（1：可用 0：禁用）',
sex int(1) NOT NULL DEFAULT '1' COMMENT '性别（0：女 1：男）',
cell_phone_number varchar(15) DEFAULT NULL COMMENT '手机号',
address varchar(100) DEFAULT NULL COMMENT '住址',
entry_date varchar(25) DEFAULT NULL COMMENT '登录日期',
last_login varchar(25) DEFAULT NULL COMMENT '上次登录日期',
email varchar(100) DEFAULT NULL COMMENT '电子邮件',
is_super int(1) DEFAULT '0' COMMENT '是否超级管理员（0：否 1：是）',
create_time varchar(25) DEFAULT NULL COMMENT '创建日期',
modify_time varchar(25) DEFAULT NULL COMMENT '修改日期',
remarks varchar(255) DEFAULT NULL COMMENT '备注信息',
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

---

-- Records of users

---

INSERT INTO users VALUES ('00dc3d392604422a91850d6c4f9071ec', '司马光', 'd93ae65992caf6a8751e334d0a716ad8', '司马光', '1', '1', '15678905678', '广东省广州天河区棠下街18号', '2024-07-06 16:41:55', '2024-07-06 16:39:15', '[email protected]', '0', '2023-03-04 23:02:46', '2024-07-06 15:39:17', '总部采购部经理。');

---

-- Table structure for organization

---

DROP TABLE IF EXISTS organization;
CREATE TABLE organization (
org_id varchar(64) NOT NULL COMMENT '组织ID',
org_name varchar(100) NOT NULL COMMENT '组织名称',
org_type varchar(20) NOT NULL COMMENT '组织类型（1：总公司 2：分公司 3：部门）',
org_code varchar(20) DEFAULT NULL COMMENT '组织编码',
parent_id varchar(64) NOT NULL COMMENT '上级组织ID',
position int(3) DEFAULT NULL COMMENT '节点位置，越小越靠前',
icon varchar(50) DEFAULT NULL COMMENT '字体图标',
is_super int(2) DEFAULT '0' COMMENT '是否是超级管理员节点（1 是 0 不是）',
PRIMARY KEY (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='组织表（有上下级关系）';

---

-- Records of organization

---

INSERT INTO organization VALUES ('9ffc66b087a145348e52028a0d64bf3b', '玉兔公司总部', '1', '', '-1', '1', 'layui-icon layui-icon-home', '0');
INSERT INTO organization VALUES ('3e542424f7ee45d988aab05152400328', '采购部', '3', '', '9ffc66b087a145348e52028a0d64bf3b', '0', 'layui-icon layui-icon-home', '0');

SET FOREIGN_KEY_CHECKS=0;

---

-- Table structure for post

---

DROP TABLE IF EXISTS post;
CREATE TABLE post (
post_id varchar(64) NOT NULL COMMENT '职位ID',
post_name varchar(20) NOT NULL COMMENT '职位名称',
post_code varchar(64) DEFAULT NULL COMMENT '职位编码',
parent_id varchar(64) NOT NULL COMMENT '上级职位ID',
org_id varchar(64) NOT NULL COMMENT '所属组织ID',
icon varchar(50) DEFAULT NULL COMMENT '字体图标',
position int(3) DEFAULT NULL COMMENT '节点位置，越小越靠前',
PRIMARY KEY (post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='职位表';

---

-- Records of post

---

INSERT INTO post VALUES ('ee4ac9b12a054088bb86687d50f8ddb7', '采购经理', '', '-1', '3e542424f7ee45d988aab05152400328', 'layui-icon layui-icon-user', '2');

SET FOREIGN_KEY_CHECKS=0;

---

-- Table structure for user_post

---

DROP TABLE IF EXISTS user_post;
CREATE TABLE user_post (
user_id varchar(64) NOT NULL COMMENT '用户ID',
post_id varchar(64) NOT NULL COMMENT '职位ID',
PRIMARY KEY (user_id,post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户-职位关联表';

---

-- Records of user_post

---

INSERT INTO user_post VALUES ('00dc3d392604422a91850d6c4f9071ec', 'ee4ac9b12a054088bb86687d50f8ddb7');

The java entity class has:

User.java
`public class User implements Serializable {

private static final long serialVersionUID = 4513862602468058360L;
private String userId;
/**
 * 用户名
 */
private String userName;
/**
 * 用户别名
 */
private String userAlias;
/**
 * 用户性别 0：女 1：男
 */
private Integer sex;
/**
 * 用户手机号码
 */
private String cellPhoneNumber;
/**
 * 用户住址
 */
private String address;
/**
 * 用户登录时间
 */
private String entryDate;
/**
 * 用户上次登录时间
 */
private String lastLogin;
/**
 * 用户是否是超级管理员 1：是 0：否
 */
private Integer isSuper;

/**
 * 密码
 */
private String password;
/**
 * 邮箱地址
 */
private String email;
/**
 * 1:可用 0：不可用
 */
private Integer isEnable;
/***
 * 创建时间
 */
private String createTime;
/**
 * 修改时间
 */
private String modifyTime;
/**
 * 备注
 */
private String remarks;

private Post post;


public String getUserId() {
return userId;
}

public void setUserId(String userId) {
this.userId = userId;
}

public String getUserName() {
return userName;
}

public void setUserName(String userName) {
this.userName = userName;
}

public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getRemarks() {
return remarks;
}

public void setRemarks(String remarks) {
this.remarks = remarks;
}

public String getCreateTime() {
return createTime;
}

public void setCreateTime(String createTime) {
this.createTime = createTime;
}

public String getModifyTime() {
return modifyTime;
}

public void setModifyTime(String modifyTime) {
this.modifyTime = modifyTime;
}

public String getUserAlias() {
return userAlias;
}

public void setUserAlias(String userAlias) {
this.userAlias = userAlias;
}

public String getCellPhoneNumber() {
return cellPhoneNumber;
}

public void setCellPhoneNumber(String cellPhoneNumber) {
this.cellPhoneNumber = cellPhoneNumber;
}

public String getAddress() {
return address;
}

public void setAddress(String address) {
this.address = address;
}

public String getEntryDate() {
return entryDate;
}

public void setEntryDate(String entryDate) {
this.entryDate = entryDate;
}

public String getLastLogin() {
return lastLogin;
}

public void setLastLogin(String lastLogin) {
this.lastLogin = lastLogin;
}

public Integer getSex() {
return sex;
}

public void setSex(Integer sex) {
this.sex = sex;
}

public Integer getIsSuper() {
return isSuper;
}

public void setIsSuper(Integer isSuper) {
this.isSuper = isSuper;
}

public Integer getIsEnable() {
return isEnable;
}

public void setIsEnable(Integer isEnable) {
this.isEnable = isEnable;
}



public Post getPost() {
return post;
}

public void setPost(Post post) {
this.post = post;
}

}`

Post.java
`public class Post implements Serializable {
private static final long serialVersionUID = -1256403720282748631L;
/**
* 职位ID
/
private String postId;
/*
* 职位名称
/
private String postName;
/*
* 职位编码
/
private String postCode;
/*
* 上级职位ID
/
private String parentId;
/*
* 父节点名称
/
private String parentName;
/*
* 所属组织ID
/
private String orgId;
/*
* 职位字体图标
/
private String icon;
/*
* 节点位置，最小值1，越小越靠前
*/
private Integer position;

public String getPostId() {
return postId;
}

public void setPostId(String postId) {
this.postId = postId;
}

public String getPostName() {
return postName;
}

public void setPostName(String postName) {
this.postName = postName;
}

public String getPostCode() {
return postCode;
}

public void setPostCode(String postCode) {
this.postCode = postCode;
}

public String getParentId() {
return parentId;
}

public void setParentId(String parentId) {
this.parentId = parentId;
}

public String getOrgId() {
return orgId;
}

public void setOrgId(String orgId) {
this.orgId = orgId;
}

public String getIcon() {
return icon;
}

public void setIcon(String icon) {
this.icon = icon;
}

public Integer getPosition() {
return position;
}

public void setPosition(Integer position) {
this.position = position;
}

public String getParentName() {
return parentName;
}

public void setParentName(String parentName) {
this.parentName = parentName;
}

}`

UserMapper.java
public interface UserMapper { List<User> queryUserDetail(Map<String, String> map); }

userMapper.xml
`

SELECT u.user_id user_id,u.username username,u.email email,u.cell_phone_number cell_phone_number ,u.address address,u.user_alias user_alias,concat(o.org_name,'-',p.post_name) post_name,u.create_time create_time,u.modify_time modify_time, u.entry_date entry_date,u.last_login last_login,u.is_enable is_enable,u.sex sex,u.remarks remarks from users u JOIN user_post up on u.user_id=up.user_id JOIN post p on up.post_id=p.post_id JOIN organization o ON o.org_id=p.org_id u.user_id=#{userId} u.username=#{userName} union SELECT u.user_id user_id,u.username username,u.email email,u.cell_phone_number cell_phone_number ,u.address address,u.user_alias user_alias,'无' post_name,u.create_time create_time,u.modify_time modify_time, u.entry_date entry_date,u.last_login last_login,u.is_enable is_enable,u.sex sex,u.remarks remarks from users u u.user_id=#{userId} u.username=#{userName} </select> ` Why does the following sql statement return two records when executed in a command line window, but only one record when executed in mybatis? But if the entire select statement does not query the u.user_id user_id attribute, i.e., something like SELECT u.user_id user_id,u.username username,u.email email..., the result of the query is correct.I looked at the mybatis source code, and I found that the storeObject(resultHandler, resultContext, rowValue, parentMapping, resultSet) method of the handleRowValuesForNestedResultMap method of DefaultResultSetHandler is executed only once, and the properties in the Post of the first User object in the ResultSet result set are overwritten by the Post properties of the subsequent parsed User objects. Is there no problem with the logic of the DefaultResultSetHandler#handleRowValuesForNestedResultMap method? Looking forward to mybatis developers to solve this problem, thanks! The sql query statement is as follows: SELECT u.user_id user_id,u.username username,u.email email,u.cell_phone_number cell_phone_number ,u.address address, u.user_alias user_alias,concat(o.org_name,'-',p.post_name) post_name,u.create_time create_time,u.modify_time modify_time, u.entry_date entry_date,u.last_login last_login,u.is_enable is_enable,u.sex sex,u.remarks remarks from users u JOIN user_post up on u.user_id=up.user_id JOIN post p on up.post_id=p.post_id JOIN organization o ON o.org_id=p.org_id WHERE u.user_id='00dc3d392604422a91850d6c4f9071ec' union SELECT u.user_id user_id,u.username username,u.email email,u.cell_phone_number cell_phone_number ,u.address address, u.user_alias user_alias,'无' post_name,u.create_time create_time,u.modify_time modify_time, u.entry_date entry_date,u.last_login last_login, u.is_enable is_enable,u.sex sex,u.remarks remarks from users u WHERE u.user_id='00dc3d392604422a91850d6c4f9071ec'

[harawata]

Hello @cenlm ,

The example is pretty complex, so I just took a quick look [1].

There is <id property="userId" column="user_id" /> in the result map, so user_id is used to identify the result object.
In other words, rows with the same user_id are aggregated into a single User.
https://mybatis.org/mybatis-3/sqlmap-xml.html#id-result

I'm going to close this as it is clearly not a bug.

I usually propose a solution, but I do not understand the design just by looking at your example.
I'm not sure why you expect two User instances for the same user.
It is unclear what you expect when there are multiple posts for a user.
In case you need further assistance with the mapping, please clarify these points.

[1] Here are some guides for writing a good example.

• https://stackoverflow.com/help/minimal-reproducible-example
• https://sscce.org