[jira] [Commented] (DERBY-5111) NullPointerException on unique constraint violation with unique index

["John Hendrikx (JIRA)" <ji...@apache.org>]

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

John Hendrikx commented on DERBY-5111:
--------------------------------------

Just ran into this problem as well, with the latest Derby (10.10.1.1).   Database is (generated):

CREATE TABLE KEYTYPE
(
    ID INTEGER NOT NULL generated always as identity (start with 1),
    "NAME" VARCHAR(255) NOT NULL
)

ALTER TABLE KEYTYPE ADD CONSTRAINT KEYTYPE_PK PRIMARY KEY (ID)

CREATE TABLE MAPPING
(
    MAPPING_ID BIGINT NOT NULL generated always as identity (start with 1),
    CONFIGSET_ID_OID INTEGER NOT NULL,
    "KEY" VARCHAR(255) NOT NULL,
    KEYTYPE_ID_OID INTEGER NOT NULL,
    UUID VARCHAR(255) NOT NULL
)

ALTER TABLE MAPPING ADD CONSTRAINT MAPPING_PK PRIMARY KEY (MAPPING_ID)

CREATE TABLE CONFIGSET
(
    ID INTEGER NOT NULL generated always as identity (start with 1),
    "DATE" TIMESTAMP NOT NULL,
    "NAME" VARCHAR(255) NOT NULL
)

ALTER TABLE CONFIGSET ADD CONSTRAINT CONFIGSET_PK PRIMARY KEY (ID)
CREATE UNIQUE INDEX KEYTYPE_U1 ON KEYTYPE ("NAME")
CREATE INDEX MAPPING_N49 ON MAPPING (KEYTYPE_ID_OID)
CREATE INDEX MAPPING_N50 ON MAPPING (CONFIGSET_ID_OID)
ALTER TABLE MAPPING ADD CONSTRAINT MAPPING_FK1 FOREIGN KEY (CONFIGSET_ID_OID) REFERENCES CONFIGSET (ID) 
ALTER TABLE MAPPING ADD CONSTRAINT MAPPING_FK2 FOREIGN KEY (KEYTYPE_ID_OID) REFERENCES KEYTYPE (ID) 
CREATE UNIQUE INDEX MAPPING_U1 ON MAPPING (UUID,KEYTYPE_ID_OID)
CREATE UNIQUE INDEX MAPPING_U2 ON MAPPING (KEYTYPE_ID_OID,"KEY")

NPE occurs when MAPPING_U2 is violate by means of an insert.  Note that there is no overlap in indexes in this case.

Stacktrace below.

Unfortunately, it will mean we cannot use this in production.  I don't quite understand why this bug, open since 2011, hasn't been tackled -- it deals with a rather basic feature of a database.

Caused by: java.lang.NullPointerException
	at org.apache.derby.impl.sql.execute.IndexChanger.insertAndCheckDups(Unknown Source)
	at org.apache.derby.impl.sql.execute.IndexChanger.doInsert(Unknown Source)
	at org.apache.derby.impl.sql.execute.IndexChanger.insert(Unknown Source)
	at org.apache.derby.impl.sql.execute.IndexSetChanger.insert(Unknown Source)
	at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(Unknown Source)
	at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown Source)
	at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
	at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
	... 72 more
                
> NullPointerException on unique constraint violation with unique index
> ---------------------------------------------------------------------
>
>                 Key: DERBY-5111
>                 URL: https://issues.apache.org/jira/browse/DERBY-5111
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1
>            Reporter: Mark La Rue
>              Labels: derby_triage10_9
>
> Given the below DDL, violations of the unique constraint (an example follows the DDL) specified on table t1 will result in a NullPointerException that terminates the active connection.  The root cause appears to be related to the unique indexes; removing them results in the correct constraint violation.
> CREATE TABLE s.t0 (t0_id INTEGER NOT NULL, value VARCHAR(75) NOT NULL);
> ALTER TABLE s.t0 ADD CONSTRAINT pk_1 PRIMARY KEY (t0_id);
> CREATE TABLE s.t1 (t1_id INTEGER NOT NULL, t0_id INTEGER NOT NULL, value VARCHAR(75) NOT NULL);
> CREATE UNIQUE INDEX s.ui_1 ON s.t1 (t1_id);
> ALTER TABLE s.t1 ADD CONSTRAINT pk_2 PRIMARY KEY (t1_id);
> ALTER TABLE s.t1 ADD CONSTRAINT fk_1 FOREIGN KEY (t0_id) REFERENCES s.t0 (t0_id) ON DELETE CASCADE;
> CREATE UNIQUE INDEX s.ui_2 ON s.t1 (t0_id, value);
> ALTER TABLE s.t1 ADD CONSTRAINT uc_1 UNIQUE (t0_id, value);
> Minimal DML to trigger the constraint violation:
> INSERT INTO s.t0 VALUES (0, 'foobar');
> INSERT INTO s.t1 VALUES(0, 0, 'Test');
> INSERT INTO s.t1 VALUES(1, 0, 'Test');

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira