You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
An error occurs when I trying to read the records from the global temporary table in the order of the index. Table must contains the blob field.
Script to reproduce the problem:
1. test.gtt_blob_DDL.sql:
SET SQL DIALECT 3;
SET NAMES WIN1251;
CREATE DATABASE 'D:\fb_dbs\TEST_GTT_BLOB.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET WIN1251 COLLATION WIN1251;
CREATE GLOBAL TEMPORARY TABLE G1 (
ID INTEGER NOT NULL,
TT VARCHAR(100) NOT NULL,
BB BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL
) ON COMMIT DELETE ROWS;
COMMIT;
CREATE GLOBAL TEMPORARY TABLE G2 (
ID INTEGER NOT NULL,
ID_G1 INTEGER NOT NULL,
TT VARCHAR(100) NOT NULL,
BB BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL
) ON COMMIT DELETE ROWS;
COMMIT;
ALTER TABLE G1 ADD CONSTRAINT PK_G1 PRIMARY KEY (ID);
COMMIT;
ALTER TABLE G2 ADD PRIMARY KEY (ID);
COMMIT;
ALTER TABLE G2 ADD FOREIGN KEY (ID_G1) REFERENCES G1 (ID);
COMMIT;
SET TERM ^ ;
CREATE OR ALTER PROCEDURE TEST_GG
AS
declare I int = 0;
begin
in autonomous transaction do while (I<100) do
begin
insert into G1 (ID, TT, BB) values (:I, rand(), rand());
CREATE OR ALTER TRIGGER ON_COMMIT
ACTIVE ON TRANSACTION COMMIT POSITION 999
AS
declare variable ID integer;
declare variable TT varchar(100);
declare variable BB BLOB SUB_TYPE 1;
declare I int=0;
begin
for select ID, TT, BB
from G1
order by ID
into :ID, :TT, :BB
do
begin
for select ID_G1, TT, BB
from G2
where ID_G1=:ID
order by ID
into :ID_G1, :TT_2, :BB_2
do
begin
I=I+1;
end
end
end^
COMMIT^
SET TERM ; ^
2. test.gtt_blob.sql:
CONNECT 'D:\fb_dbs\TEST_GTT_BLOB.FDB' USER 'SYSDBA' PASSWORD 'masterkey';
execute procedure TEST_GG;
COMMIT;
3. Execute scripts:
C:\Firebird\2_5\bin>isql.exe -i test.gtt_blob_DDL.sql
Use CONNECT or CREATE DATABASE to specify a database
C:\Firebird\2_5\bin>isql.exe -i test.gtt_blob.sql
Use CONNECT or CREATE DATABASE to specify a database
Statement failed, SQLSTATE = 42000
index unexpectedly deleted
-At trigger 'ON_COMMIT' line: 14, col: 3
After line 2 in file test.gtt_blob.sql
Statement failed, SQLSTATE = 42000
index unexpectedly deleted
-At trigger 'ON_COMMIT' line: 14, col: 3
After line 2 in file test.gtt_blob.sql
Statement failed, SQLSTATE = 42000
index unexpectedly deleted
-At trigger 'ON_COMMIT' line: 14, col: 3
After line 3 in file test.gtt_blob.sql
Fix for CORE3855 assign tx num to the blob record (it was never assigned before).
It is necessary to detect correct page space when blob will be stored on disk.
But tx num was assigned using blob's transaction, not current transaction.
This is wrong in case of autonomous transaction - blob binded to the outer transaction while record should contain number of storing transaction.
Submitted by: Mikhail (s-m)
Is related to CORE3855
An error occurs when I trying to read the records from the global temporary table in the order of the index. Table must contains the blob field.
Script to reproduce the problem:
1. test.gtt_blob_DDL.sql:
SET SQL DIALECT 3;
SET NAMES WIN1251;
CREATE DATABASE 'D:\fb_dbs\TEST_GTT_BLOB.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET WIN1251 COLLATION WIN1251;
CREATE GLOBAL TEMPORARY TABLE G1 (
ID INTEGER NOT NULL,
TT VARCHAR(100) NOT NULL,
BB BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL
) ON COMMIT DELETE ROWS;
COMMIT;
CREATE GLOBAL TEMPORARY TABLE G2 (
ID INTEGER NOT NULL,
ID_G1 INTEGER NOT NULL,
TT VARCHAR(100) NOT NULL,
BB BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL
) ON COMMIT DELETE ROWS;
COMMIT;
ALTER TABLE G1 ADD CONSTRAINT PK_G1 PRIMARY KEY (ID);
COMMIT;
ALTER TABLE G2 ADD PRIMARY KEY (ID);
COMMIT;
ALTER TABLE G2 ADD FOREIGN KEY (ID_G1) REFERENCES G1 (ID);
COMMIT;
SET TERM ^ ;
CREATE OR ALTER PROCEDURE TEST_GG
AS
declare I int = 0;
begin
in autonomous transaction do while (I<100) do
begin
insert into G1 (ID, TT, BB) values (:I, rand(), rand());
end^
COMMIT^
CREATE OR ALTER TRIGGER ON_COMMIT
ACTIVE ON TRANSACTION COMMIT POSITION 999
AS
declare variable ID integer;
declare variable TT varchar(100);
declare variable BB BLOB SUB_TYPE 1;
declare variable ID_G1 integer;
declare variable TT_2 varchar(100);
declare variable BB_2 BLOB SUB_TYPE 1;
declare I int=0;
begin
for select ID, TT, BB
from G1
order by ID
into :ID, :TT, :BB
do
begin
for select ID_G1, TT, BB
from G2
where ID_G1=:ID
order by ID
into :ID_G1, :TT_2, :BB_2
do
begin
I=I+1;
end
end
end^
COMMIT^
SET TERM ; ^
2. test.gtt_blob.sql:
CONNECT 'D:\fb_dbs\TEST_GTT_BLOB.FDB' USER 'SYSDBA' PASSWORD 'masterkey';
execute procedure TEST_GG;
COMMIT;
3. Execute scripts:
C:\Firebird\2_5\bin>isql.exe -i test.gtt_blob_DDL.sql
Use CONNECT or CREATE DATABASE to specify a database
C:\Firebird\2_5\bin>isql.exe -i test.gtt_blob.sql
Use CONNECT or CREATE DATABASE to specify a database
Statement failed, SQLSTATE = 42000
index unexpectedly deleted
-At trigger 'ON_COMMIT' line: 14, col: 3
After line 2 in file test.gtt_blob.sql
Statement failed, SQLSTATE = 42000
index unexpectedly deleted
-At trigger 'ON_COMMIT' line: 14, col: 3
After line 2 in file test.gtt_blob.sql
Statement failed, SQLSTATE = 42000
index unexpectedly deleted
-At trigger 'ON_COMMIT' line: 14, col: 3
After line 3 in file test.gtt_blob.sql
C:\Firebird\2_5\bin>
Commits: 32ebb58 3e09208 0e766ce
The text was updated successfully, but these errors were encountered: