db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: Collation and parameter markers (?)
Date Tue, 05 Jun 2007 21:31:16 GMT
OK, so I need to admit that my head is spinning with all these different
collation rules. Mike I think you are right that the result of the CAST
should pick the collation of the cast operand as mentioned in rule #4 on
wiki. I will go ahead and fix the CAST code to pick up it's collation from
it's operand.

So, going back to my original mail earlier, I think with the current code of
char(10) always taking UCS_BASIC collation, we still have the problem that
CAST(? as char(10)) will always return UCS_BASIC and hence we can't compare
such a CAST with persistent character column from a user table.

> Consider following case for parameter with CAST.
> select * from customer where CAST(? as char(10)) = name
> The CAST node will assign the DTD associated with char(10) to the ?. But
> what should be the collation type for char(10). Currently in the code,
> it is UCS_BASIC and hence ? ends up getting UCS_BASIC. As a result, the
> result of the CAST operand gets a collation type of UCS_BASIC amd the
> comparison against "name" fails with compilation time error. Now doing a
> CAST on "name" is not an option to fix this problem because CAST(name as
> char(10)) will result in a character string type with collation of
> territory based. So, the question is what should be the collation type
> of char(10) in the eg above?


On 6/5/07, Mike Matrigali <mikem_app@sbcglobal.net> wrote:
>
>
>
> Mamta Satoor wrote:
> > you are right. Both the sides have the same collation which is UCS_BASIC
>
> > since result of CAST picks up the DTD associated with what the operand
> > is getting CASTed to. Since char(10) has collation type of UCS_BASIC
> > attached to it, the result of CAST picks up that collation irrespective
> > of collation of the operand.  I am wondering though if that is what user
> > would expect when writing a query like following
> > select * from customer where CAST(? as char(10)) = cast(name as
> char(10)).
> > That is UCS_BASIC getting used for comparison rather than territory
> > based collation.
> >
> Can you explain why cast(name as char(10)) becomes USC_BASIC?
>
> I would have thought from #4 on wiki:
> 4)<cast specification> Section 6.12 Syntax Rule 10 says that if the
> target data type of CAST is character string type, then collation type
> of result character string of CAST specification is the collation of the
> character set of the target data type. Based on 3) above, that will be
> the collation of the current schema's character set. The collation
> derivation will be implicit.
>
> That this would mean the cast would pick up the collation of the compile
> schema, which I assume in the example is user.
>
> as a naive user I would either expect the cast to inherit the implicit
> collation of the target of the cast, or the collation of the schema.
> Not sure with USC_BASIC came from.
>
>
> > Mamta
> >
> >
> >
> > On 6/5/07, *Daniel John Debrunner* <djd@apache.org
> > <mailto:djd@apache.org>> wrote:
> >
> >     Mamta Satoor wrote:
> >      > I realize that we are still discussing what should be the right
> >      > collation for different character string type instances, but I
> >     want to
> >      > put out cases which will hopefully aid in making a decision or
> expose
> >      > different scenarios.
> >      >
> >      > Consider following case for parameter with CAST.
> >      > select * from customer where CAST(? as char(10)) = name
> >      > The CAST node will assign the DTD associated with char(10) to the
> >     ?. But
> >      > what should be the collation type for char(10). Currently in the
> >     code,
> >      > it is UCS_BASIC and hence ? ends up getting UCS_BASIC. As a
> >     result, the
> >      > result of the CAST operand gets a collation type of UCS_BASIC amd
> the
> >      > comparison against "name" fails with compilation time error. Now
> >     doing a
> >      > CAST on "name" is not an option to fix this problem because
> >     CAST(name as
> >      > char(10)) will result in a character string type with collation
> of
> >      > territory based. So, the question is what should be the collation
>
> >     type
> >      > of char(10) in the eg above?
> >
> >     Why would the two casts be different here? What makes one UCS_BASIC
> and
> >     the other territory based?
> >
> >     Dan.
> >
> >
>

Mime
View raw message