[
https://issues.apache.org/jira/browse/DERBY-3458?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12572368#action_12572368
]
Mamta A. Satoor commented on DERBY-3458:
----------------------------------------
Stephan, I did not think through completely when I sent my feedback on the
patch(DERBY-3458_patch_1.txt) earlier today. The patch is CASTing the system
table columns (all the system table character columns have UCS_BASIC collation
associated with them) to current schema's collation and then comparing the
resultant character string with a constant character string (constant character
strings always take the collation of the schema in which the statement is
getting compiled). This is really not we want. We want to ensure that when
dealing with system table character columns, we use UCS_BASIC for collation.
This may or may not happen with the supplied patch because we may or may not be
system schema when the queries get compiled. In other words, the patch could
cause Derby to use territory based collation for system columns rather than
using UCS_BASIC collation because the current schema is user schema and not
system schema. (I am not familiar enough with dblook to know what the current
schema is when these queries are run).
I think the correct solution is to make sure that we are in the system schema
when these queries are compiled. That will ensure that the character string
constants will take the collation of the system schema. This will also ensure
that we do not run into collation mismatch errors.
I apologize if you ended up wasting lot of time on this issue based on my
earlier feedback.
> dblook fails on TERRITORY_BASED databases
> -----------------------------------------
>
> Key: DERBY-3458
> URL: https://issues.apache.org/jira/browse/DERBY-3458
> Project: Derby
> Issue Type: Bug
> Components: Tools
> Affects Versions: 10.3.2.1
> Reporter: Stephan van Loendersloot
> Assignee: Stephan van Loendersloot
> Priority: Minor
> Fix For: 10.4.0.0
>
> Attachments: DERBY-3458_patch_1.stat, DERBY-3458_patch_1.txt,
> DERBY-3458_patch_2.stat, DERBY-3458_patch_2.txt
>
>
> I've created small patches for myself by replacing all related queries in the
> 'tools' section with CASTs to CHARs and VARCHARs and would like to contribute
> these to the community in case anyone else can confirm this is a bug.
> A small test case to reproduce the problem is provided below, the version of
> Derby that provides the stacktrace is 10.3.2.1.
> Regards,
> Stephan van Loendersloot.
> Reproduction steps:
> ---------- 1: create_territory_db.sql ----------
> CONNECT
> 'jdbc:derby://localhost/dutch;user=dutch;password=dutch;create=true;territory=nl_NL;collation=TERRITORY_BASED';
> AUTOCOMMIT OFF;
> CREATE TABLE AIRLINES
> (
> AIRLINE CHAR(2) NOT NULL ,
> AIRLINE_FULL VARCHAR(24),
> BASIC_RATE DOUBLE PRECISION,
> DISTANCE_DISCOUNT DOUBLE PRECISION,
> BUSINESS_LEVEL_FACTOR DOUBLE PRECISION,
> FIRSTCLASS_LEVEL_FACTOR DOUBLE PRECISION,
> ECONOMY_SEATS INTEGER,
> BUSINESS_SEATS INTEGER,
> FIRSTCLASS_SEATS INTEGER
> );
> COMMIT;
> DISCONNECT;
> EXIT;
> ---------- 2: use dbloook ----------
> dblook -d "jdbc:derby://localhost/dutch;user=dutch;password=dutch" -o
> dutch.sql
> ---------- 3: stacktrace ----------
> java.sql.SQLSyntaxErrorException: Comparisons between 'CHAR (UCS_BASIC)' and
> 'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable. String
> types must also have matching collation. If collation does not match, a
> possible solution is to cast operands to force them to the default collation
> (e.g. select tablename from sys.systables where CAST(tablename as
> VARCHAR(128)) = 'T1')
> at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
> at org.apache.derby.client.am.Statement.executeQuery(Unknown Source)
> at org.apache.derby.tools.dblook.prepForDump(Unknown Source)
> at org.apache.derby.tools.dblook.go(Unknown Source)
> at org.apache.derby.tools.dblook.<init>(Unknown Source)
> at org.apache.derby.tools.dblook.main(Unknown Source)
> Caused by: org.apache.derby.client.am.SqlException: Comparisons between 'CHAR
> (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported. Types must be
> comparable. String types must also have matching collation. If collation does
> not match, a possible solution is to cast operands to force them to the
> default collation (e.g. select tablename from sys.systables where
> CAST(tablename as VARCHAR(128)) = 'T1')
> at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
> at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown
> Source)
> at
> org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown
> Source)
> at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown
> Source)
> at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
> at org.apache.derby.client.am.Statement.executeQueryX(Unknown Source)
> ... 5 more
> -- **--> DEBUG: Comparisons between 'CHAR (UCS_BASIC)' and 'CHAR
> (TERRITORY_BASED)' are not supported. Types must be comparable. String types
> must also have matching collation. If collation does not match, a possible
> solution is to cast operands to force them to the default collation (e.g.
> select tablename from sys.systables where CAST(tablename as VARCHAR(128)) =
> 'T1')
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
