Regression in 3.0.x: wrong handling in FOR-cursor when NOT EXISTS( select from
<VIEW> ) statement is used to check results obtained from SP
---------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4497
URL: http://tracker.firebirdsql.org/browse/CORE-4497
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0 Alpha 2
Reporter: Pavel Zotov
DDL:
####
create or alter procedure z_pget as begin end;
create or alter view z_vdbg as select 1 as not_handled_agent_id from
rdb$database;
commit;
recreate global temporary table z_gtt(id int, agent_id int) on commit delete
rows;
recreate table z_doc(id int, agent_id int);
commit;
insert into z_doc(id, agent_id) values (101, 7);
-- var-1:
-- insert into z_doc(id, agent_id) values (100, 1); -- <<<<<<<<<<<<<< ::: NB
::: uncomment this line for TEST-1, see below
-- var-2
-- insert into z_doc(id, agent_id) values (102, 1); -- <<<<<<<<<<<<<< ::: NB
::: uncomment this line for TEST-2, see below
commit;
set term ^;
create or alter procedure z_pget
returns (
clo_doc_id int,
clo_agent_id int)
as
begin
delete from z_gtt;
insert into z_gtt select * from z_doc;
for
select f.id, f.agent_id
from z_gtt f
order by f.id
into clo_doc_id, clo_agent_id
do
suspend;
end
^
set term ;^
commit;
Script to be run:
============
-- File 'test.sql'
set term ^;
execute block returns( doc_for_handling int, agent_for_handling int )
as
declare v_agent_id int;
begin
for
select p.clo_doc_id, p.clo_agent_id from z_pget p
into doc_for_handling, v_agent_id
do begin
agent_for_handling = null;
if ( NOT exists(
select * from z_vdbg v
where v.not_handled_agent_id =
(select h.agent_id
from z_doc h
where h.id= :doc_for_handling
)
)
) then
begin
agent_for_handling = v_agent_id;
end
suspend;
end
end
^
set term ;^
commit;
TEST-1:
######
1) uncomment line "insert into z_doc(id, agent_id) values (100, 1);" in the DDL
script which is shown above
2) run the script 'test.sql':
Result in LI-V2.5.3.26788:
DOC_FOR_HANDLING AGENT_FOR_HANDLING
================ ==================
100 <null>
101 7
(it's correct)
Result in LI-T3.0.0.31228:
DOC_FOR_HANDLING AGENT_FOR_HANDLING
================ ==================
100 <null>
101 <null>
(WRONG! Record with DOC_FOR_HANDLING *must* have the value = 7 in the column
AGENT_FOR_HANDLING)
TEST-2:
######
1) uncomment line "insert into z_doc(id, agent_id) values (102, 1);" in the DDL
script which is shown above
2) run the script 'test.sql':
Result in LI-V2.5.3.26788:
DOC_FOR_HANDLING AGENT_FOR_HANDLING
================ ==================
101 7
102 <null>
(it's correct)
Result in LI-T3.0.0.31228:
DOC_FOR_HANDLING AGENT_FOR_HANDLING
================ ==================
101 7
102 1
(WRONG! Record with AGENT_FOR_HANDLING = 1 should never be displayed!)
PS. Sorry for too long subject of this tiocket: I couldn`t understand what
exactly has most influence on such results in 3.0.
--
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
------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now.
http://p.sf.net/sfu/bds
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
