Re: [Postgres-xc-developers] GROUP BY clause with JOIN clause and DISTINCT clause and combination of implicit/explicit table specification

[Masataka S. <pg...@gm...>]

Hi Ashutosh,

Specified groupColIdx doesn't found in tlist.

It seems that groupColIdx is correct and tlist is wrong.
Because the value of ressortgroupref in the target entry(base_tlist) they
want to locate was zero.

The mechanism why it comes to be zero is that when pgxc_rqplan_adjust_tlist
updates base_tlist  using pgxc_build_shippable_tlist, a ressortgroupref in
a target entry is reset if a plan's target list has two equal Vars.

It occurs when SELECT target has two aliases to the same column and each
are referred by GROUP BY clause and ORDER BY clause. I will show a sample
query at next.

postgres-# CREATE TABLE tbl(i INT);
postgres-# SELECT i AS a, i AS b FROM tbl GROUP BY a ORDER BY b;

It needs more analysis, but I'm thinking how about using
locate_grouping_columns with plan's target list and base_tlist instead of
pgxc_locate_grouping_columns.

Regards.


On Thu, Nov 21, 2013 at 1:40 PM, Ashutosh Bapat <
ash...@en...> wrote:

> Hi Masataka,
> To start debugging, you might want to fire this query with debugger
> breaking on errstart() or errfinish() or some error reporting hook where
> you can break. The stack stress should give you a lead as to where the
> error is coming from. It's high chance that it's coming from
> pgxc_locate_grouping_columns(). But anyway, start looking around the
> callers of get_sortgroupref_tle() and check their arguments. Best luck.
>
>
> On Thu, Nov 21, 2013 at 6:51 AM, Masataka Saito <pg...@gm...> wrote:
>
>> Thanks for your information.
>> It seems very helpful.
>>
>> cx=# explain verbose SELECT DISTINCT t2.b FROM t1 JOIN t2 ON t1.id =
>> t2.id GROUP BY b;
>> ERROR:  XX000: ORDER/GROUP BY expression not found in targetlist
>> LOCATION:  get_sortgroupref_tle, tlist.c:251
>>
>> On Thu, Nov 21, 2013 at 9:53 AM, 鈴木 幸市 <ko...@in...> wrote:
>> > Yes, you can do it with SET command as well just for a target statement.
>> >
>> > Regards;
>> > ---
>> > Koichi Suzuki
>> >
>> > 2013/11/21 7:46、Michael Paquier <mic...@gm...> のメール：
>> >
>> >> On Thu, Nov 21, 2013 at 12:32 AM, Masataka Saito <pg...@gm...>
>> wrote:
>> >>> XC can't build a plan.
>> >>>
>> >>> db=# explain verbose SELECT DISTINCT t2.b FROM t1 JOIN t2 ON t1.id =
>> >>> t2.id GROUP BY b;
>> >>> ERROR:  ORDER/GROUP BY expression not found in targetlist
>> >> This is a higher-level bug, planner bug just by looking at this code
>> >> path src/backend/optimizer/util/tlist.c...
>> >>
>> >> Note: Setting up VERBOSITY to verbose in .psqlrc helps grabbing more
>> >> details about the errors that occurred in server like the file name +
>> >> name of this elog/ereport ERROR:
>> >> \set VERBOSITY verbose
>> >> For dev purposes it is a pretty useful default ;)
>> >>
>> >> Regards,
>> >> --
>> >> Michael
>> >>
>> >>
>> ------------------------------------------------------------------------------
>> >> Shape the Mobile Experience: Free Subscription
>> >> Software experts and developers: Be at the forefront of tech
>> innovation.
>> >> Intel(R) Software Adrenaline delivers strategic insight and
>> game-changing
>> >> conversations that shape the rapidly evolving mobile landscape. Sign
>> up now.
>> >>
>> http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk
>> >> _______________________________________________
>> >> Postgres-xc-developers mailing list
>> >> Pos...@li...
>> >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>> >>
>> >
>>
>>
>> ------------------------------------------------------------------------------
>> Shape the Mobile Experience: Free Subscription
>> Software experts and developers: Be at the forefront of tech innovation.
>> Intel(R) Software Adrenaline delivers strategic insight and game-changing
>> conversations that shape the rapidly evolving mobile landscape. Sign up
>> now.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>