[DDC-1521] No way reuse parameters in custom function twice Created: 07/Dec/11  Updated: 16/Jan/12  Resolved: 16/Jan/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.1.1
Fix Version/s: 2.x
Security Level: All

Type: Bug Priority: Major
Reporter: Konstantin Assignee: Guilherme Blanco
Resolution: Invalid Votes: 1
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?).



 Comments   
Comment by Guilherme Blanco [ 20/Dec/11 ]

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

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

Comment by Guilherme Blanco [ 20/Dec/11 ]

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

Reopening and trying a fix.

Comment by Guilherme Blanco [ 20/Dec/11 ]

Updating fix version

Comment by Guilherme Blanco [ 16/Jan/12 ]

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.

Generated at Sat Apr 19 22:37:25 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.