Thread: subselect field "problem"

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

On Thu, Nov 08, 2007 at 09:35:19PM +0100, Thomas H. wrote:
> 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.

Pick up any SQL book and read up on correlated subqueries.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

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.   

Pick up any SQL book and read up on correlated subqueries.

Have a nice day, 

thanks! i didn't knew this term. the result is pretty obvious now, and so is the high cost prediction.

regards,
thomas