Re: [Postgres-xc-developers] Fix for bug 3604199: GROUP BY clause with primary key

[Amit K. <ami...@en...>]

On 7 June 2013 15:28, Ashutosh Bapat <ash...@en...>wrote:

> Hi All,
> If there is a primary key column in the GROUP BY clause, every group will
> have only one row. In this case, even if there ungrouped columns in the
> query, PG 9.2 doesn't raise any error. But this behaviour is only allowed
> if the functional dependence of the ungrouped columns is not needed to be
> inferred from subqueries (if there are any). I tried to provide a patch to
> extend the functional dependency to subqueries, but community is not
> willing to have this extension. See mail thread "Functional dependencies
> and GROUP BY - for subqueries" for more details.
>
>
Instead of a new field Query->has_func_grouping, can we use
Query->constraintDeps field to check the presence of functional
dependencies in the query ? From looking at the code, it seems this field
gives the same information that you need.

Apart from the scenario of target list containing ungrouped columns, the
other scenario is that of  HAVING clause containing ungrouped columns. In
the latter  case, the remote query contains a corresponding WHERE clause,
so the remote query does not fail with an error unlike how it fails when
target list contains ungrouped columns. But, with your fix even the query
with HAVING clause is prevented from a reduced group-by query. I think that
might be deemed ok, on the basis of the theory that an aggregate query that
generates single row groups is anyway not going to benefit from the
reduction. So I am fine with it, but wanted to mention it just in case you
are not aware that the fix also affects HAVING clause in the way mentioned
above.

So, in XC, we have to disable GROUP BY optimization for such queries, since
> the way queries are constructed in XC (to be pushed to the datanodes), they
> end up with subqueries.
>
> Here's the patch for the same. This fixes bug 3604199 and regression test
> functional_deps.
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Postgres Database Company
>
>
> ------------------------------------------------------------------------------
> How ServiceNow helps IT people transform IT departments:
> 1. A cloud service to automate IT design, transition and operations
> 2. Dashboards that offer high-level views of enterprise services
> 3. A single system of record for all IT processes
> http://p.sf.net/sfu/servicenow-d2d-j
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>