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

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

Closed
firebird-automations opened this issue Jul 23, 2014 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

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

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.

Commits: ce80938 fe24642 FirebirdSQL/fbt-repository@bbf0a33 FirebirdSQL/fbt-repository@4474a26

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Regression: 3.0 Alpha 2 [ 10560 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 RC 1 [ 10584 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ] => Dmitry Yemanov [ dimitr ]

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

resolution: Fixed [ 1 ]

@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: @pavel-zotov

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