NH-3286 - Projections.SubQuery with DetachedCriteria having SetMaxResults broke on paginated query.

[nhibernate-bot]

Fabrizio Gaiardo created an issue — 5th October 2012, 12:27:12:

Tested with:
2.1.2 GA / .NET Framework 2 / MsSql2005Dialect
3.3.1 GA / .NET Framework 4 / MsSql2005Dialect

While using Criteria API, any paginated Criteria having a DetachedCriteria with SetMaxResults(anyvalue) as a Projections.SubQuery produces the following exception while trying to retrieve second page or successive.

Incorrect syntax near '?'.
Incorrect syntax near the keyword 'as'.

It seems is trying to wrongly inject the generated rowcounter alias in the subquery producing incorrect sql syntax.

---

Alexander Zaytsev added a comment — 5th October 2012, 14:04:57:

Any samples?

---

Fabrizio Gaiardo added a comment — 5th October 2012, 14:39:51:

I'll provide an example as soon as possible, thank you in advance.

---

Fabrizio Gaiardo added a comment — 8th October 2012, 10:31:30:

VS project reproducing the reported exception.

---

Alexander Zaytsev added a comment — 8th October 2012, 11:06:46:

Thanks!

---

Fabrizio Gaiardo added a comment — 8th October 2012, 11:11:17:

You are welcome :)

---

olivier added a comment — 31st July 2013, 10:15:37:

I have the same issue with the QueryOver api using 3.3.3 GA with a 2008 SQL Server configuration.
Would you like a sample ?

---

CBP added a comment — 13th May 2014, 8:40:42:

This is due to two bugs - one in the dialect, and one in the SqlString.GetSubselectString method.

Here is a very ugly workaround in case you're desperate. This works in my solution which has hundreds of crazy Nhibernate queries under test.

You need to override the MsSql2008Dialect and replace the entire GetLimitString with a copy of the code from the real MsSql2008Dialect class (you will need a copy of the NH source). Then replace the GetFromIndex method with this:

private static int GetFromIndex(SqlString querySqlString)
{
	var queryRealString = querySqlString.ToString();

	// Remove any 'TOP (?)' clauses from the query because 'GetSubselectString' doesn't handle them
	var querySqlStringWithoutTops = new SqlString(queryRealString.Replace(" TOP (?)", ""));

	string subselect = querySqlStringWithoutTops.GetSubselectString().ToString().TrimEnd();

	// Regex match the subselect - the original code got this part wrong too
	int fromIndex = Regex.Match(querySqlStringWithoutTops.ToString(), Regex.Escape(subselect) <ins> @"($|\s|\,|\)|\n)", RegexOptions.Multiline).Index;

	// Not sure if the next bit is going to work with our changes...
	if (fromIndex == -1)
	{
		fromIndex = queryRealString.ToLowerInvariant().IndexOf(subselect.ToLowerInvariant());
	}

	// Work out the length of all the 'TOP (?)' that were removed above
	var currentStart = 0;
	var lengthOfDeletedTops = 0;
	int ixOfTops;
	var topLength = " TOP (?)".Length;
	while (currentStart < fromIndex
		&& (ixOfTops = queryRealString.IndexOf(" TOP (?)", currentStart, fromIndex - currentStart, StringComparison.OrdinalIgnoreCase)) >= 0)
	{
		lengthOfDeletedTops </ins>= topLength;
		currentStart = ixOfTops <ins> topLength;
	}

	return fromIndex </ins> lengthOfDeletedTops;
}

---

CBP added a comment — 13th May 2014, 8:41:44:

-All of those (?) should be an open-bracket, followed by question mark, followed by a close-bracket.- Thanks for fixing, Alexander.

---

Fabrizio Gaiardo added a comment — 14th May 2014, 8:52:46:

Actually i managed an application-level workaround splitting required data loading into multiple sql commands instead of using subqueries.
Your workaround performs brighter, i'll sure give it a try! Hope this get fixed for a cleaner implementation.
Many thanks for considering my request, Fabrizio.