[
https://issues.apache.org/jira/browse/DERBY-2946?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12523043
]
Mamta A. Satoor commented on DERBY-2946:
----------------------------------------
While working on DERBY-573 (optimizer overrides), we made the decision for
metadata calls to always goto metadata.properties rather than SYSSTATEMENTS
during soft upgrade. The reason for this was that there were changes made to
the metadata queries such that they were recognized only by the 10.2 release
and 10.2 code was changed to look for the updated metadata queries and hence
when running in soft upgrade mode, the 10.2 engine was expecting the metadata
queries which corresponded to 10.2 release rather than pre-10.2 release.
This approach of going to metadata.properties rather than SYSSTATMENTS during
soft upgrade poses a challenge for collation though. The reason for it is that
when the metadata queries are run using the plan in the SYSSTATEMENTS table,
they are always run in the SYS schema. And hence when the metadata query has
something like following in it's WHERE clause
P.SELECTPRIV = 'Y'
the left hand side will have collation of UCS_BASIC because column SELECTPRIV
is from a table in SYS schema. The constant character string 'Y' on the right
hand side will also have collation of UCS_BASIC because constant character
strings pick up their collation from current compilation schema which is SYS
when the metadata queries are run using SYSSTATEMENTS and hence we do not run
into mismatched collation types. But during soft upgrade, when the same clause
is run, the left hand side will have collation type of UCS_BASIC because it
belongs to a table in SYS schema but the constant character string 'Y' will
have collation for whatever the current compilation schema may be. So, if the
metadata query is getting run in soft upgrade mode when the current compilation
schema is a user schema, then P.SELECTPRIV = 'Y' will fail because the
collation of the 2 sides do not match. So, the challenege is to somehow make
the current compilation schema to be SYS schema only for the duration of
metadata query run during a soft upgrade. At this point, I am not sure how to
do it and I will continue looking at it but I will appreciate if someone has
any thoughts on the approach or any thoughts on how to change the compilation
schema to be SYS during the metadata query execution in soft upgrade mode.
> In soft upgrade mode, the metadata queries do not get compiled in SYS schema.
> This will cause problems in future releases of Derby.
> -----------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2946
> URL: https://issues.apache.org/jira/browse/DERBY-2946
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Mamta A. Satoor
>
> EmbedDatabaseMetaData implements metadata queries. The implementation of
> metadata queries in EmbedDatabaseMetaData checks if we are in soft upgrade
> mode (getPreparedQuery() has following code)
> if (notInSoftUpgradeMode())
> s = getPreparedQueryUsingSystemTables(queryName, net);
> else {
> try {
> //Can't use stored prepared statements because we are in soft
> upgrade
> //mode and hence need to get metadata sql from
> metadata.properties file
> //or metadata_net.properties
> String queryText = getQueryFromDescription(queryName, net);
> s = getEmbedConnection().prepareMetaDataStatement(queryText);
> } catch (Throwable t) {
> throw handleException(t);
> }
> }
> So, when running in soft upgrade mode, we do not go through SYSSTATEMENT but
> this will cause the metadata query to run in whatever the current schema may
> be rather than in SYS schema. This is not a problem in Derby 10.3 release
> because during soft upgarde, the collation will always be UCS_BASIC and hence
> collation mismatch can't happen.
> But in Derby 10.3, a user can create a database with territory based
> collation. When such a database will be in soft upgrade mode for say Derby
> 10.4, the metadata queries will run into problem if they are run in a user
> schema. This is because in soft upgrade mode, we do not go through
> SYSSTATEMENTS and instead compile the metadata queries in current schema. If
> the current schema is user schema, then character string literals in metadata
> queries will get the collation type of territory based and when compared with
> character string colunms from SYS tables with collation type of UCS_BASIC, we
> will end up getting compilation error for the metadata queries. So, before
> the next release, we will have to fix this problem. One solution may be to
> somehow make the current compilation schema as SYS when running the metadata
> queries in soft upgrade mode.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
