Thread: subqueries and qualification of table names

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

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