Pivotal Greenplum (GPDB) < 18.104.22.168
When using sort together with limit in one query, results could be incorrect under certain circumstances.
This is a known bug with multiple key sorts. For these sorted limit queries (such as "select * from foo order by bar limit 10"), mksort first collects the data up to the limit and then builds an in-memory heap of size "limit" to sort the data.
If the database runs out of memory before building the in-memory heap (e.g, very large tuples which cannot fit in the allocated memory or very large limit), mksort switches to disk based sorting. Unfortunately, in such cases, limit sort crashes or gives the wrong results.
This issue is fixed in GPDB 22.214.171.124. Upgrade to version later than 126.96.36.199.
Workaround, disable multiple key sort by
Note: This setting could affect the performance of the database when sorting. It is better to set for the specific statements that receive this error.