|
History
| Log In
|
|
|
|
Full debug log for test NHibernate.Test.Criteria.CriteriaQueryTest.NH_1155_ShouldNotLoadAllChildrenInPagedSubSelect()
executed against 1.2.x branch Full debug log for test NHibernate.Test.SubselectFetchTest.SubselectFetchFixture.SubselectFetchWithLimit()
executed against 1.2.x branch Wow, that took some finding.
The issue is in SubselectFetch. We are building the query there, including the limit string, but along the way, we lose the parameters for the binding. This is not an issue generally because most dialect just put the limit inline, without parameters, in this case, it is obviously not the case, and it is causing issues. Need some thinking... This is a partial fix to this issue.
It ensure that we are correctly binding the parameters (and simplifies the SubselectFetch considerably). However, it also breaks SubselectFetchFixture.SubselectFetchWithLimit testcase, and I am not sure why. In practices the patch is a revert of r3020. I'm working on the issue looking a way to fix NH-1155 maintaining the same behavior of H3.2.
No, it is not a revert, it is moving the functionality.
Note that we are now explicitly setting the row selection, which allows the code to know what the paging is. I don't have Postgre but I think that the solution is easy.
Try the override of GetLimitString(SqlString, int, int) in the Postgre dialect and send us a patch when the work is done. This is the candidate implementation: public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) { SqlStringBuilder pagingBuilder = new SqlStringBuilder(); pagingBuilder.Add(querySqlString); pagingBuilder.Add(" limit " + limit); if (offset > 0) { pagingBuilder.Add(" offset " + offset); } return pagingBuilder.ToSqlString(); } Thanks. So, as apparently 1.2.x is closed, I've re-passed every tests on 2.0.x branch (r3636)
The following tests are failing with the symptoms described above: - NHibernate.Test.SubselectFetchTest.SubselectFetchFixture.SubselectFetchWithLimit() - NHibernate.Test.Criteria.CriteriaQueryTest.NH_1155_ShouldNotLoadAllChildrenInPagedSubSelect() - NHibernate.Test.Criteria.CriteriaQueryTest.AllowToSetLimitOnSubqueries() I've tried to pass those three tests against MySQL 5.1, using MySQLDialect. This dialect uses parameters for limits (variable limit), as does PostgreSQLDialect. The three tests are failing in a similar way than with PostgreSQL, even that in this case the error message is much more 'verbose': > MySql.Data.MySqlClient.MySqlException: Parameter '?p0' must be defined. In conclusion: NH does not handle correctly SubSelects and 'Variable Limits', independently of the used database. (and it's maybe the reason why many dialects do not use 'variable limits', even if they technically could) Fabio, the solution that you describe in your last comment works, that's what I briefly referred as an eventual workaround in my initial post. I've attached a patch with this workaround, even if I think that it shouldn't be applied. The problem is somewhere in NH core and fix this issue that way will not solve the real problem. Unfortunately I haven't enough NH internals knowledges to provide a better patch, sorry. Tell me if/how I can help you solve this. many thanks 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? 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... Fixed reverting NH-1155 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. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Bug
Closed
Major
Some tests related to paged subselect are failing under PostgreSQL
Could someone with the necessaries privileges change the affected version of this issue to 1.2.1? thanks.
The following new files are attached:
log-1.2.x-ShouldNotLoadAllChildrenInPagedSubSelect.txt
log-1.2.x-SubselectFetchWithLimit.txt
They include an indication of where the test fails and the full debug log executed against branch 1.2.x (r3141) (under PostgreSQL 8.0 using Npgsql2.0beta1)