Hi Thomas, Thanks for the update.
Would it help to use an extended SpatialKey say called NullGeometrySpatialKey whose extents have the xlo and ylo greater than xhi and yhi. That way when the index is read you can tell, that because xlo and ylo are greater than xhi and yhi, that it is a null geometry entry. Would that prevent the envelope expanding? Just a thought. Thanks again John On 16 April 2015 at 12:07, Thomas Mueller <[email protected]> wrote: > Hi, > > You are right, this is not yet working. I will committed a fix for it > shortly. However I'm not sure performance is good with many null entries (I > didn't test it). I think we need to implement non-covering indexes (indexes > where null entries are not stored). Right now, only covering indexes are > implemented. > > Regards, > Thomas > > > On Thursday, April 16, 2015, John McMahon <[email protected]> > wrote: > >> I have another problem related to the NULL geometry problem. >> >> >> Test cases are below and tested against 1.4.187. >> >> If I add rows in a spatial table with null geometry then try to delete >> the rows, the second and subsequent deletes throw errors. >> >> Updates seem to be okay but below is a test for that too. >> >> I am investigating other problems but wanted to post these while I work >> on the others. >> >> Thanks >> John >> >> >> *TEST CASES* >> private void testNullableGeometryDelete() throws SQLException { >> deleteDb("spatial"); >> Connection conn = getConnection(url); >> Statement stat = conn.createStatement(); >> >> stat.execute("create memory table test" >> + "(id int primary key, the_geom geometry)"); >> stat.execute("create spatial index on test(the_geom)"); >> stat.execute("insert into test values(1, null)"); >> stat.execute("insert into test values(2, null)"); >> stat.execute("insert into test values(3, null)"); >> ResultSet rs = stat.executeQuery("select * from test"); >> assertTrue(rs.next()); >> assertEquals(1, rs.getInt(1)); >> assertNull(rs.getObject(2)); >> >> stat.execute("delete from test where id = 1"); >> stat.execute("delete from test where id = 2"); >> stat.execute("delete from test where id = 3"); >> >> stat.execute("insert into test values(4, null)"); >> stat.execute("insert into test values(5, null)"); >> stat.execute("insert into test values(6, null)"); >> >> stat.execute("delete from test where id = 4"); >> stat.execute("delete from test where id = 5"); >> stat.execute("delete from test where id = 6"); >> >> >> conn.close(); >> >> deleteDb("spatial"); >> } >> >> private void testNullableGeometryUpdate() throws SQLException { >> deleteDb("spatial"); >> Connection conn = getConnection(url); >> Statement stat = conn.createStatement(); >> >> stat.execute("create memory table test" >> + "(id int primary key, the_geom geometry, description >> varchar2(32))"); >> stat.execute("create spatial index on test(the_geom)"); >> stat.execute("insert into test values(1, null, null)"); >> stat.execute("insert into test values(2, null, null)"); >> stat.execute("insert into test values(3, null, null)"); >> ResultSet rs = stat.executeQuery("select * from test"); >> assertTrue(rs.next()); >> assertEquals(1, rs.getInt(1)); >> assertNull(rs.getObject(2)); >> >> stat.execute("update test set description='DESCRIPTION' where id >> = 1"); >> stat.execute("update test set description='DESCRIPTION' where id >> = 2"); >> stat.execute("update test set description='DESCRIPTION' where id >> = 3"); >> >> conn.close(); >> >> deleteDb("spatial"); >> } >> >> >> *ERROR* >> delete from test where id = 2 [90112-187] >> 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.mvstore.db.MVSpatialIndex.remove(MVSpatialIndex.java:174) >> at org.h2.mvstore.db.MVTable.removeRow(MVTable.java:611) >> at org.h2.command.dml.Delete.update(Delete.java:94) >> at org.h2.command.CommandContainer.update(CommandContainer.java:78) >> at org.h2.command.Command.executeUpdate(Command.java:254) >> at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:184) >> at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158) >> at >> org.h2.test.db.TestSpatial.testNullableGeometryDelete(TestSpatial.java:959) >> at org.h2.test.db.TestSpatial.testSpatial(TestSpatial.java:94) >> at org.h2.test.db.TestSpatial.test(TestSpatial.java:64) >> at org.h2.test.db.TestSpatial.main(TestSpatial.java:50) >> >> >> >> On Wednesday, 15 April 2015 15:57:26 UTC-4, John McMahon wrote: >>> >>> Hi Thomas >>> >>> I will verify against 187. >>> >>> Thanks >>> John >>> On 15 Apr 2015 20:32, "Thomas Mueller" <[email protected]> >>> wrote: >>> >>>> Hi, >>>> >>>> It has been fixed in version 1.4.187. According to the error code >>>> [50000-186], you are using version 1.4.186. >>>> >>>> Regards, >>>> Thomas >>>> >>>> >>>> On Wednesday, April 15, 2015, John McMahon <[email protected]> >>>> wrote: >>>> >>>>> Hi Thomas, >>>>> >>>>> I'm afraid that there is still a problem with this fix. >>>>> >>>>> The testcase passes alright, but if you re-open the H2 database using >>>>> the H2 console the following error is observed. >>>>> >>>>> org.h2.jdbc.JdbcSQLException: General error: >>>>> "java.nio.BufferUnderflowException" >>>>> [50000-186] >>>>> at org.h2.message.DbException.getJdbcSQLException( >>>>> DbException.java:345 >>>>> <http://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=345&build=186>) >>>>> >>>>> at org.h2.message.DbException.get(DbException.java:168 >>>>> <http://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=168&build=186>) >>>>> >>>>> at org.h2.message.DbException.convert(DbException.java:295 >>>>> <http://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=295&build=186>) >>>>> >>>>> at org.h2.engine.Database.openDatabase(Database.java:297 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/Database.java&line=297&build=186>) >>>>> >>>>> at org.h2.engine.Database.<init>(Database.java:260 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/Database.java&line=260&build=186>) >>>>> >>>>> at org.h2.engine.Engine.openSession(Engine.java:60 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/Engine.java&line=60&build=186>) >>>>> >>>>> at org.h2.engine.Engine.openSession(Engine.java:167 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/Engine.java&line=167&build=186>) >>>>> >>>>> at org.h2.engine.Engine.createSessionAndValidate(Engine.java:145 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/Engine.java&line=145&build=186>) >>>>> >>>>> at org.h2.engine.Engine.createSession(Engine.java:128 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/Engine.java&line=128&build=186>) >>>>> >>>>> at org.h2.engine.Engine.createSession(Engine.java:26 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/Engine.java&line=26&build=186>) >>>>> >>>>> at org.h2.engine.SessionRemote.connectEmbeddedOrServer( >>>>> SessionRemote.java:347 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/SessionRemote.java&line=347&build=186>) >>>>> >>>>> at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:108 >>>>> <http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcConnection.java&line=108&build=186>) >>>>> >>>>> at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:92 >>>>> <http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcConnection.java&line=92&build=186>) >>>>> >>>>> at org.h2.Driver.connect(Driver.java:72 >>>>> <http://h2database.com/html/source.html?file=org/h2/Driver.java&line=72&build=186>) >>>>> >>>>> at org.h2.server.web.WebServer.getConnection(WebServer.java:750 >>>>> <http://h2database.com/html/source.html?file=org/h2/server/web/WebServer.java&line=750&build=186>) >>>>> >>>>> at org.h2.server.web.WebApp.login(WebApp.java:957 >>>>> <http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=957&build=186>) >>>>> >>>>> at org.h2.server.web.WebApp.process(WebApp.java:211 >>>>> <http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=211&build=186>) >>>>> >>>>> at org.h2.server.web.WebApp.processRequest(WebApp.java:170 >>>>> <http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=170&build=186>) >>>>> >>>>> at org.h2.server.web.WebThread.process(WebThread.java:137 >>>>> <http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=137&build=186>) >>>>> >>>>> at org.h2.server.web.WebThread.run(WebThread.java:93 >>>>> <http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=93&build=186>) >>>>> >>>>> at java.lang.Thread.run(Unknown Source) >>>>> Caused by: java.nio.BufferUnderflowException >>>>> at java.nio.Buffer.nextGetIndex(Unknown Source) >>>>> at java.nio.HeapByteBuffer.getFloat(Unknown Source) >>>>> at org.h2.mvstore.rtree.SpatialDataType.read( >>>>> SpatialDataType.java:101 >>>>> <http://h2database.com/html/source.html?file=org/h2/mvstore/rtree/SpatialDataType.java&line=101&build=186>) >>>>> >>>>> at org.h2.mvstore.rtree.SpatialDataType.read( >>>>> SpatialDataType.java:61 >>>>> <http://h2database.com/html/source.html?file=org/h2/mvstore/rtree/SpatialDataType.java&line=61&build=186>) >>>>> >>>>> at org.h2.mvstore.Page.read(Page.java:701 >>>>> <http://h2database.com/html/source.html?file=org/h2/mvstore/Page.java&line=701&build=186>) >>>>> >>>>> at org.h2.mvstore.Page.read(Page.java:196 >>>>> <http://h2database.com/html/source.html?file=org/h2/mvstore/Page.java&line=196&build=186>) >>>>> >>>>> at org.h2.mvstore.MVStore.readPage(MVStore.java:1846 >>>>> <http://h2database.com/html/source.html?file=org/h2/mvstore/MVStore.java&line=1846&build=186>) >>>>> >>>>> at org.h2.mvstore.MVMap.readPage(MVMap.java:736 >>>>> <http://h2database.com/html/source.html?file=org/h2/mvstore/MVMap.java&line=736&build=186>) >>>>> >>>>> at org.h2.mvstore.MVMap.setRootPos(MVMap.java:746 >>>>> <http://h2database.com/html/source.html?file=org/h2/mvstore/MVMap.java&line=746&build=186>) >>>>> >>>>> at org.h2.mvstore.MVStore.openMap(MVStore.java:469 >>>>> <http://h2database.com/html/source.html?file=org/h2/mvstore/MVStore.java&line=469&build=186>) >>>>> >>>>> at org.h2.mvstore.db.MVSpatialIndex.<init>(MVSpatialIndex.java:103 >>>>> <http://h2database.com/html/source.html?file=org/h2/mvstore/db/MVSpatialIndex.java&line=103&build=186>) >>>>> >>>>> at org.h2.mvstore.db.MVTable.addIndex(MVTable.java:433 >>>>> <http://h2database.com/html/source.html?file=org/h2/mvstore/db/MVTable.java&line=433&build=186>) >>>>> >>>>> at org.h2.command.ddl.CreateIndex.update(CreateIndex.java:94 >>>>> <http://h2database.com/html/source.html?file=org/h2/command/ddl/CreateIndex.java&line=94&build=186>) >>>>> >>>>> at org.h2.engine.MetaRecord.execute(MetaRecord.java:58 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/MetaRecord.java&line=58&build=186>) >>>>> >>>>> at org.h2.engine.Database.open(Database.java:732 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/Database.java&line=732&build=186>) >>>>> >>>>> at org.h2.engine.Database.openDatabase(Database.java:266 >>>>> <http://h2database.com/html/source.html?file=org/h2/engine/Database.java&line=266&build=186>) >>>>> >>>>> ... 17 more >>>>> >>>>> >>>>> Thanks >>>>> John >>>>> >>>>> >>>>> On 10 April 2015 at 10:28, Thomas Mueller < >>>>> [email protected]> wrote: >>>>> >>>>>> Hi, >>>>>> >>>>>> Ah, sorry, I thought it's the same problem... You are right, it's a >>>>>> second issue. I have a patch now: >>>>>> >>>>>> Index: src/main/org/h2/mvstore/rtree/SpatialDataType.java >>>>>> =================================================================== >>>>>> --- src/main/org/h2/mvstore/rtree/SpatialDataType.java (revision >>>>>> 6091) >>>>>> +++ src/main/org/h2/mvstore/rtree/SpatialDataType.java (working copy) >>>>>> @@ -83,6 +83,10 @@ >>>>>> >>>>>> @Override >>>>>> public void write(WriteBuffer buff, Object obj) { >>>>>> + if (obj == null) { >>>>>> + buff.putVarInt(-1); >>>>>> + return; >>>>>> + } >>>>>> SpatialKey k = (SpatialKey) obj; >>>>>> int flags = 0; >>>>>> for (int i = 0; i < dimensions; i++) { >>>>>> @@ -103,6 +107,9 @@ >>>>>> @Override >>>>>> public Object read(ByteBuffer buff) { >>>>>> int flags = DataUtils.readVarInt(buff); >>>>>> + if (flags == -1) { >>>>>> + return null; >>>>>> + } >>>>>> float[] minMax = new float[dimensions * 2]; >>>>>> for (int i = 0; i < dimensions; i++) { >>>>>> float min = buff.getFloat(); >>>>>> Index: src/test/org/h2/test/db/TestSpatial.java >>>>>> =================================================================== >>>>>> --- src/test/org/h2/test/db/TestSpatial.java (revision 6113) >>>>>> +++ src/test/org/h2/test/db/TestSpatial.java (working copy) >>>>>> @@ -924,8 +924,17 @@ >>>>>> assertTrue(rs.next()); >>>>>> assertEquals(1, rs.getInt(1)); >>>>>> assertNull(rs.getObject(2)); >>>>>> - stat.execute("drop table test"); >>>>>> conn.close(); >>>>>> + if (!config.memory) { >>>>>> + conn = getConnection(url); >>>>>> + stat = conn.createStatement(); >>>>>> + rs = stat.executeQuery("select * from test"); >>>>>> + assertTrue(rs.next()); >>>>>> + assertEquals(1, rs.getInt(1)); >>>>>> + assertNull(rs.getObject(2)); >>>>>> + conn.close(); >>>>>> + } >>>>>> + >>>>>> deleteDb("spatial"); >>>>>> } >>>>>> >>>>>> >>>>>> Regards, >>>>>> Thomas >>>>>> >>>>>> >>>>>> On Fri, Apr 10, 2015 at 10:45 AM, Noel Grandin <[email protected] >>>>>> > wrote: >>>>>> >>>>>>> Hi >>>>>>> >>>>>>> I can confirm that this bug is not fixed as of current SVN (r6118) >>>>>>> >>>>>>> H2 test case attached. >>>>>>> >>>>>>> >>>>>>> Regards, Noel >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> 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 email to [email protected]. >>>>>>> To post to this group, send email to [email protected]. >>>>>>> Visit this group at http://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 email to [email protected]. >>>>>> To post to this group, send email to [email protected]. >>>>>> Visit this group at http://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 email to [email protected]. >>>>> To post to this group, send email to [email protected]. >>>>> Visit this group at http://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 email to [email protected]. >>>> To post to this group, send email to [email protected]. >>>> Visit this group at http://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 >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://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 > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://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 email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
