Bug Report: Error caused due to wrong ordering of filters

From: Ekta Khanna <ekhanna(at)pivotal(dot)io>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Sambitesh Dash <sdash(at)pivotal(dot)io>
Subject: Bug Report: Error caused due to wrong ordering of filters
Date: 2018-05-07 23:24:53
Message-ID: CACMP9w1KV_g17gJuvwiLdtPn2jqEpS4UoQ0gWWTZF9eLemu5NA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello PGSQL Hackers,

We have come across the following issue on Postgres REL_10_STABLE. Below is
the repro:

CREATE TABLE foo (a int, b text); INSERT INTO foo values(1, '3'); SELECT *
FROM (SELECT * FROM foo WHERE length(b)=8)x WHERE to_date(x.b,'YYYYMMDD') >
'2018-05-04';
ERROR: source string too short for "YYYY" formatting field DETAIL: Field
requires 4 characters, but only 1 remain. HINT: If your source string is
not fixed-width, try using the "FM" modifier.

On looking at the explain plan, we see the order of the clauses is reversed
due to costing of clauses in the function order_qual_clauses() below is the
plan :
*Actual Plan:*
QUERY PLAN
-------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.40 rows=2 width=36) Filter: ((to_date(b,
'YYYYMMDD'::text) > '2018-05-04'::date) AND (length(b) = 8)) (2 rows)

Expected plan should execute the qual as part of the FROM clause before
executing the qual in the WHERE clause:
*Plan expected: *
QUERY PLAN
-------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.40 rows=2 width=36) Filter: (length(b) = 8))
AND ((to_date(b, 'YYYYMMDD'::text) > '2018-05-04'::date) (2 rows)

Has anyone come across similar issue ?
In the plan, we see that planner merges the quals from FROM clause and the
WHERE clause in the same RESTRICTINFO. Is this the expected behavior?

Thanks & Regards,
Ekta & Sam

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2018-05-07 23:53:19 Re: perlcritic and perltidy
Previous Message Michael Paquier 2018-05-07 22:19:04 Re: Refreshing findoidjoins for v11