Char value gets 'cached' in Where-queries
Description
Environment
Attachments
Activity
Frédéric Delaporte March 19, 2017 at 11:30 PM
PR done.
Frédéric Delaporte March 17, 2017 at 4:41 PM
It could be possible to fix by moving RemoveCharToIntConversion call before calling ExpressionParameterVisitor in NhLinqExpression. But this would cause this transformation to be executed at each query execution, instead of being executed only at query plan creation. (And will also cause an additional tree visit at each query execution.)
Alex Zaytsev October 6, 2012 at 11:11 AM
Regression caused by
Ondřej Novotný October 3, 2012 at 1:50 PMEdited
We can confirm this behavior in v3.3.1. When we changed the type to string, it works.
Select generated in previous version - with parameters(CASE_SIZE was 'V', that's right):
NHibernate.SQL DEBUG 10/03/2012 15:27:24 - select cast(count as NUMBER(10,0)) as col_0_0_ from PRODUCT productta0_ left outer join PRODUCT productcat4_ on productta0_.CATALOG_PRODUCT_ID=productcat4_.Id left outer join CHANGESET changeset5_ on productta0_.ALLOCATED_BY_CHANGESET_ID=changeset5_.Id left outer join PRODUCT_LIM_HLP productlim6_ on productta0_.ID=productlim6_.PRODUCT_ID where productta0_.PRODUCT_TYPE_ID='2' and productta0_.PART_CLASS=0 and productta0_.CASE_SIZE=1;0 = 'NA' [Type: String (0)], 1 = 'V' [Type: String (0)]
Select generated in 3.3.1- no parameters(CASE_SIZE 'F' was "cached" from previous select because the value passed to query was not 'F')
NHibernate.SQL DEBUG 10/03/2012 15:05:51 - select cast(count as NUMBER(10,0)) as col_0_0_ from PRODUCT productta0_ left outer join PRODUCT productcat4_ on productta0_.CATALOG_PRODUCT_ID=productcat4_.Id left outer join CHANGESET changeset5_ on productta0_.ALLOCATED_BY_CHANGESET_ID=changeset5_.Id left outer join PRODUCT_LIM_HLP productlim6_ on productta0_.ID=productlim6_.PRODUCT_ID where productta0_.PRODUCT_TYPE_ID='2' and productta0_.CASE_SIZE='F'
Magnus Ellinge August 22, 2012 at 11:34 PM
Also the problem occurs even if you don't call FirstOrDefault after Where with the only difference that it's missing top(1).
Also there's a problem with EnsureTestData (only fills the db more), it should be:
if (!tmQuery.Any(dbtm => dbtm.StringValue == tdtm.StringValue))
{
Trace.WriteLine("Adds missing test data");
session.SaveOrUpdate(tdtm);
}
In 3.3.0.GA there was a bug introduced when it came to Char-properties with Linq which was fixed (see NH-3124)
Another issue seems to be introduced in 3.3.1.GA where queries with Char-properties generates a sql query with a 'cached' value from the initial query. I've made a test solution which tests the behaviour in 3.2.4000, 3.3.0.4000 and 3.3.1.4000. The projects uses NuGet for dependencies. I'm also using Fluent NHibernate. I've seen the problem from for SQL 2008, iSeries and SQLite (NET4.0).
The problem occurs when using Where and not if using FirstOrDefault.
FirstOrDefaultQuery(Char charValue)
session.Query<TestModel>().FirstOrDefault(e => e.CharValue == charValue);WhereQuery(Char charValue)
session.Query<TestModel>().Where(e => e.CharValue == charValue).FirstOrDefault();Calling FirstOrDefault with 'A' then 'B' gives:
select TOP (1) testmodel0_.ID as ID0_, testmodel0_.CharValue as CharValue0_, testmodel0_.StringValue as StringVa3_0_ from [TestModel] testmodel0_ where testmodel0_.CharValue='A'select TOP (1) testmodel0_.ID as ID0_, testmodel0_.CharValue as CharValue0_, testmodel0_.StringValue as StringVa3_0_ from [TestModel] testmodel0_ where testmodel0_.CharValue='B'Calling WhereQuery with 'A' then 'B' gives:
select TOP (1) testmodel0_.ID as ID0_, testmodel0_.CharValue as CharValue0_, testmodel0_.StringValue as StringVa3_0_ from [TestModel] testmodel0_ where testmodel0_.CharValue='A'select TOP (1) testmodel0_.ID as ID0_, testmodel0_.CharValue as CharValue0_, testmodel0_.StringValue as StringVa3_0_ from [TestModel] testmodel0_ where testmodel0_.CharValue='A'