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
