Commented by: @paulbeach

From another Firebird user:

We make use of rdb$db_key , in FB 2.52 and it works fine.
We do a select rdb$db_key as db_key from <table>
and we use it to UPDATE the same record
using a : update <table> ...Where (rdb$db_key=:db_key)
But! in FB 3 alpha, we don't get any error, but nothing is updated!

Commented by: @asfernandes

Can these bugs be reproduced with parameters in PSQL, with a reproducible test case?

Commented by: @dyemanov

Firebird 3 really converts client-side CHARs to VARCHARs internally, and vice versa. But AFAIU it only makes the problem better visible:

-- Tested in FB 2.5 and FB3.0 -- same result
select 1 from rdb$database where rdb$db_key = (select rdb$db_key from rdb$database);
-- one row returned
select 1 from rdb$database where rdb$db_key = cast((select rdb$db_key from rdb$database) as char(8));
-- one row returned
select 1 from rdb$database where rdb$db_key = cast((select rdb$db_key from rdb$database) as varchar(8));
-- no rows returned

i.e. DBKEY cannot be looked up using a VARCHAR argument. So I doubt that:

select 1 from rdb$database where rdb$db_key = cast(? as varchar(8));

really works in FB3, as stated in this ticket.

Anyway, you can try your samples with the next snapshot build, as it will have this issue fixed.

Commented by: @mrotteveel

I didn't cast the parameter, but the RDB$KEY pseudocolumn for my workaround (see http://sourceforge.net/p/firebird/code/58746/ ) (which probably isn't a good thing for performance BTW)

I can only observe that using RDB$DB_KEY = ? doesn't work, where it does work in Firebird 2.5.

Commented by: @dyemanov

OK, this explains why it worked for you.

Commented by: @mrotteveel

Reproduction in PSQL:
-- Setup table
CREATE TABLE dbkeytest
(
ID INTEGER PRIMARY KEY,
SEEN BOOLEAN DEFAULT FALSE
);
COMMIT;
INSERT INTO dbkeytest (ID) VALUES (1);
INSERT INTO dbkeytest (ID) VALUES (2);
INSERT INTO dbkeytest (ID) VALUES (3);
INSERT INTO dbkeytest (ID) VALUES (4);
COMMIT;

-- Actual test:
SET TERM #⁠;
EXECUTE BLOCK
AS
DECLARE THEKEY CHAR(8);
DECLARE THEID INTEGER;
BEGIN
FOR SELECT ID, RDB$DB_KEY FROM DBKEYTEST INTO THEID, THEKEY DO
BEGIN
EXECUTE STATEMENT ('UPDATE DBKEYTEST SET SEEN = TRUE WHERE RDB$DB_KEY = ?') (THEKEY);
END
END#⁠
SET TERM ;#⁠
COMMIT;

SELECT * FROM dbkeytest;

-- => Expected: All rows have seen = TRUE
-- => Actual: No rows changed, all rows have seen = FALSE

Commented by: @mrotteveel

Interestingly, explicitly casting the parameter to CHAR(8) also solves the problem.

Commented by: @mrotteveel

Tested with Jaybird and with above script in 3.0.0.30717 and I can confirm it has been fixed.