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

Parametrized queries using RDB$DB_KEY do not work [CORE4255] #4579

Closed
firebird-issue-importer opened this issue Nov 2, 2013 · 13 comments
Closed

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Nov 2, 2013

Submitted by: @mrotteveel

Block progress on JDBC330

Parametrized queries using RDB$DB_KEY do not work. Jaybird uses this for updatable resultsets when there is no primary key. When a cast to CHAR(8), VARCHAR(8) (with or without OCTETS) is added to the relevant code it works. This works without problem on Firebird 2.5 and earlier.

The interesting thing is that the sqlvar describes the parameter as SQL_TEXT (or CHAR):
sqltype = 453
sqlscale = 0
sqlsubtype = 1
sqllen = 8

However a trace shows the parameter as VARCHAR(8), which seems to indicate some kind invalid conversion is occurring with either the parameter itself or the received argument.

Statement 145:
-------------------------------------------------------------------------------
SELECT RDB$DB_KEY,"ID","LONG_STR","STR","CamelStr"
FROM TEST_TABLE2 WHERE
RDB$DB_KEY = ?

param0 = varchar(8), "�"

Same test executed on Firebird 2.5:

Statement 204:
-------------------------------------------------------------------------------
SELECT RDB$DB_KEY,"ID","LONG_STR","STR","CamelStr"
FROM TEST_TABLE2 WHERE
RDB$DB_KEY = ?

param0 = char(8), "�"

Commits: d0d26c6 FirebirdSQL/jaybird@d38d886 FirebirdSQL/fbt-repository@89ffc58 FirebirdSQL/fbt-repository@184e947

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 2, 2013

Modified by: @mrotteveel

Link: This issue block progress on JDBC330 [ JDBC330 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 7, 2013

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!

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 7, 2013

Commented by: @asfernandes

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 7, 2013

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 7, 2013

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 7, 2013

Commented by: @dyemanov

OK, this explains why it worked for you.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 7, 2013

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 7, 2013

Commented by: @mrotteveel

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 8, 2013

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 2 [ 10560 ]

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 9, 2013

Commented by: @mrotteveel

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 29, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 29, 2015

Modified by: @pavel-zotov

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

Test Details: TODO: add statements of other kind that violates not only not-null definition (PK/UK/FK/CHECK, domain boundaries ?)

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 29, 2015

Modified by: @pavel-zotov

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

Test Details: TODO: add statements of other kind that violates not only not-null definition (PK/UK/FK/CHECK, domain boundaries ?) =>

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