Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 0.6.0

Bug #4035 Update statements should recognize FROM clause
Submitted: 2005-04-02 22:42 UTC Modified: 2008-07-23 10:24 UTC
From: epte at ruffdogs dot com Assigned: cybot
Status: Closed Package: SQL_Parser
PHP Version: Irrelevant OS: Irrelevant
Roadmaps: 0.5.1    
Subscription  


 [2005-04-02 22:42 UTC] epte at ruffdogs dot com
Description: ------------ Some valid UPDATE statements have from clauses to set the table (or join) that the source data is coming from (that you're using to populate whatever it is that you're updating). (Should it error if the FROM clause isn't used? Assuming no...) Reproduce code: --------------- UPDATE tblTicklerPatientData SET tblTicklerPatientData.final_diagnosis = ptc.path_diagnosis_codes FROM tblTicklerPatientData INNER JOIN `PatientTest(Case)` as ptc ON tblTicklerPatientData.PatientTest_key = ptc.PatientTest_key WHERE ptc.path_diagnosis_codes Is Not Null and tblTicklerPatientData.final_diagnosis is null'; $_SQLParser = new SQL_Parser(NULL, 'MySQL'); $parseRes = $_SQLParser->parse($sql); Actual result: -------------- Parse error: Expected "where" or "," on line 4 FROM ^ found: "FROM"

Comments

 [2005-04-02 23:00 UTC] epte at ruffdogs dot com
The following patch seems to fix this portion, but it is hard to tell until I get the other issues worked out of this query. More to come. Index: Parser.php =================================================================== RCS file: /repository/pear/SQL_Parser/Parser.php,v retrieving revision 1.27 diff -u -r1.27 Parser.php --- Parser.php 3 Jul 2004 04:49:44 -0000 1.27 +++ Parser.php 3 Apr 2005 04:59:20 -0000 @@ -143,6 +143,7 @@ // {{{ isVal() function isVal() { return (($this->token == 'real_val') || + ($this->token == 'function') || ($this->token == 'int_val') || ($this->token == 'text_val') || ($this->token == 'null')); @@ -766,12 +767,25 @@ return $this->raiseError('Expected ='); } $this->getTok(); - if (!$this->isVal($this->token)) { - return $this->raiseError('Expected a value'); + if (!$this->isVal($this->token) && $this->token != 'ident') { + return $this->raiseError('Expected a value or column name'); } $tree['values'][] = array('value'=>$this->lexer->tokText, 'type'=>$this->token); $this->getTok(); + if ($this->token == ',') { + continue; + } + + if ($this->token == 'from') { + $clause = $this->parseFromClause(); + if (PEAR::isError($clause)) { + return $clause; + } + $tree = array_merge($tree, $clause); + $this->getTok(); + } + if ($this->token == 'where') { $clause = $this->parseSearchClause(); if (PEAR::isError($clause)) { @@ -780,7 +794,7 @@ $tree['where_clause'] = $clause; break; } elseif ($this->token != ',') { - return $this->raiseError('Expected "where" or ","'); + return $this->raiseError('Expected "where"'); } } return $tree; @@ -936,10 +950,97 @@ } else { return $this->raiseError('Expected columns or a set function'); } - if ($this->token != 'from') { + if ($this->token == 'from') { + $clause = $this->parseFromClause(); + if (PEAR::isError($clause)) { + return $clause; + } + $tree = array_merge($tree, $clause); + $this->getTok(); + } else { return $this->raiseError('Expected "from"'); } + while (!is_null($this->token) && (!$subSelect || $this->token != ')') + && $this->token != ')') { + switch ($this->token) { + case 'where': + $clause = $this->parseSearchClause(); + if (PEAR::isError($clause)) { + return $clause; + } + $tree['where_clause'] = $clause; + break; + case 'order': + $this->getTok(); + if ($this->token != 'by') { + return $this->raiseError('Expected "by"'); + } + $this->getTok(); + while ($this->token == 'ident') { + $col = $this->lexer->tokText; + $this->getTok(); + if (isset($this->synonyms[$this->token])) { + $order = $this->synonyms[$this->token]; + if (($order != 'asc') && ($order != 'desc')) { + return $this->raiseError('Unexpected token'); + } + $this->getTok(); + } else { + $order = 'asc'; + } + if ($this->token == ',') { + $this->getTok(); + } + $tree['sort_order'][$col] = $order; + } + break; + case 'limit': + $this->getTok(); + if ($this->token != 'int_val') { + return $this->raiseError('Expected an integer value'); + } + $length = $this->lexer->tokText; + $start = 0; + $this->getTok(); + if ($this->token == ',') { + $this->getTok(); + if ($this->token != 'int_val') { + return $this->raiseError('Expected an integer value'); + } + $start = $length; + $length = $this->lexer->tokText; + $this->getTok(); + } + $tree['limit_clause'] = array('start'=>$start, + 'length'=>$length); + break; + case 'group': + $this->getTok(); + if ($this->token != 'by') { + return $this->raiseError('Expected "by"'); + } + $this->getTok(); + while ($this->token == 'ident') { + $col = $this->lexer->tokText; + $this->getTok(); + if ($this->token == ',') { + $this->getTok(); + } + $tree['group_by'][] = $col; + } + break; + default: + return $this->raiseError('Unexpected clause'); + } + } + return $tree; + } + // }}} + + // {{{ parseFromClause() + function parseFromClause() { $this->getTok(); + $tree = array(); while ($this->token == 'ident') { $tree['table_names'][] = $this->lexer->tokText; $this->getTok(); @@ -1032,83 +1133,9 @@ break; } } - while (!is_null($this->token) && (!$subSelect || $this->token != ')') - && $this->token != ')') { - switch ($this->token) { - case 'where': - $clause = $this->parseSearchClause(); - if (PEAR::isError($clause)) { - return $clause; - } - $tree['where_clause'] = $clause; - break; - case 'order': - $this->getTok(); - if ($this->token != 'by') { - return $this->raiseError('Expected "by"'); - } - $this->getTok(); - while ($this->token == 'ident') { - $col = $this->lexer->tokText; - $this->getTok(); - if (isset($this->synonyms[$this->token])) { - $order = $this->synonyms[$this->token]; - if (($order != 'asc') && ($order != 'desc')) { - return $this->raiseError('Unexpected token'); - } - $this->getTok(); - } else { - $order = 'asc'; - } - if ($this->token == ',') { - $this->getTok(); - } - $tree['sort_order'][$col] = $order; - } - break; - case 'limit': - $this->getTok(); - if ($this->token != 'int_val') { - return $this->raiseError('Expected an integer value'); - } - $length = $this->lexer->tokText; - $start = 0; - $this->getTok(); - if ($this->token == ',') { - $this->getTok(); - if ($this->token != 'int_val') { - return $this->raiseError('Expected an integer value'); - } - $start = $length; - $length = $this->lexer->tokText; - $this->getTok(); - } - $tree['limit_clause'] = array('start'=>$start, - 'length'=>$length); - break; - case 'group': - $this->getTok(); - if ($this->token != 'by') { - return $this->raiseError('Expected "by"'); - } - $this->getTok(); - while ($this->token == 'ident') { - $col = $this->lexer->tokText; - $this->getTok(); - if ($this->token == ',') { - $this->getTok(); - } - $tree['group_by'][] = $col; - } - break; - default: - return $this->raiseError('Unexpected clause'); - } - } return $tree; } - // }}} - + // {{{ parse($string) function parse($string = null) {
 [2008-04-01 07:41 UTC] doconnor (Daniel O'Connor)
http://dev.mysql.com/doc/refman/5.0/en/update.html The example query doesn't look valid, and I can't find anything in the manual which indicates it is valid... What have I missed?
 [2008-07-23 10:24 UTC] cybot (Sebastian Mendel)
This bug has been fixed in CVS. If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET). If this was a problem with the pear.php.net website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better.