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 <[EMAIL PROTECTED]> 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* <[EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]>> 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. > >
