db-derby-dev mailing list archives

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

    [ https://issues.apache.org/jira/browse/DERBY-3458?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12572621#action_12572621
] 

Stephan van Loendersloot commented on DERBY-3458:
-------------------------------------------------

Hello Mamta,

Thanks for taking time to review my proposed solution. I really don't think I've wasted time,
since the functional test took more time than creating the patch, but it provided some useful
insights on how this stuff currently works in Derby. Besides, I'll need the test anyway when
trying to resolve this issue.

Now, if I understand correctly, you're basically implying that *all* operations on system
tables should be done with UCS_BASIC collation, which can be enforced by changing the current
connection schema to SYS. I read about the UCS_BASIC part while lurking on the dev-list, but
a proposed workaround seemed to use casting.

Having investigated the internals of dblook, I found that only one connection to the database
is made, which is passed on to all required (static) methods, with no other instances around.
This would really make the patch a lot simpler.

While poking around in a few other tests, but specifcally CollationTest.java, I came up with
the idea to ensure that for 'dblook_test.java' the UCS_BASIC collation type is used right
after the database has been created and for 'dblook_test_territory' the 'TERRITORY_BASED'
collation type is used, just to be sure.

This will affect the network test as well, but that doesn't have a territory-based test yet
(as I still have to correct my environment), so I don't think it causes any problems.

I'll make the changes and return shortly with a report and most likely a new patch.


 --Stephan.



> 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