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.]
