Pivotal Knowledge Base


Wrong Results when using sort and limit


Pivotal Greenplum (GPDB) and below


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 Upgrade to version later than

Workaround, disable multiple key sort by

set gp_enable_mk_sort=off;

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.


Powered by Zendesk