Ok, I issued a bug report… Hope this gets fixed soon J
Shehi
From: Hector Virgen [mailto:djvirgen@gmail.com]
Sent: Friday, May 28, 2010 7:16 PM
To: Shehi
Cc: fw-db@lists.zend.com
Subject: Re: [fw-db] Statement containing both parentheses and AS clause
I believe it's best practice to explicitly wrap all SQL expressions in a Zend_Db_Expr object to help reduce the chance of Zend_Db incorrectly quoting it, including field identifiers.
However, you are right about the auto-detection of expressions. I'm looking at ZF 1.8 source (sorry, don't have a more recent ZF on me at the moment) and Zend_Db_Select#_tableCols() is looking for "AS" before checking if it looks like an expression:
# Zend/Db/Select.php:895
if (is_string($col)) {
// Check for a column matching "<column> AS <alias>" and extract the alias name
if (preg_match('/^(.+)\s+' . self::SQL_AS . '\s+(.+)$/i', $col, $m)) {
$col = $m[1];
$alias = $m[2];
}
// Check for columns that look like functions and convert to Zend_Db_Expr
if (preg_match('/\(.*\)/', $col)) {
$col = new Zend_Db_Expr($col);
} elseif (preg_match('/(.+)\.(.+)/', $col, $m)) {
$currentCorrelationName = $m[1];
$col = $m[2];
}
}
If this problem still exists in the latest version of ZF, please file a bug report:
--
Hector
On Fri, May 28, 2010 at 7:05 AM, Shehi <shehi@imanov.name> wrote:
Example SELECT statement is like this:
SELECT CAST(column_name AS UNSIGNED)
FROM table1
Code I used was:
1. $select = $db->select()
2. ->from(array('table1'),
3. array('CAST(column_name AS UNSIGNED)',
4. );
which did not work - error returned, was: Could not find "table1.CAST(column_name" field. As we know, if there is a parentheses inside the clause, it is considered to be a Zend_Db_Expr. But on the other hand, the existence of "AS" clause messes things up, making it think we have a field-name-alias here, which we don't. Its just CAST function's syntax. Of course I overcame the problem by explicitly telling it we are having Zend_Db_Expr with this code which worked:
1. $select = $db->select()
2. ->from(array('table1'),
3. array('column_name' => new Zend_Db_Expr('CAST(column_name AS UNSIGNED)'))
4. );
Question for developers of ZF is: Shouldn't you guys not touch any clauses, including AS clause, inside the statement which contains parentheses - the same way you do with Zend_Db_Expr objects? Statements with parentheses are considered to be Zend_Db_Expr objects, but at the same time they are not...
Shehi
没有评论:
发表评论