Thread: Bug Report: Error caused due to wrong ordering of filters
Hello PGSQL Hackers,
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?
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';
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:
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)
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
>>>>> "Ekta" == Ekta Khanna <ekhanna@pivotal.io> writes:
Ekta> Hello PGSQL Hackers,
Ekta> We have come across the following issue on Postgres
Ekta> REL_10_STABLE. Below is the repro:
[...]
Ekta> In the plan, we see that planner merges the quals from FROM
Ekta> clause and the WHERE clause in the same RESTRICTINFO. Is this the
Ekta> expected behavior?
Yes, it's entirely expected. You CANNOT make assumptions about the order
of evaluation of quals; the planner will rearrange them freely, even
across subquery boundaries (where the semantics allow).
You can do this:
WHERE CASE WHEN length(b) = 8
THEN to_date(b, 'YYYYMMDD') > '2018-05-04'
ELSE false END
since one of the few guarantees about execution order is that a CASE
will evaluate its condition tests before any non-constant subexpressions
in the corresponding THEN clause.
(Another method is to put an OFFSET 0 in the subquery, but that's more
of a hack)
--
Andrew (irc:RhodiumToad)