当前使用版本(必填,否则不予处理)

3.2.0

该问题是如何引起的？(确定最新版也有问题再提!!!)

com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor类的concatOrderBy方法在查询语句添加排序字段时未考虑存在with子语句情况，导致处理后sql丢掉with部分 mybatis plus存在with语句是分页排序问题

重现步骤(如果有就写完整)

Postgresql数据库sql存在with语句需要进行分页排序时就会出现

报错信息

mybatis打印sql丢掉with子语句部分

Comment From: 1176896650

目前处理方式为判断条件增加WithItemsList判断，若存在with子语句原SQL语句增加排序部分即可 public static String concatOrderBy(String originalSql, IPage<?> page) { if (CollectionUtils.isNotEmpty(page.orders())) { try { List orderList = page.orders(); Select selectStatement = (Select) CCJSqlParserUtil.parse(originalSql); if (selectStatement.getWithItemsList() != null && !selectStatement.getWithItemsList().isEmpty()) { StringBuilder orderSql = new StringBuilder(" order by"); String split = " "; for (final OrderItem item : orderList) { final OrderByElement element = new OrderByElement(); element.setExpression(new Column(item.getColumn())); element.setAsc(item.isAsc()); orderSql.append(split + element.toString()); split = ","; } return originalSql + orderSql; } else if (selectStatement.getSelectBody() instanceof PlainSelect) { PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody(); List orderByElements = plainSelect.getOrderByElements(); List orderByElementsReturn = addOrderByElements(orderList, orderByElements); plainSelect.setOrderByElements(orderByElementsReturn); return plainSelect.toString(); } else if (selectStatement.getSelectBody() instanceof SetOperationList) { SetOperationList setOperationList = (SetOperationList) selectStatement.getSelectBody(); List orderByElements = setOperationList.getOrderByElements(); List orderByElementsReturn = addOrderByElements(orderList, orderByElements); setOperationList.setOrderByElements(orderByElementsReturn); return setOperationList.toString(); } else if (selectStatement.getSelectBody() instanceof WithItem) { // todo: don't known how to resole return originalSql; } else { return originalSql; }

        } catch (JSQLParserException e) {
            logger.warn("failed to concat orderBy from IPage, exception=" + e.getMessage());
        }
    }
    return originalSql;
}

Comment From: miemieYaho

原始sql: __? 预期sql: __?

Comment From: 1176896650

测试表结构、数据如下： create table com_ibd ( id int8 not null, pid int8, education varchar(32), school varchar(128) ); alter table com_ibd add constraint com_ibd_pkey primary key (id); insert into com_ibd values (1, 1, '本科', '长安大学'); insert into com_ibd values (2, 1, '研究生', '西安科技大学'); insert into com_ibd values (3, 2, '本科', '西北大学');

create table com_demo ( id int8 not null, name varchar(32), sex varchar(4), age int4 ); alter table com_demo add constraint com_demo_pkey primary key (id); insert into com_demo values (1, '张三', '男', 28); insert into com_demo values (2, '李四', '女', 36);

测试SQL（含with语句）： with com_education as (select pid,string_agg(education, ',') as education from com_ibd group by pid) select a.id, a.name, a.sex, a.age, b.education from com_demo a left join com_education b on a.id=b.pid

使用mybatis plus（版本：3.2.0）分页、排序后实际SQL： 1、统计SQL with com_education as (select pid, string_agg(education, ',') as education from com_ibd group by pid) select count(1) from com_demo a left join com_education b on a.id = b.pid 2、查询SQL select a.id, a.name, a.sex, a.age, b.education from com_demo a left join com_education b on a.id = b.pid order by id limit 20 offset 0

统计SQL正常，查询SQL异常（丢掉with语句） 期望查询SQL： with com_education as (select pid, string_agg(education, ',') as education from com_ibd group by pid) select a.id, a.name, a.sex, a.age, b.education from com_demo a left join com_education b on a.id = b.pid order by id limit 20 offset 0

Comment From: 1176896650

原始sql: __? 预期sql: __?

已回复至上面，谢谢

Comment From: miemieYaho

用 https://mybatis.plus/guide/interceptor.html

Comment From: 1176896650

用 https://mybatis.plus/guide/interceptor.html

我们现在使用的就是这个#spring-mvc配置的PaginationInnerInterceptor，那目前这个PaginationInnerInterceptor 和原有的有啥区别

Comment From: 1176896650

用 https://mybatis.plus/guide/interceptor.html

PaginationInnerInterceptor 新的分页拦截器处理该问题的思路是什么？我们目前升级版本比较困难只能针对性优化

Comment From: miemieYaho

看源码

Comment From: jamescheng16

我现在项目已经上线了，不敢大改，只好自己把with拼回去