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(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

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(a)gmail.com&gt; 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(a)lists.jboss.org > > https://lists.jboss.org/mailman/listinfo/hibernate-dev > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev >

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(a)hibernate.org&gt; 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(a)gmail.com&gt; 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(a)lists.jboss.org >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev >> >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> >

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(a)hibernate.org <mailto:steve@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(a)gmail.com <mailto:christian.beikov@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(a)hibernate.org <mailto:steve@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(a)gmail.com > <mailto:christian.beikov@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(a)lists.jboss.org > <mailto:hibernate-dev@lists.jboss.org> > > https://lists.jboss.org/mailman/listinfo/hibernate-dev > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > <mailto:hibernate-dev@lists.jboss.org> > https://lists.jboss.org/mailman/listinfo/hibernate-dev >

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(a)gmail.com <mailto:christian.beikov@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(a)hibernate.org <mailto:steve@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(a)gmail.com > <mailto:christian.beikov@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(a)hibernate.org <mailto:steve@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(a)gmail.com >> <mailto:christian.beikov@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(a)lists.jboss.org >> <mailto:hibernate-dev@lists.jboss.org> >> > >> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> <mailto:hibernate-dev@lists.jboss.org> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> >