Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1521

No way reuse parameters in custom function twice

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: 2.1.1
    • Fix Version/s: 2.x
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      sf2

      Description

      There is custom function:

      class DistanceFunction extends FunctionNode
      {
      	protected $fromLat;
      	protected $fromLng;
      	protected $toLat;
      	protected $toLng;
      
      	public function parse(\Doctrine\ORM\Query\Parser $parser)
      	{
      		$parser->match(Lexer::T_IDENTIFIER);
      		$parser->match(Lexer::T_OPEN_PARENTHESIS);
      
      		$this->fromLat = $parser->ArithmeticPrimary();
      		$parser->match(Lexer::T_COMMA);
      
      		$this->fromLng = $parser->ArithmeticPrimary();
      		$parser->match(Lexer::T_COMMA);
      
      		$this->toLat = $parser->ArithmeticPrimary();
      		$parser->match(Lexer::T_COMMA);
      
      		$this->toLng = $parser->ArithmeticPrimary();
      
      		$parser->match(Lexer::T_CLOSE_PARENTHESIS);
      	}
      
      	public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
      	{
      		$fromLat = $this->fromLat->dispatch($sqlWalker);
      		$fromLng = $this->fromLng->dispatch($sqlWalker);
      		$toLat = $this->toLat->dispatch($sqlWalker);
      		$toLng = $this->toLng->dispatch($sqlWalker);
      
      		$earthDiameterInKM = 1.609344 * 3956 * 2;
      
      		$sql = "($earthDiameterInKM * ASIN(SQRT(POWER(" .
      			"SIN(($fromLat - ABS($toLat)) * PI() / 180 / 2), 2) + " .
      			"COS($fromLat * PI() / 180) * COS(ABS($toLat) * PI() / 180) * " .
      			"POWER(SIN(($fromLng - $toLng) * PI() / 180 / 2), 2) " .
      			")))";
      
      //echo $sql;
      
      		return $sql;
      	}
      

      and the usage of it

      $em
      	->createQuery('SELECT DISTANCE(a.latitude, a.longitude, :lat, :lng) FROM Ololo:AbstractArea a WHERE a = 2')
      	->setParameter('lat', 1)
      	->setParameter('lng', 2)
      	->getResult();
      

      what this function generate:

      (12733.129728 * ASIN(SQRT(POWER(SIN((t0_.latitude - ABS(?)) * PI() / 180 / 2), 2) + COS(t0_.latitude * PI() / 180) * COS
      (ABS(?) * PI() / 180) * POWER(SIN((t0_.longitude - ?) * PI() / 180 / 2), 2) )))
      

      and exception raised: "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

      Don't know, does it affects on master branch (does they support native named parameters?).

        Activity

        Konstantin created issue -
        Guilherme Blanco made changes -
        Field Original Value New Value
        Assignee Benjamin Eberlei [ beberlei ] Guilherme Blanco [ guilhermeblanco ]
        Guilherme Blanco made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Hide
        Guilherme Blanco added a comment -

        Issue cannot be reproduced on master.
        Marking ticket as "cannot reproduce".

        Added coverage on this commit: https://github.com/doctrine/doctrine2/commit/f6eb83705adc9603a87e8d194aa7f29e8ab36ebe

        Show
        Guilherme Blanco added a comment - Issue cannot be reproduced on master. Marking ticket as "cannot reproduce". Added coverage on this commit: https://github.com/doctrine/doctrine2/commit/f6eb83705adc9603a87e8d194aa7f29e8ab36ebe
        Guilherme Blanco made changes -
        Status In Progress [ 3 ] Resolved [ 5 ]
        Fix Version/s 2.2 [ 10157 ]
        Resolution Cannot Reproduce [ 5 ]
        Hide
        Guilherme Blanco added a comment -

        Ok, it seems this is only reproduceable on MySQL and PgSQL.

        Reopening and trying a fix.

        Show
        Guilherme Blanco added a comment - Ok, it seems this is only reproduceable on MySQL and PgSQL. Reopening and trying a fix.
        Guilherme Blanco made changes -
        Resolution Cannot Reproduce [ 5 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        Guilherme Blanco made changes -
        Status Reopened [ 4 ] In Progress [ 3 ]
        Hide
        Guilherme Blanco added a comment -

        Updating fix version

        Show
        Guilherme Blanco added a comment - Updating fix version
        Guilherme Blanco made changes -
        Fix Version/s 2.x [ 10090 ]
        Fix Version/s 2.2 [ 10157 ]
        Hide
        Guilherme Blanco added a comment -

        This issue is invalid.

        Whenever you want to point to an InputParameter, you have to call the dispatch() function.
        Unfortunately, you cannot assign to a variable and reuse it freely. That way, Doctrine would only be notified once about the existence of a parameter, while actually you'd be using in multiple places.

        The funny part is that pdo_sqlite addresses the issue internally, but other drivers don't. That was why I was unable to verify the issue in the first time.

        Show
        Guilherme Blanco added a comment - This issue is invalid. Whenever you want to point to an InputParameter, you have to call the dispatch() function. Unfortunately, you cannot assign to a variable and reuse it freely. That way, Doctrine would only be notified once about the existence of a parameter, while actually you'd be using in multiple places. The funny part is that pdo_sqlite addresses the issue internally, but other drivers don't. That was why I was unable to verify the issue in the first time.
        Guilherme Blanco made changes -
        Status In Progress [ 3 ] Resolved [ 5 ]
        Resolution Invalid [ 6 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 13243 ] jira-feedback [ 15134 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 15134 ] jira-feedback2 [ 16998 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 16998 ] jira-feedback3 [ 19251 ]

        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=DDC-1521, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Konstantin
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: