DDC-1521: No way reuse parameters in custom function twice #2153

Closed
doctrinebot opened this Issue Dec 7, 2011 · 5 comments

2 participants

@doctrinebot

Jira issue originally created by user koc:

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) <ins> " .
            "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) </ins> 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?).

@doctrinebot

Comment created by @guilhermeblanco:

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

Added coverage on this commit: f6eb837

@doctrinebot

Comment created by @guilhermeblanco:

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

Reopening and trying a fix.

@doctrinebot

Comment created by @guilhermeblanco:

Updating fix version

@doctrinebot

Comment created by @guilhermeblanco:

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.

@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot doctrinebot added this to the 2.x milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment