Issue Details (XML | Word | Printable)

Key: JDBC-621
Type: Bug Bug
Status: Closed Closed
Resolution: Duplicate
Priority: Major Major
Assignee: Mark Rotteveel
Reporter: Lukas Eder
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Jaybird JDBC Driver

Wrong error reported when using bind variables on MERGE statement

Created: 08/Apr/20 03:55 PM   Updated: 18/Apr/20 04:03 PM
Component/s: JDBC driver
Affects Version/s: Jaybird 4.0.0
Fix Version/s: None

Issue Links:
Duplicate
 


 Description  « Hide
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}

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 09/Apr/20 04:00 PM
This is probably a bug in Firebird server, but I'll verify that first and if necessary report it under CORE.

Mark Rotteveel added a comment - 09/Apr/20 06:04 PM
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

Mark Rotteveel added a comment - 09/Apr/20 06:51 PM
Reported CORE-6280 for this issue.

Lukas Eder added a comment - 09/Apr/20 08:43 PM
Thanks for investigating this.

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

Mark Rotteveel added a comment - 18/Apr/20 04:03 PM
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/).