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
>