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:

http://framework.zend.com/issues

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