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

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

     [ http://jira.nhibernate.org/browse/NH-829?page=all ]
     
Fabio Maulo closed NH-829:
--------------------------

    Resolution: Fixed

> 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
>      Fix For: 1.2.0.Beta3
>  Attachments: NH829.patch
>
> 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