SYSCS_COMPRESS_TABLE disables unique constraints
------------------------------------------------
Key: DERBY-4677
URL: https://issues.apache.org/jira/browse/DERBY-4677
Project: Derby
Issue Type: Bug
Affects Versions: 10.6.1.0, 10.5.3.0, 10.5.1.1
Environment: Output of sysinfo:
------------------ Java Information ------------------
Java Version: 1.6.0_20
Java Vendor: Sun Microsystems Inc.
Java home: C:\Program Files (x86)\Java\jre6
Java classpath: .;C:\Program Files
(x86)\Java\jre6\lib\ext\QTJava.zip;C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\bin\../lib/derby.jar;C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\bin\../lib/derbynet.jar;C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\bin\../lib/derbyclient.jar;C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\bin\../lib/derbytools.jar
OS name: Windows 7
OS architecture: x86
OS version: 6.1
Java user name: bmason
Java user home: C:\Users\BMASON
Java user dir: C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\bin
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
java.runtime.version: 1.6.0_20-b02
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\lib\derby.jar] 10.6.1.0 -
(938214)
[C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\lib\derbytools.jar]
10.6.1.0 - (938214)
[C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\lib\derbynet.jar] 10.6.1.0
- (938214)
[C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\lib\derbyclient.jar]
10.6.1.0 - (938214)
------------------------------------------------------
----------------- Locale Information -----------------
Current Locale : [English/New Zealand [en_NZ]]
Found support for locale: [cs]
version: 10.6.1.0 - (938214)
Found support for locale: [de_DE]
version: 10.6.1.0 - (938214)
Found support for locale: [es]
version: 10.6.1.0 - (938214)
Found support for locale: [fr]
version: 10.6.1.0 - (938214)
Found support for locale: [hu]
version: 10.6.1.0 - (938214)
Found support for locale: [it]
version: 10.6.1.0 - (938214)
Found support for locale: [ja_JP]
version: 10.6.1.0 - (938214)
Found support for locale: [ko_KR]
version: 10.6.1.0 - (938214)
Found support for locale: [pl]
version: 10.6.1.0 - (938214)
Found support for locale: [pt_BR]
version: 10.6.1.0 - (938214)
Found support for locale: [ru]
version: 10.6.1.0 - (938214)
Found support for locale: [zh_CN]
version: 10.6.1.0 - (938214)
Found support for locale: [zh_TW]
version: 10.6.1.0 - (938214)
------------------------------------------------------
Reporter: Brett Mason
It appears that running SYSCS_UTIL.SYSCS_COMPRESS_TABLE on a table with a
null-able unique constraint will disable the unique constraint. The script
below should reproduce the problem. The expected behaviour is for the second
insert to fail due to the unique constraint but instead it is allowed. The
second insert will fail as expected if either the call to SYSCS_COMPRESS_TABLE
is skipped or if the column is declared NOT NULL.
I have reproduced the problem using embedded Derby 10.5.1.1, 10.5.3.0 and
10.6.1.0 using ij.
CREATE TABLE TABLE1(NAME1 INT UNIQUE);
CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TABLE1', 1);
INSERT INTO TABLE1(NAME1) VALUES(1);
INSERT INTO TABLE1(NAME1) VALUES(1);
SELECT * FROM TABLE1;
DROP TABLE TABLE1;
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
