[hibernate-dev] SQM and cast targets

[Steve Ebersole]

Oh, I just saw your last statement.  I'm not a fan of "only ever [using
cast function] for the pass-through case"

On Wed, May 31, 2017 at 6:43 AM Steve Ebersole <steve at hibernate.org> wrote:

> You mean something like `treat( cast(x as some_db_type) as String)`?
>
>
> On Wed, May 31, 2017 at 1:12 AM Christian Beikov <
> christian.beikov at gmail.com> wrote:
>
>> So during parsing you try to lookup the castTarget and if it can't be
>> found, just pass through? If you pass it through, what would be the type of
>> the expression?
>>
>> I'd like to present an idea I just had. How about we reuse the "TREAT"
>> function/operator for doing these "casts" to named types. Applying the
>> operator does not necessarily cause a SQL "cast" i.e. if the expression is
>> a select item and the JDBC driver supports converting a value to the
>> desired type automatically, there is no need for a cast. The main
>> difference to a "cast" function would be, that the expression type will be
>> set to the desired type, whereas the "cast" function will set the type to
>> "unknown" i.e. requiring the user to use the treat operator around the
>> cast. The cast function will then only ever be used for the pass-through
>> case. Wdyt?
>>
>> Mit freundlichen Grüßen,
>> ------------------------------
>> *Christian Beikov*
>> Am 30.05.2017 um 18:00 schrieb Steve Ebersole:
>>
>> How about this rule then?
>>
>> castTarget
>> // should allow either
>> // - named cast (IDENTIFIER)
>> // - JavaTypeDescriptorRegistry (imported) key
>> // - java.sql.Types field NAME (coded cast synonym - field's value)
>> // - "pass through"
>> // - coded cast (INTEGER_LITERAL)
>> // - SqlTypeDescriptorRegistry key
>> : IDENTIFIER | INTEGER_LITERAL
>> ;
>>
>> On Mon, May 29, 2017 at 11:16 AM Steve Ebersole <steve at hibernate.org>
>> wrote:
>>
>>> Yes, ultimately these need to resolve to SqlTypeDescriptor.  So perhaps
>>> we allow both.
>>>
>>> What I just want to get out of is the open-ended-ness.  Non-determinism
>>> is bad.  E.g., like what you just mentioned...  how should the parser
>>> understand that "TEXT" `cast(x as TEXT)` is a database type name versus
>>> Java class name versus something else?  Structurally we cannot - one String
>>> is syntactically the same as any other String.
>>>
>>> So do we just accept some policy of "well if we don't understand it
>>> we'll just pass it through to the database"?  To me that's just a cop-out.
>>> Not to mention that it invariably leaves the door open to non-portability.
>>> If instead we limited this to just Java types (JavaTypeDescriptorRegistry
>>> keys) and JDBC type codes (SqlTypeDescriptorRegistry keys) we can fully
>>> support this in a portable manner.  Now that does lead to a question for
>>> databases which make the silly decision (looking at you pgsql) to map
>>> multiple types to the same JDBC type code.
>>>
>>> As much as possible I think we ought to not be relying on the database
>>> to validate these kinds of things.  An error from the database is going to
>>> be much less descriptive as to what exactly is wrong compared to a
>>> validation done by Hibernate.
>>>
>>> Not sure the correct answer, just some thoughts.
>>>
>>> An option is to allow 3 types of cast targets:
>>>
>>>    1. Java type name we can resolve against the
>>>    JavaTypeDescriptorRegistry
>>>    2. A JDBC type (either by code or name) we can resolve against the
>>>    SqlTypeDescriptorRegistry
>>>    3. Any other text we can resolve against the Dialect as a "valid SQL
>>>    type"
>>>
>>> I'm kind of leery of (3), but if everyone else agrees it is important to
>>> allow that non-portability then I will consider it.  And keep in mind that
>>> this is really only needed for databases like pgsql to handle the multiple
>>> types it maps to a single JDBC type code...  all other cases can (and
>>> should) be handled by (1) and (2).
>>>
>>>
>>>
>>> On Mon, May 29, 2017 at 10:36 AM Christian Beikov <
>>> christian.beikov at gmail.com> wrote:
>>>
>>>> Sounds good, although I guess there might be cases when ONLY this
>>>> approach won't work that well.
>>>>
>>>> I am specifically thinking about casts to the various character types
>>>> that are available in the different DBMS. A cast to "String" might work
>>>> most of the time, but we should still have an option to cast to CLOB,
>>>> TEXT or whatever other datatype a DBMS offers.
>>>>
>>>>
>>>> Mit freundlichen Grüßen,
>>>> ------------------------------------------------------------------------
>>>> *Christian Beikov*
>>>> Am 29.05.2017 um 16:17 schrieb Steve Ebersole:
>>>> > Currently casting in HQL is under-defined and open-ended (and
>>>> therefore
>>>> > pretty inconsistent).  What does that mean?  Well, what is a valid
>>>> cast
>>>> > target in HQL?  There really is not a defined
>>>> > answer to that.
>>>> >
>>>> > I'd like to start formalizing the answer to this.
>>>> >
>>>> > Specifically, I am thinking this should be defined around
>>>> > JavaTypeDescriptor.  So that we'd understand any Java type registered
>>>> with
>>>> > with JavaTypeDescriptorRegistry, and specifically any that properly
>>>> > implements `#getJdbcRecommendedSqlType` (using the Dialect to resolve
>>>> the
>>>> > cast target in the generated SQL).
>>>> >
>>>> > Anyone have objections to this?  Thoughts?
>>>> > _______________________________________________
>>>> > hibernate-dev mailing list
>>>> > hibernate-dev at lists.jboss.org
>>>> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>>
>>>> _______________________________________________
>>>> hibernate-dev mailing list
>>>> hibernate-dev at lists.jboss.org
>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>>
>>>
>>