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 Mon, 11 Jun 2007 05:22:05 GMT
UPPER and LOWER also have same issue.

Mamta


On 6/9/07, Mamta Satoor <msatoor@gmail.com> wrote:
>
> I have a question about parameter and LTRIM/RTRIM functions. As per the http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478
>  page, LTRIM/RTRIM take the collation of their operand(Rule 5). But what
> if the operand is a parameter? What should be the collation type of the ? In
> general, we want the rule for ? to be take the collation from the context.
>
> But for LTRIM(?), there is no context for ?. The current code sets the ?'s
> collation to be current compilation schema's collation. Is that assumption
> correct ie when the context is not available for a parameter, take the
> current compilation schema's collation?
>
> Mamta
>
>
> On 6/6/07, Mamta Satoor <msatoor@gmail.com> wrote:
> >
> > The problem can be fixed by (the CastNode does that already in it's
> > bindCastNodeOnly()) having the result of CAST node take it's collation from
> > current compilation schema.
> >
> > In short, we have no problem with CAST. Sorry about the confusion. Not
> > sure why I was getting confused yesterday. The query below (assume we are in
> > user schema) will pass because CAST will have collation type of territory
> > based which is the same collation that "name" has.
> > select * from customer where CAST(? as char(10)) = name
> >
> > Mamta
> >
> >  On 6/6/07, Mamta Satoor <msatoor@gmail.com> wrote:
> > >
> > > Dan and Mike asked
> > > "Not sure where this UCS_BASIC is coming from, can you explain?"
> > >
> > > For the CHAR(10) part of the CAST(name as CHAR(10)) expression,
> > > sqlgrammar's dataTypeCommon method calls characterStringType() which makes
> > > following call to generate DTD for character string type
> > >
> > > DataTypeDescriptor.getBuiltInDataTypeDescriptor(type, length)
> > > Note that this method has no info about collation type and hence we
> > > end up generating a DTD with default value of UCS_BASIC for collation type
> > > and IMPLICIT for collation derivation(these default values are in
> > > TypeDescriptorImpl lines 58 and 60). Next, when CastNode.init method
> > > is called, it gets the CAST target as a DTD with collation type of UCS_BASIC
> > > and collation derivation of IMPLICIT. Later at bind time of CastNode, it's
> > > bindCastNodeOnly() method gets called which sets the DTD of the result of
> > > the CAST to the same value as the DTD fro the CAST target and hence it ends
> > > up having collation type of UCS_BASIC. The call in bindCastNodeOnly() looks
> > > as follows   setType(castTarget);
> > >
> > >
> > > Mamta
> > >
> > >
> > >
> > >  On 6/5/07, Daniel John Debrunner <djd@apache.org > wrote:
> > > >
> > > > Mamta Satoor wrote:
> > > > > 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.
> > > >
> > > > That's not what Mike says, or the wiki page says. Mike wrote, which
> > > > matches the wiki page:
> > > > > > That this would mean the cast would pick up the collation of
the
> > > > >>     compile
> > > > >>     schema, which I assume in the example is user.
> > > >
> > > >
> > > > > 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.
> > > >
> > > > Not sure where this UCS_BASIC is coming from, can you explain?
> > > >
> > > > Dan.
> > > > >
> > > > >  > 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
> > > > > <mailto: 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.
> > > > >
> > > >
> > > >
> > >
> >
>

Mime
View raw message