#20143 [NEW]: dbh->limitQuery dies in oci8.php because of sql error

From: aprasad@nhgri.nih.gov Operating system: Solaris PHP version: 4.2.3 PHP Bug Type: PEAR related Bug description: dbh->limitQuery dies in oci8.php because of sql error When running a limitQuery like: $query = $dbh->limitQuery('select t1.id, t2.id from t1,t2', 1, 2); echo $query->getDebugInfo(); You get the Warning: OCIStmtExecute: ORA-00918: column ambiguously defined in /usr/local/lib/php/DB/oci8.php on line 532 returns Object [nativecode=ORA-00900: invalid SQL statement] This appears to me to be because the sql query generated on line 528: $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL"; (SELECT * FROM (select t1.id, t2.id from t1, t2) where NULL=NULL" ) Does not compile for Oracle. I'm not sure why you have to use a sub-query here and not just use the query passed in to get the column names. ie. $q_fields = $query; If you make it past this problem you later have the problem of trying to execute: $query = "SELECT $fields FROM". " (SELECT rownum as linenum, $fields FROM". " ($query)". " WHERE rownum <= ". ($from + $count) . ") WHERE linenum >= $from"; which will fail also because $fields will contain id, id I haven't done a lot of PHP or Oracle development before, and I couldn't think up a solution to this problem that doesn't involve lots of query parsing which probably should be avoided if possible. For now I've modified the code to return an error if there are column names returned from OCIColumName() that are the same. At the bottom of this message is the context diff for my very poorly written added warning which is not production ready. I needed something fast. It uses a redmsg() function that I've defined elsewhere because I have no idea how to do error handling. I've only been writing PHP for about two weeks so it is extremely ugly. (Whew, that was a lot of disclaimers) If someone comes up with a good fix I would be interested in seeing it. Thanks, Arjun Prasad *** oci8.php.orig Mon Oct 28 20:47:14 2002 --- oci8.php Mon Oct 28 20:56:27 2002 *************** *** 526,533 **** // Let Oracle return the name of the columns instead of // coding a "home" SQL parser ! $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL"; ! if (!$result = OCIParse($this->connection, $q_fields)) { return $this->oci8RaiseError(); } if (!OCIExecute($result, OCI_DEFAULT)) { --- 526,536 ---- // Let Oracle return the name of the columns instead of // coding a "home" SQL parser ! // BUG FIX: modification of the query unnecessary here ! // -- Arjun Prasad <aprasad@nhgri.nih.gov> ! // $q_fields = "SELECT * FROM ($query) WHERE NULL = NULL"; ! // if (!$result = OCIParse($this->connection, $q_fields)) { ! if (!$result = OCIParse($this->connection, $query)) { return $this->oci8RaiseError(); } if (!OCIExecute($result, OCI_DEFAULT)) { *************** *** 538,543 **** --- 541,566 ---- for ( $i = 1; $i <= $ncols; $i++ ) { $cols[] = OCIColumnName($result, $i); } + // BUG alert: check to make sure no column names are the + // same if they are then report error. + $dup_cols = array(); + for ($i = 0; $i < $ncols; $i++) { + for ($n = $i + 1; $n < $ncols; $n++) { + if ($cols[$i] == $cols[$n]) { + $dup_cols[] = $cols[$i]; + } + } + } + if (sizeof($dup_cols) > 0) { + $col_list = implode(', ', $dup_cols); + redmsg("Error: returned duplicate columns ($col_list). " + ."Modify query to give each returned column a unique name."); + } + // now just let it fail. (This should be only a temporary solution) + // END BUG "alert" -- Arjun Prasad <aprasad@nhgri.nih.gov> + + + $fields = implode(', ', $cols); // XXX Test that (tip by John Lim) //if(preg_match('/^\s*SELECT\s+/is', $query, $match)) { -- Edit bug report at http://bugs.php.net/?id=20143&edit=1 -- Try a CVS snapshot: http://bugs.php.net/fix.php?id=20143&r=trysnapshot Fixed in CVS: http://bugs.php.net/fix.php?id=20143&r=fixedcvs Fixed in release: http://bugs.php.net/fix.php?id=20143&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=20143&r=needtrace Try newer version: http://bugs.php.net/fix.php?id=20143&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=20143&r=support Expected behavior: http://bugs.php.net/fix.php?id=20143&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=20143&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=20143&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=20143&r=globals PHP 3 support discontinued: http://bugs.php.net/fix.php?id=20143&r=php3 Daylight Savings: http://bugs.php.net/fix.php?id=20143&r=dst IIS Stability: http://bugs.php.net/fix.php?id=20143&r=isapi