subqueries and qualification of table names

Started by Kevin Murphyalmost 21 years ago2 messagesgeneral
Jump to latest
#1Kevin Murphy
murphy@genome.chop.edu

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Kevin Murphy (#1)
Re: subqueries and qualification of table names

Kevin Murphy <murphy@genome.chop.edu> writes:

... In the following query, PG treats the phrase "and chromosome
= chromosome" as "and genetic.chromosome = genetic.chromosome".

And that surprises you why?

regards, tom lane