ADQL ORDER BY DISTANCE fails

[almicol]

I get an error when executing the following query:

SELECT top 10 centroid(s_region), distance(centroid(s_region), point('', 187.48, 2.05))
FROM ivoa.ObsCore
ORDER BY distance(centroid(s_region), point('', 187.48, 2.05))

Without the ORDER BY it works smoothly.

Cheers,
Alberto

[pdowler]

At least with postgresql, you can make it work by declaring an alias in the select list and using the alias in the order by....

However, that query has to do a table scan into a temp table so it will always be slow; you can make it faster by setting a limit on the distance that will also use an index to find candidates, say regions within ~5 deg, e.g.:

WHERE 1=INTERSECTS(s_region, circle('', 187.48, 2.05, 5.0))

Techncially, ADQL permits a function call in the order by clause so this is a bug. It could be made to work by detecting the equivalent item in the select list and order by and injecting the alias usage, but I'l not sure I would want to make it work. I don't see a way to make this use a spatial index though... Are you using the library in a TAP service? Which back-end db? How would you optimise query execution?