In NEARBY, the LIMIT is applied before WHERE clause, not after

[rshura]

DROP test
SET test 1 FIELD foo 10 OBJECT '{"type":"Point","coordinates":[-122.463680031,37.719409964]}'
SET test 2 FIELD foo 20 OBJECT '{"type":"Point","coordinates":[-122,37]}'
NEARBY LIMIT 1 WHERE foo 15 25 POINT 37.7 -122.4
{"ok":true,"fields":["foo"],"objects":[],"count":0,"cursor":0,"elapsed":"23.32µs"}

The NEARBY returns nothing, although there's an object 2 that satisfies the WHERE clause. It seems that the 1 closest object is first found, and then the WHERE clause is applied, producing empty result list.

This is counter-intuitive and unlike SQL, where LIMIT is applied at the end, after all WHERE clauses and just before returning the results. Is this behavior by design, or is this a bug?

[tidwall]

LIMIT/CURSOR are applied at the geo index level. WHERE works as a filter on those results prior to being sent to the client. I agree that this is counter-intuitive, especially when coming from a Postgres/MySQL where LIMIT/OFFSET are applied after the WHERE clause.

All pageable commands, such as NEARBY/INTERSECTS/WITHIN/SCAN/SEARCH are designed to work this way. I think this behavior should be changed.

I'll investigate further and keep you posted.

Thanks

[rshura]

Yes, as it exists now, the WHERE clause is not very useful -- I could do the same filtering myself if I first ask for limit and then iterate over a small number of results.

As a workaround for "getting K closest items with the correct field values", I'd have to get e.g. get a thousand results with LIMIT 1000 then see if enough survived the filtering, and if not then repeat with larger limit etc.

If there was a way to first "only consider items matching WHERE clause" and then "select KNN from those" it would be a super-useful feature. Thanks for considering this!

Alex

[tidwall]

I just pushed an update that applies the LIMIT after the WHERE. Please let me know if you run into any issues. Thanks.

[rshura]

Awesome, thanks!

I was trying to build the current git version 3fae3f70 but got this error (go 1.6 on ubuntu):

$ make
# github.com/tidwall/tile38/controller/endpoint
controller/endpoint/http.go:29: unknown http.Transport field 'IdleConnTimeout' in struct literal
make: *** [all] Error 2

Do you think this is a problem with my setup?

[rshura]

It worked with go 1.8, maybe you need to update the build script to require that. Currently it asks for go > 1.5.

[tidwall]

Nice find. The build script now requires 1.7 or higher for 14cc6ce.