当前使用版本(必填,否则不予处理)
3.5.3.2
该问题是如何引起的？(确定最新版也有问题再提!!!)
切换最新版也有问题，使用Page对象自定义SQL分页时出现查询SQL与xmlSQL不一致。
重现步骤(如果有就写完整)
`表sql： create table sys_base_account ( base_id bigint auto_increment comment 'base_id' primary key, user_id bigint null comment '归属用户id', base_name varchar(50) null comment '用户名', password varchar(100) null comment '用户密码', create_by bigint null comment '创建人id', create_time datetime null comment '创建时间', update_by bigint null comment '修改人id', update_time datetime null comment '修改时间', expire_time datetime null comment '到期时间', status tinyint default 1 null, is_delete tinyint(1) default 0 null comment '逻辑删除（0：正常、1：删除）' ) comment '账户' charset = utf8;
create table sys_user ( user_id bigint auto_increment comment '用户id' primary key, username varchar(50) charset utf8mb4 not null comment '用户名', password varchar(100) charset utf8mb4 not null comment '密码', email varchar(100) charset utf8mb4 null comment '邮箱', mobile varchar(50) charset utf8mb4 null comment '手机号', member_level tinyint null comment '会员等级（1：普通、2：超级会员）', status tinyint default 1 null comment '状态', create_time datetime null comment '创建时间', update_by bigint null comment '修改人id', update_time datetime null comment '修改时间', expire_time datetime null comment '账号失效时间', remark varchar(64) null comment '备注信息', is_delete tinyint(1) default 0 null comment '逻辑删除（0：正常、1：删除）', constraint email_uni_pk unique (email), constraint mobile_uni_pk unique (mobile), constraint sys_user_pk unique (username) ) comment '用户信息' charset = utf8;
create table sys_login_log ( log_id bigint auto_increment comment 'login_log_id' primary key, event_class tinyint not null comment '事件大类id（1：user、2：基站、3：移动站）', operation_id bigint null comment '操作人id', ip varchar(16) null comment 'ip', reason varchar(50) null comment '原因', login_time datetime null comment '登录时间', logout_time datetime null comment '登出时间', upload_size bigint null comment '当前值（基站记录上传大小，移动站记录上传GGA条数）' ) comment '登录日志' charset = utf8; `
xml sql：
select
a1.*, IF(ISNULL(logout_time), 1, 0) onlineStatus, a2.login_time lastOnlineDateTime, a2.logout_time
lastOfflineDateTime, a2.upload_size onlineUploadSize
from
(
select
a.base_id, a.base_name, a.password, a.remark, a.status, a.covering_radius, a.create_time,
a.expire_time, b.username, b.user_id
from
sys_base_account a left join sys_user b on a.user_id = b.user_id
where
a.is_delete = 0
<if test="qo.status != null">
and a.status = #{qo.status}
</if>
<if test="qo.baseName != null and qo.baseName != ''">
and a.base_name like concat('%', #{qo.baseName}, '%')
</if>
<if test="qo.createDateStart != null and qo.createDateEnd != null ">
and a.create_time between #{qo.createDateStart} and #{qo.createDateEnd}
</if>
<if test="qo.expireDateStart != null and qo.expireDateEnd != null ">
and a.expire_time between #{qo.expireDateStart} and #{qo.expireDateEnd}
</if>
and b.status = 1
and b.is_delete = 0
and b.expire_time > now()
<if test="qo.username != null and qo.username != ''">
and b.username like concat('%', #{qo.username}, '%')
</if>
)
a1 left join
(
select
operation_id, login_time, logout_time, upload_size
from
sys_login_log
where
event_class = 2
group by
operation_id
having
max(login_time)
) a2 on a1.base_id = a2.operation_id
where
not isnull(a1.user_id)
<if test="qo.loginDateStart != null and qo.loginDateEnd != null ">
and login_time between #{qo.loginDateStart} and #{qo.loginDateEnd}
</if>
<if test="qo.onlineStatus != null">
<if test="qo.onlineStatus = 0">
and not isnull(logout_time)
</if>
<if test="qo.onlineStatus = 1">
and isnull(logout_time)
</if>
</if>
order by a1.create_time desc
报错信息
sql1：SELECT COUNT(*) AS total FROM (SELECT a.base_id, a.base_name, a.password, a.remark, a.status, a.covering_radius, a.create_time, a.expire_time, b.username, b.user_id FROM sys_base_account a LEFT JOIN sys_user b ON a.user_id = b.user_id WHERE a.is_delete = 0 AND a.status = '1' AND a.create_time BETWEEN '2023-09-30' AND '2023-10-31' AND a.expire_time BETWEEN '2023-09-30' AND '2025-11-19' AND b.status = 1 AND b.is_delete = 0 AND b.expire_time > now()) a1 WHERE NOT isnull(a1.user_id) AND login_time BETWEEN '2023-09-30' AND '2023-11-30' AND isnull(logout_time);
sql2：SELECT COUNT(*) AS total FROM (SELECT a.base_id, a.base_name, a.password, a.remark, a.status, a.covering_radius, a.create_time, a.expire_time, b.username, b.user_id FROM sys_base_account a LEFT JOIN sys_user b ON a.user_id = b.user_id WHERE a.is_delete = 0 AND a.status = ? AND a.create_time BETWEEN ? AND ? AND a.expire_time BETWEEN ? AND ? AND b.status = 1 AND b.is_delete = 0 AND b.expire_time > now()) a1 WHERE NOT isnull(a1.user_id) AND login_time BETWEEN ? AND ? AND isnull(logout_time)
Comment From: miemieYaho
debug插件看看改造前的sql是什么,不要发xml的sql
Comment From: YiMoWanXia
找到问题了，optimizeJoinOfCountSql 优化导致左联被去掉了