JDBCDataStore: Unique visitor not always compatible with sortBy
Description
In GeoTools it’s entirely possible to build a feature collection with a query sorting on a few properties, say p1, p2 and p2. The collection can then accept a UniqueVisitor on p1 alone, and that’s also fine, logically, the data will be fetched in the desired order, and given in such order to the visitor to process. The visitor will return the unique list in whatever order it wants, as there is no advertised ordering feature.
The above works fine in all stores, but in JDBCDataStore, which will utter a:
Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
Position: 302
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
The issue is, in SQL one can use distinct and ORDER BY toghether only if they are working on the same columns, but it’s not possible to distinct on p1 while ordering on p2 and p3 too.
The JDBCDataStore is tasked to optimize the visit in such a way that it mimicks the in-memory operation. To do so, for unique, it should rebuild the GeoTools Query without any sorting, before encoding it in SQL along with the distinct.
In GeoTools it’s entirely possible to build a feature collection with a query sorting on a few properties, say p1, p2 and p2. The collection can then accept a UniqueVisitor on p1 alone, and that’s also fine, logically, the data will be fetched in the desired order, and given in such order to the visitor to process. The visitor will return the unique list in whatever order it wants, as there is no advertised ordering feature.
The above works fine in all stores, but in JDBCDataStore, which will utter a:
Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list Position: 302 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
The issue is, in SQL one can use distinct and ORDER BY toghether only if they are working on the same columns, but it’s not possible to distinct on p1 while ordering on p2 and p3 too.
The JDBCDataStore is tasked to optimize the visit in such a way that it mimicks the in-memory operation. To do so, for unique, it should rebuild the GeoTools Query without any sorting, before encoding it in SQL along with the distinct.