José Soares <jose@sferacarta.com> writes:
> And now the other SELECT bug in the same data:
> select master1.*, detail1.*
> from master1 m, detail1 d
> where trim(m.code)=trim(d.code);
This one is definitely pilot error. Since you've renamed master1 and
detail1 in the FROM clause, your use of the original names in the SELECT
list is treated as adding more FROM items. Effectively your query is
select m2.*, d2.*
from master1 m, detail1 d, master1 m2, detail1 d2
where trim(m.code)=trim(d.code);
You're getting a four-way join with only one restriction clause...
There was a thread just the other day about whether we ought to allow
queries like this, because of someone else making exactly the same
error. I believe allowing tables to be referenced without FROM entries
is a holdover from the old Postquel language that's not found in SQL92.
Maybe we should get rid of it on the grounds that it creates confusion.
regards, tom lane