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.