description: The following statement crashes the server reliably. It needs a union, 2 joins, a stored function and order by

select u.f1, u.f2
from (select split(line) f1, split(line, 2) f2 from lines1
union
select split(line), split(line, 2) from lines2) u
join (select split(line) f1, split(line, 2) f2 from lines1) a on a.f1 = u.f1 and a.f2 = u.f2
join (select split(line) f1, split(line, 2) f2 from lines2) b on b.f1 = u.f1 and b.f2 = u.f2
order by 1, 2

results in

Access violation.
The code attempted to access a virtual address without privilege to do so.
This exception will cause the Firebird server to terminate abnormally.

It works if

1. the order by is omitted
2. one of the joins is omitted or both of them
3. only one split SF is used per select statement instead of two
4. left and substring is used instead of split SF

I could not narrow it down further.

To reproduce, please create two tables and a stored function:

CREATE TABLE LINES1 (
LINE VARCHAR(2000)
);

INSERT INTO LINES1 (LINE) VALUES('2007 abcabcabc xx');
INSERT INTO LINES1 (LINE) VALUES('2007 defdefdef xx');
INSERT INTO LINES1 (LINE) VALUES('2007 ghighighi xx');
INSERT INTO LINES1 (LINE) VALUES('2008 defdefdef xx');

COMMIT WORK;

CREATE TABLE LINES2 (
LINE VARCHAR(2000)
);

INSERT INTO LINES2 (LINE) VALUES('2007 abcabcabc xx');
INSERT INTO LINES2 (LINE) VALUES('2007 defgdefg xx');
INSERT INTO LINES2 (LINE) VALUES('2007 ghighighi xx');
INSERT INTO LINES2 (LINE) VALUES('2008 abcabcabc xx');
INSERT INTO LINES2 (LINE) VALUES('2008 defdefdef xx');

COMMIT WORK;

create function split (
s varchar(32000),
n integer = 1
)
returns varchar(32000)
as
declare startpos integer;
declare pos integer;
begin
-- extract tab separated parts from string
pos = 0;
while (n > 0) do
begin
startpos = pos + 1;
pos = position(ascii_char(9), :s, :startpos);
if (pos = 0) then break;
n = n - 1;
end
if (pos > 0) then
return nullif(substring(s from :startpos for pos - startpos), '');
-- get part after last tab
else if (n = 1) then
return nullif(substring(s from :startpos), '');
end;

Best regards, Volker

=>

The following statement crashes the server reliably. It needs a union, 2 joins, a stored function and order by

select u.f1, u.f2
from (select split(line) f1, split(line, 2) f2 from lines1
union
select split(line), split(line, 2) from lines2) u
join (select split(line) f1, split(line, 2) f2 from lines1) a on a.f1 = u.f1 and a.f2 = u.f2
join (select split(line) f1, split(line, 2) f2 from lines2) b on b.f1 = u.f1 and b.f2 = u.f2
order by 1, 2

results in

Access violation.
The code attempted to access a virtual address without privilege to do so.
This exception will cause the Firebird server to terminate abnormally.

It works if

1. the order by is omitted
2. one of the joins is omitted or both of them
3. only one split SF is used per select statement instead of two
4. left and substring is used instead of split SF

I could not narrow it down further.

To reproduce, please create two tables and a stored function:

CREATE TABLE LINES1 (
LINE VARCHAR(2000)
);

INSERT INTO LINES1 (LINE) VALUES('2007 abcabcabc xx');
INSERT INTO LINES1 (LINE) VALUES('2007 defdefdef xx');
INSERT INTO LINES1 (LINE) VALUES('2007 ghighighi xx');
INSERT INTO LINES1 (LINE) VALUES('2008 defdefdef xx');

-- note that spaces between line values should be tabs, \t, ascii_char(9)

COMMIT WORK;

CREATE TABLE LINES2 (
LINE VARCHAR(2000)
);

INSERT INTO LINES2 (LINE) VALUES('2007 abcabcabc xx');
INSERT INTO LINES2 (LINE) VALUES('2007 defgdefg xx');
INSERT INTO LINES2 (LINE) VALUES('2007 ghighighi xx');
INSERT INTO LINES2 (LINE) VALUES('2008 abcabcabc xx');
INSERT INTO LINES2 (LINE) VALUES('2008 defdefdef xx');

COMMIT WORK;

create function split (
s varchar(32000),
n integer = 1
)
returns varchar(32000)
as
declare startpos integer;
declare pos integer;
begin
-- extract tab separated parts from string
pos = 0;
while (n > 0) do
begin
startpos = pos + 1;
pos = position(ascii_char(9), :s, :startpos);
if (pos = 0) then break;
n = n - 1;
end
if (pos > 0) then
return nullif(substring(s from :startpos for pos - startpos), '');
-- get part after last tab
else if (n = 1) then
return nullif(substring(s from :startpos), '');
end;

Best regards, Volker

assignee: Dmitry Yemanov [ dimitr ]

summary: combination of stored function, union, joins and order by crashes server => Server crashes while sorting records longer than 128KB

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

status: Resolved [ 5 ] => Closed [ 6 ]