[hibernate-dev] How to deal with a constant in CriteriaQuery

[Steve Ebersole]

We can propose it.  Personally I do not think that section explicitly
precludes what we do, but I agree that it does imply the function
invocation should be "passed through".  I think this might be difficult to
get consensus on though - just a quick look at EclipseLink and TopLink show
that they define a completely different mechanism for such "provider
treated" function calls (OPERATION rather than FUNCTION), so I doubt there
will be much support for changing the wording here.  We'll see...

I think we should standardize a number of HQL functions beyond the JPA ones
across Dialects (we have Dialect coverage for many of these already):

   - Date / Time (already standardized across Dialects)
      - year
      - month
      - day
      - hour
      - minute
      - second
   - Date / Time (can be done through datetime arithmetic)
      - datediff
      - dateadd
   - Convert / Cast
      - to_char
      - to_number
      - to_date
   - Trigonometry
      - cos
      - sin
      - tan
      - ...


As for other cases like "date field" where we need to be able to refer to
SQL-y things, we should add some kind of support for that generically imo.
This is something I've wanted to do for some time - I just don't know a
good syntax yet.  I had thought to use braces (e.g. `extract( {day} from
startDate)`) but we use braces already for a few other things.  We could
take a page from JDBC (escape syntax) and have `extract( {sql day} from
startDate)` - that makes it more syntactically understandable.

On Wed, Apr 25, 2018 at 1:51 AM Christian Beikov <christian.beikov at gmail.com>
wrote:

> Am 25.04.2018 um 00:46 schrieb Steve Ebersole:
> >
> >
> > On Tue, Apr 24, 2018 at 5:43 PM Christian Beikov
> > <christian.beikov at gmail.com <mailto:christian.beikov at gmail.com>> wrote:
> >
> >     That's a possibility indeed, but there will most likely always be
> >     some
> >     nice function that uses a weird keyword syntax for which there is no
> >     first class support.
> >
> >
> > And not only that but some databases allow extensions to the SQL spec
> > as to what can be extracted.  So using an enum is nice, but limiting
> >
> >     So even if we propose this, IMO we should still also propose to add a
> >     note to the function invocation syntax section, that a function may
> >     resolve to a JPA vendor defined funciton as well. This would be
> >     like a
> >     "last resort" to use a function at least in a "standard way". If
> >     the JPA
> >     vendor doesn't define the desired function, it's up to the user to
> >     making use of JPA vendor specific APIs for registering the function.
> >
> >
> > I'm not sure what this means
> That means that section 4.6.17.3 should be changed to allow for
> FUNCTION('XYZ', arg1, ...) to resolve to a JPA vendor specific function
> like the ones registered in a Hibernate Dialect. To me it seems that
> currently the wording requires to pass this through to the SQL directly
> as XYZ(arg1). This would allow the use of user defined SQLFunctions
> through the JPA Criteria API.
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev