高手请看:Hibernate Oracle9Dialect 翻页查询的一个BUG!!!!

[qnjian]

我的一个项目使用了Hibernate3操作Oracle9i数据库,遇到一个很奇怪的问题,即在按某些使用了聚合函数的值的升序排序后,翻页到一定页数(通常是3或5)以后,显示的内容不会变化,即出现不是期望的查询结果.
刚开始而般排查,找不原因.后来通过查Hibernate 包中的Oracle9Dialect及OracleDialect的源码,将Dialect换成 OracleDialect后问题解决.
但是,问题虽解决了,根源何在呢?为什么Oracle9却要用到OracleDialect(源码注解中说这是兼容8i)的才能解决问题呢?!
查看源码,发现两都取得分页查询字符串的方式是有点区别的(在 getlimitString()方法中).

在Oracle9Dialect中:

public String getLimitString(String sql, boolean hasOffset) {

sql = sql.trim();
boolean isForUpdate = false;
if ( sql.toLowerCase().endsWith(" for update") ) {
sql = sql.substring( 0, sql.length()-11 );
isForUpdate = true;
}

StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
if (hasOffset) {
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
}
else {
pagingSelect.append("select * from ( ");
}
pagingSelect.append(sql);
if (hasOffset) {
pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
}
else {
pagingSelect.append(" ) where rownum <= ?");
}

if (isForUpdate) pagingSelect.append(" for update");

return pagingSelect.toString();
}

在OracleDialect中:
public String getLimitString(String sql, boolean hasOffset) {

sql = sql.trim();
boolean isForUpdate = false;
if ( sql.toLowerCase().endsWith(" for update") ) {
sql = sql.substring( 0, sql.length()-11 );
isForUpdate = true;
}

StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
if (hasOffset) {
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
}
else {
pagingSelect.append("select * from ( ");
}
pagingSelect.append(sql);
if (hasOffset) {
pagingSelect.append(" ) row_ ) where rownum_ <= ? and rownum_ > ?");
}
else {
pagingSelect.append(" ) where rownum <= ?");
}

if (isForUpdate) pagingSelect.append(" for update");

return pagingSelect.toString();
}

两都的区别主要在于,前者:
row_ where rownum <= ?) where rownum_ > ?");
后者:
where rownum_ <= ? and rownum_ > ?

我模拟了我的出问题的查询,使用前者问题重现,使用后者不出问题.

我的语句分别如下:
*************************************************************************
第一种:
select * from (

select rownum row_num ,t.* from(
select Sum(b.disp_Count) , Sum(b.click_Count) ,
Sum(b.total_Price) , Sum(b.return_Cost) ,
avg(b.rank) , b.sta_Date , b.keyword_Name ,
b.union_Name
from Bid_Report b
where (b.sta_Date ='20051129')
group by b.sta_Date, b.keyword_Name, b.union_Name
order by Sum(b.click_Count), b.sta_Date desc
) t where rownum<=60
) where row_num > 40
(在Hibernate3中,Oracle9Dialect的getlimitString()方法采取类似方法)

第二种:
select * from (

select rownum row_num ,t.* from(
select Sum(b.disp_Count) , Sum(b.click_Count) ,
Sum(b.total_Price) , Sum(b.return_Cost) ,
avg(b.rank) , b.sta_Date , b.keyword_Name ,
b.union_Name
from Bid_Report b
where (b.sta_Date ='20051129')
group by b.sta_Date, b.keyword_Name, b.union_Name
order by Sum(b.click_Count), b.sta_Date desc
) t
) where row_num <=60 and row_num>40

(在Hibernate3中,OracleDialect的getlimitString()方法采取类似方法)

**********************************************************************

现在,问题是:为什么采取后都就可以解决问题了呢?这是不是Oracle9Dialect的一个bug呢?!
哪位高手能给我详析,感激不尽!!!!

[Bluce]

学习