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-2955) ERROR 42ZA2 creating table with check constraint with literal comparison
Date Fri, 20 Jul 2007 06:45:06 GMT

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


Mime
View raw message