PostgreSQL Discussion - subqueries and qualification of table names

Author

subqueries and qualification of table names

Kevin Murphy

2005-04-26, 8:23 pm

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane

2005-04-27, 3:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Sponsored Links