Omitted results in queries with LIMIT and order by

[beacomni]

I've tried queries against the aws api gateway tables, ordering by name, with LIMIT. They seem to omit many entries. This is not what I would expect.

For example, with this query:

select *
from aws.aws_api_gateway_rest_api api
order by api.name
LIMIT 5

I get back this result, starting with apis whose names begin with "D*":

Contrast to the console, where there are many entries before we get to apis whose names begin with "D*":

Any idea about the explanation for this? I've tried looking for the underlying GO api call parameters in the INFO debug log in ~/.steampipe/logs, but there's not much helpful there.

Is it expected that the order by is applied after some subset of results is first returned with LIMIT? I would expect the order by to be applied first?

[e-gineer]

The results should behave as you'd expect ... run the query, order the result, then limit it to the first 5. Behind the scenes, the plugin foreign data wrapper actually executes the query and returns the full set of table results to postgres. Postgres then does the order by and limit in it's own space. (FYI, this is why the cache is still used even if you change the order by.)

Questions:

• Had your data changed between queries? Was the cache in play?
• What client are you using for the queries / screenshot above?
• Do you see the same issue in the steampipe CLI?
• Does the same problem happen for other tables, or just the gateway rest api?

[beacomni]

Thanks for the reply. I feel a bit silly, but it appears the issue may be down to something in postgres about the collation on text data type. Still looking for where in the documentation, but upon looking at the full results, they're ordered in this way because uppercase "D" comes before lowercase "a". The official word may be in some combination of:

https://www.postgresql.org/docs/9.5/queries-order.html
https://www.postgresql.org/docs/9.5/indexes-opclass.html
https://www.postgresql.org/docs/9.1/functions-comparison.html

[e-gineer]

oh! interesting! Thanks for the update.