[DERBY-3300] CREATE INDEX that matches an existing index succeeds with a 01504 warning but does not create the index.

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 10.3.1.4, 10.3.2.1
• Component/s: SQL
• Labels: derby_triage10_5_2
• Urgency: Normal
• Issue & fix info: Repro attached
• Assignee: Unassigned
• Reporter: Daniel John Debrunner
• Created: 03/Jan/08 00:18
• Updated: 18/Jun/13 16:36

Description

Since CREATE INDEX statement succeeds the index should be created, the warning is useful but doesn't imply the index was not created.
Derby allows a single physical index to support multiple logical indexes so allowing the index to be created should be fine.

ij> create table t(i int);
0 rows inserted/updated/deleted
ij> create index ti1 on t;
0 rows inserted/updated/deleted
ij> create index ti2 on t;
0 rows inserted/updated/deleted
WARNING 01504: The new index is a duplicate of an existing index: TI1.
ij> drop index ti2;
ERROR 42X65: Index 'TI2' does not exist.

Issue Links

• is related to: DERBY-991 Defining the same primary key twice on a table actually attempts to create two constraints.
• is related to: DERBY-6269 Misleading warning text if you create an index duplicating a constraint.
• is related to: DERBY-789 Usability issue: "Constraints have the same set of columns"