combination of stored function, union, joins and order by crashes server
------------------------------------------------------------------------
Key: CORE-4419
URL: http://tracker.firebirdsql.org/browse/CORE-4419
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Alpha 2
Environment: Win7 64, WI-T3.0.0.31093 Firebird 3.0 Alpha 2 32Bit
Reporter: Volker Rehn
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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Is your legacy SCM system holding you back? Join Perforce May 7 to find out:
• 3 signs your SCM is hindering your productivity
• Requirements for releasing software faster
• Expert tips and advice for migrating your SCM now
http://p.sf.net/sfu/perforce
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
