2015-03-02 23:07 GMT+09:00 Kouhei Kaigai <kaigai@ak.jp.nec.com>:
>> 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.
Actually. I fixed that part.
>> 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?
I'd like to vote for keeping current aliasing style, use "l" and "r"
for join source relations, and use a_0, a_1, ... for each column of
them.
--
Shigeru HANADA