Creating an SQL function that uses a SPATIAL INDEX and NEAR with maxDistance fails (NullPointerException and no results) #6776

danstruthers · 2016-10-03T23:30:14Z

v2.2.11

Operating System

Linux
MacOSX
[+] Windows
Other Unix
Other, name?

Expected behavior and actual behavior

It is expected that I can create a function that returns a set of vertices that are near a particular latitude/longitude pair (with max_dist kilometers), using WHERE [x,y,$spatial] NEAR [$a,$b,{maxDistance, $c}] (where $a, $b, and $c are function parameters).

What I get is no results, and this error in the server log:

$ANSI{green {db=test}} Error on using index GeoLocation.lat_lon in query 'SELECT FROM GeoLocation WHERE [latitude, longitude, $spatial] NEAR [$lat, $lon, {"max
istance": $max_dist}]'. Probably you need to rebuild indexes. Now executing query using cluster scan
java.lang.NullPointerException
        at com.orientechnologies.spatial.engine.OLuceneLegacySpatialIndexEngine.searchIntersect(OLuceneLegacySpatialIndexEngine.java:87)
        at com.orientechnologies.spatial.engine.OLuceneLegacySpatialIndexEngine.legacySearch(OLuceneLegacySpatialIndexEngine.java:73)
        at com.orientechnologies.spatial.engine.OLuceneLegacySpatialIndexEngine.getInTx(OLuceneLegacySpatialIndexEngine.java:140)
        at com.orientechnologies.spatial.engine.OLuceneLegacySpatialIndexEngine.get(OLuceneLegacySpatialIndexEngine.java:149)
        at com.orientechnologies.spatial.engine.OLuceneSpatialIndexEngineDelegate.get(OLuceneSpatialIndexEngineDelegate.java:136)
        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.doGetIndexValue(OAbstractPaginatedStorage.java:1921)
        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.getIndexValue(OAbstractPaginatedStorage.java:1910)
        at com.orientechnologies.lucene.index.OLuceneIndexNotUnique.get(OLuceneIndexNotUnique.java:213)
        at com.orientechnologies.lucene.index.OLuceneIndexNotUnique.get(OLuceneIndexNotUnique.java:45)
        at com.orientechnologies.spatial.operator.OLuceneNearOperator.executeIndexQuery(OLuceneNearOperator.java:131)
        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.searchForIndexes(OCommandExecutorSQLSelect.java:2179)
        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.searchInClasses(OCommandExecutorSQLSelect.java:1001)
        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLResultsetAbstract.assignTarget(OCommandExecutorSQLResultsetAbstract.java:209)
        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.assignTarget(OCommandExecutorSQLSelect.java:530)
        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.executeSearch(OCommandExecutorSQLSelect.java:512)
        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.execute(OCommandExecutorSQLSelect.java:488)
        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLDelegate.execute(OCommandExecutorSQLDelegate.java:74)
        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.executeCommand(OAbstractPaginatedStorage.java:2672)
        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.command(OAbstractPaginatedStorage.java:2618)
        at com.orientechnologies.orient.core.command.OCommandRequestTextAbstract.execute(OCommandRequestTextAbstract.java:69)
        at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeCommand(OCommandExecutorScript.java:484)
        at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeSQLScript(OCommandExecutorScript.java:351)
        at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeSQL(OCommandExecutorScript.java:197)
        at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeInContext(OCommandExecutorScript.java:104)
        at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.execute(OCommandExecutorScript.java:88)
        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.executeCommand(OAbstractPaginatedStorage.java:2672)
        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.command(OAbstractPaginatedStorage.java:2618)
        at com.orientechnologies.orient.core.command.OCommandRequestTextAbstract.execute(OCommandRequestTextAbstract.java:69)
        at com.orientechnologies.orient.core.sql.OSQLScriptEngine.eval(OSQLScriptEngine.java:71)
        at com.orientechnologies.orient.core.command.script.OCommandExecutorFunction.executeInContext(OCommandExecutorFunction.java:100)
        at com.orientechnologies.orient.core.metadata.function.OFunction.executeInContext(OFunction.java:164)
        at com.orientechnologies.orient.core.metadata.function.ODatabaseFunction.execute(ODatabaseFunction.java:43)
        at com.orientechnologies.orient.core.sql.functions.OSQLFunctionRuntime.execute(OSQLFunctionRuntime.java:128)
        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.applyExpand(OCommandExecutorSQLSelect.java:2558)
        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.execute(OCommandExecutorSQLSelect.java:489)
        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLDelegate.execute(OCommandExecutorSQLDelegate.java:74)
        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.executeCommand(OAbstractPaginatedStorage.java:2672)
        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.command(OAbstractPaginatedStorage.java:2618)
        at com.orientechnologies.orient.core.command.OCommandRequestTextAbstract.execute(OCommandRequestTextAbstract.java:69)
        at com.orientechnologies.orient.server.network.protocol.http.command.post.OServerCommandPostCommand.execute(OServerCommandPostCommand.java:106)
        at com.orientechnologies.orient.graph.server.command.OServerCommandPostCommandGraph.execute(OServerCommandPostCommandGraph.java:37)
        at com.orientechnologies.orient.server.network.protocol.http.ONetworkProtocolHttpAbstract.service(ONetworkProtocolHttpAbstract.java:168)
        at com.orientechnologies.orient.server.network.protocol.http.ONetworkProtocolHttpAbstract.execute(ONetworkProtocolHttpAbstract.java:616)
        at com.orientechnologies.common.thread.OSoftThread.run(OSoftThread.java:77)

Steps to reproduce the problem

CREATE CLASS GeoLocation EXTENDS V
CREATE PROPERTY GeoLocation.latitude Double
CREATE PROPERTY GeoLocation.longitude Double
CREATE INDEX GeoLocation.lat_lon ON GeoLocation (latitude, longitude) SPATIAL ENGINE LUCENE
...insert a bunch of GeoLocation data from somewhere (attached, using oetl)
citystate.zip...
CREATE FUNCTION findNearby "SELECT FROM GeoLocation WHERE [latitude, longitude, $spatial] NEAR [$lat, $lon, {maxDistance: $max_dist}]" PARAMETERS [lat,lon,max_dist] LANGUAGE SQL
SELECT EXPAND(findNearby(45.0, -122.0, 300))

The text was updated successfully, but these errors were encountered:

danstruthers · 2016-10-03T23:37:09Z

A workaround function is defined as follows:

CREATE FUNCTION findNearbyWorks "SELECT *, distance($current.latitude, $current.longitude, $LAT, $LON) AS distance FROM GeoLocation WHERE distance($current.latitude, $current.longitude, $LAT, $LON) < $MAXD" PARAMETERS [LAT, LON, MAXD] LANGUAGE SQL

Not sure if that helps or not, but it seems much slower than the equivalent NEAR clause.

The workaround above does not work without the $current prefixes in the distance function, either. A different bug, or is that just documentation that needs updating?

danstruthers · 2016-10-03T23:44:32Z

One more thing, a select query against the same data, like the following, works fine:

SELECT FROM GeoLocation WHERE [latitude, longitude, $spatial] NEAR [45, -122, {maxDistance: 500}]

wolf4ood · 2016-10-04T10:12:41Z

@danstruthers
let me check.

btw i would recommend to use the new Spatial module for 2.2.x

orientdb.com/docs/2.2/Spatial-Index.html

wolf4ood · 2016-10-04T12:51:46Z

@danstruthers
if you want bind parmeters with sql function you should use : instead of $

CREATE FUNCTION findNearby "SELECT FROM GeoLocation WHERE [latitude, longitude, $spatial] NEAR [:lat, :lon, {maxDistance: :max_dist}]" PARAMETERS [lat,lon,max_dist] LANGUAGE SQL

danstruthers · 2016-10-04T15:56:13Z

Using colons instead of $s just gets rid of the exception server side. I still get no results. The $s work in the workaround function, which does return results. I am using the new spatial module for 2.2.11. I thought the NEAR SQL keyword was part of the spatial module. I could try the ST_xxx functions to see if they will work.

wolf4ood · 2016-10-04T15:58:53Z

@danstruthers

this works for me

 @Test
  public void nearInFunctionTest() {

    ODatabaseDocumentTx db = new ODatabaseDocumentTx("memory:nearInFunctionTest");
    db.create();

    try {
      db.command(new OCommandSQL("CREATE CLASS GeoLocation EXTENDS V")).execute();
      db.command(new OCommandSQL("CREATE PROPERTY GeoLocation.latitude Double")).execute();
      db.command(new OCommandSQL("CREATE PROPERTY GeoLocation.longitude Double")).execute();
      db.command(new OCommandSQL("CREATE INDEX GeoLocation.lat_lon ON GeoLocation (latitude, longitude) SPATIAL ENGINE LUCENE"))
          .execute();
      db.command(new OCommandSQL(
          "CREATE FUNCTION findNearby \"SELECT FROM GeoLocation WHERE [latitude, longitude, $spatial] NEAR [:lat, :lon, {maxDistance: :max_dist}]\" PARAMETERS [lat,lon,max_dist] LANGUAGE SQL"))
          .execute();
      db.save(new ODocument("GeoLocation").field("latitude", 44.73378).field("longitude", "-122.151665"));

      List<?> query = db.query(
          new OSQLSynchQuery("SELECT FROM GeoLocation WHERE [latitude, longitude, $spatial] NEAR [45, -122, {maxDistance: 300}]"));

      Assert.equals(1, query.size());

      db.query(new OSQLSynchQuery("SELECT EXPAND(findNearby(45.0, -122.0, 300))"));

      Assert.equals(1, query.size());

    } finally {
      db.drop();
    }
  }

danstruthers · 2016-10-04T16:11:45Z

Sorry, I was testing it inside Studio in the Functions tab (where I just get an empty array as the result set). Inside the Browse tab it work as expected now.

I tried the ":" binding before (after reading some other posts), but only tried to test it using the functions tab in studio (using the execute button at the bottom of the panel). I will not rely on that in the future.

Why does my workaround function work with $ bound variables, but this one does not?

Thank you for your help!

wolf4ood · 2016-10-05T10:06:22Z

@danstruthers

let me check about Studio Function tab. I did not test it.

I guess because they are used in function distance.

wolf4ood · 2016-10-05T10:11:41Z

@danstruthers

i'v found that Studio uses the old functions http api that send the parameters as string. That is why is's not working with studio.
I will fix this issue.

lvca · 2017-01-30T22:02:08Z

@maggiolo00 What's the status of this issue?

wolf4ood · 2017-01-31T09:28:05Z

@lvca i've opened
this orientechnologies/orientdb-studio#445
for parameters in body

I'm closing this since it's an enhancements fo Studio Function Management

wolf4ood added the bug label Oct 4, 2016

wolf4ood self-assigned this Oct 4, 2016

wolf4ood added question and removed bug labels Oct 4, 2016

wolf4ood added the waiting reply label Oct 5, 2016

santo-it added bug studio and removed question waiting reply labels Jan 30, 2017

wolf4ood closed this as completed Jan 31, 2017

wolf4ood added question and removed bug studio labels Jan 31, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Creating an SQL function that uses a SPATIAL INDEX and NEAR with maxDistance fails (NullPointerException and no results) #6776

Creating an SQL function that uses a SPATIAL INDEX and NEAR with maxDistance fails (NullPointerException and no results) #6776

danstruthers commented Oct 3, 2016 •

edited by wolf4ood

Loading

danstruthers commented Oct 3, 2016 •

edited

Loading

danstruthers commented Oct 3, 2016 •

edited

Loading

wolf4ood commented Oct 4, 2016

wolf4ood commented Oct 4, 2016

danstruthers commented Oct 4, 2016

wolf4ood commented Oct 4, 2016

danstruthers commented Oct 4, 2016

wolf4ood commented Oct 5, 2016

wolf4ood commented Oct 5, 2016

lvca commented Jan 30, 2017

wolf4ood commented Jan 31, 2017

Creating an SQL function that uses a SPATIAL INDEX and NEAR with maxDistance fails (NullPointerException and no results) #6776

Creating an SQL function that uses a SPATIAL INDEX and NEAR with maxDistance fails (NullPointerException and no results) #6776

Comments

danstruthers commented Oct 3, 2016 • edited by wolf4ood Loading

Operating System

Expected behavior and actual behavior

Steps to reproduce the problem

danstruthers commented Oct 3, 2016 • edited Loading

danstruthers commented Oct 3, 2016 • edited Loading

wolf4ood commented Oct 4, 2016

wolf4ood commented Oct 4, 2016

danstruthers commented Oct 4, 2016

wolf4ood commented Oct 4, 2016

danstruthers commented Oct 4, 2016

wolf4ood commented Oct 5, 2016

wolf4ood commented Oct 5, 2016

lvca commented Jan 30, 2017

wolf4ood commented Jan 31, 2017

danstruthers commented Oct 3, 2016 •

edited by wolf4ood

Loading

danstruthers commented Oct 3, 2016 •

edited

Loading

danstruthers commented Oct 3, 2016 •

edited

Loading