Hi.

I was reporting this "bug" to the tracker on firebird@sourceforge, but
learned there of the exixtence of this mailing list.

So I'm here with this strange and quite frightening behaviour. I hope
to have missed something...

I have dropped and recreated a table (with some modifications) under a
non-autocommit transaction. This table have a primary key on a couple
of integer fields. Before this operation, the primary key was
(obviously, I'd say) correctly behaving, but after having
reconstructed the table and its primary key, I'm now able to insert
duplicated values!

If the same operations are executed with DDL autocommit enabled, this
problem doesn't arise.

I've verified this more and more and always got the same misbehaviour.

Here's a couple of scripts for recreating the situation.

1) Initial Database and Table creation:

---

SET SQL DIALECT 3;

CREATE DATABASE '/data/fb_bug.fdb' page_size 4096
user 'SYSDBA' password 'masterkey';

CREATE TABLE TESTTBL (
TESTFLD INTEGER NOT NULL);

ALTER TABLE TESTTBL ADD CONSTRAINT PK_TESTTBL PRIMARY KEY (TESTFLD);

INSERT INTO TESTTBL (TESTFLD) VALUES (1);
INSERT INTO TESTTBL (TESTFLD) VALUES (1); /* PK VIOLATION ERROR */

COMMIT;

SELECT * FROM TESTTBL;

---

This creates a simple table and tries to write two identical values on
the PK field, obviously raising an error on the second insert statement.

2) Table recreation with problems:

---

SET AUTODDL OFF;

DROP TABLE TESTTBL;

CREATE TABLE TESTTBL (
TESTFLD INTEGER NOT NULL,
NEWFLD INTEGER);

ALTER TABLE TESTTBL ADD CONSTRAINT PK_TESTTBL PRIMARY KEY (TESTFLD);

COMMIT;

SET AUTODDL ON;

INSERT INTO TESTTBL (TESTFLD) VALUES (1);
INSERT INTO TESTTBL (TESTFLD) VALUES (1); /* ACCEPTED!!! */

COMMIT;

SELECT * FROM TESTTBL;

---

As you can see, there's a simple DROP, a CREATE and the enforcement of
the primary key into a non-autocommit transaction. After the commit,
two identical values are inserted on the PK field and Firebird doesn't
hesitate to allow this!

You can run these scripts from isql, but be warned that the problem
only appears if you run them in two separate sessions, i.e. start
isql, execute the first script, close isql (or maybe close the
connection with the database), reopen isql (or reconnect to the
database) and execute the second script...

WHAT AM I MISSING? Or is this a true bug?

Regards,
Bozzy.