Re: BUG #16010: Unexpected reordering of WHERE clause operations and SELECT list function calls

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: spkendall(at)gmail(dot)com
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16010: Unexpected reordering of WHERE clause operations and SELECT list function calls
Date: 2019-09-17 16:24:05
Message-ID: 6683.1568737445@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> Expected output is a list of tables where the names contain simple date
> strings that fall within a range. Actual result is an error because one or
> more table names that do not contain validly formatted dates are passed to
> TO_TIMESTAMP(). This should not happen because the table with the invalid
> date in its name is in a schema that should be filtered out by a WHERE
> clause before the evaluation of the TO_TIMESTAMP().

There is not any particular guarantee about the order of evaluation of
WHERE clauses [1], and we're not going to make one because it would
be catastrophic for performance in many real-world cases. In this case
you might be able to force it by inserting an optimization fence in the
subquery (so that the "where table_date >= time_frame" clause can't be
pushed down into it); but you'd probably be better off to make the
table-name-to-timestamp conversion expression more robust. You could
do something like

case when tablename ~ '^[^0-9]*[0-9]{8}$'
then to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd')
else null
end

Another approach, but one that's also going to cost you performance,
is to wrap expressions that might throw errors into plpgsql functions
that you mark as volatile. That will discourage the planner from
moving them around ... but it will probably also cripple optimizations
that you still want.

regards, tom lane

[1] https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-09-17 17:45:02 BUG #16011: Select * query for sequences does not show all columns in output.
Previous Message PG Bug reporting form 2019-09-17 15:47:19 BUG #16010: Unexpected reordering of WHERE clause operations and SELECT list function calls