You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Kathey Marsden (JIRA)" <ji...@apache.org> on 2007/07/19 16:44:06 UTC
[jira] Created: (DERBY-2955) ERROR 42ZA2 creating table with check
constraint with literal comparison
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
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.
[jira] Assigned: (DERBY-2955) ERROR 42ZA2 creating table with check
constraint with literal comparison
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor reassigned DERBY-2955:
--------------------------------------
Assignee: Mamta A. Satoor
> 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.
[jira] Commented: (DERBY-2955) ERROR 42ZA2 creating table with
check constraint with literal comparison
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Closed: (DERBY-2955) ERROR 42ZA2 creating table with check
constraint with literal comparison
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor closed DERBY-2955.
----------------------------------
> 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
> Fix For: 10.3.1.4, 10.4.0.0
>
>
> 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.
[jira] Commented: (DERBY-2955) ERROR 42ZA2 creating table with
check constraint with literal comparison
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2955?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12513913 ]
Kathey Marsden commented on DERBY-2955:
---------------------------------------
Here is another error that can occur with check constraint from the temp_schema10 test.
ij> CREATE TABLE RET_CATALOG (
VENDOR_ID INT,
PRODUCT_ID INT,
WHOLESALE NUMERIC (10,2),
RETAIL NUMERIC (10,2),
MARKUP NUMERIC (10,2),
EXPORT_CODE CHAR(2),
EXPORT_LICNSE_DATE CHAR(20),
CHECK (EXPORT_LICNSE_DATE IS NULL OR (
EXPORT_CODE = 'F1' OR
EXPORT_CODE = 'F2' OR
EXPORT_CODE = 'F3' )),
CHECK (EXPORT_CODE <> 'F2' OR WHOLESALE > 10000.00),
CHECK (RETAIL >= WHOLESALE),
CHECK (RETAIL = WHOLESALE + MARKUP));
ERROR 42818: Comparisons between 'CHAR' and 'CHAR' are not supported.
> 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
>
> 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.
[jira] Resolved: (DERBY-2955) ERROR 42ZA2 creating table with check
constraint with literal comparison
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor resolved DERBY-2955.
------------------------------------
Resolution: Fixed
Fix Version/s: 10.4.0.0
10.3.1.4
Migrated changes into 10.3 codeline as part of revision 557910
> 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
> Fix For: 10.3.1.4, 10.4.0.0
>
>
> 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.