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 Tue, 05 Jun 2007 05:40:24 GMT
I will work on modifying the collation type setting code for parameters to
pick up their collation from the context rather than from the current
compilation schema.


On 6/4/07, Daniel John Debrunner <djd@apache.org> wrote:
> The (very useful) collation wiki page says this for the collation of
> parameter markers:
> "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?
> It seems wrong because now simple column comparisons like this will fail
>   sys.systables.TABLENAME = ?   (when in a user schema with non
> UCS_BASIC collation)
>   A.T.C1 = ?     (when in a system schema)
>   A.T.C1 = ?     (when multiple collations are support and the
> collation  for A does not match the current schema)
> These expressions are also now dependent on the current schema which is
> not something I think an application developer is expecting.
> While they currently can be re-written to consistently use the collation
> of the current schema (e.g. CAST (A.T.C1) AS CHAR(10)) = ?) I'm not sure
> how these can be re-written to ensure the collation of the column type,
> that seems a significant problem to me [1].
> I believe the correct behaviour for parameters markers is the existing
> behaviour, described in:
> http://db.apache.org/derby/docs/10.2/ref/rrefsqlj1083019.html
> This states that a parameter marker takes on the type of its context,
> e.g. in a binary comparison the type of the parameter is the type of the
> other side. Now since collation information is an *attribute* of the
> type, this implies the collation of the parameter marker will come from
> the context that implies its type. This would mean that these
> expressions will work consistently, regardless of the current schema.
> sys.systables.TABLENAME = ?
> A.T.C1 = ?
> I think also the behaviour of these expressions is what an application
> developer is expecting, the collation of the column type.
> Dan.
> [1] [Supporting a COLLATE clause (which I think will be required for
> multiple user collations) would allow forcing of the collation to be
> that of the user column, however it requires the collation in the SQL
> statement explicitly match that of the column. I think it's wrong to
> require such a clause for a simple comparison. That would mean if the
> collation of the column changes then an application developer needs to
> go through and change all of their SQL for any comparison. Imagine an
> application where the install process asked the user how they wanted to
> compare items, one would expect that only the create table would need to
> change, not every SQL statement involving a comparison.]

View raw message