General error: "java.lang.NullPointerException" [50000-175] HY000/50000 in 1.3.175 - with 'case' clause

Please send a question to the H2 Google Group or StackOverflow first,
and only then, once you are completely sure it is an issue, submit it here.
The reason is that only very few people actively monitor the issue tracker.

Before submitting a bug, please also check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

select A
  from (
    select 
      true as B,
      case
        when a.X = 'X' then a.X
        else a.X 
      end as A
      from (
        select 'X' as X  from dual 
      )  a 
  )
where A = 'X' and B = true;

What is the expected output? What do you see instead?
Expected, one row as:

A 
--  
X 

Result:

General error: "java.lang.NullPointerException" [50000-175] HY000/50000 (Help)
org.h2.jdbc.JdbcSQLException: General error: "java.lang.NullPointerException" 
[50000-175] 
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:332) 
    at org.h2.message.DbException.get(DbException.java:161) 
    at org.h2.message.DbException.convert(DbException.java:284) 
    at org.h2.message.DbException.toSQLException(DbException.java:257) 
    at org.h2.message.TraceObject.logAndConvert(TraceObject.java:368) 
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158) 
    at org.h2.server.web.WebApp.getResult(WebApp.java:1321) 
    at org.h2.server.web.WebApp.query(WebApp.java:1010) 
    at org.h2.server.web.WebApp$1.next(WebApp.java:972) 
    at org.h2.server.web.WebApp$1.next(WebApp.java:959) 
    at org.h2.server.web.WebThread.process(WebThread.java:167) 
    at org.h2.server.web.WebThread.run(WebThread.java:94) 
    at java.lang.Thread.run(Unknown Source) 
Caused by: java.lang.NullPointerException 
    at org.h2.expression.Function.getCost(Function.java:2391) 
    at org.h2.expression.Comparison.getCost(Comparison.java:480) 
    at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:133) 
    at org.h2.command.dml.Select.prepare(Select.java:813) 
    at org.h2.command.Parser.prepare(Parser.java:220) 
    at org.h2.engine.Session.prepare(Session.java:403) 
    at org.h2.index.ViewIndex.getCost(ViewIndex.java:159) 
    at org.h2.table.TableView.getBestPlanItem(TableView.java:210) 
    at org.h2.table.TableFilter.getBestPlanItem(TableFilter.java:184) 
    at org.h2.table.Plan.calculateCost(Plan.java:111) 
    at org.h2.command.dml.Optimizer.testPlan(Optimizer.java:177) 
    at org.h2.command.dml.Optimizer.calculateBestPlan(Optimizer.java:81) 
    at org.h2.command.dml.Optimizer.optimize(Optimizer.java:230) 
    at org.h2.command.dml.Select.preparePlan(Select.java:933) 
    at org.h2.command.dml.Select.prepare(Select.java:834) 
    at org.h2.command.Parser.prepareCommand(Parser.java:240) 
    at org.h2.engine.Session.prepareLocal(Session.java:436) 
    at org.h2.engine.Session.prepareCommand(Session.java:379) 
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138) 
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:168) 
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:156) 
    ... 7 more 

What version of the product are you using? On what operating system, file
system, and virtual machine?

1.3.175, Windows 7/64, 8/64, NTFS, Java 1.6

Do you know a workaround?

Use 1.3.174

What is your use case, meaning why do you need this feature?

Sample sql provided to show issue.
Sample query is using dual, but fail with any tables or views.

How important/urgent is the problem for you?

Please provide any additional information below.
If nested query is abstarcted in a view, it would cause the same error.

If case is replaced by just getting the field value it would not fail:

-- fail
case
when a.X= 'X' then a.X
else  a.X 
end as A

-- works
a.X as A   

Also when 'and B = true' condition is removed the query works.

-- fail
where A = 'X' and B = true;

-- works
where A = 'X';
Original issue reported on code.google.com by jean.gui...@gmail.com on 3 Apr 2014 at 10:49
kashipai / h2database

General error: "java.lang.NullPointerException" [50000-175] HY000/50000 in 1.3.175 - with 'case' clause #556
