I came across a query that returned incorrect results and I traced it down to being caused by duplicate unique key values in an inheritance table. As a simple example, consider
create table p (a int primary key, b int); create table c () inherits (p); insert into p select 1, 1; insert into c select 1, 2; select a, b from p; a | b ---+--- 1 | 1 1 | 2 (2 rows) explain (verbose, costs off) select a, b from p group by a; QUERY PLAN -------------------------------------- HashAggregate Output: p.a, p.b Group Key: p.a -> Append -> Seq Scan on public.p p_1 Output: p_1.a, p_1.b -> Seq Scan on public.c p_2 Output: p_2.a, p_2.b (8 rows) The parser considers 'p.b' functionally dependent on the group by column 'p.a' because 'p.a' is identified as the primary key for table 'p'. However, this causes confusion for the executor when determining which 'p.b' value should be returned for each group. In my case, I observed that sorted and hashed aggregation produce different results for the same query. Reading the doc, it seems that this is a documented limitation of the inheritance feature that we would have duplicate unique key values in inheritance tables. Even adding a unique constraint to the children does not prevent duplication compared to the parent. As a workaround for this issue, I'm considering whether we can skip checking functional dependency on primary keys for inheritance parents, given that we cannot guarantee uniqueness on the keys in this case. Maybe something like below. @@ -1421,7 +1427,9 @@ check_ungrouped_columns_walker(Node *node, Assert(var->varno > 0 && (int) var->varno <= list_length(context->pstate->p_rtable)); rte = rt_fetch(var->varno, context->pstate->p_rtable); - if (rte->rtekind == RTE_RELATION) + if (rte->rtekind == RTE_RELATION && + !(rte->relkind == RELKIND_RELATION && + rte->inh && has_subclass(rte->relid))) { if (check_functional_grouping(rte->relid, Any thoughts? Thanks Richard