Re: bug in info.c file - incorrect SQL - Mailing list pgsql-odbc
| From | Bruce Momjian |
|---|---|
| Subject | Re: bug in info.c file - incorrect SQL |
| Date | |
| Msg-id | 200301032117.h03LHCX10106@candle.pha.pa.us Whole thread Raw |
| In response to | Re: bug in info.c file - incorrect SQL ("Hiroshi Inoue" <Inoue@tpf.co.jp>) |
| Responses |
Re: bug in info.c file - incorrect SQL
Re: bug in info.c file - incorrect SQL |
| List | pgsql-odbc |
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));
pgsql-odbc by date: