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

The following bug has been logged on the website:

Bug reference: 16010
Logged by: Stephen Kendall
Email address: spkendall(at)gmail(dot)com
PostgreSQL version: 12beta3
Operating system: MacOS
Description:

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().

All but the first query shown below actually work in our production
environment (Amazon RDS 10.6). They don't error there and do return the
expected list of tables. However, that environment is too complex and too
full of proprietary data; I can not provide reproduction steps for it.

In any event, I believe that either the first query after the CREATE TABLE
statements should work because I think TO_TIMESTAMP() should *not* be run
against rows that fail the first WHERE clause predicate. That query errors
in both environments.

spkmbp:~ spk$ psql12 service=app-12-postgres-postgres
psql12 (12beta3)
Type "help" for help.

localhost: spk(at)postgres=# \set VERBOSITY verbose
localhost: spk(at)postgres=# set client_min_messages to debug ;
SET
localhost: spk(at)postgres=# CREATE SCHEMA proc ;
CREATE SCHEMA
localhost: spk(at)postgres=# CREATE SCHEMA bkup_proc ;
CREATE SCHEMA
localhost: spk(at)postgres=# CREATE TABLE proc.decide_proc_tst040318 () ;
CREATE TABLE
localhost: spk(at)postgres=# CREATE TABLE bkup_proc.solution_use_proc_20190730
() ;
CREATE TABLE
localhost: spk(at)postgres=# SELECT * FROM (
localhost: spk(at)postgres(# SELECT schemaname, tablename,
current_date-interval'2'month time_frame,
localhost: spk(at)postgres(#
to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd') table_date
localhost: spk(at)postgres(# from pg_tables
localhost: spk(at)postgres(# where schemaname = 'bkup_proc'
localhost: spk(at)postgres(# and (tablename ~* 'decide_proc_'
localhost: spk(at)postgres(# or tablename ~* 'solution_use_proc_'
localhost: spk(at)postgres(# )
localhost: spk(at)postgres(# ) subq
localhost: spk(at)postgres-# where table_date >= time_frame
localhost: spk(at)postgres-# ;
ERROR: 22008: date/time field value out of range: "040318"
LOCATION: DateTimeParseError, datetime.c:3741
localhost: spk(at)postgres=# WITH
localhost: spk(at)postgres-# tabs AS (
localhost: spk(at)postgres(# SELECT schemaname, tablename
localhost: spk(at)postgres(# FROM pg_tables
localhost: spk(at)postgres(# WHERE schemaname = 'bkup_proc'
localhost: spk(at)postgres(# AND (tablename ~* 'decide_proc_'
localhost: spk(at)postgres(# OR tablename ~* 'solution_use_proc_'
localhost: spk(at)postgres(# )
localhost: spk(at)postgres(# )
localhost: spk(at)postgres-# SELECT * FROM (
localhost: spk(at)postgres(# SELECT schemaname, tablename,
current_date-interval'2'month time_frame,
localhost: spk(at)postgres(#
to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd') table_date
localhost: spk(at)postgres(# FROM tabs
localhost: spk(at)postgres(# ) subq
localhost: spk(at)postgres-# where table_date >= time_frame
localhost: spk(at)postgres-# ;
ERROR: 22008: date/time field value out of range: "040318"
LOCATION: DateTimeParseError, datetime.c:3741
localhost: spk(at)postgres=# WITH
localhost: spk(at)postgres-# tabs AS (
localhost: spk(at)postgres(# SELECT schemaname, tablename
localhost: spk(at)postgres(# FROM pg_tables
localhost: spk(at)postgres(# WHERE schemaname = 'bkup_proc'
localhost: spk(at)postgres(# )
localhost: spk(at)postgres-# ,proc_tabs AS (
localhost: spk(at)postgres(# SELECT schemaname, tablename
localhost: spk(at)postgres(# FROM tabs
localhost: spk(at)postgres(# WHERE (tablename ~* 'decide_proc_'
localhost: spk(at)postgres(# OR tablename ~* 'solution_use_proc_'
localhost: spk(at)postgres(# )
localhost: spk(at)postgres(# )
localhost: spk(at)postgres-# SELECT * FROM (
localhost: spk(at)postgres(# SELECT schemaname, tablename,
current_date-interval'2'month time_frame,
localhost: spk(at)postgres(#
to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd') table_date
localhost: spk(at)postgres(# FROM proc_tabs
localhost: spk(at)postgres(# ) subq
localhost: spk(at)postgres-# where table_date >= time_frame
localhost: spk(at)postgres-# ;
ERROR: 22008: date/time field value out of range: "040318"
LOCATION: DateTimeParseError, datetime.c:3741
localhost: spk(at)postgres=# WITH
localhost: spk(at)postgres-# tabs AS (
localhost: spk(at)postgres(# SELECT schemaname, tablename
localhost: spk(at)postgres(# FROM pg_tables
localhost: spk(at)postgres(# WHERE schemaname = 'bkup_proc'
localhost: spk(at)postgres(# )
localhost: spk(at)postgres-# ,proc_tabs AS (
localhost: spk(at)postgres(# SELECT schemaname, tablename
localhost: spk(at)postgres(# FROM tabs
localhost: spk(at)postgres(# WHERE (tablename ~* 'decide_proc_'
localhost: spk(at)postgres(# OR tablename ~* 'solution_use_proc_'
localhost: spk(at)postgres(# )
localhost: spk(at)postgres(# )
localhost: spk(at)postgres-# ,proc_tabs_calc AS (
localhost: spk(at)postgres(# SELECT schemaname, tablename,
current_date-interval'2'month time_frame,
localhost: spk(at)postgres(#
to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd') table_date
localhost: spk(at)postgres(# FROM proc_tabs
localhost: spk(at)postgres(# )
localhost: spk(at)postgres-# SELECT *
localhost: spk(at)postgres-# FROM proc_tabs_calc
localhost: spk(at)postgres-# WHERE table_date >= time_frame
localhost: spk(at)postgres-# ;
ERROR: 22008: date/time field value out of range: "040318"
LOCATION: DateTimeParseError, datetime.c:3741
localhost: spk(at)postgres=# WITH
localhost: spk(at)postgres-# tabs AS (
localhost: spk(at)postgres(# SELECT schemaname, tablename
localhost: spk(at)postgres(# FROM pg_tables
localhost: spk(at)postgres(# WHERE schemaname = 'bkup_proc'
localhost: spk(at)postgres(# )
localhost: spk(at)postgres-# ,proc_tabs AS (
localhost: spk(at)postgres(# SELECT schemaname, tablename,
current_date-interval'2'month time_frame,
localhost: spk(at)postgres(#
to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd') table_date
localhost: spk(at)postgres(# FROM tabs
localhost: spk(at)postgres(# WHERE tablename ~* 'decide_proc_'
localhost: spk(at)postgres(# or tablename ~* 'solution_use_proc_'
localhost: spk(at)postgres(# )
localhost: spk(at)postgres-# SELECT *
localhost: spk(at)postgres-# FROM proc_tabs
localhost: spk(at)postgres-# WHERE table_date >= time_frame
localhost: spk(at)postgres-# ;
ERROR: 22008: date/time field value out of range: "040318"
LOCATION: DateTimeParseError, datetime.c:3741
localhost: spk(at)postgres=#