After some experimentation I was actually able to find the potential
root cause, which is the MAX() aggregate in the sub select of the
view.
When changing the view definition from
create view if not exists some_view as
select a.id,
a.status,
count(svs.id) as vulnerabilities,
(select max(id) from acme where id < a.id and some_id =
some_table.some_id) previous_a_id
from acme a left outer join svs on a.svs_id = svs.id
group by s.id, s.status
to
create view if not exists some_view as
select a.id,
a.status,
count(svs.id) as vulnerabilities,
(SELECT id FROM acme WHERE id < a.id AND some_id =
some_table.some_id ORDER BY id DESC LIMIT 1)
from acme a left outer join svs on a.svs_id = svs.id
group by s.id, s.status
there are no more exceptions.
Hope this helps,
Chris
On Oct 11, 8:49 pm, Chris <[email protected]> wrote:
> I was finally able to reproduce the problems and was able to narrow it
> down to simple queries.
>
> The problem occurs when there is a join query across a view and
> another table with a WHERE clause referencing the table:
>
> select some_view.id from some_view inner join some_table on
> some_view.some_id=some_table.id where some_table.another_id in (1,2)
>
> Changing the "inner join" to "left outer join" doesn't make a
> difference.
>
> I noticed that the stack trace contained some "updateAggregate"
> references, so here's a simplified definition of the view:
>
> create view if not exists some_view as
> select a.id,
> a.status,
> count(svs.id) as vulnerabilities,
> (select max(id) from acme where id < a.id and some_id =
> some_table.some_id) previous_a_id
> from acme a left outer join svs on a.svs_id = svs.id
> group by s.id, s.status
>
> Notice that some_table is referenced in the sub-select as well as in
> the original query above.
>
> Let me know if there is some more specific information I should
> provide,
>
> Chris
>
> On Sep 29, 1:09 pm, Thomas Mueller <[email protected]>
> wrote:
>
>
>
> > Hi,
>
> > Could you append ";TRACE_LEVEL_FILE=2" to the database URL and then
> > check in the .trace.db file what SQL statement caused this problem? Or
> > maybe there is another way to find out what SQL statement caused the
> > problem. It looks like a bug in H2 where the internal query data
> > structure is broken when re-running a query. However, I don't
> > understand what the problem could be. With the actual statement it's
> > much simpler to analyze the problem.
>
> > Regards,
> > Thomas
>
> > On Mon, Sep 27, 2010 at 11:09 PM, Chris <[email protected]> wrote:
> > > Hi,
>
> > > We're using H2 (version 1.2.143) in a Grails project with connection
> > > string
> > > "jdbc:h2:file:mydb;MVCC=TRUE;DEFAULT_LOCK_TIMEOUT=3000;AUTO_SERVER=TRUE;CAC
> > > HE_SIZE=262144"
> > > When using a pooled datasource, the following stacktrace occurs
> > > regularly when performing a criteria query over a view:
>
> > > 13:55:08,168 ERROR [GrailsExceptionResolver] - General error:
> > > "java.lang.NullPointerException" [50000-143]
> > > org.h2.jdbc.JdbcSQLException: General error:
> > > "java.lang.NullPointerException" [50000-143]
> > > at org.h2.message.DbException.getJdbcSQLException(DbException.java:
> > > 327)
> > > at org.h2.message.DbException.get(DbException.java:156)
> > > at org.h2.message.DbException.convert(DbException.java:279)
> > > at
> > > org.h2.server.TcpServerThread.sendError(TcpServerThread.java:186)
> > > at org.h2.server.TcpServerThread.run(TcpServerThread.java:139)
> > > at java.lang.Thread.run(Thread.java:637)
> > > Caused by: java.lang.NullPointerException
> > > at org.h2.index.IndexCursor.get(IndexCursor.java:209)
> > > at org.h2.table.TableFilter.getValue(TableFilter.java:824)
> > > at
> > > org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:
> > > 133)
> > > at
> > > org.h2.expression.Comparison.updateAggregate(Comparison.java:411)
> > > at
> > > org.h2.expression.ConditionAndOr.updateAggregate(ConditionAndOr.java:
> > > 242)
> > > at org.h2.command.dml.Select.updateAggregate(Select.java:1106)
> > > at org.h2.expression.Subquery.updateAggregate(Subquery.java:92)
> > > at org.h2.expression.Alias.updateAggregate(Alias.java:76)
> > > at org.h2.command.dml.Select.queryGroup(Select.java:336)
> > > at org.h2.command.dml.Select.queryWithoutCache(Select.java:575)
> > > at org.h2.command.dml.Query.query(Query.java:241)
> > > at org.h2.index.ViewIndex.find(ViewIndex.java:231)
> > > at org.h2.index.IndexCursor.find(IndexCursor.java:138)
> > > at org.h2.table.TableFilter.next(TableFilter.java:309)
> > > at org.h2.table.TableFilter.next(TableFilter.java:379)
> > > at org.h2.table.TableFilter.next(TableFilter.java:379)
> > > at org.h2.table.TableFilter.next(TableFilter.java:379)
> > > at org.h2.command.dml.Select.queryFlat(Select.java:493)
> > > at org.h2.command.dml.Select.queryWithoutCache(Select.java:580)
> > > at org.h2.command.dml.Query.query(Query.java:241)
> > > at org.h2.index.ViewIndex.find(ViewIndex.java:231)
> > > at org.h2.index.IndexCursor.find(IndexCursor.java:138)
> > > at org.h2.table.TableFilter.next(TableFilter.java:309)
> > > at org.h2.command.dml.Select.queryFlat(Select.java:493)
> > > at org.h2.command.dml.Select.queryWithoutCache(Select.java:580)
> > > at org.h2.command.dml.Query.query(Query.java:241)
> > > at org.h2.command.CommandContainer.query(CommandContainer.java:80)
> > > at org.h2.command.Command.executeQuery(Command.java:132)
> > > at org.h2.server.TcpServerThread.process(TcpServerThread.java:278)
> > > at org.h2.server.TcpServerThread.run(TcpServerThread.java:137)
>
> > > This doesn't happen the first time the query runs after the
> > > application starts, but every time afterwards. Also, when not using a
> > > pooled connection, this problem doesn't seem to appear, so we are
> > > currently assuming it might have something to do with Hibernate
> > > session management in Grails. However, theNullPointerException
> > > doesn't give us any hints on where to start and there is no related
> > > Hibernate exception.
>
> > > The AUTO_SERVER feature is used, because there are actually two
> > > separate web applications running, sharing the same database and it's
> > > not predictable which application starts first.
>
> > > I'd appreciate any hints for a workaround or solution,
>
> > > Chris
>
> > > --
> > > You received this message because you are subscribed to the Google Groups
> > > "H2 Database" group.
> > > To post to this group, send email to [email protected].
> > > To unsubscribe from this group, send email to
> > > [email protected].
> > > For more options, visit this group
> > > athttp://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
