BUG #16010: Unexpected reordering of WHERE clause operations and SELECT list function calls - Mailing list pgsql-bugs
From | PG Bug reporting form |
---|---|
Subject | BUG #16010: Unexpected reordering of WHERE clause operations and SELECT list function calls |
Date | |
Msg-id | 16010-251e81f75eeaf50e@postgresql.org Whole thread Raw |
Responses |
Re: BUG #16010: Unexpected reordering of WHERE clause operations and SELECT list function calls
|
List | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 16010 Logged by: Stephen Kendall Email address: spkendall@gmail.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@postgres=# \set VERBOSITY verbose localhost: spk@postgres=# set client_min_messages to debug ; SET localhost: spk@postgres=# CREATE SCHEMA proc ; CREATE SCHEMA localhost: spk@postgres=# CREATE SCHEMA bkup_proc ; CREATE SCHEMA localhost: spk@postgres=# CREATE TABLE proc.decide_proc_tst040318 () ; CREATE TABLE localhost: spk@postgres=# CREATE TABLE bkup_proc.solution_use_proc_20190730 () ; CREATE TABLE localhost: spk@postgres=# SELECT * FROM ( localhost: spk@postgres(# SELECT schemaname, tablename, current_date-interval'2'month time_frame, localhost: spk@postgres(# to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd') table_date localhost: spk@postgres(# from pg_tables localhost: spk@postgres(# where schemaname = 'bkup_proc' localhost: spk@postgres(# and (tablename ~* 'decide_proc_' localhost: spk@postgres(# or tablename ~* 'solution_use_proc_' localhost: spk@postgres(# ) localhost: spk@postgres(# ) subq localhost: spk@postgres-# where table_date >= time_frame localhost: spk@postgres-# ; ERROR: 22008: date/time field value out of range: "040318" LOCATION: DateTimeParseError, datetime.c:3741 localhost: spk@postgres=# WITH localhost: spk@postgres-# tabs AS ( localhost: spk@postgres(# SELECT schemaname, tablename localhost: spk@postgres(# FROM pg_tables localhost: spk@postgres(# WHERE schemaname = 'bkup_proc' localhost: spk@postgres(# AND (tablename ~* 'decide_proc_' localhost: spk@postgres(# OR tablename ~* 'solution_use_proc_' localhost: spk@postgres(# ) localhost: spk@postgres(# ) localhost: spk@postgres-# SELECT * FROM ( localhost: spk@postgres(# SELECT schemaname, tablename, current_date-interval'2'month time_frame, localhost: spk@postgres(# to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd') table_date localhost: spk@postgres(# FROM tabs localhost: spk@postgres(# ) subq localhost: spk@postgres-# where table_date >= time_frame localhost: spk@postgres-# ; ERROR: 22008: date/time field value out of range: "040318" LOCATION: DateTimeParseError, datetime.c:3741 localhost: spk@postgres=# WITH localhost: spk@postgres-# tabs AS ( localhost: spk@postgres(# SELECT schemaname, tablename localhost: spk@postgres(# FROM pg_tables localhost: spk@postgres(# WHERE schemaname = 'bkup_proc' localhost: spk@postgres(# ) localhost: spk@postgres-# ,proc_tabs AS ( localhost: spk@postgres(# SELECT schemaname, tablename localhost: spk@postgres(# FROM tabs localhost: spk@postgres(# WHERE (tablename ~* 'decide_proc_' localhost: spk@postgres(# OR tablename ~* 'solution_use_proc_' localhost: spk@postgres(# ) localhost: spk@postgres(# ) localhost: spk@postgres-# SELECT * FROM ( localhost: spk@postgres(# SELECT schemaname, tablename, current_date-interval'2'month time_frame, localhost: spk@postgres(# to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd') table_date localhost: spk@postgres(# FROM proc_tabs localhost: spk@postgres(# ) subq localhost: spk@postgres-# where table_date >= time_frame localhost: spk@postgres-# ; ERROR: 22008: date/time field value out of range: "040318" LOCATION: DateTimeParseError, datetime.c:3741 localhost: spk@postgres=# WITH localhost: spk@postgres-# tabs AS ( localhost: spk@postgres(# SELECT schemaname, tablename localhost: spk@postgres(# FROM pg_tables localhost: spk@postgres(# WHERE schemaname = 'bkup_proc' localhost: spk@postgres(# ) localhost: spk@postgres-# ,proc_tabs AS ( localhost: spk@postgres(# SELECT schemaname, tablename localhost: spk@postgres(# FROM tabs localhost: spk@postgres(# WHERE (tablename ~* 'decide_proc_' localhost: spk@postgres(# OR tablename ~* 'solution_use_proc_' localhost: spk@postgres(# ) localhost: spk@postgres(# ) localhost: spk@postgres-# ,proc_tabs_calc AS ( localhost: spk@postgres(# SELECT schemaname, tablename, current_date-interval'2'month time_frame, localhost: spk@postgres(# to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd') table_date localhost: spk@postgres(# FROM proc_tabs localhost: spk@postgres(# ) localhost: spk@postgres-# SELECT * localhost: spk@postgres-# FROM proc_tabs_calc localhost: spk@postgres-# WHERE table_date >= time_frame localhost: spk@postgres-# ; ERROR: 22008: date/time field value out of range: "040318" LOCATION: DateTimeParseError, datetime.c:3741 localhost: spk@postgres=# WITH localhost: spk@postgres-# tabs AS ( localhost: spk@postgres(# SELECT schemaname, tablename localhost: spk@postgres(# FROM pg_tables localhost: spk@postgres(# WHERE schemaname = 'bkup_proc' localhost: spk@postgres(# ) localhost: spk@postgres-# ,proc_tabs AS ( localhost: spk@postgres(# SELECT schemaname, tablename, current_date-interval'2'month time_frame, localhost: spk@postgres(# to_timestamp(regexp_replace(tablename,'[^0-9]*',''),'yyyymmdd') table_date localhost: spk@postgres(# FROM tabs localhost: spk@postgres(# WHERE tablename ~* 'decide_proc_' localhost: spk@postgres(# or tablename ~* 'solution_use_proc_' localhost: spk@postgres(# ) localhost: spk@postgres-# SELECT * localhost: spk@postgres-# FROM proc_tabs localhost: spk@postgres-# WHERE table_date >= time_frame localhost: spk@postgres-# ; ERROR: 22008: date/time field value out of range: "040318" LOCATION: DateTimeParseError, datetime.c:3741 localhost: spk@postgres=#
pgsql-bugs by date: