Using "for json path" in formula generates wrong sql.

[marjohansson]

Hi,

I tried to use a formula with "for json path" to return the result as a json string.
(Maybe I am doing something wrong here then I apologize.)

Formula from hbm file.
" (select
sum(j.FullSuccessfulBackupDataLength) as FullValue,
sum(j.DiffSuccessfulBackupDataLength) as DiffValue,
sum(j.IncrSuccessfulBackupDataLength) as IncValue,
sum(j.LogSuccessfulBackupDataLength) as LogValue,
sum(j.DupSuccessfulBackupDataLength) as DupValue,
sum(j.DedupSuccessfulBackupDataLength) as DedupValue
from BackupBinder b
join BackupJobUnit j on j.Binder_cid = b.cid
where b.cid=Cid and j.Status_cid=67
for json path, include_null_values, Without_Array_Wrapper)"

The SQL generated.
(select
sum(j.FullSuccessfulBackupDataLength) as binder0_.FullValue,
sum(j.DiffSuccessfulBackupDataLength) as binder0_.DiffValue,
sum(j.IncrSuccessfulBackupDataLength) as binder0_.IncValue,
sum(j.LogSuccessfulBackupDataLength) as binder0_.LogValue,
sum(j.DupSuccessfulBackupDataLength) as binder0_.DupValue,
sum(j.DedupSuccessfulBackupDataLength) as binder0_.DedupValue
from BackupBinder b
join BackupJobUnit j on j.Binder_cid = b.cid
where b.cid=binder0_.Cid and j.Status_cid=67
for binder0_.json path, binder0_.include_null_values, binder0_.Without_Array_Wrapper)

If not trying to return the string as json it works perfectly.

[fredericDelaporte]

NHibernate does some basic, and somewhat brittle, parsing for injecting table aliases as prefixes to columns found in the formula. It seems this logic obviously does not support for json path expression and interprets json as being a column name.

So, this is a currently unsupported case.

[marjohansson]

Thanks for the answer.
Is it possible to intercept the formula parsing?

[maca88]

Is it possible to intercept the formula parsing?

You could try to extend the dialect that you are using, example for MsSql2008Dialect:

public class CustomMsSql2008Dialect : MsSql2008Dialect
{
  public CustomMsSql2008Dialect()
  {
    RegisterKeyword("json");
  }
}

and use CustomMsSql2008Dialect dialect instead.

[fredericDelaporte]

I had forgotten about this, yes you can handle it the way maca88 suggest. But be aware that any added keyword will cause NHibernate to change behavior with columns named like the added keyword: of course it will no more treat them as column in formulas, but also it will "quote" them in a number of circumstances.

[marjohansson]

Thanks !
Should try this. Is it possible that this will be supported in the future?

[maca88]

Is it possible that this will be supported in the future?

Yes it probably is, I have this on my todo list as I also stumbled on this issue many times, but I haven't yet took the time to investigate it. The parser would need to be extended in order to detect clauses that are database specific, like the one that you've used FOR JSON.

[megafetis]

Замечательный проект NHibernate загибается? Bug Issues are open many years