Re: Text->Date conversion in a WHERE clause

cadiolis(at)gmail(dot)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