To: cadiolis@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Text->Date conversion in a WHERE clause 
In-reply-to: <1129153075.962155.131260@g43g2000cwa.googlegroups.com> 
References: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com>
	<1129153075.962155.131260@g43g2000cwa.googlegroups.com>
Comments: In-reply-to cadiolis@gmail.com
	message dated "12 Oct 2005 14:37:56 -0700"
Date: Wed, 12 Oct 2005 19:00:05 -0400
Message-ID: <5023.1129158005@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

cadiolis@gmail.com writes:
> SELECT *
> FROM
> (
> 	SELECT u.user_id, ud.data
> 	FROM users u, userdata ud
> 	WHERE u.user_id = ud.user_id
> 	AND u.type = 1
> ) subusers
> WHERE subusers.data::text::date <  now();

> So my question is how does this query ever even SEE the row containing
> "052-44-5863"?  The sub-query doesn't return that row so I don't see
> how it can get this error.

BTW, the fallacy in this idea is that the planner pushes WHERE clauses
as far down the plan tree as it can.  EXPLAIN would show you the actual
plan tree, but it's probably along the lines of

	Join using u.user_id = ud.user_id
		Scan users u where u.type = 1
		Scan userdata ud where ud.data::text::date <  now();

If we did not do this, it would pretty much cripple the performance
of queries involving views (since a view is nothing but a macro for a
sub-select).

			regards, tom lane