Null Pointer Exception in TableFilter.setPlanItem with 1.3.151

[GoogleCodeExporter]

We are getting the following running some of our SQL against the latest 
available H2

Caused by: java.lang.NullPointerException
  at org.h2.table.TableFilter.setPlanItem(TableFilter.java:226)
  at org.h2.command.dml.Optimizer.optimize(Optimizer.java:248)
  at org.h2.command.dml.Select.preparePlan(Select.java:897)
  at org.h2.command.dml.Select.prepare(Select.java:804)
  at org.h2.command.Parser.prepare(Parser.java:202)
  at org.h2.command.Parser.prepareCommand(Parser.java:214)
  at org.h2.engine.Session.prepareLocal(Session.java:426)
  at org.h2.engine.Session.prepareCommand(Session.java:374)
  at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1089)
  at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
  at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:597)

I have debugged this and the NullPointer is obviously caused by a Null PlanItem 
being passed in optimize() method of the Optimizer class.
How this is becoming null is due to the "break;" in the calculateCost method of 
the Plan class.

As the filters are being iterated through i.e. 

for (TableFilter tableFilter : allFilters) {

and the planItems created

planItems.put(tableFilter, item);

the break is called when an invalid plan is found.

This leaves an incomplete planItems list that is being later queried in that 
optimize() method, hence the null.

I have simply removed the break and this resolves the NullPointer.

I will let you decide whether that was the appropriate resolution for this.

Thanks.

Ronan

Original issue reported on code.google.com by Ronan.Mu...@gmail.com on 22 Feb 2011 at 11:18

[GoogleCodeExporter]

Hi,

This is a strange bug... I don't understand how I could reproduce it. Simply 
removing the 'break' statement might work, but the question is why should the 
optimizer select an invalid plan? Could you attach a reproducible test case, or 
at least the query?

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 24 Feb 2011 at 7:21

[GoogleCodeExporter]

Attaching a test case which reproduces for me.

Original comment by Ronan.Mu...@gmail.com on 25 Feb 2011 at 12:07

Attachments:

• H2Test.java

[GoogleCodeExporter]

I think I found the problem now, and will try to fix it in the next release. I 
have committed the fix in revision 3443 - if you have time could you verify it 
works for you now?

Please note there is a problem in the test case: it contains a sub-query that 
is not supported by H2 (and all other databases except MySQL) in this form:

SELECT TABLE2.FIELD2, COUNT(*) AS TABLE2count FROM TABLE2

This sub-query needs to be changed to 

SELECT TABLE2.FIELD2, COUNT(*) AS TABLE2count FROM TABLE2 GROUP BY Table2.FIELD2

Original comment by thomas.t...@gmail.com on 26 Feb 2011 at 11:38

• Changed state: Accepted

[GoogleCodeExporter]

I had shortened the SQL to make it a simpler test case and had removed the 
group by, sorry.

The following is our full SQL

SELECT 
 Position.positionID, 
 Position.name, 
 Position.leadPositionInd, 
 Position.jobID, 
 OrgUnitPositionLink.organisationStructureID, 
 OrgUnitPositionLink.organisationUnitID, 
 Position.fromDate, 
 Position.toDate, 
 Job.name, 
 OrganisationStructure.name, 
 OrganisationUnit.name, 
 Users.fullName, 
 Position.recordStatus, 
 CASE WHEN PHLcount = 0 THEN '1' WHEN PHLcount IS NULL THEN '1' ELSE '0' END 
 FROM OrganisationStructure, OrganisationUnit, OrgUnitPositionLink, Position 
 RIGHT OUTER JOIN (
  SELECT PositionHolderLink.positionID, COUNT(*) AS PHLcount 
  FROM PositionHolderLink WHERE PositionHolderLink.userName = ? 
  AND (PositionHolderLink.organisationStructureID = ? 
  OR (? <> '0')) 
  AND PositionHolderLink.recordStatus = ? 
  AND PositionHolderLink.fromDate <= ? 
  AND (PositionHolderLink.toDate >= ? 
  OR PositionHolderLink.toDate IS NULL) GROUP BY PositionHolderLink.positionID) 
  VPHL ON Position.positionID = VPHL.positionID, PositionHolderLink, Users, Job 
  WHERE Users.userName = ? 
  AND PositionHolderLink.userName = Users.userName 
  AND (PositionHolderLink.organisationStructureID = ? 
  OR (? <> '0')) 
  AND Position.positionID = PositionHolderLink.positionID 
  AND OrgUnitPositionLink.organisationUnitID = OrganisationUnit.organisationUnitID 
  AND OrgUnitPositionLink.organisationStructureID = PositionHolderLink.organisationStructureID 
  AND OrgUnitPositionLink.positionID = Position.positionID 
  AND Job.jobID = Position.jobID 
  AND OrganisationStructure.organisationStructureID = PositionHolderLink.organisationStructureID

and with this I get the following with the latest on the trunk

org.h2.jdbc.JdbcSQLException: Column "POSITION.POSITIONID" must be in the GROUP 
BY list; SQL statement:

which means that the SQL still fails and the SQL looks correct based on your 
last comment i.e. that the group by should be on PositionHolderLink.positionID.

The same SQL works against Oracle and various flavors of DB2 so feel uneasy to 
say this is an SQL issue here.

Original comment by Ronan.Mu...@gmail.com on 28 Feb 2011 at 10:54

[GoogleCodeExporter]

Hi,

Could you upload the CREATE TABLE and CREATE INDEX statements as well please? 
So that I have a reproducible test case.

Thanks a lot!

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 2 Mar 2011 at 5:13

[GoogleCodeExporter]

Attaching SQL for relevant tables and indices.

Original comment by Ronan.Mu...@gmail.com on 2 Mar 2011 at 8:47

Attachments:

• 288.sql

[GoogleCodeExporter]

I don't know the problem yet, but I have a complete (and simpler) test case now.

Original comment by thomas.t...@gmail.com on 2 Mar 2011 at 7:30

Attachments:

• 288b.sql

[GoogleCodeExporter]

This is now fixed in the trunk, in revision 3484

Original comment by thomas.t...@gmail.com on 6 Mar 2011 at 7:38

[GoogleCodeExporter]

Version 1.3.153

Original comment by thomas.t...@gmail.com on 25 Mar 2011 at 7:22

• Changed state: Fixed