Hi,
Ok sorry. I thought possibly it would be relatively easy to debug if you
could see it happening.
I can certainly create the test as a standalone stored procedure, and then
I suppose I should have a go at removing bits to try and reduce it.
In terms of h2 code is there a reason that the parameter references need to
be copied in the manner that they are? In my simplistic understanding for a
given stored procedure there will always be a fixed number of parameters,
so in an ideal world a single data structure could be used to store those
parameters. (Any generated sub queries would have to reference the
parameters by number but presumably we always control that),
I'll email an updated test case when I have it ready,
regards,
Mike
On Sat, Sep 28, 2013 at 5:57 PM, Thomas Mueller <
thomas.tom.muel...@gmail.com> wrote:
> Hi,
>
> Unfortunately, it's quite hard to analyze the problem with this test case.
> The test case doesn't include source code, and the statement is very large
> (500 lines or so). Would it be possible to simplify the test case to just a
> few lines of SQL statement, and to a simple, standalone test case with
> source code?
>
> Regards,
> Thomas
>
>
> On Wed, Sep 25, 2013 at 7:35 PM, Mike Goodwin <mkpgood...@gmail.com>wrote:
>
>> I replied off the list with a link to download a test case.
>>
>> - mike
>>
>>
>> On Tue, Sep 24, 2013 at 6:29 PM, Thomas Mueller <
>> thomas.tom.muel...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> There were a few issues about parameter indexes in combination with
>>> views or subqueries in the past, it's possible that there are remaining
>>> issues.
>>>
>>> > I can provide a standalone, albeit complicated test case for this
>>> issue.
>>>
>>> That would be great! I wouldn't want to change the code without having a
>>> proper test case.
>>>
>>> Regards,
>>> Thomas
>>> 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.
>>>
>>> --
>>> 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.
>>>
>>
>> --
>> 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.
>>
>
> --
> 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.
>
--
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.
