NH-3336 - MySQL does not support limit and IN/ALL/ANY/SOME subquery

[nhibernate-bot]

Joel Hervén created an issue — 3rd December 2012, 11:28:29:

Using the Linq provider can cause invalid SQL when you try to limit your query. If you write statements that generates SQL with an IN clause and also tries to limit your result it will cause an error as MySQL doesn´t support it "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery". Without the limit the query is perfectly valid.

Maybe an inner join would be the solution in this cause instead of the IN statement

Tested: MySQL 5.5.27 & 5.0.67 .NET 4.0

---

Oskar Berggren added a comment — 3rd December 2012, 12:27:10:

Please provide at least example query in LINQ and the generated SQL.

---

Joel Hervén added a comment — 3rd December 2012, 15:03:08:

LINQ query can look like this:
Query().Where(c => c.LanguageSpecificNames.Translations.Any(a => a.Value.Value.Contains("Washingto") && a.Value.Language == LanguageCode.SV)).ToList();

Executed SQL:
SELECT translatio0*.LanguageSpecificNames_DestinationId as Language1_0_, translatio0_.LanguageSpecificNames_Language as Language2_0_, translatio0_.LanguageSpecificNames_Value as Language3_0_, translatio0_.LanguageSpecificNames_IndexKey as Language4_0_ FROM dest_Translation translatio0_ WHERE translatio0_.LanguageSpecificNames_DestinationId in (select city0_.Destination_id from dest_City city0_ inner join dest_Destination city0_1_ on city0_.Destination_id=city0_1_.Destination_Id where (exists (select translatio1_.Destination_Id from dest_Translation translatio1_ where city0_1_.Destination_id=translatio1_.LanguageSpecificNames_DestinationId and (translatio1_.LanguageSpecificNames_Value like concat('%','Washingto','%')) and translatio1_.LanguageSpecificNames_Language='SV')) and city0_1*.SelectedDestination=1 limit 2147483647)

The Translation collection (dest_Translation table) uses Fetchmode Subselect

Just tell if I can submit any more usable information.

---

Alexander Zaytsev added a comment — 15th January 2013, 11:43:43:

Also these test failing:

Criteria:

• CriteriaQueryTest.SubqueryPagination
• CriteriaQueryTest.SubqueryPaginationOnlyWithFirst
• NH1792.Fixture.PageWithDetachedCriteriaSubqueryWithOrderBy
• NH2251.Fixture.MultiplePagingParametersInSingleQuery

HQL

• NH2296.Fixture.Test

Linq:

• PagingTests.PagedProductsWithOuterWhereClause
• PagingTests.PagedProductsWithOuterWhereClauseAndComplexProjection
• PagingTests.PagedProductsWithOuterWhereClauseAndProjection
• PagingTests.PagedProductsWithOuterWhereClauseEquivalent

---

Alexander Zaytsev added a comment — 15th January 2013, 23:14:27:

If wrap subquery with SELECT * FROM ( ... ) ALIAS it works fine. We need to determine what version of MySQL support this and what does not

---

Alexander Zaytsev added a comment — 7th March 2013, 22:47:51:

Can someone try the fix? https://github.com/hazzik/nhibernate-core/tree/MySQL-limit

---

Alexander Zaytsev added a comment — 9th May 2013, 14:08:48:

Fixed in 3.3.x at 24ee723

---

Alexander Zaytsev added a comment — 23rd May 2013, 23:47:38:

Following test are failing

OrderByTests.OrderByWithSelfReferencedSubquery1
OrderByTests.OrderByWithSelfReferencedSubquery2