Query with incorrect sorting when using legacy planner in Greenplum Database (GPDB)

Products

• VMware Tanzu Greenplum

Issue/Introduction

In some cases, when running a query similar to the one below with legacy planner, the result that the query returned might not be able to be sorted correctly. Please refer to the example below:

-- example query (with optimizer=off)
> select <column_A> ,count(*)
from <table_name>
where <column_B> = '2019-06-30'
group by 1 order by <column_A>::date;

-- result:
 column_A  | count
-----------+-------
2020-03-31 | 6673
2019-11-30 | 12354
2019-09-30 | 11564
2019-12-31 | 10626
2020-02-29 | 9418
2019-10-31 | 12346
2019-07-31 | 6853
2019-06-30 | 79
2019-08-31 | 14065
2020-01-31 | 10171
2020-04-30 | 61

Environment

Product Version: 5.25

Resolution

This issue has been fixed GPDB version prior to 5.27.1. Please refer to the release notes below:
https://gpdb.docs.pivotal.io/5270/relnotes/gpdb-5271-release-notes.html

"30548 - Postgres Planner

The Postgres planner returned incorrect results for some queries that join multiple tables and also contain a DISTINCT clause and an aggregate function. The Postgres planner did not perform the multi-phase aggregation correctly. This issue is resolved."