From: <no...@so...> - 2000-11-21 13:07:51
|
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 |
From: <ano...@we...> - 2000-11-21 17:20:38
|
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 |
From: Ann W. H. <har...@ne...> - 2000-11-21 17:47:10
|
At 09:04 AM 11/21/2000 -0800, ano...@we... wrote: >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 In an ordinary assignment, when a blob is copied from one row to another, InterBase makes a new copy. Blob handling never really made it into stored procedures. I think this behavior is a bug. Regards, Ann |
From: <ano...@we...> - 2000-11-21 18:42:17
|
I won't argue that, but, what is the correct solution... Perhaps nulling them unless they are specifically loaded? quoting from the MS-SQL books online re CREATE TRIGGER (T-SQL): Triggers can include any number and kind of Transact-SQL statements except SELECT. A trigger is designed to check or change data based on a data modification statement; it should not return data to the user. The Transact-SQL statements in a trigger often include control-of-flow language. A few special tables are used in CREATE TRIGGER statements: deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined (that is, the table on which the user action is attempted) and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use: SELECT * FROM deleted In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext or image column references in the inserted and deleted tables if the compatibility level is equal to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable. END QUOTATION So they require special handling. The reason is apparently due to the performance considerations (memory etc) which would be required for dealing with the potentially large field values (GB and GB - right?) By doing this, they allow an optimization of skipping the fields which would result a severe performance hit. QUESTION: Is there something different about the structure of Interbase which would allow improvement on this functionality without killing speed? I have my doubts as now for example, tables involving blobs still suffer from that scrollback problem when accessed without special consideration - right? am. -----Original Message----- From: fir...@li... [mailto:fir...@li...]On Behalf Of Ann W. Harrison Sent: Tuesday, November 21, 2000 9:45 AM To: fir...@li... Subject: RE: [Firebird-devel] [Bug #123056] Blob-IDs are sometimes shared between more rows At 09:04 AM 11/21/2000 -0800, ano...@we... wrote: >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 In an ordinary assignment, when a blob is copied from one row to another, InterBase makes a new copy. Blob handling never really made it into stored procedures. I think this behavior is a bug. Regards, Ann _______________________________________________ Firebird-devel mailing list Fir...@li... http://lists.sourceforge.net/mailman/listinfo/firebird-devel |
From: <no...@so...> - 2001-03-23 10:52:44
|
Bugs item #223056, was updated on 2000-11-21 05:07 You can respond by visiting: http://sourceforge.net/tracker/?func=detail&atid=109028&aid=223056&group_id=9028 Category: Core Engine Group: Confirmed Bug Status: Open Priority: 5 Submitted By: Ivan Prenosil (prenosil) Assigned to: Nobody/Anonymous (nobody) Summary: Blob-IDs are sometimes shared between more rows Initial Comment: 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. ************/ ---------------------------------------------------------------------- Comment By: Nobody/Anonymous (nobody) Date: 2001-03-23 02:52 Message: Logged In: NO More observations: -just adding COMMIT between calling procedures will not change anything (i.e. blob-id will be "reused") EXECUTE PROCEDURE p 1; COMMIT; EXECUTE PROCEDURE p 101; COMMIT; EXECUTE PROCEDURE p 101; -adding CONNECT causes that blob-ids will be correct ! EXECUTE PROCEDURE p 1; COMMIT; EXECUTE PROCEDURE p 101; COMMIT; CREATE DATABASE 'C:\test.gdb' USER 'SYSDBA' PASSWORD 'pwd'; EXECUTE PROCEDURE p 101; In this case first "EXECUTE PROCEDURE p 101" will create wrong blob-id, but second one will create correct - new blob-id. ---------------------------------------------------------------------- You can respond by visiting: http://sourceforge.net/tracker/?func=detail&atid=109028&aid=223056&group_id=9028 |
From: <no...@so...> - 2001-07-01 01:04:36
|
Bugs item #223056, was opened at 2000-11-21 05:07 You can respond by visiting: http://sourceforge.net/tracker/?func=detail&atid=109028&aid=223056&group_id=9028 Category: Core Engine Group: Confirmed Bug >Status: Closed >Resolution: Fixed >Priority: 7 Submitted By: Ivan Prenosil (prenosil) >Assigned to: Claudio Valderrama C. (robocop) Summary: Blob-IDs are sometimes shared between more rows Initial Comment: 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. ************/ ---------------------------------------------------------------------- >Comment By: Claudio Valderrama C. (robocop) Date: 2001-06-30 18:04 Message: Logged In: YES user_id=62823 Dirty buffer provided for a new record by VIO was the cause. Thanks to Ann for providing the code that solves the problem. Thanks to Ivan for providing a brief and reproducible test case. C. ---------------------------------------------------------------------- Comment By: Nobody/Anonymous (nobody) Date: 2001-03-23 02:52 Message: Logged In: NO More observations: -just adding COMMIT between calling procedures will not change anything (i.e. blob-id will be "reused") EXECUTE PROCEDURE p 1; COMMIT; EXECUTE PROCEDURE p 101; COMMIT; EXECUTE PROCEDURE p 101; -adding CONNECT causes that blob-ids will be correct ! EXECUTE PROCEDURE p 1; COMMIT; EXECUTE PROCEDURE p 101; COMMIT; CREATE DATABASE 'C:\test.gdb' USER 'SYSDBA' PASSWORD 'pwd'; EXECUTE PROCEDURE p 101; In this case first "EXECUTE PROCEDURE p 101" will create wrong blob-id, but second one will create correct - new blob-id. ---------------------------------------------------------------------- You can respond by visiting: http://sourceforge.net/tracker/?func=detail&atid=109028&aid=223056&group_id=9028 |
From: <no...@so...> - 2001-11-13 04:05:00
|
Bugs item #223056, was opened at 2000-11-21 05:07 You can respond by visiting: http://sourceforge.net/tracker/?func=detail&atid=109028&aid=223056&group_id=9028 Category: Core Engine >Group: Fixed v0.9-5 Status: Closed Resolution: Fixed Priority: 7 Submitted By: Ivan Prenosil (prenosil) Assigned to: Claudio Valderrama C. (robocop) Summary: Blob-IDs are sometimes shared between more rows Initial Comment: 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. ************/ ---------------------------------------------------------------------- Comment By: Claudio Valderrama C. (robocop) Date: 2001-06-30 18:04 Message: Logged In: YES user_id=62823 Dirty buffer provided for a new record by VIO was the cause. Thanks to Ann for providing the code that solves the problem. Thanks to Ivan for providing a brief and reproducible test case. C. ---------------------------------------------------------------------- Comment By: Nobody/Anonymous (nobody) Date: 2001-03-23 02:52 Message: Logged In: NO More observations: -just adding COMMIT between calling procedures will not change anything (i.e. blob-id will be "reused") EXECUTE PROCEDURE p 1; COMMIT; EXECUTE PROCEDURE p 101; COMMIT; EXECUTE PROCEDURE p 101; -adding CONNECT causes that blob-ids will be correct ! EXECUTE PROCEDURE p 1; COMMIT; EXECUTE PROCEDURE p 101; COMMIT; CREATE DATABASE 'C:\test.gdb' USER 'SYSDBA' PASSWORD 'pwd'; EXECUTE PROCEDURE p 101; In this case first "EXECUTE PROCEDURE p 101" will create wrong blob-id, but second one will create correct - new blob-id. ---------------------------------------------------------------------- You can respond by visiting: http://sourceforge.net/tracker/?func=detail&atid=109028&aid=223056&group_id=9028 |