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

Jump to bottom

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

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

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

firebird-automations opened this issue Apr 8, 2020 · 7 comments
Assignees
@mrotteveel
Labels
affect-version: Jaybird 4.0.0 component: jdbc driver priority: major resolution: duplicate type: bug

Comments

@firebird-automations
Copy link

firebird-automations commented Apr 8, 2020 •
edited by mrotteveel

Submitted by: Lukas Eder (lukas.eder)

Duplicates CORE6280

Consider this 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");
    }
}

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

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

As a workaround, this seems to work:

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");
    }
}
@firebird-automations
Copy link
Author

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-automations
Copy link
Author

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-automations
Copy link
Author

Commented by: @mrotteveel

Reported CORE6280 for this issue.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

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

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue duplicates CORE6280 [ CORE6280 ]

@firebird-automations
Copy link
Author

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-automations
Copy link
Author

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
Assignees

@mrotteveel mrotteveel

Labels
affect-version: Jaybird 4.0.0 component: jdbc driver priority: major resolution: duplicate type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@mrotteveel @firebird-automations