Home » Database » SQL_Parser » Bug #4035

[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"

[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)
{