RE: [Firebird-devel] [Bug #123056] Blob-IDs are sometimes shared between more rows

[<ano...@we...>]

Is this a BUG or a limitation?

Lots of database systems have special handling of large fields within stored
procedures / triggers... This doesn't surprise me at all - I think you'd see
the same thing in MS-SQL 7

am.

-----Original Message-----
From: fir...@li...
[mailto:fir...@li...]On Behalf Of
no...@so...
Sent: Tuesday, November 21, 2000 5:08 AM
To: pre...@ms...; no...@so...;
fir...@li...
Subject: [Firebird-devel] [Bug #123056] Blob-IDs are sometimes shared
between more rows


Bug #123056, was updated on 2000-Nov-21 05:07
Here is a current snapshot of the bug.

Project: Firebird
Category: Core Engine
Status: Open
Resolution: None
Bug Group: Confirmed
Priority: 5
Summary: Blob-IDs are sometimes shared between more rows

Details: In SP, when you copy record (from/to the same table) by
  INSERT INTO tab SELECT ... FROM tab WHERE ...;
or by
  SELECT ... FROM ... INTO _local_variables_;
  INSERT INTO tab VALUES (_local_variables_);

and the record contains BLOB, then _sometimes_
newly created row will not contain its own copy
of the BLOB, but instead it will use the same
blob-id as the original record.
(i.e. single blob is shared among more rows)

This is really severe bug, because it will cause
data lost - when you delete one of these rows,
and then try to read the other one, you will get
"BLOB not found" error !!!

Interestingly, when you execute exactly the same
command directly, not as part of SP, blob-ids will be o.k.

Here is script to reproduce this bug:
=====================================
CREATE DATABASE 'C:\test.gdb' USER 'SYSDBA' PASSWORD 'masterkey';

CREATE TABLE t (
  i INTEGER,
  b BLOB );

SET TERM ^;

CREATE PROCEDURE p (x INTEGER) AS
BEGIN
  INSERT INTO t (i,b)
    SELECT i+100, b FROM t WHERE i=:x;
END^

SET TERM ;^

COMMIT;

/* insert some blob into our test table */
INSERT INTO t (i,b)
  SELECT 1, rdb$trigger_blr
    FROM rdb$triggers
   WHERE rdb$trigger_name='RDB$TRIGGER_1';

COMMIT;


/* now make copy of that row ... */
/* INSERT INTO t (i,b)
  SELECT i+100, b FROM t WHERE i=1; */
INSERT INTO t (i,b)
  SELECT i+100, b FROM t WHERE i=1;
INSERT INTO t (i,b)
  SELECT i+100, b FROM t WHERE i=101;
INSERT INTO t (i,b)
  SELECT i+100, b FROM t WHERE i=101;

SET BLOBDISPLAY OFF;
/* ... and show result; */
/* all blobs will have different blob-ids */
SELECT * FROM t;

ROLLBACK;

/* now execute the same insert statements, but wrapped in SP */
/* EXECUTE PROCEDURE p 1; */
EXECUTE PROCEDURE p 1;
EXECUTE PROCEDURE p 101;
EXECUTE PROCEDURE p 101;

/* here you will see that blob-id is shared ... */
SELECT * FROM t;


/* and here you can see what will happen when one row is deleted ... */
SET BLOBDISPLAY ALL;
DELETE FROM t WHERE i=101;
COMMIT;
SELECT * FROM t;


/************
One more test - uncomment first of "EXECUTE PROCEDURE p 1" commands
and run this script again - you will see correct blob-ids,
i.e. the wrong behaviour is not consistent.
************/



For detailed info, follow this link:
http://sourceforge.net/bugs/?func=detailbug&bug_id=123056&group_id=9028
_______________________________________________
Firebird-devel mailing list
Fir...@li...
http://lists.sourceforge.net/mailman/listinfo/firebird-devel