?

??????? 我的一個項目使用了Hibernate3操作Oracle9i數據庫,遇到一個很奇怪的問題,即在按某些使用了聚合函數的值的升序排序后,翻頁到一定頁數(通常是3或5)以后,顯示的內容不會變化,即出現不是期望的查詢結果.
??????? 剛開始仔細排查,找不出原因.后來通過查Hibernate 包中的Oracle9Dialect及OracleDialect的源碼,將Dialect換成 OracleDialect后問題解決.
?????? 但是,問題雖解決了,根源何在呢?為什么Oracle9卻要用到OracleDialect(源碼注解中說這是兼容Oracle8i)的才能解決問題呢?!
???? ?查看源碼,發現兩者取得分頁查詢字符串的方式是有點區別的(在 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?
????????)?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??
????????)?t??
??)? where ?row_num? <= 60 ? and ?row_num > 40

(在Hibernate3中,OracleDialect的getLimitString()方法采取類似實現方式)

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

現在,問題是:為什么采取后者就可以解決問題了呢?這是不是Oracle9Dialect的一個bug呢?!
哪位高手能給我詳析,感激不盡!!!!