Error "context already in use" for the simple case function with a sub-select operand [CORE3296]

[firebird-automations]

Submitted by: @livius2

Is related to QA286

CREATE TABLE VAT_ZAK
(
ID Integer NOT NULL,
SYS_NR Integer,
CONSTRAINT PK_VAT_ZAK__ID PRIMARY KEY (ID)
);

CREATE TABLE ELEMENTY
(
ID Integer NOT NULL,
ID_VAT_SPRZ Integer,
CONSTRAINT PK_ELEMENTY__ID PRIMARY KEY (ID)
);

UPDATE
ELEMENTY E
SET
E.ID_VAT_SPRZ=
CASE
(SELECT V.SYS_NR FROM VAT_ZAK V WHERE V.ID=E.ID_VAT_SPRZ)
WHEN 1 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=7)
WHEN 2 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=8)
WHEN 3 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=9)
ELSE E.ID_VAT_SPRZ END

Engine Code : 335544343
Engine Message :
invalid request BLR at offset 95
context already in use (BLR error)

but this form of case statement work

UPDATE
ELEMENTY E
SET
E.ID_VAT=
CASE

WHEN (SELECT V.SYS_NR FROM VAT_ZAK V WHERE V.ID=E.ID_VAT) = 1 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=7)
WHEN (SELECT V.SYS_NR FROM VAT_ZAK V WHERE V.ID=E.ID_VAT) = 2 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=8)
WHEN (SELECT V.SYS_NR FROM VAT_ZAK V WHERE V.ID=E.ID_VAT) = 3 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=9)
ELSE E.ID_VAT END

Commits: 5fe6508

[firebird-automations]

Modified by: @livius2

description: CREATE TABLE VAT_ZAK
(
ID Integer NOT NULL,
SYS_NR Integer,
CONSTRAINT PK_VAT_ZAK__ID PRIMARY KEY (ID)
);

CREATE TABLE ELEMENTY
(
ID Integer NOT NULL,
ID_VAT_SPRZ Integer,
CONSTRAINT PK_ELEMENTY__ID PRIMARY KEY (ID)
);

UPDATE
ELEMENTY E
SET
E.ID_VAT_SPRZ=
CASE
(SELECT V.SYS_NR FROM VAT_ZAK V WHERE V.ID=E.ID_VAT_SPRZ)
WHEN 1 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=7)
WHEN 2 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=8)
WHEN 3 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=9)
ELSE E.ID_VAT_SPRZ END

Engine Code : 335544343
Engine Message :
invalid request BLR at offset 95
context already in use (BLR error)

=>

CREATE TABLE VAT_ZAK
(
ID Integer NOT NULL,
SYS_NR Integer,
CONSTRAINT PK_VAT_ZAK__ID PRIMARY KEY (ID)
);

CREATE TABLE ELEMENTY
(
ID Integer NOT NULL,
ID_VAT_SPRZ Integer,
CONSTRAINT PK_ELEMENTY__ID PRIMARY KEY (ID)
);

UPDATE
ELEMENTY E
SET
E.ID_VAT_SPRZ=
CASE
(SELECT V.SYS_NR FROM VAT_ZAK V WHERE V.ID=E.ID_VAT_SPRZ)
WHEN 1 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=7)
WHEN 2 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=8)
WHEN 3 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=9)
ELSE E.ID_VAT_SPRZ END

Engine Code : 335544343
Engine Message :
invalid request BLR at offset 95
context already in use (BLR error)

but this form of case statement work

UPDATE
ELEMENTY E
SET
E.ID_VAT=
CASE

WHEN (SELECT V.SYS_NR FROM VAT_ZAK V WHERE V.ID=E.ID_VAT) = 1 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=7)
WHEN (SELECT V.SYS_NR FROM VAT_ZAK V WHERE V.ID=E.ID_VAT) = 2 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=8)
WHEN (SELECT V.SYS_NR FROM VAT_ZAK V WHERE V.ID=E.ID_VAT) = 3 THEN (SELECT http://V.ID FROM VAT_ZAK V WHERE V.SYS_NR=9)
ELSE E.ID_VAT END

[firebird-automations]

Modified by: @dyemanov

summary: invalid request BLR at offset 95 => Error "context already in use"

[firebird-automations]

Modified by: @dyemanov

summary: Error "context already in use" => Error "context already in use" for the simple case function with a sub-select operand

[firebird-automations]

Commented by: @dyemanov

Fixed for v2.1.5. More recent FB versions (2.5 and 3.0) already have this issue fixed.

[firebird-automations]

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.1.5 [ 10420 ]

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA286 [ QA286 ]

[firebird-automations]

Modified by: @pmakowski

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

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: Done successfully