Pivotal Knowledge Base

Follow

Wrong Results when using sort and limit

Environment

Pivotal Greenplum (GPDB) < 4.2.5.2

Problem

When using sort together with limit in one query, results could be incorrect under certain circumstances.

Cause

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.

Solution

This issue is fixed in GPDB 4.2.5.2. Upgrade to version later than 4.2.5.2.

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.

Comments

Powered by Zendesk