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