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] Updated: (DERBY-4677) SYSCS_COMPRESS_TABLE disables unique constraints
Date Mon, 21 Jun 2010 16:38:27 GMT

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


Mime
View raw message