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-4677) SYSCS_COMPRESS_TABLE disables unique constraints
Date Fri, 04 Jun 2010 05:10:59 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4677?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12875485#action_12875485
] 

Mamta A. Satoor commented on DERBY-4677:
----------------------------------------

I wanted to try out the case where because of the bug in Derby, you are able to insert duplicate
rows after compress table even though the table has unique nullable index defined on it. Next,
I fixed the Derby code to properly copy the unique nullable property during index recreation
time of the compress table process. I ran the buggy database with the new Derby code on my
machine. First I inserted more duplicate rows(because store still thinks the index is not
unique from the compress earlier with the buggy Derby version), and then did compress on the
table. After this compress, I can't insert any more duplicate row. Interestingly enough though,
no errors were raised during compress because of the duplicate rows. I will look into the
code why we do not run into problems during index recreation when there are duplicate rows
in the table.

 Following are the exact steps followed for this testing

Build Derby code based on the trunk codeline. This Derby version has the bug which allows
duplicate rows after compress table. Use this buggy Derby version to run following ij scripts
which shows that duplicate rows are allowed
$ java -Dij.exceptionTrace=true org.apache.derby.tools.ij
ij version 10.7
ij> connect 'jdbc:derby:testDB4677;create=true';
ij> CREATE TABLE TABLE1(NAME1 INT UNIQUE);
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TABLE1', 1);
0 rows inserted/updated/deleted
ij> INSERT INTO TABLE1(NAME1) VALUES(1);
1 row inserted/updated/deleted
ij> INSERT INTO TABLE1(NAME1) VALUES(1);
1 row inserted/updated/deleted
ij> SELECT * FROM TABLE1;
NAME1
-----------
1
1

2 rows selected
ij> exit;

Notice the duplicate rows in TABLE1 in the above ij session.

Next, in my trunk codeline, fix AlterTableConstantAction to use the unique nullable property
during index recreation if the index was defined unique nullable. This is done when the table
is compressed. 

When I run the above db with this fixed code, it first allows duplicate row creations because
I have not yet run compress table. 
$ java -Dij.exceptionTrace=true org.apache.derby.tools.ij
ij version 10.7
ij> connect 'jdbc:derby:testDB4677;create=true';
WARNING 01J01: Database 'testDB4677' not created, connection made to existing da
tabase instead.
ij> INSERT INTO TABLE1(NAME1) VALUES(1);
1 row inserted/updated/deleted
ij> SELECT * FROM TABLE1;
NAME1
-----------
1
1
1

3 rows selected

But once I do compress table, now we start disallowing creation of further duplicate rows.

ij> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TABLE1', 1);
0 rows inserted/updated/deleted
ij> SELECT * FROM TABLE1;
NAME1
-----------
1
1
1

3 rows selected
ij> INSERT INTO TABLE1(NAME1) VALUES(1);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value
in a unique or primary key constraint or unique index identified by 'SQL100603220123640' defined
on 'TABLE1'.

But I had thought that with my changes, the compress table operation would fail because of
the duplicate rows but it does not fail. I will look further into this.


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