Bug Report: Error caused due to wrong ordering of filters
| От | Ekta Khanna |
|---|---|
| Тема | Bug Report: Error caused due to wrong ordering of filters |
| Дата | |
| Msg-id | CACMP9w1KV_g17gJuvwiLdtPn2jqEpS4UoQ0gWWTZF9eLemu5NA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Bug Report: Error caused due to wrong ordering of filters
|
| Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: