[nhibernate-issues] [JIRA] Created: (NH-829) pagination select doesn't support 'distinct' for NHibernate.Dialect.FirebirdDialect

[NHibernate J. <mik...@us...>]

pagination select doesn't support 'distinct' for NHibernate.Dialect.FirebirdDialect
-----------------------------------------------------------------------------------

         Key: NH-829
         URL: http://jira.nhibernate.org/browse/NH-829
     Project: NHibernate
        Type: Bug
  Components: Core  
    Versions: 1.2.0.Beta2    
    Reporter: Edward Zhou


Dialect: NHibernate.Dialect.FirebirdDialect
driver_class: NHibernate.Driver.FirebirdClientDriver
dotNet Framework 2.0
Firebird 2.0 final
Firebird .Net Provider 2.01

I encountered an bug, when I put 'distinct' into the HQL statement under pagination, it will cause failure:
eg:
[code]
string hql = "select distinct po from PurchaseBill po left join fetch po.Company company  join po.Items pi where (po.BillDate >= :beginDate) and (po.BillDate <= :endDate) and (pi.Product.Id like :productId) order by po.BillDate Desc, po.BillId Desc"
IQuery query = session.CreateQuery(hql);
// seting up parameters
// ..
IList<PurchaseBill> list = query.SetFirstResult(0).SetMaxResults(100).List<PurchaseBill>();
[/code]
it will gets an error. and I trace the sql generated by NHibernate is:
select distinct first 100 purchasebi0_.f_key_id as f1_16_0_, ... from t_purchase_bill purchasebi0_ left outer join t_company company1_ on purchasebi0_.f_company_id=company1_.f_key_id inner join t_purchase_item items2_ on purchasebi0_.f_key_id=items2_.f_bill_key_id, t_product product3_ where ((product3_.f_id like @p0  and items2_.f_product_id=product3_.f_key_id)) order by  purchasebi0_.f_bill_date Desc , purchasebi0_.f_bill_id Desc

Obviously, the keyword 'distinct' is placed in wrong position. it should be placed like
select first 100 distinct .....




-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://jira.nhibernate.org/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira