Just to make life a little easier for Thomas, could you forward the DDL for
your various tables ("vulnerabilites", "some_table" and "acme") so he has a
self-contained test to run that illustrates the problem.On Wed, Oct 13, 2010 at 9:54 AM, Chris <[email protected]> wrote: > 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]<h2-database%[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]<h2-database%[email protected]> > . > For more options, visit this group at > http://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.
