[DBAL-398] Native query does not allow mysql assignment operator := Created: 18/Dec/12  Updated: 03/Jan/13  Resolved: 22/Dec/12

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.3
Fix Version/s: 2.3.2
Security Level: All

Type: Bug Priority: Major
Reporter: David Ward Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: mysql

Using Doctrine within Symfony 2.1.x

Attachments: Text File FixNativeAssignmentOperator.patch    


When trying to use the mysql assignment operator in a native query one gets an exception as SqlParserUtils does not qualify the character after the : as being part of a valid parameter value.

Undefined index: in vendor/doctrine/dbal/lib/Doctrine/DBAL/SQLParserUtils.php line 156 (uncaught exception)

A simple example is
$rsm = new ResultSetMapping();
$rsm->addScalarResult('rank', 'rank');
$qry = $em->createNativeQuery("
SELECT (@rank := 1) AS rank
$result = $qry->getResult(Query::HYDRATE_ARRAY);

Or a more complicated example is (similar to actual use):

$rsm = new ResultSetMapping();
$rsm->addScalarResult('rank', 'rank');
$qry = $em->createNativeQuery("
(SELECT (@rank := @rank +1) AS rank FROM (SELECT @rank :=0) rnk2) rnk1
$result = $qry->getResult(Query::HYDRATE_ARRAY);

I have attached quick-fix patch, but it looks like the getPlaceholderPositions method is wanting something better overall (due to the TODO comment in it).

Comment by David Ward [ 18/Dec/12 ]

A pull request has been added at https://github.com/doctrine/dbal/pull/237 which also has tests added.

Comment by Bryson Armstrong [ 03/Jan/13 ]

I ran into this error and the fix caused other queries to have errors.

I fixed it by changing line 57 in vendor/doctrine/dbal/lib/Doctrine/DBAL/SQLParserUtils.php:

if ($statement[$i] == $match && !$inLiteral && (!$isPositional && $statement[$i+1] != '=')) {


if ($statement[$i] == $match && !$inLiteral && ($isPositional || $statement[$i+1] != '=')) {
Generated at Mon Nov 30 12:05:33 EST 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.