Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Server crashes while sorting records longer than 128KB [CORE4419] #4741

Closed
firebird-automations opened this issue May 5, 2014 · 6 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Volker Rehn (vr2_s18)

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

Commits: b17b5ef FirebirdSQL/fbt-repository@aacd00e

@firebird-automations
Copy link
Collaborator Author

Modified by: Volker Rehn (vr2_s18)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants