subqueries and qualification of table names

From: Kevin Murphy <murphy(at)genome(dot)chop(dot)edu>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: subqueries and qualification of table names
Date: 2005-04-26 21:05:58
Message-ID: 379ef67eff2f6cb6ab6e302d457b945a@genome.chop.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a query which didn't work properly until I fully qualified
columns used in a a subquery with the appropriate table names. The
reason is that both tables have a column named 'chromosome' used in the
subquery. In the following query, PG treats the phrase "and chromosome
= chromosome" as "and genetic.chromosome = genetic.chromosome". I.e.
it treats:

---
# select chromosome, layer, rank,
(select refsnp_id
from genetic
where extended_frame = True
and chromosome = chromosome
and gl_left = rank)
from framework
where name = 'D3S3610'
and layer = 'GL';
---

as:
---
select chromosome, layer, rank,
(select refsnp_id
from genetic
where genetic.extended_frame = True
and genetic.chromosome = genetic.chromosome
and genetic.gl_left = framework.rank)
from framework
where name = 'D3S3610'
and layer = 'GL';
---

Is that the appropriate SQL behavior? Personally I don't care; I'm
just curious.

Thanks,
Kevin Murphy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Typing80wpm 2005-04-26 21:30:49 www.thekompany.com rekall
Previous Message Werner Bohl 2005-04-26 20:57:47 Re: plruby on Windows