诡异的fetch first n rows only ?！！
?
前天解决了系统一个历史科目分页查询的bug，虽然当时解决了，但并不知道造成bug的具体原因。于是，这两天通过查资料和测试终于知道了bug产生的原因
?
?
bug描述：系统在分页查询的时候，从第2页开始，以后的每一页数据都和第2页数据相同的，正确的情况应该是每页都不相同的
?
数据描述：历史科目表中的数据每天都会有数据生成，生成数据的时候会专门给数据指定一个排序号，放入列sortNum中。所以在同一天内排序号都是不同的，但是天与天之间，对应同一个科目号的排序号sortNum有大量的重复
?
? ? ? ? ? ? ? ? ? ? ? ? ? ??
原始有bug的sql如下： ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
select * from ( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
select tba.*, ROW_NUMBER() OVER() AS ROWNUM from
( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? select * from 历史科目表 where 1=1 ? ? ? ? ? ??
? and glcode = ‘3801‘ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? order by ?sortNum ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
) tba fetch first 50 rows only ? ? ? ? ? ? ? ?
) where ROWNUM >40 and ROWNUM <=50 ? ?
?
?
当时解决的办法，是在子查询的order by后面多添加了日期acdate，结果正确了
?
select * from ( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
select tba.*, ROW_NUMBER() OVER() AS ROWNUM from
( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? select * from 历史科目表 where 1=1 ? ? ? ? ? ??
? and glcode = ‘3801‘ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? order by ?sortNum ，acdate ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
) tba fetch first 50 rows only ? ? ? ? ? ? ? ?
) where ROWNUM >40 and ROWNUM <=50 ? ?
?
可是面对如此奇怪的问题，到底出现了在哪呢？
?
经过实验发现，如下2个对sql的改动都能使结果正确
?
1、把order by ?sortNum 子句在 放入 OVER()函数中，如下
select * from ( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
select tba.*, ROW_NUMBER() OVER(order by ?sortNum ) AS ROWNUM from
( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? select * from 历史科目表 where 1=1 ? ? ? ? ? ??
? and glcode = ‘3801‘ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? order by ?sortNum ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
) tba fetch first 50 rows only ? ? ? ? ? ? ? ?
) where ROWNUM >40 and ROWNUM <=50 ?
?
?
2、把 fetch first 50 rows only 的语句去掉
?
select * from ( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
select tba.*, ROW_NUMBER() OVER() AS ROWNUM from
( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? select * from 历史科目表 where 1=1 ? ? ? ? ? ??
? and glcode = ‘3801‘ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? order by ?sortNum ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
) tba ? ? ? ? ? ? ? ?
) where ROWNUM >40 and ROWNUM <=50 ?
?
?
怀疑是fetch first n rows only这条语句搞的鬼，于是进行了如下的数据比对
?
把 select * from 历史科目表 where 1=1 and glcode = ‘3801‘ order by sortNum 查询结果前10条记录复制日期列到UtraEdit中
?
和把select * from 历史科目表 where 1=1 and glcode = ‘3801‘ order by sortNum fetch first 10 rows only查询结果的日期列也复制到UtraEdit中，在UtraEdit的列模式下进行比对
?
?
发现10条数据是一样的，但排序不一样了，后来把测试范围扩大到20，30,40,50条，发现一个规律，加不加fetch first n rows only最后取得的结果记录都是一样的，但是加了fetch first n rows only后，会对原来order by 相同列的记录的换了一种方式排序。原来order by如果列有相同值就按物理顺序排的（即先插入的数据排在前面），但是 加了fetch first n rows only 后竟然改变了对查询结果的排序方式，在ordery by 字段值相同的时候，90% 以上的数据是按键值（这里是日期acdate）倒序排序的。所以按照
?
select * from ( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
select tba.*, ROW_NUMBER() OVER() AS ROWNUM from
( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? select * from 历史科目表 where 1=1 ? ? ? ? ? ??
? and glcode = ‘3801‘ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? order by ?sortNum ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
) tba fetch first 50 rows only ? ? ? ? ? ? ? ?
) where ROWNUM >40 and ROWNUM <=50 ?
?
查出的结果，如果sortNum都相同的话，永远取到日期acdate 最小的那10条数据，所以分页查询的时候，就表现为点后面几页的按钮，显示的结果都是一样的状况
?
?
原来如此！ 诡异的fetch first n rows only ?！！ 但不知道这是db2的故意优化呢，还是DB2的bug，其他的数据库oracle、sqlserver也会是这样吗的，没有环境这里就暂时先不测试了。如果有网友能有这方面的测试数据，分享一下，那就太感激了！！
?
周末愉快~ ^-^?
原文：http://zhouchaofei2010.iteye.com/blog/2249684