Thread: Index selection bug

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Andriy I Pilipenko
Your email address      : bamby@marka.net.ua


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)  : FreeBSD 3.x

  PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.2

  Compiler used (example:  gcc 2.8.0)           : gcc 2.7.2.3


Please enter a FULL description of your problem:
------------------------------------------------

PostgreSQL refuses to use index if WHERE clause contains function call.
This problem exists in 6.5.3 also.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Do following queries:

    create table t (f int);

    create index i on t (f);

    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)


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

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;
  }