Re: Join push-down support for foreign tables

On 2 March 2015 at 14:07, Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com> wrote:

> > I seem to be getting a problem with whole-row references:
> >
> > # SELECT p.name, c.country, e.pet_name, p FROM pets e INNER JOIN people
> p on
> > e.person_id = p.id inner join countries c on p.country_id = c.id;
> > ERROR: table "r" has 3 columns available but 4 columns specified
> > CONTEXT: Remote SQL command: SELECT r.a_0, r.a_1, r.a_2, l.a_1 FROM
> (SELECT id,
> > country FROM public.countries) l (a_0, a_1) INNER JOIN (SELECT id, name,
> > country_id FROM public.people) r (a_0, a_1, a_2, a_3) ON ((r.a_3 =
> l.a_0))
> >
> In this case, the 4th target-entry should be "l", not l.a_1.
>

This will no doubt be my naivety talking, but if we know we need the whole
row, can we not request the row without additionally requesting individual
columns?

> > And the error message could be somewhat confusing. This mentions table
> "r", but
> > there's no such table or alias in my actual query.
> >
> However, do we have a mechanical/simple way to distinguish the cases when
> we need relation alias from the case when we don't need it?
> Like a self-join cases, we has to construct a remote query even if same
> table is referenced multiple times in a query. Do you have a good idea?
>

Then perhaps all that's really needed here is to clarify that the error
pertains to the remote execution plan rather than the query crafted by the
user. Or maybe I'm nitpicking.

--
Thom