substring in HQL

[Oskar Berggren]

I merged the following recently.

NH-3067: Linq - substring function does not work
https://github.com/nhibernate/nhibernate-core/pull/89

Unfortunately it causes problems on Firebird due to Firebird only
supporting integer literals, not expressions, for the substring index.
The problem is that if two-argument substring is used in linq, the
linq-provider constructs the third argument to hql substring as a
calculation based on column length and the start index. This is
because not all dialects support two argument substring.

One solution idea: If the third argument needs to be added by the linq
provider, pass Int32.MaxValue. Some databases seem to handle this
nicely, but at least DB2 claims this is an error.

Another idea: Make HQL substring() ALWAYS support either two or three
arguments. Some dialects already do this, and IMHO this is something
the dialect should abstract. This is easy if "all" databases support a
third argument that is either optional or can be specified as
Int32.MaxValue. Haven't checked this yet.

Btw, anyone using DB2? I suspect it's dialect's substring
implementation doesn't work at all.

Any input on this?

/Oskar

[Richard Brown]

This seems like a limitation of Firebird, rather than NH or Linq.  Shouldn't we just ignore this test on Firebird?

[Oskar Berggren]

2012/3/12 Richard Brown <fluk...@googlemail.com>:

> This seems like a limitation of Firebird, rather than NH or Linq.  Shouldn't
> we just ignore this test on Firebird?

Firebird supports two-argument substring, so it seems a shame if the
linq provider obstructs this by insisting on creating a third
argument. So in a way I think this is LINQ trying to work around an
HQL limitation.

Concerning making HQL always support the two-argument form, things
seem to look rather good. For the dialects which does not already
support this:

MSSQL (all versions, it seems): Always requires three arguments, but
the third argument can be larger than the length of the remainder of
the string, i.e. Int32.MaxValue.

MySql: Actually supports ANSI substring(), so the dialect should just
be corrected.

DB2: I suspect the current dialect's substring doesn't work at all
because DB2 requires a weird fourth argument, which I think the
dialect can insert as a constant. Other than that it should use ANSI
substring, which supports optional length.

Ingres: “Ingres 2006 SQL reference guide”: Supports substring(str from
loc [for len])

SybaseASE15Dialect: This seems to be the only question mark. The third
argument is not optional, and there is no statement what happens if
the length is "oversized". This can be worked around by having the
dialect construct a third argument from len(first_argument) - start.

In summary: all dialects can be made to support two-argument substring
with little work.

/Oskar

[Fabio Maulo]

Which is the HQL limitation ?

--
Fabio Maulo

[Oskar Berggren]

2012/3/17 Fabio Maulo <fabio...@gmail.com>:

> Which is the HQL limitation ?
>

NH-3086 (fixed)

/Oskar

[Fabio Maulo]

I don't know what happen with my JIRA account, I can't login anymore

--
Fabio Maulo

[Fabio Maulo]

btw Oskar, which is the HQL limitation ?

Each HQL function can be translated in different ways by each dialect

--
Fabio Maulo

[Oskar Berggren]

Only some dialects could handle substring(expr, start), i.e. without
the length parameter. I've made it so that all dialects can do this,
so that HQL users such as the LINQ provider can rely on this always
being available (to cope with e.g. str.Substring(start). The details
are in the issue report if you can figure out the login problem.

/Oskar

2012/3/18 Fabio Maulo <fabio...@gmail.com>: