Re: bug in info.c file - incorrect SQL

Hiroshi Inoue wrote:
> -----Original Message-----
> From: Jim Holliday
>
> > For the ODBC driver psqlodbc.dll version 7.02.0003 there is an error in
> the info.c.
> > In 2 places the SQL contains an alias in the FROM clause for the
> pg_namespace
> > table and then references the table in the where clause.  The below cut
> from the
> > info.c file should replace all instances on pg_namespace. in the where
> clause with
> > pn. This should be done in both places.
>
> Does the code give you a wrong result in the first place ?

I see what Hiroshi is saying now --- there was a reference to
pg_namespace (in WHERE but not in FROM) _and_ a reference to 'pn' as an
alias for pg_namespace (in WHERE and FROM).

The attached patch fixes this by creating a pn1 and pn2 alias for
pg_namespace in FROM.  It also improves some of the alias tags.  Applied
to ODBC CVS.  Can someone run some tests on these?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: info.c
===================================================================
RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/info.c,v
retrieving revision 1.91
diff -c -c -r1.91 info.c
*** info.c    2 Jan 2003 22:30:13 -0000    1.91
--- info.c    3 Jan 2003 21:13:12 -0000
***************
*** 3423,3500 ****
          if (conn->schema_support)
          {
              schema_strcat(schema_needed, "%.*s", szFkTableOwner, cbFkTableOwner, szFkTableName, cbFkTableName, conn);
    
!             sprintf(tables_query, "SELECT    pt.tgargs, "
!                 "        pt.tgnargs, "
!                 "        pt.tgdeferrable, "
!                 "        pt.tginitdeferred, "
!                 "        pp1.proname, "
                  "        pp2.proname, "
!                 "        pc.oid, "
                  "        pc1.oid, "
!                 "        pc1.relname, "
!                 "        pn.nspname "
!                 "FROM    pg_class pc, "
                  "        pg_proc pp1, "
                  "        pg_proc pp2, "
                  "        pg_trigger pt1, "
                  "        pg_trigger pt2, "
!                 "        pg_proc pp, "
!                 "        pg_trigger pt, "
!                 "        pg_class pc1, "
!                 "        pg_namespace pn "
!                 "WHERE    pt.tgrelid = pc.oid "
!                 "AND pp.oid = pt.tgfoid "
!                 "AND pt1.tgconstrrelid = pc.oid "
                  "AND pp1.oid = pt1.tgfoid "
!                 "AND pt2.tgfoid = pp2.oid "
!                 "AND pt2.tgconstrrelid = pc.oid "
!                 "AND ((pc.relname='%s') "
!                 "AND (pn.oid = pc.relnamespace) "
!                 "AND (pn.nspname = '%s') "
!                 "AND (pp.proname LIKE '%%ins') "
!                 "AND (pp1.proname LIKE '%%upd') "
!                 "AND (pp2.proname LIKE '%%del') "
!                 "AND (pt1.tgrelid=pt.tgconstrrelid) "
!                 "AND (pt1.tgconstrname=pt.tgconstrname) "
!                 "AND (pt2.tgrelid=pt.tgconstrrelid) "
!                 "AND (pt2.tgconstrname=pt.tgconstrname) "
!                 "AND (pt.tgconstrrelid=pc1.oid) "
!                 "AND (pc1.relnamespace=pn.oid))",
                  fk_table_needed, schema_needed);
          }
          else
!             sprintf(tables_query, "SELECT    pt.tgargs, "
!                 "        pt.tgnargs, "
!                 "        pt.tgdeferrable, "
!                 "        pt.tginitdeferred, "
!                 "        pp1.proname, "
                  "        pp2.proname, "
!                 "        pc.oid, "
                  "        pc1.oid, "
!                 "        pc1.relname "
!                 "FROM    pg_class pc, "
                  "        pg_proc pp1, "
                  "        pg_proc pp2, "
!                 "        pg_trigger pt1, "
                  "        pg_trigger pt2, "
!                 "        pg_proc pp, "
!                 "        pg_trigger pt, "
!                 "        pg_class pc1 "
!                 "WHERE    pt.tgrelid = pc.oid "
!                 "AND pp.oid = pt.tgfoid "
!                 "AND pt1.tgconstrrelid = pc.oid "
                  "AND pp1.oid = pt1.tgfoid "
!                 "AND pt2.tgfoid = pp2.oid "
!                 "AND pt2.tgconstrrelid = pc.oid "
!                 "AND ((pc.relname='%s') "
!                 "AND (pp.proname LIKE '%%ins') "
!                 "AND (pp1.proname LIKE '%%upd') "
!                 "AND (pp2.proname LIKE '%%del') "
!                 "AND (pt1.tgrelid=pt.tgconstrrelid) "
!                 "AND (pt1.tgconstrname=pt.tgconstrname) "
!                 "AND (pt2.tgrelid=pt.tgconstrrelid) "
!                 "AND (pt2.tgconstrname=pt.tgconstrname) "
!                 "AND (pt.tgconstrrelid=pc1.oid)) ",
                  fk_table_needed);

          result = PGAPI_ExecDirect(htbl_stmt, tables_query, strlen(tables_query));
--- 3423,3503 ----
          if (conn->schema_support)
          {
              schema_strcat(schema_needed, "%.*s", szFkTableOwner, cbFkTableOwner, szFkTableName, cbFkTableName, conn);
    
!             sprintf(tables_query,
!                 "SELECT    pt.tgargs, "
!                 "        pt1.tgnargs, "
!                 "        pt1.tgdeferrable, "
!                 "        pt1.tginitdeferred, "
                  "        pp2.proname, "
!                 "        pp3.proname, "
                  "        pc1.oid, "
!                 "        pc2.oid, "
!                 "        pc2.relname, "
!                 "        pn2.nspname "
!                 "FROM    pg_class pc1, "
!                 "        pg_class pc2, "
                  "        pg_proc pp1, "
                  "        pg_proc pp2, "
+                 "        pg_proc pp3, "
                  "        pg_trigger pt1, "
                  "        pg_trigger pt2, "
!                 "        pg_trigger pt3, "
!                 "        pg_namespace pn1, "
!                 "        pg_namespace pn2 "
!                 "WHERE    pt1.tgrelid = pc1.oid "
                  "AND pp1.oid = pt1.tgfoid "
!                 "AND pt2.tgconstrrelid = pc1.oid "
!                 "AND pp2.oid = pt2.tgfoid "
!                 "AND pt3.tgfoid = pp3.oid "
!                 "AND pt3.tgconstrrelid = pc1.oid "
!                 "AND pc1.relname='%s' "
!                 "AND pn1.oid = pc1.relnamespace "
!                 "AND pn1.nspname = '%s' "
!                 "AND pp1.proname LIKE '%%ins' "
!                 "AND pp2.proname LIKE '%%upd' "
!                 "AND pp3.proname LIKE '%%del' "
!                 "AND pt2.tgrelid=pt1.tgconstrrelid "
!                 "AND pt2.tgconstrname=pt1.tgconstrname "
!                 "AND pt3.tgrelid=pt1.tgconstrrelid "
!                 "AND pt3.tgconstrname=pt1.tgconstrname "
!                 "AND pt1.tgconstrrelid=pc2.oid "
!                 "AND pc2.relnamespace=pn2.oid ",
                  fk_table_needed, schema_needed);
          }
          else
!             sprintf(tables_query,
!                 "SELECT    pt1.tgargs, "
!                 "        pt1.tgnargs, "
!                 "        pt1.tgdeferrable, "
!                 "        pt1.tginitdeferred, "
                  "        pp2.proname, "
!                 "        pp3.proname, "
                  "        pc1.oid, "
!                 "        pc2.oid, "
!                 "        pc2.relname "
!                 "FROM    pg_class pc1, "
!                 "        pg_class pc2, "
                  "        pg_proc pp1, "
                  "        pg_proc pp2, "
!                 "        pg_proc pp3, "
!                 "        pg_trigger pt1 "
                  "        pg_trigger pt2, "
!                 "        pg_trigger pt3 "
!                 "WHERE    pt1.tgrelid = pc1.oid "
                  "AND pp1.oid = pt1.tgfoid "
!                 "AND pt2.tgconstrrelid = pc1.oid "
!                 "AND pp2.oid = pt2.tgfoid "
!                 "AND pt3.tgfoid = pp3.oid "
!                 "AND pt3.tgconstrrelid = pc1.oid "
!                 "AND pc1.relname='%s' "
!                 "AND pp1.proname LIKE '%%ins' "
!                 "AND pp2.proname LIKE '%%upd' "
!                 "AND pp3.proname LIKE '%%del' "
!                 "AND pt2.tgrelid=pt1.tgconstrrelid "
!                 "AND pt2.tgconstrname=pt1.tgconstrname "
!                 "AND pt3.tgrelid=pt1.tgconstrrelid "
!                 "AND pt3.tgconstrname=pt1.tgconstrname "
!                 "AND pt1.tgconstrrelid=pc2.oid ",
                  fk_table_needed);

          result = PGAPI_ExecDirect(htbl_stmt, tables_query, strlen(tables_query));
***************
*** 3798,3873 ****
          if (conn->schema_support)
          {
              schema_strcat(schema_needed, "%.*s", szPkTableOwner, cbPkTableOwner, szPkTableName, cbPkTableName, conn);
    
!             sprintf(tables_query, "SELECT    pt.tgargs, "
!                 "        pt.tgnargs, "
!                 "        pt.tgdeferrable, "
!                 "        pt.tginitdeferred, "
!                 "        pp.proname, "
                  "        pp1.proname, "
!                 "        pc.oid, "
                  "        pc1.oid, "
!                 "        pc1.relname, "
!                 "        pn.nspname "
!                 "FROM    pg_class pc, "
!                 "        pg_class pc1, "
                  "        pg_class pc2, "
!                 "        pg_proc pp, "
                  "        pg_proc pp1, "
!                 "        pg_trigger pt, "
                  "        pg_trigger pt1, "
                  "        pg_trigger pt2, "
!                 "        pg_namespace pn "
!                 "WHERE    pt.tgconstrrelid = pc.oid "
!                 "    AND pt.tgrelid = pc1.oid "
!                 "    AND pt1.tgfoid = pp1.oid "
!                 "    AND pt1.tgconstrrelid = pc1.oid "
                  "    AND pt2.tgconstrrelid = pc2.oid "
!                 "    AND pt2.tgfoid = pp.oid "
!                 "    AND pc2.oid = pt.tgrelid "
!                 "    AND ("
!                 "         (pc.relname='%s') "
!                 "    AND  (pn.oid = pc.relnamespace) "
!                 "    AND  (pn.nspname = '%s') "
!                 "    AND  (pp.proname Like '%%upd') "
!                 "    AND  (pp1.proname Like '%%del')"
!                 "    AND     (pt1.tgrelid = pt.tgconstrrelid) "
!                 "    AND     (pt2.tgrelid = pt.tgconstrrelid) "
!                 "    AND (pn.oid = pc1.relnamespace) "
!                 "        )",
                  pk_table_needed, schema_needed);
          }
          else
!             sprintf(tables_query, "SELECT    pt.tgargs, "
!                 "        pt.tgnargs, "
!                 "        pt.tgdeferrable, "
!                 "        pt.tginitdeferred, "
!                 "        pp.proname, "
                  "        pp1.proname, "
!                 "        pc.oid, "
                  "        pc1.oid, "
!                 "        pc1.relname "
!                 "FROM    pg_class pc, "
!                 "        pg_class pc1, "
                  "        pg_class pc2, "
!                 "        pg_proc pp, "
                  "        pg_proc pp1, "
!                 "        pg_trigger pt, "
                  "        pg_trigger pt1, "
!                 "        pg_trigger pt2 "
!                 "WHERE    pt.tgconstrrelid = pc.oid "
!                 "    AND pt.tgrelid = pc1.oid "
!                 "    AND pt1.tgfoid = pp1.oid "
!                 "    AND pt1.tgconstrrelid = pc1.oid "
                  "    AND pt2.tgconstrrelid = pc2.oid "
!                 "    AND pt2.tgfoid = pp.oid "
!                 "    AND pc2.oid = pt.tgrelid "
!                 "    AND ("
!                 "         (pc.relname='%s') "
!                 "    AND  (pp.proname Like '%%upd') "
!                 "    AND  (pp1.proname Like '%%del')"
!                 "    AND     (pt1.tgrelid = pt.tgconstrrelid) "
!                 "    AND     (pt2.tgrelid = pt.tgconstrrelid) "
!                 "        )",
                  pk_table_needed);

          result = PGAPI_ExecDirect(htbl_stmt, tables_query, strlen(tables_query));
--- 3801,3875 ----
          if (conn->schema_support)
          {
              schema_strcat(schema_needed, "%.*s", szPkTableOwner, cbPkTableOwner, szPkTableName, cbPkTableName, conn);
    
!             sprintf(tables_query,
!                 "SELECT    pt1.tgargs, "
!                 "        pt1.tgnargs, "
!                 "        pt1.tgdeferrable, "
!                 "        pt1.tginitdeferred, "
                  "        pp1.proname, "
!                 "        pp2.proname, "
                  "        pc1.oid, "
!                 "        pc2.oid, "
!                 "        pc2.relname, "
!                 "        pn2.nspname "
!                 "FROM    pg_class pc1, "
                  "        pg_class pc2, "
!                 "        pg_class pc3, "
                  "        pg_proc pp1, "
!                 "        pg_proc pp2, "
                  "        pg_trigger pt1, "
                  "        pg_trigger pt2, "
!                 "        pg_trigger pt3, "
!                 "        pg_namespace pn1, "
!                 "        pg_namespace pn2 "
!                 "WHERE    pt1.tgconstrrelid = pc1.oid "
!                 "    AND pt1.tgrelid = pc2.oid "
!                 "    AND pt2.tgfoid = pp2.oid "
                  "    AND pt2.tgconstrrelid = pc2.oid "
!                 "    AND pt3.tgconstrrelid = pc3.oid "
!                 "    AND pt3.tgfoid = pp1.oid "
!                 "    AND pc3.oid = pt1.tgrelid "
!                 "    AND pc1.relname='%s' "
!                 "    AND pn1.oid = pc1.relnamespace "
!                 "    AND pn1.nspname = '%s' "
!                 "    AND pp1.proname LIKE '%%upd' "
!                 "    AND pp2.proname LIKE '%%del'"
!                 "    AND    pt2.tgrelid = pt1.tgconstrrelid "
!                 "    AND    pt3.tgrelid = pt1.tgconstrrelid "
!                 "    AND pn2.oid = pc2.relnamespace ",
                  pk_table_needed, schema_needed);
          }
          else
!             sprintf(tables_query,
!                 "SELECT    pt1.tgargs, "
!                 "        pt1.tgnargs, "
!                 "        pt1.tgdeferrable, "
!                 "        pt1.tginitdeferred, "
                  "        pp1.proname, "
!                 "        pp2.proname, "
                  "        pc1.oid, "
!                 "        pc2.oid, "
!                 "        pc2.relname "
!                 "FROM    pg_class pc1, "
                  "        pg_class pc2, "
!                 "        pg_class pc3, "
                  "        pg_proc pp1, "
!                 "        pg_proc pp2, "
                  "        pg_trigger pt1, "
!                 "        pg_trigger pt2, "
!                 "        pg_trigger pt3 "
!                 "WHERE    pt1.tgconstrrelid = pc1.oid "
!                 "    AND pt1.tgrelid = pc2.oid "
!                 "    AND pt2.tgfoid = pp2.oid "
                  "    AND pt2.tgconstrrelid = pc2.oid "
!                 "    AND pt3.tgconstrrelid = pc3.oid "
!                 "    AND pt3.tgfoid = pp1.oid "
!                 "    AND pc3.oid = pt1.tgrelid "
!                 "    AND pc1.relname='%s' "
!                 "    AND pp1.proname Like '%%upd' "
!                 "    AND pp2.proname Like '%%del' "
!                 "    AND pt2.tgrelid = pt1.tgconstrrelid "
!                 "    AND pt3.tgrelid = pt1.tgconstrrelid ",
                  pk_table_needed);

          result = PGAPI_ExecDirect(htbl_stmt, tables_query, strlen(tables_query));