Hi,

I have found what I believe to be an issue with parameter handling in a
complex query. It seems it is possible for the incorrect parameter to be
returned as a value when evaluating the query (in my case a timestamp
parameter was evaluating to a long - using the value of a separate
parameter of from the query).

The query itself is generated and quite complex with a fair amount of
context so it is not trivial to make a stand alone test case for this.
Below is the error.

LIMIT 10000 [22007-173]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:331)
    at org.h2.message.DbException.get(DbException.java:160)
    at org.h2.value.ValueTimestamp.parse(ValueTimestamp.java:87)
    at org.h2.value.Value.convertTo(Value.java:834)
    at org.h2.value.Value.getTimestamp(Value.java:395)
    at org.h2.expression.Function.getValueWithArgs(Function.java:1161)
    at org.h2.expression.Function.getValue(Function.java:466)
    at org.h2.expression.Operation.getValue(Operation.java:110)
    at org.h2.expression.Aggregate.updateAggregate(Aggregate.java:246)
    at org.h2.expression.Function.updateAggregate(Function.java:2117)
    at org.h2.expression.Function.updateAggregate(Function.java:2117)
    at org.h2.expression.Alias.updateAggregate(Alias.java:88)
    at org.h2.command.dml.Select.queryGroup(Select.java:344)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:620)
    at org.h2.command.dml.Query.query(Query.java:314)
    at org.h2.command.dml.Query.query(Query.java:284)
    at org.h2.index.ViewIndex.find(ViewIndex.java:253)
    at org.h2.index.BaseIndex.find(BaseIndex.java:126)
    at org.h2.index.IndexCursor.find(IndexCursor.java:155)
    at org.h2.table.TableFilter.next(TableFilter.java:328)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.command.dml.Select.queryGroup(Select.java:316)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:620)
    at org.h2.command.dml.Query.query(Query.java:314)
    at org.h2.command.dml.Query.query(Query.java:284)
    at org.h2.expression.Subquery.getValue(Subquery.java:37)
    at org.h2.expression.Function.getNullOrValue(Function.java:974)
    at org.h2.expression.Function.getSimpleValue(Function.java:815)
    at org.h2.expression.Function.getValueWithArgs(Function.java:992)
    at org.h2.expression.Function.getValue(Function.java:466)
    at org.h2.expression.Operation.getValue(Operation.java:110)
    at org.h2.expression.Function.getNullOrValue(Function.java:974)
    at org.h2.expression.Function.getValueWithArgs(Function.java:991)
    at org.h2.expression.Function.getValue(Function.java:466)
    at
org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:361)
    at org.h2.expression.JavaFunction.getValue(JavaFunction.java:39)
    at org.h2.expression.ExpressionList.getValue(ExpressionList.java:33)
    at
org.h2.expression.JavaAggregate.updateAggregate(JavaAggregate.java:194)
    at org.h2.expression.Function.updateAggregate(Function.java:2117)
    at org.h2.expression.Alias.updateAggregate(Alias.java:88)
    at org.h2.command.dml.Select.queryGroupSorted(Select.java:189)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:618)
    at org.h2.command.dml.Query.query(Query.java:314)
    at org.h2.command.dml.Query.query(Query.java:284)
    at org.h2.index.ViewIndex.find(ViewIndex.java:253)
    at org.h2.index.BaseIndex.find(BaseIndex.java:126)
    at org.h2.index.IndexCursor.find(IndexCursor.java:155)
    at org.h2.table.TableFilter.next(TableFilter.java:328)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.table.TableFilter.next(TableFilter.java:398)
    at org.h2.command.dml.Select.queryFlat(Select.java:518)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:625)
    at org.h2.command.dml.Query.query(Query.java:314)
    at org.h2.command.dml.Query.query(Query.java:284)
    at org.h2.command.dml.Query.query(Query.java:1)
    at org.h2.command.CommandContainer.query(CommandContainer.java:91)
    at org.h2.command.Command.executeQuery(Command.java:195)
    at
org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:106)
    ...
Caused by: java.lang.IllegalArgumentException: -5665000565650751172
    at org.h2.util.DateTimeUtils.parseDateValue(DateTimeUtils.java:270)
    at org.h2.value.ValueTimestamp.parseTry(ValueTimestamp.java:105)
    at org.h2.value.ValueTimestamp.parse(ValueTimestamp.java:85)


Debugging it I can make interventions at several points to fix the issue.
In my case parameter 23 was receiving the value from I think one of its
neighbours(22 or 24) in the ViewIndex. I believe the offset it receives
here is incorrect.

ViewIndex:234:

        int idx = originalParameters == null ? 0 :
originalParameters.size();
        idx += view.getParameterOffset();


The parameter list the offset was calculated from contained a duplicate
last parameter. So I can intervene here to remove the duplicate parameter
(in my case it is the last parameter that is duplicated). This worked in
this case and didn't break anything else for me, but I am not sure which
invariants hold true about parameter lists in general (e.g. can the contain
duplicates?, are they always in ascending order).

TableView:72:

    public TableView(Schema schema, int id, String name, String querySQL,
ArrayList<Parameter> params, String[] columnNames,
            Session session, boolean recursive) {
        super(schema, id, name, false, true);
        // remove duplicate parameter
        if(containsDuplicate(params)){
            params = new ArrayList(new LinkedHashSet(params));
        }
        init(querySQL, params, columnNames, session, recursive);
    }


I tracked the duplicate down to the Parser

Parser:2493:         (In the 'PARAMETER' case of the 'readTerm' method).
            parameters.add(p);

And changed it to:-
            // check parameter is not a duplicate before adding it
            if(!parameters.contains(p)){
                parameters.add(p);
            }

At this point I think it is because of SQL which is being generated
internally by h2 which is reusing the parameter and its index as nothing I
send directly to h2 contains the index in the parameter (i.e. I always use
'?' and not '?23' for parameters), as this is only way a duplicate can be
added.

If one of the developers would like to have a look I can provide a
standalone, albeit complicated test case for this issue.

Alternatively I can make further investigations if you have any suggestions,


many thanks,

mike

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Reply via email to