NH-3513 - Invalid SQL with property-ref on property using a formula

[nhibernate-bot]

Jeff Becker created an issue — 16th August 2013, 20:25:28:

Framework:4.5
Dialect: MsSql2008Dialect
When a many-to-one relationship with a property-ref targets a property which has a formula invalid sql as shown below is generated:

SELECT account0_.Id as Id1_0_, account0_.Name as Name1_0_, convert(int,substring(account0_.[oldaccountnumber],3,8)) as formula0_0_ FROM Account account0_ 
WHERE  account0_.=@p0 -- Empty alias missing formula on try to load many-to-one association

---

• NH-3513.zip

[bahusoid]

Fix should adjust AbstractEntityPersister.CreateUniqueKeyLoaders to take into account formulas (propertyColumnFormulaTemplates)

[PapisKang]

Hi i m new here. I would like to help on this one

[fredericDelaporte]

Hi,

You are welcome to do so. See Contributing to get started.

[PapisKang]

#3309
I have some troubles by doing the test case. I talked about it a little bit on the PR .
But overall i do not understand how can we do a many to one relationship targetting a formula.
Because what i understood is formula are evaluations and i checked the database there is no column for the formula.
So when i try to do the many to one relationship with a formula, i got this error :
Message:  OneTimeSetUp: NHibernate.HibernateException : Column 'Customer.Id' is not the same data type as referencing column 'Orders.Customer1' in foreign key 'FK_C00737E9'. Could not create constraint or index. See previous errors. ----> System.Data.SqlClient.SqlException : Column 'Customer.Id' is not the same data type as referencing column 'Orders.Customer1' in foreign key 'FK_C00737E9'. Could not create constraint or index. See previous errors. Data: HelpLink.ProdName: Microsoft SQL Server HelpLink.ProdVer: 16.00.1000 HelpLink.EvtSrc: MSSQLServer HelpLink.EvtID: 1778 HelpLink.BaseHelpUrl: https://go.microsoft.com/fwlink HelpLink.LinkId: 20476

[hazzik]

@PapisKang the original issue actually has a test case. I've added link it to the description.

[PapisKang]

@hazzik I tried to report the Test Case that have been made before but as i was afraid off, it is not possible anymore to specify both column and formula

Message:  OneTimeSetUp: NHibernate.MappingException : Could not compile the mapping document: NHibernate.Test.NHSpecificTest.GH3513.Mappings.hbm.xml ----> NHibernate.MappingException : On OldAccountNumber property: specifying both column and formula attributes is invalid. Please specify only one of them, or use sub-elements

[bahusoid]

Just remove column attribute in this case.

Please specify only one of them, or use sub-elements

But in general case to use both use sub-elements as suggested. Example:

nhibernate-core/src/NHibernate.Test/TypedManyToOne/Customer.hbm.xml

Lines 32 to 35 in 90d50c0

	<many-to-one name="ShippingAddress" entity-name="ShippingAddress" cascade="persist,save-update,delete" fetch="join">
	<column name="ShippingAddressId"/>
	<formula>'SHIPPING'</formula>
	</many-to-one>

[PapisKang]

@bahusoid Thanks for the help.
I finally succeeded to report the test case and reproduce the error.
actual-sql-query: SELECT account0_.Id as id1_1_0_, account0_.Name as name2_1_0_, convert(int,substring(account0_.[Oldaccountnumber],3,8)) as formula0_0_ FROM Account account0_ WHERE account0_.=@p0

I dont know if an explicit assertion is needed for the exception.

[PapisKang]

Hi everyone, I just want to let you know that I'm willing to address and resolve this issue. Fixing the bug should be the next step, if I'm not mistaken, as test cases have already been created.