Re: plan difference between set-returning function with ROWS within IN() and a plain join

["Merlin Moncure" <mmoncure(at)gmail(dot)com>]

On Tue, May 6, 2008 at 11:27 AM, Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> wrote:
>> > db=# explain analyse
>> >     select sum(base_total_val)
>> >     from sales_invoice
>> >     where id in (select id from si_credit_tree(80500007));
>>
>> Did you check whether this query even gives the right answer?
>
> You knew the right answer to that already ;)
>
>> I think you forgot the alias foo(id) in the subselect and it's
>> actually reducing to "where id in (id)", ie, TRUE.
>
> Tricky, but completely obvious once pointed out, that's _exactly_ what was
> happening.

This is one of the reasons why, for a table named 'foo', I name the
columns 'foo_id', not 'id'.  Also, if you prefix the id column with
the table name, you can usually use JOIN USING which is a little bit
tighter and easier than JOIN ON.

merlin