You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 [CORE4497]
#4816
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 <http://f.id>, f\.agent\_id
from z\_gtt f
order by <http://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.
Submitted by: @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
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
)
)
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.
Commits: ce80938 fe24642 FirebirdSQL/fbt-repository@bbf0a33 FirebirdSQL/fbt-repository@4474a26
The text was updated successfully, but these errors were encountered: