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

Creating a procedure containing "case when" expression leads to a server crash: [CORE3965] #4298

Closed
firebird-automations opened this issue Oct 29, 2012 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Frank Schlottmann-Goedde (fsg)

Using the following script in isql against the employee database demonstrates the error

/*
Creating a procedure containing "case when" expression leads to a server crash:

Unbehandelte Ausnahme bei 0x779015de in firebird.exe: 0xC0000005: Zugriffsverletzung beim Lesen an Position 0x00000088.
Callstack according to msvc 8:
ntdll.dll!779015de()
[Unten angegebene Rahmen sind möglicherweise nicht korrekt und/oder fehlen, keine Symbole geladen für ntdll.dll]
ntdll.dll!779015de()
ntdll.dll!778f014e()
msvcr80.dll!_strnicmp_l(const char * dst=0x02d271d4, const char * src=0x81ca1ed3, unsigned int count=24, localeinfo_struct * plocinfo=0x02d629f0) Zeile 65 + 0xc Bytes C++
engine12.dll!Firebird::MutexLockGuard::~MutexLockGuard() Zeile 373 C++
engine12.dll!Firebird::MutexLockGuard::~MutexLockGuard() Zeile 373 C++
engine12.dll!Firebird::MemoryPool::release(void * object=0x02d435d8) Zeile 559 + 0x8 Bytes C++
engine12.dll!Jrd::Parser::yyexpand() Zeile 29341 C++
engine12.dll!Jrd::Parser::yylex() Zeile 21970 C++
engine12.dll!Jrd::Parser::yylex1() Zeile 29310 + 0x7 Bytes C++
engine12.dll!Jrd::Parser::parseAux() Zeile 22810 + 0x7 Bytes C++
engine12.dll!Jrd::Parser::parse() Zeile 96 + 0x5 Bytes C++
engine12.dll!prepareStatement(Jrd::thread_db * tdbb=0x0028ef44, Jrd::dsql_dbb * database=0x00000000, Jrd::jrd_tra * transaction=0x02da0298, unsigned long textLength=1829, const char * text=0x00000000, unsigned short clientDialect=3, unsigned short parserVersion=2, bool isInternalRequest=false) Zeile 1598 C++
engine12.dll!prepareRequest(Jrd::thread_db * tdbb=0x0028ef44, Jrd::dsql_dbb * database=0x02170018, Jrd::jrd_tra * transaction=0x02da0298, unsigned long textLength=1829, const char * text=0x00393d98, unsigned short clientDialect=3, unsigned short parserVersion=0, bool isInternalRequest=false) Zeile 1526 + 0x23 Bytes C++
engine12.dll!DSQL_execute_immediate(Jrd::thread_db * tdbb=0x0028ef44, Jrd::Attachment * attachment=0x02df0018, Jrd::jrd_tra * * tra_handle=0x0028f034, unsigned long length=1829, const char * string=0x00393d98, unsigned short dialect=32, unsigned long in_blr_length=0, const unsigned char * in_blr=0x00000000, unsigned long in_msg_length=0, const unsigned char * in_msg=0x00000000, unsigned long out_blr_length=0, const unsigned char * out_blr=0x00000000, unsigned long out_msg_length=0, unsigned char * out_msg=0x00000000, bool isInternalRequest=false) Zeile 671 + 0x1c Bytes C++
engine12.dll!Jrd::JAttachment::execute(Firebird::IStatus * user_status=0x0028f104, Firebird::ITransaction * apiTra=0x02da0298, unsigned int length=1829, const char * string=0x00393d98, unsigned int dialect=32, unsigned int __formal=0, const Firebird::FbMessage * inMsgBuffer=0x0028f0f4, const Firebird::FbMessage * outMsgBuffer=0x0028f0e4) Zeile 4388 C++
fbclient.dll!Why::YAttachment::execute(Firebird::IStatus * status=0x0028f104, Firebird::ITransaction * transaction=0x003531b8, unsigned int length=1829, const char * string=0x00393d98, unsigned int dialect=32, unsigned int inMsgType=0, const Firebird::FbMessage * inMsgBuffer=0x0028f0f4, const Firebird::FbMessage * outMsgBuffer=0x0028f0e4) Zeile 4405 + 0x25 Bytes C++
firebird.exe!rem_port::execute_immediate(P_OP op=op_exec_immediate, p_sqlst * exnow=0x0028f858, packet * sendL=0x0028f360) Zeile 2775 + 0x63 Bytes C++
firebird.exe!process_packet(rem_port * port=0x00391170, packet * sendL=0x0028f360, packet * receive=0x0028f654, rem_port * * result=0x0028f948) Zeile 4107 C++
firebird.exe!SRVR_main(rem_port * main_port=0x00391170, unsigned short flags=32) Zeile 1069 + 0x18 Bytes C++
firebird.exe!service_connection(rem_port * port=0x00000000) Zeile 537 + 0x11 Bytes C++
firebird.exe!WinMain(HINSTANCE__ * hThisInst=0x00400000, HINSTANCE__ * __formal=0x00000000, char * lpszArgs=0x007d7dad, int nWndMode=10) Zeile 295 + 0x5 Bytes C++
firebird.exe!__tmainCRTStartup() Zeile 589 + 0x1d Bytes C
kernel32.dll!757933aa()
ntdll.dll!77919ef2()
ntdll.dll!77919ec5()
*/
SET TERM ^ ;
create or alter procedure P_BETEILIGUNG_ORDER (
GID char(36) character set ISO8859_1 collate ISO8859_1,
ORDERNR integer,
DIR smallint,
MIT_FUEHRENDER char(1) character set ISO8859_1 collate ISO8859_1)
as
declare variable CUR_ORDERNR integer;
declare variable MAX_ORDERNR integer;
declare variable FK_REF char(36);
begin

if (MIT_FUEHRENDER is null) then
MIT_FUEHRENDER = 'F';

select R.Qty_ORDERed, R.item_type
from sales R
where R.po_number = :GID
into :CUR_ORDERNR, :FK_REF;

if (ORDERNR is null) then
ORDERNR = CUR_ORDERNR + coalesce(DIR, 0);

if (ORDERNR <= case MIT_FUEHRENDER
when 'T' then 1
else 2
end) then
ORDERNR = case MIT_FUEHRENDER
when 'T' then 1
else 2
end;
else
begin
select max(R.qty_ordered)
from sales R
where R.item_type = :FK_REF
into :MAX_ORDERNR;
if (ORDERNR > MAX_ORDERNR) then
ORDERNR = MAX_ORDERNR;
end

if (ORDERNR = CUR_ORDERNR) then
exit;
else
if (ORDERNR < CUR_ORDERNR) then
update sales R
set R.qty_ordered = R.qty_ordered + 1
where R.item_type = :FK_REF and
R.qty_ordered between :ORDERNR and :CUR_ORDERNR;
else
update sales R
set R.qty_ordered = R.ORDERNR - 1
where R.qty_ordered = :FK_REF and
R.qty_ordered between :CUR_ORDERNR and :ORDERNR;

update sales R
set R.qty_ordered = :ORDERNR
where R.po_number = :GID;

end^
SET TERM ; ^

Commits: c15552e

====== Test Details ======

NB: `UPDATE` statement in source sample (privided by ticket-starter) contains "set r.qty_ordered = r.ordernr - 1" - but there is NO such field in the table SALES.
Changed DML to: "update sales r set r.qty_ordered = :ordernr ..." -- i.e. use OUT-prameter "ordernr", see header of SP p_beteiligung_order.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

Test Details: NB: `UPDATE` statement in source sample (privided by ticket-starter) contains "set r.qty_ordered = r.ordernr - 1" - but there is NO such field in the table SALES.
Changed DML to: "update sales r set r.qty_ordered = :ordernr ..." -- i.e. use OUT-prameter "ordernr", see header of SP p_beteiligung_order.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully => Done with caveats

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