Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andrew Snow <als(at)fl(dot)net(dot)au>
Cc: pgsql-hackers(at)postgreSQL(dot)org, pgsql-bugs(at)postgreSQL(dot)org
Subject: WHERE-clause evaluation order (was Problem with BETWEEN and a view)
Date: 2000-11-15 17:45:08
Message-ID: 13676.974310308@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Andrew Snow <als(at)fl(dot)net(dot)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 Joy
	WHERE 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

In response to

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2000-11-15 17:45:46
Subject: Re: Re: Commit finished?
Previous:From: josephDate: 2000-11-15 17:28:43
Subject: Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2000-11-16 04:57:17
Subject: Re: no meaningful way to determine fe or be version?
Previous:From: Tom LaneDate: 2000-11-15 15:40:01
Subject: Re: Fail to restore index tables by pg_dumpall

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group