Index selection bug

Andriy I Pilipenko <bamby@marka.net.ua> writes:

create function func() returns int as 'select 1' language 'sql';

set enable_seqscan to 'off';

explain select * from t where f = 1;

Index Scan using i on t (cost=0.00..2.01 rows=1 width=4)

explain select * from t where f = func();

Seq Scan on t (cost=100000000.00..100000001.34 rows=1 width=4)

Not a bug, because you didn't declare the function 'iscachable'.
For all the system knows, func() is like random() and will return a
different result at every row. An indexscan can't be used unless it's
safe to fold the function call down to a constant. See
http://www.postgresql.org/users-lounge/docs/7.0/postgres/sql-createfunction.htm

regards, tom lane

On Wed, 26 Jul 2000, Tom Lane wrote:

Andriy I Pilipenko <bamby@marka.net.ua> writes:

create function func() returns int as 'select 1' language 'sql';

set enable_seqscan to 'off';

explain select * from t where f = 1;

Index Scan using i on t (cost=0.00..2.01 rows=1 width=4)

explain select * from t where f = func();

Seq Scan on t (cost=100000000.00..100000001.34 rows=1 width=4)

Not a bug, because you didn't declare the function 'iscachable'.
For all the system knows, func() is like random() and will return a
different result at every row. An indexscan can't be used unless it's
safe to fold the function call down to a constant. See
http://www.postgresql.org/users-lounge/docs/7.0/postgres/sql-createfunction.htm

Thank you for help. I used iscachable attribute and all are mostly ok
except this:

create table a (a int)

create table b (b int)

create function f() returns int as '
select a
from a
where a = (select max(b) from b)
' language 'sql'
with (iscachable)

select f()

ERROR: replace_vars_with_subplan_refs: variable not in subplan target list

Kind regards,
Andriy I Pilipenko
PAI1-RIPE

Andriy I Pilipenko <bamby@marka.net.ua> writes:

create table a (a int)

create table b (b int)

create function f() returns int as '
select a
from a
where a = (select max(b) from b)
' language 'sql'
with (iscachable)

select f()

ERROR: replace_vars_with_subplan_refs: variable not in subplan target list

Oh my, that's interesting :-( --- especially that it doesn't happen
without iscachable. Will look into it. Thanks for the report.

regards, tom lane

Andriy I Pilipenko <bamby@marka.net.ua> writes:

create function f() returns int as '
select a
from a
where a = (select max(b) from b)
' language 'sql'
with (iscachable)

select f()

ERROR: replace_vars_with_subplan_refs: variable not in subplan target list

Fixed by the attached patch. Thanks for the report!

regards, tom lane

*** src/backend/optimizer/plan/planner.c.orig	Wed Apr 12 13:15:22 2000
--- src/backend/optimizer/plan/planner.c	Thu Jul 27 19:53:29 2000
***************
*** 53,58 ****
--- 53,74 ----
  planner(Query *parse)
  {
  	Plan	   *result_plan;
+ 	Index		save_PlannerQueryLevel;
+ 	List	   *save_PlannerInitPlan;
+ 	List	   *save_PlannerParamVar;
+ 	int			save_PlannerPlanId;
+ 
+ 	/*
+ 	 * The planner can be called recursively (an example is when
+ 	 * eval_const_expressions tries to simplify an SQL function).
+ 	 * So, global state variables must be saved and restored.
+ 	 *
+ 	 * (Perhaps these should be moved into the Query structure instead?)
+ 	 */
+ 	save_PlannerQueryLevel = PlannerQueryLevel;
+ 	save_PlannerInitPlan = PlannerInitPlan;
+ 	save_PlannerParamVar = PlannerParamVar;
+ 	save_PlannerPlanId = PlannerPlanId;

  	/* Initialize state for subselects */
  	PlannerQueryLevel = 1;
***************
*** 80,85 ****
--- 96,107 ----

  	/* final cleanup of the plan */
  	set_plan_references(result_plan);
+ 
+ 	/* restore state for outer planner, if any */
+ 	PlannerQueryLevel = save_PlannerQueryLevel;
+ 	PlannerInitPlan = save_PlannerInitPlan;
+ 	PlannerParamVar = save_PlannerParamVar;
+ 	PlannerPlanId = save_PlannerPlanId;

return result_plan;
}