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

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

On 17 June 2013 19:16, Ashutosh Bapat <ash...@en...>wrote:

>
>
>
> On Mon, Jun 17, 2013 at 4:36 PM, Amit Khandekar <
> ami...@en...> wrote:
>
>>
>>
>> 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.
>>
>>
> That's a very good suggestion. Here's updated patch.
>

Ok. This looks all good for commit.


>
>
>> 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.
>>
>>
> Yes, I am aware of this fact.
>
>
>>  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
>>>
>>>
>>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Postgres Database Company
>