Some tests related to paged subselect are failing under PostgreSQL
Description
Environment
Attachments
Activity

Fabio Maulo November 10, 2008 at 5:29 AM
Fixed reverting and supporting "Variable-Limit" for Postgre dialect.
Who want work with Postgre in NH2.0 can apply the workaround.
Who are working with trunk have all working as expected.

Kenneth Siewers Møller November 10, 2008 at 4:54 AM
Well, I guess it is the patch David Bachmann has attached to this issue. I didn't apply the actual patch however, but rather copied the snippet you provided.
It seems to work as I am using the limit and offset functions in PostgreSQL intensively in a project I'm currently working on.
But as David said, It might have something to do with the inner workings of NHibernate.
I did a compare to Hibernate's PostgreSQLDialect, and it seems to be a bit different...

Fabio Maulo November 10, 2008 at 3:34 AM
Decision taken.
The problem here is the patch of NH-1155. Fixing we had introduce a deviation from H3.2 behavior and a series of bug for all Dialects that are supporting "variable-limits".
The decision is : revert patch.

Fabio Maulo November 8, 2008 at 2:27 PM
which patch ?

Kenneth Siewers Møller November 6, 2008 at 5:08 PM
This is a serious bug in the PostgreSQL dialect. I have tried the patch, and it works for version 2.0.1, but I have no real insight in NHibernate to actually determine how this patch affects the rest of the usage.
Is there any news on this issue?
Details
Details
Assignee
Reporter

I've encountered some strange behaviour with two tests under PostgreSQL (with current trunk r3096).
The following tests are failing:
NHibernate.Test.SubselectFetchTest.SubselectFetchFixture.SubselectFetchWithLimit()
NHibernate.Test.Criteria.CriteriaQueryTest.NH_1155_ShouldNotLoadAllChildrenInPagedSubSelect()
Apparently the limit parameters are not 'binded' to the command.
Note that all other tests that uses SetMaxResults() and/or SetFirstResult() are working well.
Current implementation of the PostgreSQLDialect uses the following configuration for paged queries:
SupportsLimit: true
SupportsLimitOffset: true
SupportsVariableLimit: true
BindLimitParametersInReverseOrder: true
UseMaxForLimit: false
BindLimitParametersFirst: false
override SqlString GetLimitString(SqlString querySqlString, bool hasOffset)
I've tried to modify the dialect in order to not use parameters for 'limit' statements and the problem was corrected.
This solution could eventually be used as a workaround for people who are experiencing problem with paged subselect under PostgreSQL.
Full debug log is attached.
This was discovered under PostgreSQL 8.2 using Npgsql1.0, but is not related to a specific version of PostgreSQL or Npgsql.