[DERBY-1854] SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 10.1.3.1
• Fix Version/s: 10.1.3.3, 10.2.1.6, 10.3.1.4
• Component/s: SQL
• Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
• Urgency: Normal
• Bug behavior facts: Regression
• Assignee: Suresh Thalamati
• Reporter: Chad Loder
• Created: 15/Sep/06 00:04
• Updated: 05/Jul/10 17:32
• Resolved: 22/Sep/06 17:49

Description

Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.

connect 'jdbc:derby:/testdb;create=true';

CREATE TABLE users (
user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
user_login VARCHAR(255) NOT NULL,
PRIMARY KEY (user_id));

CREATE TABLE admins (
user_id INT NOT NULL,
PRIMARY KEY (user_id),
CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));

INSERT INTO users (user_login) VALUES('TEST1');
INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());

CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);

SELECT * from admins;

Issue Links

• duplicates: DERBY-1641 Conglomerate requested does not exist after syscs_import_table with FK
• is related to: DERBY-1641 Conglomerate requested does not exist after syscs_import_table with FK
• relates to: DERBY-1489 Provide ALTER TABLE DROP COLUMN functionality