[hibernate-dev] SQM and cast targets

Christian Beikov christian.beikov at gmail.com
Wed May 31 10:49:38 EDT 2017


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 <mailto: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 <mailto: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
>>         <mailto: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 <mailto: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
>>>                 <mailto: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
>>>                     <mailto:hibernate-dev at lists.jboss.org>
>>>                     >
>>>                     https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>
>>>                     _______________________________________________
>>>                     hibernate-dev mailing list
>>>                     hibernate-dev at lists.jboss.org
>>>                     <mailto:hibernate-dev at lists.jboss.org>
>>>                     https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>
>>
>



More information about the hibernate-dev mailing list