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? thanks, Mamta On 6/5/07, Daniel John Debrunner <[EMAIL PROTECTED]> wrote:
Mamta Satoor wrote: > Dan asked > **************************************************************************************** > "8)JDBC parameters (ie. ?) where the type of the parameter is a > character type will have the same collation as of the character set of > the schema where the statement is prepared. The collation derivation > will be implicit." > > http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478 > > This seems wrong to me, is there some section of the SQL standard that > is being followed? > **************************************************************************************** > > Dan, this came from the Collation feature discussion thread below. > http://www.nabble.com/Collation-feature-discussion-tf3418026.html#a9743522 > > Basically, we followed SQL spec section 6.1 <data *type*> Syntax > Rule *3b* and 16. But that doesn't apply to a parameter marker. 6.1 is describing the syntax and behaviour of <data type>, when a type explicitly appears in the statement, e.g. INTEGER, CHAR(10). It is not discussing general behaviours of data types. Dan.
