Thread: Optimizer problem with subselect.c?

Hi all.

Interesting result when migrating from 7.2.? to 7.4.1, the dump and import went fine, no errors or whistles, in fact
allmy tests went through okay but a few types of queries which once worked, now don't, and in an odd fashion. 

When I checked out the mailing lists and changelogs (in cvs) it seems there is some gotchas in the subselect code in
theoptimizer/plan section, so I have a hunch it's there somewhere. 

select count(id) from connection_info where now() - opentime <= '1 day';
ERROR:  variable not found in subplan target lists

This particular error seems to have some fame attached to it...

Anyway, here's the vitals, including our layout:

                    View "connection_info"
         Column         |           Type           | Modifiers
------------------------+--------------------------+-----------
 location_appearance_id | integer                  |
 status                 | integer                  |
 termination_reason     | integer                  |
 inputoctets            | integer                  |
 outputoctets           | integer                  |
 opentime               | timestamp with time zone |
 termtime               | timestamp with time zone |
 end_time               | timestamp with time zone |
 lastpackettime         | timestamp with time zone |
 id                     | integer                  |
 session_status         | integer                  |
 connection_status      | integer                  |
 location_id            | integer                  |
 client_ip              | inet                     |
 client_mac             | macaddr                  |
 original_url           | text                     |
 sshash                 | character varying(64)    |
 login_id               | integer                  |
 organization_id        | integer                  |
 connection_timeout     | interval                 |
 roaming_username       | text                     |
 actual_used            | double precision         |
 current_used           | double precision         |
 idle                   | double precision         |
View definition: SELECT "session".location_appearance_id, "session".status, "session".termination_reason,
"session".inputoctets,"session".outputoctets, "session".opentime, "session".termtime, "session".end_time,
"session".lastpackettime,"session".id, "session".status AS session_status, "session".status AS connection_status,
location_appearance.location_id,location_appearance.client_ip, location_appearance.client_mac,
location_appearance.original_url,site_session.sshash, login_site_session.login_id, login.organization_id,
login.connection_timeout,roaming_site_session.username AS roaming_username, date_part('epoch'::text,
("session".lastpackettime- "session".opentime)) AS actual_used, date_part('epoch'::text, (now() - "session".opentime))
AScurrent_used, date_part('epoch'::text, (now() - "session".lastpackettime)) AS idle FROM (((((("session" JOIN
location_appearanceON (("session".location_appearance_id = location_appearance.id))) JOIN
location_appearance_site_sessioON 
  ((location_appearance.id = location_appearance_site_sessio.location_appearance_id))) JOIN site_session ON
(((location_appearance_site_sessio.site_session_sshash)::text= (site_session.sshash)::text))) LEFT JOIN
login_site_sessionON (((login_site_session.site_session_sshash)::text = (site_session.sshash)::text))) LEFT JOIN login
ON((login_site_session.login_id = login.id))) LEFT JOIN roaming_site_session ON
(((roaming_site_session.site_session_sshash)::text= (site_session.sshash)::text))) WHERE (site_session.status <> 3); 

-----------------

                                              Table "session"
         Column         |           Type           |                       Modifiers
------------------------+--------------------------+-------------------------------------------------------
 location_appearance_id | integer                  | not null
 status                 | integer                  | default 0
 termination_reason     | integer                  |
 inputoctets            | integer                  | default 0
 outputoctets           | integer                  | default 0
 opentime               | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
 termtime               | timestamp with time zone |
 end_time               | timestamp with time zone |
 lastpackettime         | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
 id                     | integer                  | not null default nextval('"connection_id_seq"'::text)
Indexes: connection_id_idx,
         session_laid_idx,
         session_status_idx
Triggers: RI_ConstraintTrigger_776160,
          RI_ConstraintTrigger_776163,
          RI_ConstraintTrigger_776397,
          RI_ConstraintTrigger_776400


-------------------

                                       Table "location_appearance"
    Column    |           Type           |                           Modifiers
--------------+--------------------------+----------------------------------------------------------------
 id           | integer                  | not null default nextval('"location_appearance_id_seq"'::text)
 location_id  | integer                  | not null
 client_ip    | inet                     | not null
 client_mac   | macaddr                  |
 original_url | text                     |
 create_time  | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
Indexes: location_appearance_id_idx
Primary key: location_appearance_pk
Triggers: RI_ConstraintTrigger_776097,
          RI_ConstraintTrigger_776101,
          RI_ConstraintTrigger_776102,
          RI_ConstraintTrigger_776164,
          RI_ConstraintTrigger_776165,
          RI_ConstraintTrigger_776334,
          RI_ConstraintTrigger_776338,
          RI_ConstraintTrigger_776339,
          RI_ConstraintTrigger_776401,
          RI_ConstraintTrigger_776402


-------------------

                                    Table "site_session"
   Column   |           Type           |                     Modifiers
------------+--------------------------+----------------------------------------------------
 sshash     | character varying(64)    | not null
 createtime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
 lastupdate | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
 status     | integer                  | default 0
Indexes: site_session_date_idx,
         site_session_sshash_idx,
         site_session_status_idx
Primary key: site_session_pk
Triggers: RI_ConstraintTrigger_776104,
          RI_ConstraintTrigger_776105,
          RI_ConstraintTrigger_776110,
          RI_ConstraintTrigger_776111,
          RI_ConstraintTrigger_776113,
          RI_ConstraintTrigger_776114,
          RI_ConstraintTrigger_776119,
          RI_ConstraintTrigger_776120,
          RI_ConstraintTrigger_776179,
          RI_ConstraintTrigger_776180,
          RI_ConstraintTrigger_776185,
          RI_ConstraintTrigger_776186,
          RI_ConstraintTrigger_776341,
          RI_ConstraintTrigger_776342,
          RI_ConstraintTrigger_776347,
          RI_ConstraintTrigger_776348,
          RI_ConstraintTrigger_776350,
          RI_ConstraintTrigger_776351,
          RI_ConstraintTrigger_776356,
          RI_ConstraintTrigger_776357,
          RI_ConstraintTrigger_776416,
          RI_ConstraintTrigger_776417,
          RI_ConstraintTrigger_776422,
          RI_ConstraintTrigger_776423


-------------------

          Table "location_appearance_site_sessio"
         Column         |         Type          | Modifiers
------------------------+-----------------------+-----------
 location_appearance_id | integer               | not null
 site_session_sshash    | character varying(64) | not null
Indexes: lass_id_idx,
         lass_sshash_idx
Triggers: RI_ConstraintTrigger_776100,
          RI_ConstraintTrigger_776103,
          RI_ConstraintTrigger_776337,
          RI_ConstraintTrigger_776340


------------------

                                                Table "login"
       Column       |           Type           |                          Modifiers
--------------------+--------------------------+-------------------------------------------------------------
 id                 | integer                  | not null default nextval('"login_id_seq"'::text)
 organization_id    | integer                  | not null
 email              | text                     | not null
 realname           | character varying(32)    | default ''::character varying
 title              | character varying(32)    | default ''::character varying
 question           | character varying(32)    | default ''::character varying
 answer             | character varying(32)    | default ''::character varying
 contact_method     | integer                  | default 0
 connection_timeout | interval                 | default '00:15:00'::interval
 auto_connect       | boolean                  | not null default true
 createdate         | timestamp with time zone | not null default ('now'::text)::timestamp(6) with time zone
Primary key: login_pk
Unique keys: login_id_key
Triggers: RI_ConstraintTrigger_775965,
          RI_ConstraintTrigger_775969,
          RI_ConstraintTrigger_775970,
          RI_ConstraintTrigger_776026,
          RI_ConstraintTrigger_776027,
          RI_ConstraintTrigger_776035,
          RI_ConstraintTrigger_776036,
          RI_ConstraintTrigger_776044,
          RI_ConstraintTrigger_776045,
          RI_ConstraintTrigger_776053,
          RI_ConstraintTrigger_776054,
          RI_ConstraintTrigger_776056,
          RI_ConstraintTrigger_776057,
          RI_ConstraintTrigger_776083,
          RI_ConstraintTrigger_776084,
          RI_ConstraintTrigger_776107,
          RI_ConstraintTrigger_776108,
          RI_ConstraintTrigger_776205,
          RI_ConstraintTrigger_776209,
          RI_ConstraintTrigger_776210,
          RI_ConstraintTrigger_776263,
          RI_ConstraintTrigger_776264,
          RI_ConstraintTrigger_776272,
          RI_ConstraintTrigger_776273,
          RI_ConstraintTrigger_776281,
          RI_ConstraintTrigger_776282,
          RI_ConstraintTrigger_776290,
          RI_ConstraintTrigger_776291,
          RI_ConstraintTrigger_776293,
          RI_ConstraintTrigger_776294,
          RI_ConstraintTrigger_776320,
          RI_ConstraintTrigger_776321,
          RI_ConstraintTrigger_776344,
          RI_ConstraintTrigger_776345


--------------------

                                    Table "roaming_site_session"
       Column        |           Type           |                     Modifiers
---------------------+--------------------------+----------------------------------------------------
 username            | text                     | not null
 site_session_sshash | character varying(64)    | not null
 createtime          | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
Indexes: roaming_sshash_idx
Triggers: RI_ConstraintTrigger_776112,
          RI_ConstraintTrigger_776349


--------------------

                                     Table "login_site_session"
       Column        |           Type           |                     Modifiers
---------------------+--------------------------+----------------------------------------------------
 login_id            | integer                  | not null
 site_session_sshash | character varying(64)    | not null
 createtime          | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
Indexes: login_sshash_idx
Triggers: RI_ConstraintTrigger_776106,
          RI_ConstraintTrigger_776109,
          RI_ConstraintTrigger_776343,
          RI_ConstraintTrigger_776346

-----------------------

And I think that's all of them.  Most of the relevant data is in the original view (connection_info).  Also, please
pardonthe obfuscation of the database, it's the softwares' fault! 

Thanks,
--Daniel F. O'Neill
fatport.com

"Daniel O'Neill" <daniel@fatport.com> writes:
> Anyway, here's the vitals, including our layout:

Could I trouble you to provide those table and view definitions as an
SQL script?  (pg_dump -s will help you.)  I'm too short of time to
manually convert your \d listings into something executable.

            regards, tom lane

On Fri, 27 Feb 2004 00:57:36 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Daniel O'Neill" <daniel@fatport.com> writes:
> > Anyway, here's the vitals, including our layout:
>
> Could I trouble you to provide those table and view definitions as an
> SQL script?  (pg_dump -s will help you.)  I'm too short of time to
> manually convert your \d listings into something executable.
>
>             regards, tom lane
>

Hmm, the design isn't very 'pick aparty', would it be kosher of me to send you the full schema to work with?  I can
emailit directly. 

Thanks,
--Daniel

"Daniel O'Neill" <daniel@fatport.com> writes:
> Hmm, the design isn't very 'pick aparty', would it be kosher of me to send you the full schema to work with?  I can
emailit directly. 

Sure, a pg_dump -s dump would be fine.  If it's large you can just send
to me off-list.

It's possible that this is the same bug I just fixed in connection with
Damon Hart's bug report, but I'm not convinced of that; I'd still like
to reproduce your case here.

            regards, tom lane

"Daniel O'Neill" <daniel@fatport.com> writes:
> [ complex test case ]

Got it.  The patch against 7.4.* is attached and will appear in 7.4.2.

            regards, tom lane

Index: createplan.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/plan/createplan.c,v
retrieving revision 1.157.2.2
diff -c -r1.157.2.2 createplan.c
*** createplan.c    18 Jan 2004 00:31:53 -0000    1.157.2.2
--- createplan.c    29 Feb 2004 17:29:48 -0000
***************
*** 101,107 ****
  static Sort *make_sort(Query *root, List *tlist, Plan *lefttree, int numCols,
            AttrNumber *sortColIdx, Oid *sortOperators);
  static Sort *make_sort_from_pathkeys(Query *root, Plan *lefttree,
!                         Relids relids, List *pathkeys);


  /*
--- 101,107 ----
  static Sort *make_sort(Query *root, List *tlist, Plan *lefttree, int numCols,
            AttrNumber *sortColIdx, Oid *sortOperators);
  static Sort *make_sort_from_pathkeys(Query *root, Plan *lefttree,
!                         List *pathkeys);


  /*
***************
*** 1015,1021 ****
          outer_plan = (Plan *)
              make_sort_from_pathkeys(root,
                                      outer_plan,
-                           best_path->jpath.outerjoinpath->parent->relids,
                                      best_path->outersortkeys);
      }

--- 1015,1020 ----
***************
*** 1025,1031 ****
          inner_plan = (Plan *)
              make_sort_from_pathkeys(root,
                                      inner_plan,
-                           best_path->jpath.innerjoinpath->parent->relids,
                                      best_path->innersortkeys);
      }

--- 1024,1029 ----
***************
*** 1793,1799 ****
   *      Create sort plan to sort according to given pathkeys
   *
   *      'lefttree' is the node which yields input tuples
-  *      'relids' is the set of relids represented by the input node
   *      'pathkeys' is the list of pathkeys by which the result is to be sorted
   *
   * We must convert the pathkey information into arrays of sort key column
--- 1791,1796 ----
***************
*** 1806,1813 ****
   * adding a Result node just to do the projection.
   */
  static Sort *
! make_sort_from_pathkeys(Query *root, Plan *lefttree,
!                         Relids relids, List *pathkeys)
  {
      List       *tlist = lefttree->targetlist;
      List       *sort_tlist;
--- 1803,1809 ----
   * adding a Result node just to do the projection.
   */
  static Sort *
! make_sort_from_pathkeys(Query *root, Plan *lefttree, List *pathkeys)
  {
      List       *tlist = lefttree->targetlist;
      List       *sort_tlist;
***************
*** 1852,1863 ****
          }
          if (!resdom)
          {
!             /* No matching Var; look for an expression */
              foreach(j, keysublist)
              {
                  pathkey = lfirst(j);
!                 if (bms_is_subset(pull_varnos(pathkey->key), relids))
!                     break;
              }
              if (!j)
                  elog(ERROR, "could not find pathkey item to sort");
--- 1848,1869 ----
          }
          if (!resdom)
          {
!             /* No matching Var; look for a computable expression */
              foreach(j, keysublist)
              {
+                 List   *exprvars;
+                 List   *k;
+
                  pathkey = lfirst(j);
!                 exprvars = pull_var_clause(pathkey->key, false);
!                 foreach(k, exprvars)
!                 {
!                     if (!tlist_member(lfirst(k), tlist))
!                         break;
!                 }
!                 freeList(exprvars);
!                 if (!k)
!                     break;        /* found usable expression */
              }
              if (!j)
                  elog(ERROR, "could not find pathkey item to sort");