Issue Details (XML | Word | Printable)

Key: CORE-4212
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Blocker Blocker
Assignee: Vlad Khorsun
Reporter: Andrei Kireev
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Dropping FK on GTT crashes server

Created: 09/Sep/13 01:24 PM   Updated: 22/Sep/15 09:35 AM
Component/s: Engine
Affects Version/s: 3.0 Initial, 2.5.0, 2.5.1, 2.5.2, 2.5.2 Update 1, 3.0 Alpha 1
Fix Version/s: 3.0 Alpha 2, 2.5.3

Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
Here is a script which creates some permanent and global temporary tables:

============================================================

SET NAMES WIN1251;
SET SQL DIALECT 3;
CREATE DATABASE 'put_your_database_name'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET WIN1251;

CREATE DOMAIN dintkey
  AS INTEGER NOT NULL
  CHECK (VALUE > 0);

CREATE DOMAIN dtext255
  AS VARCHAR(255) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

CREATE DOMAIN dtext20
  AS VARCHAR(20) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

CREATE DOMAIN dboolean
  AS SMALLINT
  DEFAULT 0
  CHECK ((VALUE IS NULL) OR (VALUE IN (0, 1)));

CREATE DOMAIN dboolean_notnull
  AS SMALLINT
  DEFAULT 0
  NOT NULL
  CHECK (VALUE IN (0, 1));

CREATE DOMAIN dblobtext80_1251
  AS BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET win1251;

CREATE DOMAIN dname
  AS VARCHAR(60) CHARACTER SET WIN1251 NOT NULL COLLATE PXW_CYRL;

CREATE DOMAIN dclassname
  AS VARCHAR(40) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

CREATE DOMAIN dtext60
  AS VARCHAR(60) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

CREATE DOMAIN dinteger_notnull
  AS INTEGER NOT NULL;

CREATE DOMAIN dinteger
  AS INTEGER;

CREATE DOMAIN dforeignkey
  AS INTEGER;


CREATE TABLE at_namespace (
  id dintkey,
  name dtext255 NOT NULL UNIQUE,
  caption dtext255,
  filename dtext255,
  filetimestamp TIMESTAMP,
  version dtext20 DEFAULT '1.0.0.0' NOT NULL,
  dbversion dtext20,
  optional dboolean_notnull DEFAULT 0,
  internal dboolean_notnull DEFAULT 1,
  comment dblobtext80_1251,
  settingruid VARCHAR(21),

  CONSTRAINT at_pk_namespace PRIMARY KEY (id)
);

CREATE GLOBAL TEMPORARY TABLE at_namespace_file (
  filename dtext255,
  filetimestamp TIMESTAMP,
  filesize dinteger,
  name dtext255 NOT NULL UNIQUE,
  caption dtext255,
  version dtext20,
  dbversion dtext20,
  optional dboolean_notnull DEFAULT 0,
  internal dboolean_notnull DEFAULT 1,
  comment dblobtext80_1251,
  xid dinteger,
  dbid dinteger,

  CONSTRAINT at_pk_namespace_file PRIMARY KEY (filename)
)
  ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE at_namespace_sync (
  namespacekey dforeignkey,
  filename dtext255,
  operation CHAR(2) DEFAULT ' ' NOT NULL,

  CONSTRAINT at_fk_namespace_sync_nsk
    FOREIGN KEY (namespacekey) REFERENCES at_namespace (id),
  CONSTRAINT at_fk_namespace_sync_fn
    FOREIGN KEY (filename) REFERENCES at_namespace_file (filename)
      ON UPDATE CASCADE
      ON DELETE CASCADE,
  CONSTRAINT at_chk_namespace_sync_op
    CHECK (operation IN (' ', '< ', '> ', '>>', '<<', '==', '=>', '<=', '! ', '? '))
)
  ON COMMIT DELETE ROWS;

============================================================

Next statement will crash server:

alter table at_namespace_sync drop constraint at_fk_namespace_sync_nsk

Confirmed for embedded and client-server versions.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 09/Sep/13 01:38 PM
Foreign key constraint from GTT to permanent table should be prohibited.
So, real bug is that such constraint is created.

Sean Leyne added a comment - 09/Sep/13 02:48 PM
@Vlad,

It seems reasonable that a GTT should have a dependency on a permanent table. (Which is not how your comment reads)

The reverse (a permanent table depending on GTT) or a GTT depending on another GTT, however, should most certainly be prevented.

Vlad Khorsun added a comment - 09/Sep/13 03:02 PM
It is documented since introducing GTT in v2.1 that constraints between temporary and permanent tables is forbidden.
This requirement is per SQL standard.

Vlad Khorsun added a comment - 10/Sep/13 09:09 AM
Below is simple example of how to reproduce bug without creating forbidden things (which will be subject of another ticket):

SQL> create database 'core4212.fdb' default character set win1251;
SQL> create global temporary table t1 (id varchar(8) not null primary key);
SQL> create global temporary table t2 (id varchar(8));
SQL> alter table t2 add constraint t2_fk foreign key (id) references t1 (id);
SQL>
SQL> connect core4212.fdb;
Commit current transaction (y/n)?y
Committing.
Database: core4212.fdb
SQL> alter table t2 drop constraint t2_fk;
Statement failed, SQLSTATE = 08006
connection lost to database
Statement failed, SQLSTATE = 08006
Error writing data to the connection.

The key of the issue is:
a) foreign key constraint at GTT
b) at least one field in FK should be [VAR]CHAR with some international charset
c) the DROP CONSTRAINT statement should be issued in a such attachment where corresponding relation was never referenced and missed in metadata cache (therefore i do reconnect in example above)

Vlad Khorsun added a comment - 10/Sep/13 02:41 PM
Fixed in v2.5.3 and v3.0 alpha 2

v2.1 contains same code but it seems not affected because of somewhat different metadata loading.
The patch could be backported if strongly needed.

Sean Leyne added a comment - 10/Sep/13 09:42 PM
Clarification on SQL Standard:

The spec only allows permanent tables to reference permanent tables, and GTTs to reference GTTs.

So, GTT can have dependencies/references, but only to another GTT.

Vlad Khorsun added a comment - 13/Sep/13 07:26 AM
Just for info, there is simple workaround : make any DML query with detail GTT before attempt to drop FK.
It ensure that GTT's metadata is loaded by the engine.