db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Collation and parameter markers (?)
Date Mon, 11 Jun 2007 13:14:40 GMT
Hi Mamta,

I think that the ? should be treated like a string literal. The bind() 
logic should understand it as a string type and a constant string value 
will have been poked into it before execute() is called.

Regards,
-Rick

Mamta Satoor wrote:
> I have a question about parameter and LTRIM/RTRIM functions. As per 
> the 
> http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478 
> <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 
> <mailto: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
>     <mailto: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
>         <mailto: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>
>             > <mailto: 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