search

nhibernate / nhibernate-core

NHibernate Object Relational Mapper
https://nhibernate.info
GNU Lesser General Public License v2.1
2.13k stars 931 forks source link

Incorrect query when using Formula with Extract function #2186

Open DominiqueFonteyn opened 5 years ago

DominiqueFonteyn commented 5 years ago

I have used FluentNhibernate to map property to a formula, like this Map(x => x.Periode).Formula("EXTRACT(YEAR_MONTH FROM Datum)")

This has worked in the past but now there are some issues with the generated query. In the query it should generate something like this EXTRACT(YEAR_MONTH FROM this_.Datum) as formula1_0_ but it now generates this EXTRACT(this_.YEAR_MONTH FROM Datum) as formula1_0_.

I use NHibernate v4.0.3.4 and Mysql.Data v8.0.16 on an ASP.NET v4.5.2 application.

I'm not sure how I can fix this.

fredericDelaporte commented 5 years ago

This could be a trouble with the data provider: does it correctly yields its keywords list? This bug is still open on their side: https://bugs.mysql.com/bug.php?id=89639 Check if using some older version of MySql.Data fix your trouble. So it looks highly likely to me you have run into an external issue, due to previously linked bug in MySql.Data.

bahusoid commented 4 years ago

In hibernate they added code to handle extract (and also for trim)`: https://github.com/hibernate/hibernate-orm/blob/517a0b963977b5044b82593f519cd77154c0ed03/hibernate-core/src/main/java/org/hibernate/sql/Template.java#L201-L216

But I think that we should just add some flag like autoFormulaAliases which disables auto aliasing when set to false and makes user explicitly specify aliases using some placeholder (like already internally used $PlaceHolder$)