Hi, I saw it, but didn't commit it yet.
Regards, Thomas On Mon, Apr 27, 2015 at 1:38 PM, John McMahon <[email protected]> wrote: > Hi Thomas, > > I see you implemented some changes to handle the NULL geometry data. > I was testing it last week and came across a few minor issues with the > indexes. > I fixed them and I created a push request for the change on Friday. > If you have time would you be able to take a look at it? > > Thanks > John > > > > On 17 April 2015 at 12:11, Thomas Mueller <[email protected]> > wrote: > >> Hi, >> >> That's a good question, I don't know what the best solution would be. >> >> Regards, >> Thomas >> >> >> On Friday, April 17, 2015, John McMahon <[email protected]> wrote: >> >>> 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. >>> >> -- >> 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.
