Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-398

Native query does not allow mysql assignment operator :=

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.3
    • Fix Version/s: 2.3.2
    • Component/s: None
    • Security Level: All
    • Labels:
    • Environment:
      Using Doctrine within Symfony 2.1.x

      Description

      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 FROM
      (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).

        Activity

        David Ward created issue -
        David Ward made changes -
        Field Original Value New Value
        Description 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 FROM
          (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).
        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 FROM
            (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).
        Hide
        David Ward added a comment -

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

        Show
        David Ward added a comment - A pull request has been added at https://github.com/doctrine/dbal/pull/237 which also has tests added.
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.3.2 [ 10326 ]
        Resolution Fixed [ 1 ]
        Hide
        Bryson Armstrong added a comment - - edited

        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] != '=')) {
        

        to:

        if ($statement[$i] == $match && !$inLiteral && ($isPositional || $statement[$i+1] != '=')) {
        
        Show
        Bryson Armstrong added a comment - - edited 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] != '=')) { to: if ($statement[$i] == $match && !$inLiteral && ($isPositional || $statement[$i+1] != '=')) {

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-398, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            David Ward
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: