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 06:00:00 GMT
I just wanted to raise one point is with the parameters taking their
collation from the context, I think a query like following will fail
SELECT * from usertable, sys.systemtable where (userCharCol || sysCharCol) =
?
The left hand side (lhs) of the = operator will have collation derivation of
"none" because the operands of concatenation operator have different
collation types (
http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478Section
Collation Determination Rule 11). The parameter on right hand side
will get the collation from the context which in this case means it's
collation derivation will be *none* as well. Since both the operands around
= have collation derivation of none, the comparison will fail with compile
time error (As per
http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478Section
Collation Determination Rule 12). Ofcourse, the query can be fixed
by using CAST on the lhs. This will make the lhs take the collation of the
current schema and then ? will get the same collation too since ? will pick
it's collation from the context.

Mamta


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

Mime
View raw message