WHERE-clause evaluation order (was [BUGS] Problem with BETWEEN and a view)

Andrew Snow <als@fl.net.au> writes:
> Here's a simpler script which reproduces the bug:

Well, after looking at this some more, I'm not convinced it's a bug;
or at least, if it's a bug it's one that can't be fixed without a
fundamental redefinition of rules/views.  Boiled down, here's what
you are doing:

CREATE TABLE Joy (x int4, z text);

INSERT INTO Joy VALUES (1, 'i love postgresql');
INSERT INTO Joy VALUES (2, CURRENT_TIMESTAMP - '5 days'::interval);
INSERT INTO Joy VALUES (3, CURRENT_TIMESTAMP + '5 days'::interval);

CREATE VIEW foo AS SELECT z::timestamp AS Start FROM Joy WHERE x = 2;

SELECT * FROM foo;        start
------------------------2000-11-10 01:45:09-05
(1 row)

SELECT * FROM foo WHERE Start < CURRENT_TIMESTAMP;
ERROR:  Bad timestamp external representation 'i love postgresql'

Now the first select is OK because the WHERE clause filters out the
rows that don't have timestamp-looking values of z before the SELECT's
output values get evaluated.  But the second SELECT gets expanded
by the rule rewriter into

SELECT z::timestamp AS Start FROM JoyWHERE x = 2 AND z::timestamp < CURRENT_TIMESTAMP;

Then it's all a matter of what order the WHERE clauses happen to get
applied in --- if the timestamp check gets applied first, the query
fails with exactly the result you see.  There is no guarantee that the
clauses that came from inside the view will be applied before those that
came from outside.  Indeed, the planner would be very foolish to make
such a guarantee, since the clauses coming from outside the view might
be far more selective and/or might enable use of an index.

In 7.1 it would be possible to force the view to be evaluated as an
independently-planned subplan, and have the outer WHERE clauses be
applied only as filters on the result.  However, the performance
implications of doing that are painful enough that I don't really
want to do it.  Essentially, we'd be lobotomizing the planner so that
it would not be able to make any choices about when to evaluate WHERE
clauses.  This would cause it to miss a lot of good plans for queries
involving views and sub-selects.

The relevant part of SQL92 seems to be section 3.3.4.4 "Rule evaluation
order".  As far as I can tell, this leaves implementations a great deal
of freedom to determine evaluation order of elements of a WHERE
expression.  Does anyone want to argue that the spec requires us to
be stupid about evaluation order?
        regards, tom lane