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

Wrong error reported when using bind variables on MERGE statement [JDBC621] #242

Closed
firebird-issue-importer opened this issue Apr 8, 2020 · 7 comments

Comments

@firebird-issue-importer
Copy link

@firebird-issue-importer firebird-issue-importer commented Apr 8, 2020

Submitted by: Lukas Eder (lukas.eder)

Duplicates CORE6280

Consider this code:

{code}
try (Statement s = c.createStatement()) {
try {
s.executeUpdate("create table t (i int not null primary key, j int)");

    try \(PreparedStatement ps = c\.prepareStatement\(
        "merge into t using \(select 1 x from rdb$database\) on 1 = 1 "
      \+ "when matched then update set j = ? "
      \+ "when matched and i = ? then delete "
    \)\) \{
        ps\.setInt\(1, 2\);
        ps\.setInt\(2, 1\);
    \}
\}
finally \{
    s\.executeUpdate\("drop table t"\);
\}

}
{code}

Notice, the MERGE statement's second WHEN MATCHED clause doesn't really make any sense, because the first one matches all rows. Nevertheless, it seem sto be valid syntax, and the second bind parameter marker should be accepted. Yet I get this exception:

{code}
Caused by: java.sql.SQLException: Invalid column index: 2
at org.firebirdsql.jdbc.AbstractPreparedStatement.getField(AbstractPreparedStatement.java:330)
at org.firebirdsql.jdbc.AbstractPreparedStatement.setInt(AbstractPreparedStatement.java:254)
at org.jooq.test.all.testcases.InsertUpdateTests.lambda$9(InsertUpdateTests.java:2889)
... 36 more
{code}

As a workaround, this seems to work:

{code}
try (Statement s = c.createStatement()) {
try {
s.executeUpdate("create table t (i int not null primary key, j int)");

    try \(PreparedStatement ps = c\.prepareStatement\(
        "merge into t using \(select 1 x from rdb$database\) on 1 = 1 "
      \+ "when matched and 1 = 1 then update set j = ? "
      \+ "when matched and i = ? then delete "
    \)\) \{
        ps\.setInt\(1, 2\);
        ps\.setInt\(2, 1\);
    \}
\}
finally \{
    s\.executeUpdate\("drop table t"\);
\}

}
{code}

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 9, 2020

Commented by: @mrotteveel

This is probably a bug in Firebird server, but I'll verify that first and if necessary report it under CORE.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 9, 2020

Commented by: @mrotteveel

When using the PSQL execute statement to execute this, it results in an equivalent error (Input parameters mismatch), so the problem in Firebird itself:

EXECUTE BLOCK
AS
BEGIN
EXECUTE STATEMENT ('merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete') (2, 1);
END

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 9, 2020

Commented by: @mrotteveel

Reported CORE6280 for this issue.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 9, 2020

Modified by: @mrotteveel

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

resolution: Duplicate [ 3 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 9, 2020

Modified by: @mrotteveel

Link: This issue duplicates CORE6280 [ CORE6280 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 9, 2020

Commented by: Lukas Eder (lukas.eder)

Thanks for investigating this.

For the record, for the time being, we work around this issue by adding the 1 = 1 predicate in jOOQ

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 18, 2020

Commented by: @mrotteveel

The underlying issue is fixed in Firebird 3.0.6 and 4.0.0 (you can find a snapshot on https://firebirdsql.org/en/snapshot-builds/).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants