诡异的fetch first n rows only ！！

诡异的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也会是这样吗的，没有环境这里就暂时先不测试了。如果有网友能有这方面的测试数据，分享一下，那就太感激了！！

 

周末愉快~ ^-^