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

In PSQL modules with declared cursors engine could assign value to the wrong variable [CORE4233] #4557

Closed
firebird-automations opened this issue Sep 22, 2013 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @hvlad

Is related to QA608

Consider following procedure:

CREATE OR ALTER PROCEDURE HIDDEN_VARS
RETURNS (OUT_A INT, OUT_B1 INT, OUT_B2 INT)
AS
DECLARE A INT;
DECLARE C CURSOR FOR
(SELECT COALESCE( (SELECT COUNT(*) FROM RDB$RELATIONS), -1) FROM RDB$DATABASE);
DECLARE B INT = 0;
BEGIN
OUT_B1 = B;

OPEN C;
FETCH C INTO :A;
CLOSE C;

OUT_A = A;
OUT_B2 = B;
B = B + 1;
SUSPEND;
END

Results of first execution:

OUT_A OUT_B1 OUT_B2
353 0 353

Variable B was changed when cursor was fetched !

Second and all subsequent executions:

OUT_A OUT_B1 OUT_B2
353 354 353

Value of variable B keeped after procedure execution !

Commits: 650007a FirebirdSQL/fbt-repository@6396dfa FirebirdSQL/fbt-repository@402f4fe

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Version: 2.5.2 [ 10450 ]

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

PSQL module could be affected by this bug if
- it contains variable declaration after cursor declaration, and
- cursor query contains COALESCE (and probably other simple CASE equivalents)

Notes:
1. As workaround one could try to declare cursors after all variables.
2. The bug affected generated BLR so it is necessary to recompile correspondig procedures\triggers using fixed engine.
3. FB3 seems not affected because of massive changes in related DSQL code.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.3 [ 10461 ]

@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: @pcisar

Link: This issue is related to QA608 [ QA608 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment