subselect field "problem"

From: "Thomas H(dot)" <me(at)alternize(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: subselect field "problem"
Date: 2007-11-08 20:35:19
Message-ID: 47337307.30902@alternize.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi list

i was writing the query below containing a subquery. by mistake, i
referenced a field from the main table in the subquery, leading to a
very strange (but working??) result. the planner announced a insanely
high startup cost, but the query itself finished pretty quickly.

nevertheless, shouldn't pgsql warn the user if he's referencing a
non-existing field in a subquery? the field referenced in the subqueries
WHERE-clause doesn't exist in the subqueries table, thus i don't even
understand why that wouldn't throw an error and how the result would
have to be interpreted:

SELECT * FROM titles
WHERE tit_id IN
(
SELECT DISTINCT nam_tit_id
FROM names
WHERE lower(tit_name) LIKE '%best%'
)

the field "tit_name" is in "titles". the field i intented to use was
"nam_name" from table "names"...

regards,
thomas

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2007-11-08 20:45:09 Re: System V IPC on Windows
Previous Message Yancho 2007-11-08 20:14:47 Open Source GIS System