db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3458) dblook fails on TERRITORY_BASED databases
Date Tue, 26 Feb 2008 06:37:51 GMT

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


Mime
View raw message