[
https://issues.apache.org/jira/browse/DERBY-4677?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4677:
-----------------------------------
Attachment: releaseNote.html
Attaching an updated release note. Please review to see if you have any
comments.
Also, a little bit more information on the stored procedures that would cause
this bug to allow duplicates.
A unique nullable index can be defined in one of the following 2 ways
CREATE TABLE TABLE1(NAME1 INT UNIQUE);
OR
CREATE TABLE TABLE1(NAME1 INT constraint uniq_null UNIQUE);
A table compress can be done using procedure. SYSCS_UTIL.SYSCS_COMPRESS_TABLE.
eg
CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TABLE1', 1)
A bulk insert into a table can be done using procedure
SYSCS_UTIL.SYSCS_IMPORT_TABLE. The bulk insert can either REPLACE the existing
contents of the table or it can add to the existing contents of the table. The
intention of which bulk insert is intended is indicated by the last parameter
to the procedure call.
eg of bulk insert with REPLACE option
call SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP', 'TABLE1' , 'data.txt' , null,
null, 'utf-8', 1)
eg of bulk insert with INSERT option
call SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP', 'TABLE1' , 'data.txt' , null,
null, 'utf-8', 0)
Bulk insert caused problems with unique nullable indexes only for the following
2 scenarios
1)When bulk insert with REPLACE is being done with 0 rows from the data file.
It does not matter if the table was empty before bulk insert with REPLACE.
2)When bulk insert with INSERT is being done on an empty table with multiple
rows from the data file.
> SYSCS_COMPRESS_TABLE disables unique constraints
> ------------------------------------------------
>
> Key: DERBY-4677
> URL: https://issues.apache.org/jira/browse/DERBY-4677
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.4.2.1, 10.5.1.1, 10.5.2.0,
> 10.5.3.0, 10.6.1.0
> 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
> Assignee: Mamta A. Satoor
> Fix For: 10.4.2.1, 10.5.3.1, 10.6.1.1, 10.7.0.0
>
> Attachments: DERBY4677_diff_patch1.txt, DERBY4677_stat_patch1.txt,
> releaseNote.html, releaseNote.html
>
>
> 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.
