| Package home | Report new bug | New search | Development Roadmap | Status: Open | Feedback | All | Closed Since Version 0.7.0 |
| Bug #4035 | Update statements should recognize FROM clause | ||
|---|---|---|---|
| Submitted: | 2005-04-03 04:42 UTC | ||
| From: | epte at ruffdogs dot com | Assigned: | cybot |
| Status: | Closed | Package: | SQL_Parser |
| PHP Version: | Irrelevant | OS: | Irrelevant |
| Roadmaps: | 0.5.1 | ||
[2005-04-03 04: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-03 05: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 12:41 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!
[2008-07-23 15:24 UTC] User who submitted this comment has not confirmed identity
If you submitted this note, check your email.If you do not have a message, click here to re-send MANUAL CONFIRMATION IS NOT POSSIBLE. Write a message to pear-dev@lists.php.net to request the confirmation link. All bugs/comments/patches associated with this email address will be deleted within 48 hours if the account request is not confirmed!