[h2] NPE in simple Common Table Expression

[Mike Goodwin]

Hi,
I have a simple test case, where it cannot run but throws an NPE. I have
tested in 1.4.196 and in the head revision.

There seems to be a second issue whereby this cannot be rerun (in the
console at least) since you get this error, as if the WITH condition has
created a persistent view.

Hope this helps!

regards,

Mike

Cannot drop "B" because "BB, BB" depends on it; SQL statement:
<http://192.168.0.15:49741/query.do?jsessionid=6cdf827bc58d664086f36b8ae0985c48#>
----------------------------------------

DROP TABLE IF EXISTS A;
DROP TABLE IF EXISTS B;
DROP TABLE IF EXISTS C;
CREATE TABLE A(VAL VARCHAR(255));
CREATE TABLE B(A VARCHAR(255), VAL VARCHAR(255));
CREATE TABLE C(B VARCHAR(255), VAL VARCHAR(255));

INSERT INTO A VALUES('fruit');
INSERT INTO B VALUES('fruit','apple');
INSERT INTO B VALUES('fruit','banana');
INSERT INTO C VALUES('apple', 'golden delicious');
INSERT INTO C VALUES('apple', 'granny smith');
INSERT INTO C VALUES('apple', 'pippin');
INSERT INTO A VALUES('veg');
INSERT INTO B VALUES('veg', 'carrot');
INSERT INTO C VALUES('carrot', 'nantes');
INSERT INTO C VALUES('carrot', 'imperator');
INSERT INTO C VALUES(null, 'banapple');
INSERT INTO A VALUES('meat');

WITH BB as (SELECT
sum(1) as X,
a
FROM B
JOIN C ON B.val=C.b
GROUP BY a)
SELECT
A.val,
sum(SELECT X FROM BB WHERE BB.a IS A.val)
FROM A
GROUP BY A.val;;


-----------------------------------
This is the NullPointerExeption trying to run the select.

General error: "java.lang.NullPointerException" [50000-196]
<http://192.168.0.15:49741/query.do?jsessionid=6cdf827bc58d664086f36b8ae0985c48#>
HY000/50000 (Help)
<http://h2database.com/javadoc/org/h2/api/ErrorCode.html#c50000>
org.h2.jdbc.JdbcSQLException: General error:
"java.lang.NullPointerException" [50000-196]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345
<http://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=345&build=196>)

at org.h2.message.DbException.get(DbException.java:168
<http://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=168&build=196>)

at org.h2.message.DbException.convert(DbException.java:295
<http://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=295&build=196>)

at org.h2.message.DbException.toSQLException(DbException.java:268
<http://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=268&build=196>)

at org.h2.message.TraceObject.logAndConvert(TraceObject.java:358
<http://h2database.com/html/source.html?file=org/h2/message/TraceObject.java&line=358&build=196>)

at
org.h2.jdbc.JdbcResultSetMetaData.getColumnLabel(JdbcResultSetMetaData.java:70
<http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcResultSetMetaData.java&line=70&build=196>)

at org.h2.server.web.WebApp.getResultSet(WebApp.java:1646
<http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1646&build=196>)

at org.h2.server.web.WebApp.getResult(WebApp.java:1397
<http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1397&build=196>)

at org.h2.server.web.WebApp.query(WebApp.java:1053
<http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1053&build=196>)

at org.h2.server.web.WebApp$1.next(WebApp.java:1015
<http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1015&build=196>)

at org.h2.server.web.WebApp$1.next(WebApp.java:1
<http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1&build=196>)

at org.h2.server.web.WebThread.process(WebThread.java:164
<http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=164&build=196>)

at org.h2.server.web.WebThread.run(WebThread.java:89
<http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=89&build=196>)

at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.NullPointerException
at org.h2.util.StringUtils.indent(StringUtils.java:594
<http://h2database.com/html/source.html?file=org/h2/util/StringUtils.java&line=594&build=196>)

at org.h2.util.StringUtils.indent(StringUtils.java:582
<http://h2database.com/html/source.html?file=org/h2/util/StringUtils.java&line=582&build=196>)

at org.h2.table.TableView.getSQL(TableView.java:442
<http://h2database.com/html/source.html?file=org/h2/table/TableView.java&line=442&build=196>)

at org.h2.table.TableFilter.getPlanSQL(TableFilter.java:824
<http://h2database.com/html/source.html?file=org/h2/table/TableFilter.java&line=824&build=196>)

at org.h2.command.dml.Select.getPlanSQL(Select.java:1103
<http://h2database.com/html/source.html?file=org/h2/command/dml/Select.java&line=1103&build=196>)

at org.h2.expression.Subquery.getSQL(Subquery.java:93
<http://h2database.com/html/source.html?file=org/h2/expression/Subquery.java&line=93&build=196>)

at org.h2.expression.Aggregate.getSQL(Aggregate.java:574
<http://h2database.com/html/source.html?file=org/h2/expression/Aggregate.java&line=574&build=196>)

at org.h2.expression.Expression.getAlias(Expression.java:245
<http://h2database.com/html/source.html?file=org/h2/expression/Expression.java&line=245&build=196>)

at org.h2.result.LocalResult.getAlias(LocalResult.java:439
<http://h2database.com/html/source.html?file=org/h2/result/LocalResult.java&line=439&build=196>)

at
org.h2.jdbc.JdbcResultSetMetaData.getColumnLabel(JdbcResultSetMetaData.java:68
<http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcResultSetMetaData.java&line=68&build=196>)

[Noel Grandin]

Can you try a master build, we have fixed various WITH problems recently
(thanks to people like @stumc)​

[Andrei Tokar]

I think @stumc need to take a second look at this because problem still exists in master. It seems like TableView is released prematurely and ResultSetMetaData can not be constructed.

[Mike Goodwin]

Yes, I did try with the head (I assume master) revision at the time - 7099
I think.

Post by Noel Grandin
Can you try a master build, we have fixed various WITH problems recently
--
You received this message because you are subscribed to the Google Groups
"H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Stuart McMillan]

Thanks Mike,

I'll have a look at this case and see what I can think of to fix it.
Thanks for taking the time to report it!

stumc

Post by Mike Goodwin
Yes, I did try with the head (I assume master) revision at the time - 7099
I think.

Post by Noel Grandin
Can you try a master build, we have fixed various WITH problems recently
--
You received this message because you are subscribed to the Google Groups
"H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups
"H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Ayush Mathur]

Hi,

Is this issue resolved ? I'm facing a similar issue where my service is
repeatedly logging the following errors:
1. SQL Error: 50000, SQLState: HY000
2. General error: "java.lang.NullPointerException"; SQL statement: followed
by select query [50000-196]
3. Stack trace as:
Caused by: org.h2.jdbc.JdbcSQLException: General error:
"java.lang.NullPointerException"; SQL statement: select A, B, C from TABLE
where A=? [50000-196] 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.executeQuery(Command.java:215) at
org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:111)
at
org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
... 203 common frames omitted Caused by: java.lang.NullPointerException:
null at org.h2.store.LobStorageMap.copyLob(LobStorageMap.java:257) at
org.h2.value.ValueLobDb.copyToResult(ValueLobDb.java:488) at
org.h2.value.ValueLobDb.copyToResult(ValueLobDb.java:38) at
org.h2.result.LocalResult.cloneLobs(LocalResult.java:273) at
org.h2.result.LocalResult.addRow(LocalResult.java:298) at
org.h2.command.dml.Select.queryFlat(Select.java:523) at
org.h2.command.dml.Select.queryWithoutCache(Select.java:625) at
org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114) at
org.h2.command.dml.Query.query(Query.java:371) at
org.h2.command.dml.Query.query(Query.java:333) at
org.h2.command.CommandContainer.query(CommandContainer.java:113) at
org.h2.command.Command.executeQuery(Command.java:201)

Everytime this issue occurs, we are currently taking backup of db.mv.db
file and deleting it followed by service restart. Is there any fix for this
issue ?

The service is SpringBoot based and running on OCP and docker container has
no memory or CPU spikes when this happens.

~Ayush

Post by Stuart McMillan
Thanks Mike,
I'll have a look at this case and see what I can think of to fix it.
Thanks for taking the time to report it!
stumc

Post by Mike Goodwin
Yes, I did try with the head (I assume master) revision at the time -
7099 I think.

Post by Noel Grandin
Can you try a master build, we have fixed various WITH problems recently
--
You received this message because you are subscribed to the Google
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send
<javascript:>.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups
"H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an
<javascript:>.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Yannick Tailliez]

Hi,

In my case, I do :

URL :
jdbc:h2:tcp://127.0.0.1:9092/database/test;MODE=MSSQLServer;MVCC=TRUE;DEFAULT_LOCK_TIMEOUT=5000

CREATE TABLE my_tree (
id INTEGER,
parent_fk INTEGER
);

INSERT INTO my_tree ( id, parent_fk) VALUES ( 1, NULL );
INSERT INTO my_tree ( id, parent_fk) VALUES ( 11, 1 );
INSERT INTO my_tree ( id, parent_fk) VALUES ( 111, 11 );
INSERT INTO my_tree ( id, parent_fk) VALUES ( 12, 1 );
INSERT INTO my_tree ( id, parent_fk) VALUES ( 121, 12 );

CREATE OR REPLACE VIEW v_my_tree AS
WITH RECURSIVE tree_cte (sub_tree_root_id, tree_level, parent_fk, child_fk)
AS (
SELECT mt.ID AS sub_tree_root_id, CAST(0 AS INT) AS tree_level,
mt.parent_fk, mt.id
FROM my_tree mt
UNION ALL
SELECT sub_tree_root_id, mtc.tree_level + 1 AS tree_level,
mtc.parent_fk, mt.id
FROM my_tree mt
INNER JOIN tree_cte mtc ON mtc.child_fk = mt.parent_fk
)
SELECT sub_tree_root_id, tree_level, parent_fk, child_fk FROM tree_cte;

First time, the CREATE VIEW work well.

A second call return to me a NullPointerException :

Error: General error: "java.lang.NullPointerException"; SQL statement:
CREATE OR REPLACE VIEW v_my_tree [...] [50000-196]
SQLState: HY000
ErrorCode: 50000


And a third (and all following) call return to me :

Error: Timeout trying to lock table "SYS"; SQL statement:
CREATE OR REPLACE VIEW v_my_tree [...] [50200-196]
SQLState: HYT00
ErrorCode: 50200

[Stuart McMillan]

Hi Yannick,

You are testing a use case of WITH-CTE's that I never thought about.

Under the hood, the CTE's are implemented as temporary session views which
get removed after the initial WITH expression has been used (used once, I
believe). So for this implementation's use in your scenario is almost never
going to work. :-(

You need an implementation where WITH CTE's can be are expected to

i) persist as part of a permanent view (maybe the WITH statement CTE's can
dynamically re-create themselves when needed OR be scoped to live longer
but not so as to allow their names to collide with other user's table or
view objects.)
ii) be repeatably invoked every time the view is used (not just once)

This will take some time to think about - but I am very appreciative that I
have your test case to focus my thoughts on.
Thanks for taking the time to report it!

stumc

Hi,
URL : jdbc:h2:tcp://127.0.0.1:9092/database/test;MODE=
MSSQLServer;MVCC=TRUE;DEFAULT_LOCK_TIMEOUT=5000
CREATE TABLE my_tree (
id INTEGER,
parent_fk INTEGER
);
INSERT INTO my_tree ( id, parent_fk) VALUES ( 1, NULL );
INSERT INTO my_tree ( id, parent_fk) VALUES ( 11, 1 );
INSERT INTO my_tree ( id, parent_fk) VALUES ( 111, 11 );
INSERT INTO my_tree ( id, parent_fk) VALUES ( 12, 1 );
INSERT INTO my_tree ( id, parent_fk) VALUES ( 121, 12 );
CREATE OR REPLACE VIEW v_my_tree AS
WITH RECURSIVE tree_cte (sub_tree_root_id, tree_level, parent_fk,
child_fk) AS (
SELECT mt.ID AS sub_tree_root_id, CAST(0 AS INT) AS tree_level,
mt.parent_fk, mt.id
FROM my_tree mt
UNION ALL
SELECT sub_tree_root_id, mtc.tree_level + 1 AS tree_level,
mtc.parent_fk, mt.id
FROM my_tree mt
INNER JOIN tree_cte mtc ON mtc.child_fk = mt.parent_fk
)
SELECT sub_tree_root_id, tree_level, parent_fk, child_fk FROM tree_cte;
First time, the CREATE VIEW work well.
CREATE OR REPLACE VIEW v_my_tree [...] [50000-196]
SQLState: HY000
ErrorCode: 50000
CREATE OR REPLACE VIEW v_my_tree [...] [50200-196]
SQLState: HYT00
ErrorCode: 50200
--
You received this message because you are subscribed to the Google Groups
"H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.