[
https://issues.apache.org/jira/browse/DERBY-2955?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12514119
]
Mamta A. Satoor commented on DERBY-2955:
----------------------------------------
Checked in a fix for this in main with revision 557886 with following commit
comments
We used to set the collation type of character string columns in the generate
phase rather than the bind phase of create table. But this will cause problem
with following query
CREATE TABLE STAFF9 (EMPNAME CHAR(20),
CONSTRAINT STAFF9_EMPNAME CHECK (EMPNAME NOT LIKE 'T%'))
For the query above, when run in a territory based db, we need to have the
correct collation set in bind phase of create table so that when LIKE is
handled in LikeEscapeOperatorNode, we have the correct collation set for
EMPNAME otherwise it will throw an exception for 'T%' having collation of
territory based and EMPNAME having the default collation of UCS_BASIC. The
change in this commit will ensure that character string columns get their
collation set early on in the bind phase so when the bind code for LIKE kicks
in, we are all set with correct collation information.
> ERROR 42ZA2 creating table with check constraint with literal comparison
> ------------------------------------------------------------------------
>
> Key: DERBY-2955
> URL: https://issues.apache.org/jira/browse/DERBY-2955
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.0.0
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
>
> I cannot create the following table with a collated database.
> EMPNAME should be TERRITORY_BASED collation I think, but perhaps is not
> initialized in time for the check constraint processing.
> ij> connect
> 'jdbc:derby:nordb;create=true;territory=no_NO;collation=TERRITORY_BASED';
> ij> CREATE TABLE STAFF9 (EMPNUM CHAR(3) NOT NULL
> CONSTRAINT STAFF9_PK PRIMARY KEY,
> EMPNAME CHAR(20),
> GRADE DECIMAL(4),
> CITY CHAR(15),
> CONSTRAINT STAFF9_EMPNAME CHECK (EMPNAME NOT LIKE 'T%'));
> ERROR 42ZA2: Operand of LIKE predicate with type CHAR(20) and collation
> UCS_BASIC is not compatable with LIKE pattern op
> erand with type CHAR(2) and collation TERRITORY_BASED.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
