当前使用版本(必填,否则不予处理)
3.4.1
该问题是如何引起的？(确定最新版也有问题再提!!!)
--原始语句 select sb.id, sb.districtId, sb.coverUrl, sb.bookName,if(st.count>0,1,0) as hasRead , sb.bookAuthor ,#{data.studentId} as sId, #{data.gradeId} as gId //这一行的内容虽然没什么用但必须增加，否则优化就会出问题 from sx_book sb left join (select DISTINCT srt.bookId,count(*) count from sx_reader_task srt where srt.id in (SELECT DISTINCT sa.readerTaskId from sx_answer sa where sa.deleted=0 and sa.studentId= #{data.studentId}) and srt.gradeId=#{data.gradeId} group by srt.id) st on st.bookId=sb.id where sb.districtId = #{data.districtId} and sb.id in (select srt2.bookId from sx_reader_task srt2 where srt2.gradeId= #{data.gradeId} and deleted = 0 and unix_timestamp() >= srt2.startTime)
重现步骤(如果有就写完整)
去掉备注哪一行的内容之后，分页查询时会报错，
--错误的优化(如果去掉原始语句第二行的部分就会生成以下的内容)
SELECT COUNT()
FROM sx_book sb
WHERE sb.districtId = ?
AND sb.id IN (SELECT srt2.bookId FROM sx_reader_task srt2
WHERE srt2.gradeId = ? AND deleted = 0
AND unix_timestamp() >= srt2.startTime)
而实际上应该是
--正确的优化
SELECT COUNT() FROM
(SELECT sb.id, sb.districtId, sb.coverUrl, sb.bookName, if(st.count > 0, 1, 0) AS hasRead, sb.bookAuthor, ? AS sId, ? AS gId
FROM sx_book sb LEFT JOIN (SELECT DISTINCT srt.bookId, count(1) count FROM sx_reader_task srt
WHERE srt.id IN (SELECT DISTINCT sa.readerTaskId FROM sx_answer sa
WHERE sa.deleted = 0 AND sa.studentId = ?)
AND srt.gradeId = ? GROUP BY srt.id) st
ON st.bookId = sb.id
WHERE sb.districtId = ?
AND sb.id IN (SELECT srt2.bookId FROM sx_reader_task srt2
WHERE srt2.gradeId = ? AND deleted = 0 AND unix_timestamp() >= srt2.startTime)
) TOTAL
报错信息
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='data.districtId', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
从错误可以看出来应该是优化SQL语句时除了问题。
Comment From: miemieYaho
参考 https://github.com/baomidou/mybatis-plus/blob/3.0/mybatis-plus-extension/src/test/java/com/baomidou/mybatisplus/extension/plugins/inner/PaginationInnerInterceptorTest.java 给出你的复现demo