{"action":"created","issue":{"url":"https://api.github.com/repos/orientechnologies/orientdb/issues/6776","repository_url":"https://api.github.com/repos/orientechnologies/orientdb","labels_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/6776/labels{/name}","comments_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/6776/comments","events_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/6776/events","html_url":"https://github.com/orientechnologies/orientdb/issues/6776","id":180772748,"number":6776,"title":"Creating an SQL function that uses a SPATIAL INDEX and NEAR with maxDistance fails (NullPointerException and no results)","user":{"login":"danstruthers","id":18471272,"avatar_url":"https://avatars.githubusercontent.com/u/18471272?v=3","gravatar_id":"","url":"https://api.github.com/users/danstruthers","html_url":"https://github.com/danstruthers","followers_url":"https://api.github.com/users/danstruthers/followers","following_url":"https://api.github.com/users/danstruthers/following{/other_user}","gists_url":"https://api.github.com/users/danstruthers/gists{/gist_id}","starred_url":"https://api.github.com/users/danstruthers/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/danstruthers/subscriptions","organizations_url":"https://api.github.com/users/danstruthers/orgs","repos_url":"https://api.github.com/users/danstruthers/repos","events_url":"https://api.github.com/users/danstruthers/events{/privacy}","received_events_url":"https://api.github.com/users/danstruthers/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-10-03T23:30:14Z","updated_at":"2016-10-03T23:37:10Z","closed_at":null,"body":"v2.2.11\r\n## Operating System\r\n- [ ] Linux\r\n- [ ] MacOSX\r\n- [+] Windows\r\n- [ ] Other Unix\r\n- [ ] Other, name? \r\n\r\n## Expected behavior and actual behavior\r\nIt 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).\r\n\r\nWhat I get is no results, and this error in the server log:\r\n$ANSI{green {db=test}} Error on using index GeoLocation.lat_lon in query 'SELECT FROM GeoLocation WHERE [latitude, longitude, $spatial] NEAR [$lat, $lon, {\"max\r\nistance\": $max_dist}]'. Probably you need to rebuild indexes. Now executing query using cluster scan\r\njava.lang.NullPointerException\r\n        at com.orientechnologies.spatial.engine.OLuceneLegacySpatialIndexEngine.searchIntersect(OLuceneLegacySpatialIndexEngine.java:87)\r\n        at com.orientechnologies.spatial.engine.OLuceneLegacySpatialIndexEngine.legacySearch(OLuceneLegacySpatialIndexEngine.java:73)\r\n        at com.orientechnologies.spatial.engine.OLuceneLegacySpatialIndexEngine.getInTx(OLuceneLegacySpatialIndexEngine.java:140)\r\n        at com.orientechnologies.spatial.engine.OLuceneLegacySpatialIndexEngine.get(OLuceneLegacySpatialIndexEngine.java:149)\r\n        at com.orientechnologies.spatial.engine.OLuceneSpatialIndexEngineDelegate.get(OLuceneSpatialIndexEngineDelegate.java:136)\r\n        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.doGetIndexValue(OAbstractPaginatedStorage.java:1921)\r\n        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.getIndexValue(OAbstractPaginatedStorage.java:1910)\r\n        at com.orientechnologies.lucene.index.OLuceneIndexNotUnique.get(OLuceneIndexNotUnique.java:213)\r\n        at com.orientechnologies.lucene.index.OLuceneIndexNotUnique.get(OLuceneIndexNotUnique.java:45)\r\n        at com.orientechnologies.spatial.operator.OLuceneNearOperator.executeIndexQuery(OLuceneNearOperator.java:131)\r\n        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.searchForIndexes(OCommandExecutorSQLSelect.java:2179)\r\n        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.searchInClasses(OCommandExecutorSQLSelect.java:1001)\r\n        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLResultsetAbstract.assignTarget(OCommandExecutorSQLResultsetAbstract.java:209)\r\n        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.assignTarget(OCommandExecutorSQLSelect.java:530)\r\n        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.executeSearch(OCommandExecutorSQLSelect.java:512)\r\n        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.execute(OCommandExecutorSQLSelect.java:488)\r\n        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLDelegate.execute(OCommandExecutorSQLDelegate.java:74)\r\n        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.executeCommand(OAbstractPaginatedStorage.java:2672)\r\n        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.command(OAbstractPaginatedStorage.java:2618)\r\n        at com.orientechnologies.orient.core.command.OCommandRequestTextAbstract.execute(OCommandRequestTextAbstract.java:69)\r\n        at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeCommand(OCommandExecutorScript.java:484)\r\n        at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeSQLScript(OCommandExecutorScript.java:351)\r\n        at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeSQL(OCommandExecutorScript.java:197)\r\n        at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.executeInContext(OCommandExecutorScript.java:104)\r\n        at com.orientechnologies.orient.core.command.script.OCommandExecutorScript.execute(OCommandExecutorScript.java:88)\r\n        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.executeCommand(OAbstractPaginatedStorage.java:2672)\r\n        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.command(OAbstractPaginatedStorage.java:2618)\r\n        at com.orientechnologies.orient.core.command.OCommandRequestTextAbstract.execute(OCommandRequestTextAbstract.java:69)\r\n        at com.orientechnologies.orient.core.sql.OSQLScriptEngine.eval(OSQLScriptEngine.java:71)\r\n        at com.orientechnologies.orient.core.command.script.OCommandExecutorFunction.executeInContext(OCommandExecutorFunction.java:100)\r\n        at com.orientechnologies.orient.core.metadata.function.OFunction.executeInContext(OFunction.java:164)\r\n        at com.orientechnologies.orient.core.metadata.function.ODatabaseFunction.execute(ODatabaseFunction.java:43)\r\n        at com.orientechnologies.orient.core.sql.functions.OSQLFunctionRuntime.execute(OSQLFunctionRuntime.java:128)\r\n        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.applyExpand(OCommandExecutorSQLSelect.java:2558)\r\n        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.execute(OCommandExecutorSQLSelect.java:489)\r\n        at com.orientechnologies.orient.core.sql.OCommandExecutorSQLDelegate.execute(OCommandExecutorSQLDelegate.java:74)\r\n        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.executeCommand(OAbstractPaginatedStorage.java:2672)\r\n        at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.command(OAbstractPaginatedStorage.java:2618)\r\n        at com.orientechnologies.orient.core.command.OCommandRequestTextAbstract.execute(OCommandRequestTextAbstract.java:69)\r\n        at com.orientechnologies.orient.server.network.protocol.http.command.post.OServerCommandPostCommand.execute(OServerCommandPostCommand.java:106)\r\n        at com.orientechnologies.orient.graph.server.command.OServerCommandPostCommandGraph.execute(OServerCommandPostCommandGraph.java:37)\r\n        at com.orientechnologies.orient.server.network.protocol.http.ONetworkProtocolHttpAbstract.service(ONetworkProtocolHttpAbstract.java:168)\r\n        at com.orientechnologies.orient.server.network.protocol.http.ONetworkProtocolHttpAbstract.execute(ONetworkProtocolHttpAbstract.java:616)\r\n        at com.orientechnologies.common.thread.OSoftThread.run(OSoftThread.java:77)\r\n\r\n## Steps to reproduce the problem\r\n`CREATE CLASS GeoLocation EXTENDS V`\r\n`CREATE PROPERTY GeoLocation.latitude Double`\r\n`CREATE PROPERTY GeoLocation.longitude Double`\r\n`CREATE INDEX GeoLocation.lat_lon ON GeoLocation (latitude, longitude) SPATIAL ENGINE LUCENE`\r\n...insert a bunch of GeoLocation data from somewhere (attached, using oetl)\r\n[citystate.zip](https://github.com/orientechnologies/orientdb/files/507372/citystate.zip)...\r\n`CREATE FUNCTION findNearby \"SELECT FROM GeoLocation WHERE [latitude, longitude, $spatial] NEAR [$lat, $lon, {maxDistance: $max_dist}]\" PARAMETERS [lat,lon,max_dist] LANGUAGE SQL`\r\n`SELECT EXPAND(findNearby(45.0, -122.0, 300))`\r\n"},"comment":{"url":"https://api.github.com/repos/orientechnologies/orientdb/issues/comments/251257459","html_url":"https://github.com/orientechnologies/orientdb/issues/6776#issuecomment-251257459","issue_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/6776","id":251257459,"user":{"login":"danstruthers","id":18471272,"avatar_url":"https://avatars.githubusercontent.com/u/18471272?v=3","gravatar_id":"","url":"https://api.github.com/users/danstruthers","html_url":"https://github.com/danstruthers","followers_url":"https://api.github.com/users/danstruthers/followers","following_url":"https://api.github.com/users/danstruthers/following{/other_user}","gists_url":"https://api.github.com/users/danstruthers/gists{/gist_id}","starred_url":"https://api.github.com/users/danstruthers/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/danstruthers/subscriptions","organizations_url":"https://api.github.com/users/danstruthers/orgs","repos_url":"https://api.github.com/users/danstruthers/repos","events_url":"https://api.github.com/users/danstruthers/events{/privacy}","received_events_url":"https://api.github.com/users/danstruthers/received_events","type":"User","site_admin":false},"created_at":"2016-10-03T23:37:09Z","updated_at":"2016-10-03T23:37:09Z","body":"A workaround function is defined as follows:\r\n\r\n`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`\r\n\r\nNot sure if that helps or not."}}
{"actor":{"display_login":"danstruthers"}}