Multi-Threading issues

[Daniel Rosenbaum]

Running H2 version 1.4.192 with default settings and MULTI_THREADED=1.  Usually runs okay for the first minute or two but often ends up with one of two different exceptions, either "JdbcSQLException: Table TABLE_NAME not found" or "NullPointerException".

During a run my application creates many tables and views, and view definitions reference other views in joins and unions/intersections (i.e. there are multiple levels of view "nesting").  These exceptions do not occur on any particular table or view and just seem to occur at random points in execution (i.e. different runs would have the same sorts of exceptions but with different table or view names in the error message).

Note that executing all the SQL in a single thread does not result in any issues.

Below are the stack traces.  

Exception #1:

jdbc[10]: exception

org.h2.jdbc.JdbcSQLException: Table "NUMERATOR" not found; SQL statement:

INSERT INTO application.NUMERATOR_DETAIL (SELECT t51.patient_id FROM application.NUMERATOR t51 INTERSECT (

SELECT t52.patient_id FROM application.PatientCharacteristicBirthdateBirthDate_precondition_77 t52 INTERSECT 

SELECT t53.patient_id FROM application.PatientCharacteristicBirthdateBirthDate_precondition_79 t53)) [42102-192]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)

at org.h2.message.DbException.get(DbException.java:179)

at org.h2.message.DbException.get(DbException.java:155)

at org.h2.schema.Schema.getTableOrView(Schema.java:436)

at org.h2.command.Parser.readTableOrView(Parser.java:5371)

at org.h2.command.Parser.readTableFilter(Parser.java:1257)

at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1896)

at org.h2.command.Parser.parseSelectSimple(Parser.java:2044)

at org.h2.command.Parser.parseSelectSub(Parser.java:1890)

at org.h2.command.Parser.parseSelectUnion(Parser.java:1711)

at org.h2.command.Parser.parseSelect(Parser.java:1699)

at org.h2.command.Parser.parseInsert(Parser.java:1058)

at org.h2.command.Parser.parsePrepared(Parser.java:413)

at org.h2.command.Parser.parse(Parser.java:317)

at org.h2.command.Parser.parse(Parser.java:289)

at org.h2.command.Parser.prepareCommand(Parser.java:254)

at org.h2.engine.Session.prepareLocal(Session.java:560)

at org.h2.engine.Session.prepareCommand(Session.java:501)

at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1202)

at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:126)

at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:115)

at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:120)

at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)

at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:523)

at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:520)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404)

at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:534)

Exception #2:

org.h2.jdbc.JdbcSQLException: General error: "java.lang.NullPointerException"; SQL statement:

CREATE VIEW application.OccurrenceAOtherEndophthalmitis40_precondition_391 AS  SELECT t150.*, o1.ido1 FROM application.OccurrenceAOtherEndophthalmitis40 t150 JOIN application.OccurrenceACataractSurgery5 o1 ON t150.patient_id = o1.patient_id WHERE t150.negation_code_id IS NULL AND t150.effective_time_end < o1.effective_time_start [50000-192]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)

at org.h2.message.DbException.get(DbException.java:168)

at org.h2.message.DbException.convert(DbException.java:295)

at org.h2.command.Command.executeUpdate(Command.java:266)

at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:130)

at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:115)

at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:120)

at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)

at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:523)

at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:520)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404)

at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:534)

Caused by: java.lang.NullPointerException

at org.h2.engine.Session.popSubQueryInfo(Session.java:201)

at org.h2.index.ViewIndex.prepareSubQuery(ViewIndex.java:176)

at org.h2.index.ViewIndex.getQuery(ViewIndex.java:309)

at org.h2.index.ViewIndex.<init>(ViewIndex.java:102)

at org.h2.table.TableView.getBestPlanItem(TableView.java:248)

at org.h2.table.TableFilter.getBestPlanItem(TableFilter.java:220)

at org.h2.table.Plan.calculateCost(Plan.java:123)

at org.h2.command.dml.Optimizer.testPlan(Optimizer.java:183)

at org.h2.command.dml.Optimizer.calculateBruteForceAll(Optimizer.java:112)

at org.h2.command.dml.Optimizer.calculateBestPlan(Optimizer.java:83)

at org.h2.command.dml.Optimizer.optimize(Optimizer.java:242)

at org.h2.command.dml.Select.preparePlan(Select.java:1014)

at org.h2.command.dml.Select.prepare(Select.java:878)

at org.h2.command.Parser.prepare(Parser.java:239)

at org.h2.engine.Session.prepare(Session.java:525)

at org.h2.engine.Session.prepare(Session.java:512)

at org.h2.table.TableView.compileViewQuery(TableView.java:105)

at org.h2.table.TableView.initColumnsAndTables(TableView.java:156)

at org.h2.table.TableView.init(TableView.java:98)

at org.h2.table.TableView.<init>(TableView.java:65)

at org.h2.command.ddl.CreateView.update(CreateView.java:116)

at org.h2.command.CommandContainer.update(CommandContainer.java:98)

at org.h2.command.Command.executeUpdate(Command.java:258)

... 18 more

[Daniel Rosenbaum]

Update: I was able to create a test that results in the same two errors.  Please find the attached file.

Note that the exceptions do not happen every run, so you may need to run it a number of times for them to appear.

[Noel Grandin]

thank you for the excellent test-case, fix pushed to master!