Forum: Building VoltDB Applications

Post: Java stored procedure: question about mapping parameters

Java stored procedure: question about mapping parameters
pmilner
Aug 12, 2014
Hi,
I've got a problem with a Java stored procedure, related to mapping parameters when I use parentheses in my query. I'd be interested to get your comments.
I've tried to minimize the problem down to its essentials. I can't post my whole config as it's a secure project, but I hope this minimal example will show the problem.
I'm running VoltDB 4.0 (0-gab8313d-local) under Linux. Here are the relevant config extracts:

schema.xml

CREATE TABLE PAULTEST (ID INTEGER, NAME VARCHAR(12),LOCK_TIME TIMESTAMP, PRIMARY KEY (ID));
CREATE PROCEDURE ALLOW paul FROM CLASS com.jeannot.voltdb.PaulTest;

(NB I have security switched off anyway so credentials not required)

The stored procedure:

package com.jeannot.voltdb;
import org.voltdb.SQLStmt;
import org.voltdb.VoltProcedure;
import org.voltdb.VoltTable;
public class PaulTest extends VoltProcedure {
  public final SQLStmt selectSQL = new SQLStmt(
    "SELECT TOP ? FROM PAULTEST WHERE NAME IS NOT NULL AND (LOCK_TIME IS NULL OR SINCE_EPOCH(MILLIS,CURRENT_TIMESTAMP)-? < SINCE_EPOCH(MILLIS,LOCK_TIME))");
  public VoltTable[] run(int batchSize, int expiryInterval) throws VoltAbortException {
    voltQueueSQL(selectSQL,batchSize,expiryInterval);
    VoltTable[] selectedData = voltExecuteSQL(true);
    return selectedData;
  }
}


Note that the procedure has 2 parameters, the second of which is used inside a calculation to check whether the LOCK_TIME is null or older than the given amount of milliseconds ago.
I load this schema into VoltDB and then try to run the procedure from the Studio GUI as follows (NB the same occurs if I run the procedure from other Java code via the VoltDB client API):

exec PaulTest 10,10000;


This gives me the following error message:

Error: VOLTDB ERROR: HSQL-BACKEND ERROR Number of arguments provided was 2 where 4 was expected for statement SELECT TOP ? * FROM PAULTEST WHERE NAME IS NOT NULL AND (LOCK_TIME IS NULL OR SINCE_EPOCH(MILLIS,CURRENT_TIMESTAMP)-? < SINCE_EPOCH(MILLIS,LOCK_TIME)) at com.jeannot.voltdb.PaulTest.run(PaulTest.java:50)

Strangely, if I remove the parentheses from the query as follows:
  public final SQLStmt selectSQL = new SQLStmt(
    "SELECT TOP ? FROM PAULTEST WHERE NAME IS NOT NULL AND LOCK_TIME IS NULL OR SINCE_EPOCH(MILLIS,CURRENT_TIMESTAMP)-? < SINCE_EPOCH(MILLIS,LOCK_TIME)");

then I do not get this problem - the query executes successfully, although I think the order of precedence of AND/OR will mean my query doesn't do what I want. Anyway I assumed I could put in parentheses to make it clear that I want to check that LOCK_TIME is either null or old enough. NB I cannot change the real config so that the LOCK_TIME is never null, I have to cater for a possible null here.

I should also mention that if I run either of the the queries directly in the SQL GUI, they both work:
SELECT TOP 10 FROM PAULTEST WHERE NAME IS NOT NULL AND LOCK_TIME IS NULL OR SINCE_EPOCH(MILLIS,CURRENT_TIMESTAMP)-10000 < SINCE_EPOCH(MILLIS,LOCK_TIME);
SELECT TOP 10 FROM PAULTEST WHERE NAME IS NOT NULL AND (LOCK_TIME IS NULL OR SINCE_EPOCH(MILLIS,CURRENT_TIMESTAMP)-10000 < SINCE_EPOCH(MILLIS,LOCK_TIME));
they both work (in the sense that they don't return error messages...)

The error message itself is quite surprising: "Number of arguments provided was 2 where 4 was expected for statement..." - clearly only 2 should be expected, given the source code...
Your comments would be very welcome, and if you think I could optimize the query better, or recommend better ways to check timestamps (but also avoid this problem) please let me know.
Thanks,
Paul
dremella
Aug 12, 2014
Hi Paul,

We are looking into why the 2 params vs 4 params error message is coming up. In the mean time, for you to get the right results, can you please try:

"SELECT * FROM PAULTEST WHERE NAME IS NOT NULL AND (LOCK_TIME IS NULL OR ((SINCE_EPOCH(MILLIS,CURRENT_TIMESTAMP)-?) < SINCE_EPOCH(MILLIS,LOCK_TIME))) LIMIT ?"

You can reach me directly at dremella @ voltdb . com also.

Thanks,
Dheeraj
pmilner
Aug 13, 2014
Hi Dheeraj
thanks for replying. Your suggested fix does not give the error message, so that's a great help. Thanks.
pmartel
Aug 26, 2014
I have investigated this issue and narrowed it down to a parser bug.
Small adjustments to the query like adding levels of parentheses around nested expressions with parameters (that is '?') or even reordering clauses or reversing operators (like using A > B in place of B < A) MAY often change the path through the parser enough to work around the issue.
The VoltDB engineering team is looking into the best way to develop and release a code fix for this.
To date, no target release number or release date has been decided for this fix.
This forum thread will be updated as this work progresses.
The JIRA ticket is ENG-6861: https://issues.voltdb.com/browse/ENG-6861