db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Collation and parameter markers (?)
Date Mon, 04 Jun 2007 15:41:28 GMT
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