H2 not recognizing a parameter of a prepared statement (H2 version 1.4.197)

[Jayanta Majumder]

Hi,

  I would like to report what seems like a bug in H2. I have a 'merge into' prepared statement with a parameter in the insert sub-statement. I am using the JDBC driver to send the statement.  Following are the details.

First the table on which the query is being invoked:

CREATE TABLE MY_TEST_TABLE
(
    ATTR1     VARCHAR2(128) NOT NULL,
    ATTR2     VARCHAR2(50),
    ATTR3     VARCHAR2(100),
    ATTR4     VARCHAR2(100),
    ATTR5     VARCHAR2(2000),
    ATTR6     NUMBER(10),
    ATTR7     TIMESTAMP(6),
    ATTR8     VARCHAR2(10),
    ATTR9     BLOB,
    ATTR10    VARCHAR2(70),
    ATTR11    TIMESTAMP,
    ATTR12    TIMESTAMP(6),
    ATTR13    VARCHAR2(200),
    ATTR14    NUMBER(10),
    ATTR15    NUMBER(10),
    ATTR16    VARCHAR2(100),
    ATTR17    VARCHAR2(128)
);

Following is the JAVA code that sends a 'merge into' update statement using JDBC. Attached is a self-contained application code that runs the following function.

 static void bug_case (Connection conn)
  {
    System.out.
      println ("Test for which we check the param count in isolation");
    PreparedStatement statement = null;
    try
    {
      statement =
 conn.
 prepareStatement
 ("MERGE INTO MY_TEST_TABLE D USING ( SELECT '1234' as ATTR1, 'TYPE1' as ATTR2, 'FailType1' as ATTR4, 'Sample Details' as ATTR5, '1' as ATTR6, 'REPLAY' as ATTR8, 'abcd' as ATTR3 , '2' as MAX_ATTR6, 'null' as ATTR17 ,null as ATTR7, null as ATTR9, null as ATTR10, null as ATTR11, null as ATTR12, null as ATTR13, null as ATTR14 , null as ATTR15 FROM dual ) S ON (D.ATTR1 = S.ATTR1 AND D.ATTR2 = S.ATTR2) WHEN MATCHED THEN UPDATE SET D.ATTR6 = CASE WHEN S.ATTR6 < 0 THEN -1 ELSE D.ATTR6 + 1 END, D.ATTR8 = CASE WHEN D.ATTR6 < S.MAX_ATTR6-1 THEN S.ATTR8 ELSE 'FAIL' END, D.ATTR7 = sysdate, D.ATTR4 = S.ATTR4, D.ATTR5 = S.ATTR5, D.ATTR3 = S.ATTR3, D.ATTR17 = S.ATTR17 WHEN NOT MATCHED THEN INSERT (ATTR1, ATTR2, ATTR4, ATTR5, ATTR6, ATTR7, ATTR8, ATTR3, ATTR9, ATTR17) VALUES (S.ATTR1, S.ATTR2, S.ATTR4, S.ATTR5, S.ATTR6, sysdate, S.ATTR8,S.ATTR3,? ,S.ATTR17)");

      Blob b1 = conn.createBlob ();
      byte[]bytes = new byte[10];
      for (int i = 0; i < 10; ++i)
 bytes[i] = (byte) (2 * i);
      b1.setBytes (1, bytes);

      statement.setBlob (1, b1);

      statement.executeUpdate ();

    }
    catch (Exception e)
    {
      System.err.println ("ERROR: " + e.toString ());
    }
    finally
    {
      try
      {
 if (statement != null)
   {
     statement.close ();
   }
      }
      catch (SQLException e)
      {
 System.err.println ("SQL ERROR: " + e.toString ());
      }
      catch (Exception e)
      {
 System.err.println ("ERROR: " + e.toString ());
      }
    }
  }

I get the following exception:

ERROR: org.h2.jdbc.JdbcSQLException: Invalid value "1" for parameter "parameterIndex" [90008-197]

I have confirmed that the issue is not on the client side. The client-side driver sends the same prepared statement to the H2 server and the server sends back 0 as parameter count (as received by the function

void CommandRemote::prepare(SessionRemote s, boolean createParams)in the line highlighted below. Here readInt returns 0 despite the parameter highlighted in green above). 

private void prepare(SessionRemote s, boolean createParams) {
    id = s.getNextId();
    for (int i = 0, count = 0; i < transferList.size(); i++) {
        try {
            Transfer transfer = transferList.get(i);

            boolean v16 = s.getClientVersion() >= Constants.TCP_PROTOCOL_VERSION_16;

            if (createParams) {
                s.traceOperation(v16 ? "SESSION_PREPARE_READ_PARAMS2"
                        : "SESSION_PREPARE_READ_PARAMS", id);
                transfer.writeInt(
                        v16 ? SessionRemote.SESSION_PREPARE_READ_PARAMS2
                                : SessionRemote.SESSION_PREPARE_READ_PARAMS)
                        .writeInt(id).writeString(sql);
            } else {
                s.traceOperation("SESSION_PREPARE", id);
                transfer.writeInt(SessionRemote.SESSION_PREPARE).
                    writeInt(id).writeString(sql);
            }
            s.done(transfer);
            isQuery = transfer.readBoolean();
            readonly = transfer.readBoolean();

            cmdType = v16 && createParams ? transfer.readInt() : UNKNOWN;

            int paramCount = transfer.readInt();
            if (createParams) {
                parameters.clear();
                for (int j = 0; j < paramCount; j++) {
                    ParameterRemote p = new ParameterRemote(j);
                    p.readMetaData(transfer);
                    parameters.add(p);
                }
            }
        } catch (IOException e) {
            s.removeServer(e, i--, ++count);
        }
    }
}

Will much appreciate any feedback.

Best regards

Jay

[Evgenij Ryazanov]

Hello.

This is a known issue with MERGE INTO … USING statement that is not yet resolved:

https://github.com/h2database/h2database/issues/1189

[Jayanta Majumder]

Hi Evgenij,

  Thanks for your response! Just wanted to point out that this case may be slightly different from issue 1189. The parameter in question is not in the 'USING' sub-clause but in the insert sub-clause.

Any ideas on when this might get resolved?

Thanks again!

Best regards,

Jayanta

[Evgenij Ryazanov]

I think these problems have a common source. Parameters in H2 are not supported in DDL statements (this is not a big problem by itself) and also in some inner constructions, WITH and MERGE USING are the most problematic. WITH creates own DDL commands (so parameters don't work too), and current implementation of MERGE USING also generates some own code for its inner parts. I tried to fix it some time ago, but I didn't succeed.

[Evgenij Ryazanov]

Hello.

This issue was resolved. You can build database from its current sources or wait for a next release.

https://github.com/h2database/h2database