NullPointerException in select query

[GoogleCodeExporter]

H2 Version: h2-1.3.153.jar
Mode: Server
OS: Linux
JVM: 1.6.0_24 (32 Bit)

What steps will reproduce the problem?
1. Create a new db and execute the attached ddl.sql
2. f I execute the following query I get a NullPointerException.

SELECT * FROM VSCPC_SESSION_LIST_DATA
WHERE
 lang_Cd = 'en' -- if you comment out this line (or the subsequent --lines )no exception happens
 AND athlete_Id = 1000
 AND  start_ts >= DATEADD('MONTH', 1, DATE '2001-01-31') 
 AND start_ts < DATEADD('MONTH', 4, DATE '2011-04-24') 

General error: "java.lang.NullPointerException" [50000-153] HY000/50000 (Help)
org.h2.jdbc.JdbcSQLException: General error: "java.lang.NullPointerException" 
[50000-153]
    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:662)
Caused by: java.lang.NullPointerException
    at org.h2.index.BaseIndex.getColumnIndex(BaseIndex.java:250)
    at org.h2.table.TableFilter.prepare(TableFilter.java:255)
    at org.h2.command.dml.Select.preparePlan(Select.java:914)
    at org.h2.command.dml.Select.prepare(Select.java:815)
    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.server.TcpServerThread.process(TcpServerThread.java:226)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:137)
    ... 1 more

    at org.h2.engine.SessionRemote.done(SessionRemote.java:540)
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:67)
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:46)
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:418)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1100)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:164)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
    at org.h2.server.web.WebApp.getResult(WebApp.java:1308)
    at org.h2.server.web.WebApp.query(WebApp.java:994)
    at org.h2.server.web.WebApp$1.next(WebApp.java:957)
    at org.h2.server.web.WebApp$1.next(WebApp.java:960)
    at org.h2.server.web.WebThread.process(WebThread.java:166)
    at org.h2.server.web.WebThread.run(WebThread.java:93)
    at java.lang.Thread.run(Thread.java:662)

3. If the same query is executed with the lang_cd where clause, the query 
executes successfully.

SELECT * FROM VSCPC_SESSION_LIST_DATA
WHERE
  athlete_Id = 1000
 AND  start_ts >= DATEADD('MONTH', 1, DATE '2001-01-31') 
 AND start_ts < DATEADD('MONTH', 4, DATE '2011-04-24') 

Do you know a workaround?
no workaround known

What is your use case, meaning why do you need this feature?
filtering on language code

How important/urgent is the problem for you?
blocking issue

Original issue reported on code.google.com by andr...@fuerer.net on 18 Apr 2011 at 4:36

Attachments:

• ddl.sql

[GoogleCodeExporter]

Issue could also be reproduced with H2 1.3.154

Original comment by andr...@fuerer.net on 18 Apr 2011 at 4:37

[GoogleCodeExporter]

Correction for typo above:

3. If the same query is executed *without* the lang_cd where clause, the query 
executes successfully.

Original comment by andr...@fuerer.net on 19 Apr 2011 at 6:20

[GoogleCodeExporter]

Hi,

Thanks for reporting the issue. With the current version of H2 (trunk), I can't 
create the view. I get the exception:

Column "FLAG_COMMENT.COMMENT_ID" not found

With other databases, I also get exceptions:

PostgreSQL:
column "flag.session_id" must appear in the GROUP BY clause or be used in an 
aggregate function 42803/0

Derby: 
Column reference 'FLAG.SESSION_ID' is invalid. When the SELECT list contains at 
least one aggregate then all entries must be valid aggregate expressions.  

Original comment by thomas.t...@gmail.com on 17 May 2011 at 5:23

Attachments:

• test.sql

[GoogleCodeExporter]

Hi,

there was indeed a mistake in the view sql (missing aggregation), sorry for 
that. We can reproduce the same behavior as you if we use H2 from the trunk.

However, we still have a problem with this view. We made a drill-down to a 
simpler view where we still can reproduce a NPE. (using H2 from trunk)

CREATE OR REPLACE VIEW VSCPC_SESSION_LIST_DATA AS 
SELECT   
    session_t.START_TS,
    t.LANG_CD as LANG_CD        
FROM
    TSCPC_SESSION session_t
    JOIN TSCPC_SESSION_METADATA metadata ON metadata.SESSION_ID = session_t.session_id   
    JOIN TSCPR_TRAININGTYPE_TX t on t.TRAININGTYPE_CD = metadata.TRAININGTYPE_ID

SELECT * FROM VSCPC_SESSION_LIST_DATA
where lang_Cd = 'en' -- if you comment out this line (or the subsequent --lines 
)no exception happens
AND  start_ts >= DATEADD('MONTH', 1, DATE '2001-01-31')
AND start_ts < DATEADD('MONTH', 4, DATE '2011-04-24') 

General error: "java.lang.NullPointerException" [50000-154] HY000/50000 (Help)
org.h2.jdbc.JdbcSQLException: General error: "java.lang.NullPointerException" 
[50000-154]
    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:662)
Caused by: java.lang.NullPointerException
    at org.h2.index.BaseIndex.getColumnIndex(BaseIndex.java:250)
    at org.h2.table.TableFilter.prepare(TableFilter.java:255)
    at org.h2.command.dml.Select.preparePlan(Select.java:917)
    at org.h2.command.dml.Select.prepare(Select.java:818)
    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.server.TcpServerThread.process(TcpServerThread.java:226)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:137)
    ... 1 more

    at org.h2.engine.SessionRemote.done(SessionRemote.java:538)
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:67)
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:46)
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:417)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1088)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:164)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
    at org.h2.server.web.WebApp.getResult(WebApp.java:1304)
    at org.h2.server.web.WebApp.query(WebApp.java:994)
    at org.h2.server.web.WebApp$1.next(WebApp.java:957)
    at org.h2.server.web.WebApp$1.next(WebApp.java:946)
    at org.h2.server.web.WebThread.process(WebThread.java:166)
    at org.h2.server.web.WebThread.run(WebThread.java:93)
    at java.lang.Thread.run(Thread.java:662)

Original comment by andr...@fuerer.net on 19 May 2011 at 2:25

Attachments:

• test2.sql

[GoogleCodeExporter]

Hi,

I have a simpler test case now:

create table test(a int, b int);
create view test_view as select a, b from test;
select * from test_view where a between 1 and 2 and b = 2;

This will be fixed in the next release. Thanks a lot for your help!

Original comment by thomas.t...@gmail.com on 20 May 2011 at 10:05

• Changed state: Accepted

[GoogleCodeExporter]

This is fixed in version 1.3.155

Original comment by thomas.t...@gmail.com on 27 May 2011 at 10:54

• Changed state: Fixed