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
