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 06:25:01 GMT
LIKE with parameters will fall into same window as UPPER, LOWER, LTRIM and
RTRIM. Rigth now, for ? LIKE ? ESCAPE ?, none of these ? have any context to
determine their collation type. The current code has these ?s take their
collation from the current compilation schema. So, the question in this case
is same. If there is no context available for parameters to take their
collation, can we take the collation to be same as the current compilation's
schema?

Mamta


On 6/10/07, Mamta Satoor <msatoor@gmail.com> wrote:
>
> 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