[hibernate-dev] SQM and cast targets

Wed May 31 11:11:30 EDT 2017

So you see how this is getting WAAAAAAY to compicated?  I'd prefer to keep
this simple.  People know what to expect of `cast`.  I am trying to make
this as broadly applicable as *reasonably* possible, but if this is going
to "be a thing" then I say we limit this to just Java types.

On Wed, May 31, 2017 at 9:49 AM Christian Beikov <christian.beikov at gmail.com>
wrote:

> The naming problem and because people might be used to specify "managed
> type names" with "treat" is actually why I suggested to reuse "treat".
>
> I don't know how you'd like to implement the cast function, but allowing
> to mix the type names/codes with actual DBMS specific type names doesn't
> sound right to me. How about a syntax like the following
> castTarget
> // should allow either
> // - named cast (IDENTIFIER)
> // - JavaTypeDescriptorRegistry (imported) key
> // - java.sql.Types field NAME (coded cast synonym - field's value)
> // - coded cast (INTEGER_LITERAL)
> // - SqlTypeDescriptorRegistry key
> : INTEGER_LITERAL | IDENTIFIER ( '(' dbmsType=parenthesisBalancedText')')?
> parenthesisBalancedText
>         : [^\(\)]* ('(' parenthesisBalancedText ')' )?
>
> So every cast expression must define a "known type" which we use to
> determine the expression type, but can optionally define in parenthesis the
> concrete DBMS type. Valid examples would be something like
>
> cast( x as String ), cast( x as String( varchar ) ), cast( x as String(
> varchar2( 5000 ) ) )
>
> What do you think about that?
>
> Mit freundlichen Grüßen,
> ------------------------------
> *Christian Beikov*
> Am 31.05.2017 um 14:43 schrieb Steve Ebersole:
>
> We would not be able to.  That is the trouble with any kind of
> "pass-through.  We would need some form of hint/directive from the user.
>
> Also in thinking about it some more, I think that re-using treat is not
> appropriate.  treat is specifically defined in regards to hierarchies,
> which mean this use would be counter-intuitive.  But a "wrapper" of some
> sort (and really cast and treat are just specialized typing wrappers) seems
> like a good option.  We'd just need a good name for it.
>
> On Wed, May 31, 2017 at 7:24 AM Christian Beikov <
> christian.beikov at gmail.com> wrote:
>
>> Yeah the example you gave would reflect what I was thinking about.
>>
>> How would you determine the expression type if the castTarget is just
>> "passed-through" then?
>>
>> Mit freundlichen Grüßen,
>> ------------------------------
>> *Christian Beikov*
>> Am 31.05.2017 um 13:44 schrieb 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
>>>>>>
>>>>>
>>>>
>>
>