Bug in BasePeer::createSelectSql

[serg@…]

Invalid sql query generated if in select column used more then one function. For example:

$c = new Criteria(); $c->setDistinct();
$c->addSelectColumn( sprintf( "SUBSTRING_INDEX(SUBSTRING_INDEX((%s),'-',$pos),'-',-1) AS col", self::PREFIX ) );
$rs = self::doSelectRS( $c );

produced wrong statement and throw exception:

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX((fe_dip_project_type.PREFIX),'-',1),'-',-1) AS col 
FROM SUBSTRING_INDEX((fe_dip_project_type, fe_dip_project_type 
WHERE fe_dip_project_type.IS_DELETED=0 

to fix this replace strpos function with strrpos function in /pear/symfony/vendor/propel/util/BasePeer.php on line 579

...
577 $selectClause[] = $columnName; // the full column name: e.g. MAX(books.price)
578
579 $parenPos = strrpos($columnName, '(');
580 $dotPos = strpos($columnName, '.', ($parenPos !== false) ? $parenPos : 0);
...

after this fix all work fine:

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX((fe_dip_project_type.PREFIX),'-',1),'-',-1) AS col 
FROM fe_dip_project_type 
WHERE fe_dip_project_type.IS_DELETED=0

[hans]

(In [818]) Refs #425 - Added unit test for nested functions.

[hans]

(In [818]) Refs #425 - Added unit test for nested functions.

[hans]

In r818 I added a unit test that does pass (on postgres) with current BasePeer code, so this fix will actually not work for the various situations in which there can be nested functions. Unfortunately, there's not a good solution for this; there's no way to know exactly what the column is vs. other text within the clause. This would be a case for using custom SQL instead of Criteria.

So, resolving "wontfix", though "cantfix" is more accurate.

[francois]

r916 actually applies the strrpos modification. But the BasePeerTest::testMultipleFunctionInCriteria() test does not pass with Postgre as of Propel 1.5. Somehow a regression occurred, that should be looked for.

[matthew]

Per François, tickets should be in 'To be scheduled'.