[
https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12494598
]
A B commented on DERBY-2335:
----------------------------
Hi Mamta,
Thank you for your continued diligence with this work!
I was inspired by the test cases that you just checked in so I played a little
with collation sorting out of curiosity. Things are looking good :)
At one point it occurred to me try to create an XML column in a Polish
database. The table creation was fine but when I tried to insert into the
table, I got an error:
ERROR XJ001: <polish-snipped> Java:
'org.apache.derby.iapi.services.classfile.CONSTANT_Utf8_info:
java.lang.ClassCastException'.
I then deleted the database and started over, and I was able to insert without
problem. However, when I then created a table with a varchar column and tried
to select from it with WHERE clause, I got the same error as above.
So both statements worked fine in one scenario, but failed in another. I'm not
entirely clear on what's going on here, but the order of the statements does
matter. For example:
Scenario 1: Insert into the XML table succeeds, then select with a WHERE clause
fails.
ij> connect 'poldb;create=true;territory=pl;collation=TERRITORY_BASED';
ij> create table xt (x xml, id int);
0 rows inserted/updated/deleted
ij> insert into xt values (xmlparse(document '<my>Acorn</my>' preserve
whitespace), 1);
1 row inserted/updated/deleted
ij> create table t1 (id int, vc varchar(20));
0 rows inserted/updated/deleted
ij> select * from t1 where vc < 'aacorn';
ERROR XJ001: <polish-snipped> Java:
'org.apache.derby.iapi.services.classfile.CONSTANT_Utf8_info:
java.lang.ClassCastException'.
Scenario 2: Select with a WHERE clause succeeds, then insert into XML table
fails.
ij> connect 'poldb;create=true;territory=pl;collation=TERRITORY_BASED';
ij> create table t1 (id int, vc varchar(20));
0 rows inserted/updated/deleted
ij> select * from t1 where vc < 'aacorn';
ID |VC
--------------------------------
0 rows selected
ij> create table xt (x xml, id int);
0 rows inserted/updated/deleted
ij> insert into xt values (xmlparse(document '<my>Acorn</my>' preserve
whitespace), 1);
ERROR XJ001: <polish-snipped> Java:
'org.apache.derby.iapi.services.classfile.CONSTANT_Utf8_info:
java.lang.ClassCastException'.
Note that shutting down the database and re-connecting does not help for either
scenario:
ij> connect 'poldb;shutdown=true';
ij> disconnect all;
ij> connect 'poldb';
ij> insert into xt values (xmlparse(document '<my>Acorn</my>' preserve
whitespace), 1);
ERROR XJ001: <polish-snipped> Java:
'org.apache.derby.iapi.services.classfile.CONSTANT_Utf8_info:
java.lang.ClassCastException'.
But exiting ij and then re-connecting from a new ij session does:
ij> exit;
java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
ij version 10.3
ij> connect 'poldb';
ij> insert into xt values (xmlparse(document '<my>Acorn</my>' preserve
whitespace), 1);
1 row inserted/updated/deleted
I'm not sure if this is a problem in language, store, or XML. I'm also not
sure if this is just because collation work is still in progress--maybe this is
something that will be addressed as part of upcoming changes?
In any event, just thought I'd bring it up since I noticed it.
The full stack trace for the error is:
2007-05-09 23:01:46.301 GMT Thread[main,5,main] (XID = 144), (SESSIONID = 0),
(DATABASE = poldb), (DRDAID = null), Failed Statement is: select * from t1
where vc < 'aacorn'
java.lang.ClassCastException:
org.apache.derby.iapi.services.classfile.CONSTANT_Utf8_info
at
org.apache.derby.impl.services.bytecode.CodeChunk.getTypeDescriptor(CodeChunk.java:1007)
at
org.apache.derby.impl.services.bytecode.CodeChunk.getVariableStackDelta(CodeChunk.java:1059)
at
org.apache.derby.impl.services.bytecode.CodeChunk.stackWordDelta(CodeChunk.java:992)
at
org.apache.derby.impl.services.bytecode.CodeChunk.findMaxStack(CodeChunk.java:954)
at
org.apache.derby.impl.services.bytecode.CodeChunk.complete(CodeChunk.java:810)
at
org.apache.derby.impl.services.bytecode.BCMethod.complete(BCMethod.java:241)
at
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.generateQualMethod(BinaryRelationalOperatorNode.java:747)
at
org.apache.derby.impl.sql.compile.PredicateList.generateSingleQualifierCode(PredicateList.java:2817)
at
org.apache.derby.impl.sql.compile.PredicateList.generateQualifiers(PredicateList.java:3066)
at
org.apache.derby.impl.sql.compile.BaseJoinStrategy.fillInScanArgs1(BaseJoinStrategy.java:121)
at
org.apache.derby.impl.sql.compile.NestedLoopJoinStrategy.getScanArgs(NestedLoopJoinStrategy.java:248)
at
org.apache.derby.impl.sql.compile.FromBaseTable.getScanArguments(FromBaseTable.java:3445)
at
org.apache.derby.impl.sql.compile.FromBaseTable.generateResultSet(FromBaseTable.java:3135)
at
org.apache.derby.impl.sql.compile.FromBaseTable.generate(FromBaseTable.java:3062)
at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1351)
at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1302)
at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1351)
at
org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1302)
at
org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109)
at
org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:564)
at
org.apache.derby.impl.sql.compile.StatementNode.generate(StatementNode.java:347)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:447)
at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:742)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:567)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:516)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:330)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:531)
at
org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:376)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:271)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:215)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:181)
at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:56)
at org.apache.derby.tools.ij.main(ij.java:71)
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments:
> DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt,
> DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt,
> DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt,
> DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation
> order for user-defined character datatypes compared to UNICODE based
> collation, UCS_BASIC, used by system tables. This sub-task is added to handle
> the case where a comparison is made between character datatypes with
> different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for
> character types. And say there is a userSchema schema in that database which
> has a table tableInfo with column tablename defined as VARCHAR. This
> tableInfo.tablename will have territory based collation assoicated with it.
> And say this column is then compared with a VARCHAR column in SYS schema,
> then how will the comparison happen, since the 2 columns being compared have
> different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where
> systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following
> suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order
> to compare 2 strings which have different collations bound to them. Both
> string operands must have the same collation--that is my reading of Syntax
> rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the
> operands so that you can compare them. I think you need to write an
> expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE
> userStringColumnsCollation )
> Here's an example I googled up:
> http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
