postgres-xc-developers

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

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

Hello, all.

I'm testing complex SQLs on Postgres-XC 1.1.
And I found bad SQL that XC doesn't accept.

The SQL is  from answer 2 of chapter 17 in SQL puzzle book by Joe Celko.
I simplified it for everyone's reproduction.

db=# CREATE TABLE t1(id INT, a INT);
CREATE TABLE
db=# CREATE TABLE t2(id INT, b INT);
CREATE TABLE
db=# 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 SQL is accepted by PostgreSQL 9.2.4.

Can anyone solve or explain this issue?

Regards.

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

[Ashutosh B. <ash...@en...>]

Can you please provide output of EXPLAIN verbose?


On Thu, Nov 14, 2013 at 1:44 PM, Masataka Saito <pg...@gm...> wrote:

> Hello, all.
>
> I'm testing complex SQLs on Postgres-XC 1.1.
> And I found bad SQL that XC doesn't accept.
>
> The SQL is  from answer 2 of chapter 17 in SQL puzzle book by Joe Celko.
> I simplified it for everyone's reproduction.
>
> db=# CREATE TABLE t1(id INT, a INT);
> CREATE TABLE
> db=# CREATE TABLE t2(id INT, b INT);
> CREATE TABLE
> db=# 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 SQL is accepted by PostgreSQL 9.2.4.
>
> Can anyone solve or explain this issue?
>
> Regards.
>
>
> ------------------------------------------------------------------------------
> DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
> OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
> Free app hosting. Or install the open source package on any LAMP server.
> Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
> http://pubads.g.doubleclick.net/gampad/clk?id=63469471&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

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

[Koichi S. <koi...@gm...>]

I'm afraid the error occurs before the plan is build.
---
Koichi Suzuki


2013/11/19 Ashutosh Bapat <ash...@en...>:
> Can you please provide output of EXPLAIN verbose?
>
>
> On Thu, Nov 14, 2013 at 1:44 PM, Masataka Saito <pg...@gm...> wrote:
>>
>> Hello, all.
>>
>> I'm testing complex SQLs on Postgres-XC 1.1.
>> And I found bad SQL that XC doesn't accept.
>>
>> The SQL is  from answer 2 of chapter 17 in SQL puzzle book by Joe Celko.
>> I simplified it for everyone's reproduction.
>>
>> db=# CREATE TABLE t1(id INT, a INT);
>> CREATE TABLE
>> db=# CREATE TABLE t2(id INT, b INT);
>> CREATE TABLE
>> db=# 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 SQL is accepted by PostgreSQL 9.2.4.
>>
>> Can anyone solve or explain this issue?
>>
>> Regards.
>>
>>
>> ------------------------------------------------------------------------------
>> DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
>> OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
>> Free app hosting. Or install the open source package on any LAMP server.
>> Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=63469471&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
>
> ------------------------------------------------------------------------------
> 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
>

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

[Ashutosh B. <ash...@en...>]

My guess is that it's an error coming from the datanode and thus we need
EXPLAIN output.


On Wed, Nov 20, 2013 at 6:53 AM, Koichi Suzuki <koi...@gm...>wrote:

> I'm afraid the error occurs before the plan is build.
> ---
> Koichi Suzuki
>
>
> 2013/11/19 Ashutosh Bapat <ash...@en...>:
> > Can you please provide output of EXPLAIN verbose?
> >
> >
> > On Thu, Nov 14, 2013 at 1:44 PM, Masataka Saito <pg...@gm...>
> wrote:
> >>
> >> Hello, all.
> >>
> >> I'm testing complex SQLs on Postgres-XC 1.1.
> >> And I found bad SQL that XC doesn't accept.
> >>
> >> The SQL is  from answer 2 of chapter 17 in SQL puzzle book by Joe Celko.
> >> I simplified it for everyone's reproduction.
> >>
> >> db=# CREATE TABLE t1(id INT, a INT);
> >> CREATE TABLE
> >> db=# CREATE TABLE t2(id INT, b INT);
> >> CREATE TABLE
> >> db=# 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 SQL is accepted by PostgreSQL 9.2.4.
> >>
> >> Can anyone solve or explain this issue?
> >>
> >> Regards.
> >>
> >>
> >>
> ------------------------------------------------------------------------------
> >> DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
> >> OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
> >> Free app hosting. Or install the open source package on any LAMP server.
> >> Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
> >>
> >>
> http://pubads.g.doubleclick.net/gampad/clk?id=63469471&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
> >
> >
> ------------------------------------------------------------------------------
> > 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

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

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

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

Regards.

On 11/20/13, Ashutosh Bapat <ash...@en...> wrote:
> My guess is that it's an error coming from the datanode and thus we need
> EXPLAIN output.
>
>
> On Wed, Nov 20, 2013 at 6:53 AM, Koichi Suzuki
> <koi...@gm...>wrote:
>
>> I'm afraid the error occurs before the plan is build.
>> ---
>> Koichi Suzuki
>>
>>
>> 2013/11/19 Ashutosh Bapat <ash...@en...>:
>> > Can you please provide output of EXPLAIN verbose?
>> >
>> >
>> > On Thu, Nov 14, 2013 at 1:44 PM, Masataka Saito <pg...@gm...>
>> wrote:
>> >>
>> >> Hello, all.
>> >>
>> >> I'm testing complex SQLs on Postgres-XC 1.1.
>> >> And I found bad SQL that XC doesn't accept.
>> >>
>> >> The SQL is  from answer 2 of chapter 17 in SQL puzzle book by Joe
>> >> Celko.
>> >> I simplified it for everyone's reproduction.
>> >>
>> >> db=# CREATE TABLE t1(id INT, a INT);
>> >> CREATE TABLE
>> >> db=# CREATE TABLE t2(id INT, b INT);
>> >> CREATE TABLE
>> >> db=# 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 SQL is accepted by PostgreSQL 9.2.4.
>> >>
>> >> Can anyone solve or explain this issue?
>> >>
>> >> Regards.
>> >>
>> >>
>> >>
>> ------------------------------------------------------------------------------
>> >> DreamFactory - Open Source REST & JSON Services for HTML5 & Native
>> >> Apps
>> >> OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
>> >> Free app hosting. Or install the open source package on any LAMP
>> >> server.
>> >> Sign up and see examples for AngularJS, jQuery, Sencha Touch and
>> >> Native!
>> >>
>> >>
>> http://pubads.g.doubleclick.net/gampad/clk?id=63469471&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
>> >
>> >
>> ------------------------------------------------------------------------------
>> > 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
>

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

[Michael P. <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

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

[鈴木 幸市 <ko...@in...>]

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
> 

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

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

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
>>
>

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

[Ashutosh B. <ash...@en...>]

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

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
>

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

[Ashutosh B. <ash...@en...>]

On Wed, Jan 15, 2014 at 1:55 PM, Masataka Saito <pg...@gm...> wrote:

> 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.
>
>
You have spotted the problem correctly. While writing this code, I thought
that having two entries in target list with same expression would be
problem when it comes to setting references (set_plan_ref), and hence chose
to disallow that. The assumption there was, if locate_grouping_columns()
doesn't find the column, it would return an invalid value, and thus we can
choose not to optimize that case. But locate_grouping_columns() is actually
throwing error, which breaks the assumption.

So, there are two possibilities
1. bite the bullet and let there be two entries in the targetlist with same
expressions but different ressortgroupref and see if that approach has any
other problems - we will have to test a bunch of scenarios to make sure
that there is no problem there.
2. Modify pgxc_locate_grouping_columns() to return NULL when no expression
is found with matching ressortgroupref. But that might need some magic with
PG_TRY() and CATCH or code duplication.


> 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
>>
>
>


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

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

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

I would take option #1 if possible, but I think estimating impact of the
fix and testing requires intimate knowledge of SQL and the optimizer: How
the planner optimize a plan tree and what SQL could have two entries in a
target list with the same value, How ressortgroup, resno, plan's target
list and remote query's target list and base tlist is used by the optimizer
and executor.
It is still hard for me.

My concern with 2nd option is how's the performance compared to the shipped
case and how much case are there that SQLs is not optimized.
I made some aggregation queries to test how's the query optimized with the
attached patch, and I found that just returning false in
pgxc_locate_grouping_columns() is not enough to fix this problem.

For example, the next query generates a borked plan.
postgres=# CREATE TABLE tbl AS SELECT * FROM (SELECT generate_series(1,3)
as i) a, (SELECT generate_series(1,3) AS j) b;
INSERT 0 9
postgres=# EXPLAIN VERBOSE SELECT i AS a, i AS b, avg(j) AS c FROM tblGROUP BY
a ORDER BY b;
                                            QUERY PLAN

---------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=49.83..57.45 rows=10 width=8)
   Output: tbl.i, tbl.i, avg(tbl.i)
   ->  Sort  (cost=49.83..52.33 rows=1000 width=8)
         Output: tbl.i, tbl.i
         Sort Key: tbl.i
         ->  Data Node Scan on tbl "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00
rows=1000 width=8)
               Output: tbl.i, tbl.i, (avg(tbl.j))
               Node/s: datanode1, datanode2
               Remote query: SELECT i, pg_catalog.int8_avg(avg(j)) FROM
ONLY public.tbl WHERE true
(9 rows)

Of course, it fails.
postgres=# SELECT i AS a, i AS b, avg(j) AS c FROM tbl GROUP BY a ORDER BY
b;
ERROR:  column "tbl.i" must appear in the GROUP BY clause or be used in an
aggregate function

In any case, this problem needs more study.

Regards.



On Wed, Jan 15, 2014 at 7:18 PM, Ashutosh Bapat <
ash...@en...> wrote:

>
>
>
> On Wed, Jan 15, 2014 at 1:55 PM, Masataka Saito <pg...@gm...> wrote:
>
>> 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.
>>
>>
> You have spotted the problem correctly. While writing this code, I thought
> that having two entries in target list with same expression would be
> problem when it comes to setting references (set_plan_ref), and hence chose
> to disallow that. The assumption there was, if locate_grouping_columns()
> doesn't find the column, it would return an invalid value, and thus we can
> choose not to optimize that case. But locate_grouping_columns() is actually
> throwing error, which breaks the assumption.
>
> So, there are two possibilities
> 1. bite the bullet and let there be two entries in the targetlist with
> same expressions but different ressortgroupref and see if that approach has
> any other problems - we will have to test a bunch of scenarios to make sure
> that there is no problem there.
> 2. Modify pgxc_locate_grouping_columns() to return NULL when no expression
> is found with matching ressortgroupref. But that might need some magic with
> PG_TRY() and CATCH or code duplication.
>
>
>> 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
>>>
>>
>>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>

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

I made two patches in different approach.

The borked plan is made from updating local_plan in the function before
arriving pgxc_locate_grouping_columns. The patch #07 attached backups
local_plan before it is re-written. Is this a right approach?

As far as I'm concerned using PG_TRY to handle a non-error situation is
dirty. I also wrote patch #06 in another approach to handle this case. It
makes different alias which have the expression to have single
sortgroupref. Do you have any concern about this approach?

Regards.


On 17 January 2014 16:47, Masataka Saito <pg...@gm...> wrote:

> I would take option #1 if possible, but I think estimating impact of the
> fix and testing requires intimate knowledge of SQL and the optimizer: How
> the planner optimize a plan tree and what SQL could have two entries in a
> target list with the same value, How ressortgroup, resno, plan's target
> list and remote query's target list and base tlist is used by the
> optimizer and executor.
> It is still hard for me.
>
> My concern with 2nd option is how's the performance compared to the
> shipped case and how much case are there that SQLs is not optimized.
> I made some aggregation queries to test how's the query optimized with the
> attached patch, and I found that just returning false in
> pgxc_locate_grouping_columns() is not enough to fix this problem.
>
> For example, the next query generates a borked plan.
> postgres=# CREATE TABLE tbl AS SELECT * FROM (SELECT generate_series(1,3)
> as i) a, (SELECT generate_series(1,3) AS j) b;
> INSERT 0 9
> postgres=# EXPLAIN VERBOSE SELECT i AS a, i AS b, avg(j) AS c FROM tblGROUP BY
> a ORDER BY b;
>                                             QUERY PLAN
>
>
> ---------------------------------------------------------------------------------------------------
>  GroupAggregate  (cost=49.83..57.45 rows=10 width=8)
>    Output: tbl.i, tbl.i, avg(tbl.i)
>    ->  Sort  (cost=49.83..52.33 rows=1000 width=8)
>          Output: tbl.i, tbl.i
>          Sort Key: tbl.i
>          ->  Data Node Scan on tbl "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00
> rows=1000 width=8)
>                Output: tbl.i, tbl.i, (avg(tbl.j))
>                Node/s: datanode1, datanode2
>                Remote query: SELECT i, pg_catalog.int8_avg(avg(j)) FROM
> ONLY public.tbl WHERE true
> (9 rows)
>
> Of course, it fails.
> postgres=# SELECT i AS a, i AS b, avg(j) AS c FROM tbl GROUP BY a ORDER
> BY b;
> ERROR:  column "tbl.i" must appear in the GROUP BY clause or be used in
> an aggregate function
>
> In any case, this problem needs more study.
>
> Regards.
>
>
>
> On Wed, Jan 15, 2014 at 7:18 PM, Ashutosh Bapat <
> ash...@en...> wrote:
>
>>
>>
>>
>> On Wed, Jan 15, 2014 at 1:55 PM, Masataka Saito <pg...@gm...> wrote:
>>
>>> 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.
>>>
>>>
>> You have spotted the problem correctly. While writing this code, I
>> thought that having two entries in target list with same expression would
>> be problem when it comes to setting references (set_plan_ref), and hence
>> chose to disallow that. The assumption there was, if
>> locate_grouping_columns() doesn't find the column, it would return an
>> invalid value, and thus we can choose not to optimize that case. But
>> locate_grouping_columns() is actually throwing error, which breaks the
>> assumption.
>>
>> So, there are two possibilities
>> 1. bite the bullet and let there be two entries in the targetlist with
>> same expressions but different ressortgroupref and see if that approach has
>> any other problems - we will have to test a bunch of scenarios to make sure
>> that there is no problem there.
>> 2. Modify pgxc_locate_grouping_columns() to return NULL when no
>> expression is found with matching ressortgroupref. But that might need some
>> magic with PG_TRY() and CATCH or code duplication.
>>
>>
>>> 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
>>>>
>>>
>>>
>>
>>
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EnterpriseDB Corporation
>> The Postgres Database Company
>>
>
>

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

[Ahsan H. <ahs...@en...>]

Hi Ashutosh,

You are going to review the patches from Masataka right? We need to include
these changes for 1.2 GA.

-- Ahsan



On Mon, Feb 3, 2014 at 4:02 PM, Masataka Saito <pg...@gm...> wrote:

> Hi, Ashutosh
>
> I made two patches in different approach.
>
> The borked plan is made from updating local_plan in the function before
> arriving pgxc_locate_grouping_columns. The patch #07 attached backups
> local_plan before it is re-written. Is this a right approach?
>
> As far as I'm concerned using PG_TRY to handle a non-error situation is
> dirty. I also wrote patch #06 in another approach to handle this case. It
> makes different alias which have the expression to have single
> sortgroupref. Do you have any concern about this approach?
>
> Regards.
>
>
> On 17 January 2014 16:47, Masataka Saito <pg...@gm...> wrote:
>
>> I would take option #1 if possible, but I think estimating impact of the
>> fix and testing requires intimate knowledge of SQL and the optimizer: How
>> the planner optimize a plan tree and what SQL could have two entries in a
>> target list with the same value, How ressortgroup, resno, plan's target
>> list and remote query's target list and base tlist is used by the
>> optimizer and executor.
>> It is still hard for me.
>>
>> My concern with 2nd option is how's the performance compared to the
>> shipped case and how much case are there that SQLs is not optimized.
>> I made some aggregation queries to test how's the query optimized with
>> the attached patch, and I found that just returning false in
>> pgxc_locate_grouping_columns() is not enough to fix this problem.
>>
>> For example, the next query generates a borked plan.
>> postgres=# CREATE TABLE tbl AS SELECT * FROM (SELECT generate_series(1,3)
>> as i) a, (SELECT generate_series(1,3) AS j) b;
>> INSERT 0 9
>> postgres=# EXPLAIN VERBOSE SELECT i AS a, i AS b, avg(j) AS c FROM tblGROUP BY
>> a ORDER BY b;
>>                                             QUERY PLAN
>>
>>
>> ---------------------------------------------------------------------------------------------------
>>   GroupAggregate  (cost=49.83..57.45 rows=10 width=8)
>>    Output: tbl.i, tbl.i, avg(tbl.i)
>>    ->  Sort  (cost=49.83..52.33 rows=1000 width=8)
>>          Output: tbl.i, tbl.i
>>          Sort Key: tbl.i
>>          ->  Data Node Scan on tbl "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00
>> rows=1000 width=8)
>>                Output: tbl.i, tbl.i, (avg(tbl.j))
>>                Node/s: datanode1, datanode2
>>                Remote query: SELECT i, pg_catalog.int8_avg(avg(j)) FROM
>> ONLY public.tbl WHERE true
>> (9 rows)
>>
>> Of course, it fails.
>> postgres=# SELECT i AS a, i AS b, avg(j) AS c FROM tbl GROUP BY a ORDER
>> BY b;
>> ERROR:  column "tbl.i" must appear in the GROUP BY clause or be used in
>> an aggregate function
>>
>> In any case, this problem needs more study.
>>
>> Regards.
>>
>>
>>
>> On Wed, Jan 15, 2014 at 7:18 PM, Ashutosh Bapat <
>> ash...@en...> wrote:
>>
>>>
>>>
>>>
>>> On Wed, Jan 15, 2014 at 1:55 PM, Masataka Saito <pg...@gm...>wrote:
>>>
>>>> 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.
>>>>
>>>>
>>> You have spotted the problem correctly. While writing this code, I
>>> thought that having two entries in target list with same expression would
>>> be problem when it comes to setting references (set_plan_ref), and hence
>>> chose to disallow that. The assumption there was, if
>>> locate_grouping_columns() doesn't find the column, it would return an
>>> invalid value, and thus we can choose not to optimize that case. But
>>> locate_grouping_columns() is actually throwing error, which breaks the
>>> assumption.
>>>
>>> So, there are two possibilities
>>> 1. bite the bullet and let there be two entries in the targetlist with
>>> same expressions but different ressortgroupref and see if that approach has
>>> any other problems - we will have to test a bunch of scenarios to make sure
>>> that there is no problem there.
>>> 2. Modify pgxc_locate_grouping_columns() to return NULL when no
>>> expression is found with matching ressortgroupref. But that might need some
>>> magic with PG_TRY() and CATCH or code duplication.
>>>
>>>
>>>> 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
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Best Wishes,
>>> Ashutosh Bapat
>>> EnterpriseDB Corporation
>>> The Postgres Database Company
>>>
>>
>>
>
>
> ------------------------------------------------------------------------------
> Managing the Performance of Cloud-Based Applications
> Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
> Read the Whitepaper.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=121051231&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>
>


-- 
Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.