ExpressionBuilder::between() returns string instead of Expression/Function #5541

Open
opened 2026-01-22 15:10:37 +01:00 by admin · 12 comments
Owner

Originally created by @holtkamp on GitHub (May 18, 2017).

The PHPDoc suggests that a BETWEEN Expression is returned of type Expr\Func, while it is a plain string. Is this intended behaviour?

It seems this results in problems when using it in other Expressions like andX and orX. Will try to come up with a test case.

Originally created by @holtkamp on GitHub (May 18, 2017). The [PHPDoc](https://github.com/doctrine/doctrine2/blob/334b7e68a7d14443962eff5452f1c7dfe92eff17/lib/Doctrine/ORM/Query/Expr.php#L632-L637) suggests that a `BETWEEN` Expression is returned of type `Expr\Func`, while it is a plain string. Is this intended behaviour? It seems this results in problems when using it in other Expressions like `andX` and `orX`. Will try to come up with a test case.
Author
Owner

@lcobucci commented on GitHub (May 18, 2017):

@holtkamp thanks.

It seems to be a docblock error since we have strings for isNull() and isNotNull(). Also we won't have any issue if the expression is a string: 334b7e68a7/lib/Doctrine/ORM/Query/Expr/Base.php (L91)

@lcobucci commented on GitHub (May 18, 2017): @holtkamp thanks. It seems to be a docblock error since we have strings for `isNull()` and `isNotNull()`. Also we won't have any issue if the expression is a string: https://github.com/doctrine/doctrine2/blob/334b7e68a7d14443962eff5452f1c7dfe92eff17/lib/Doctrine/ORM/Query/Expr/Base.php#L91
Author
Owner

@holtkamp commented on GitHub (May 18, 2017):

@lcobucci thanks for the swift response.

For me it results in an error when using something like:

$queryBuilder->andWhere(
   $expressionBuilder->orX(
       $expressionBuilder->isNull('entity.someProperty'),
       $expressionBuilder->between('entity.someProperty', 1, 10),
   );
);

When assembling the SQL, this results in an exception:

[Syntax Error] line 0, col 1039: Error: Expected =, <, <=, <>, >, >=, !=, got 'BETWEEN'
Stack trace:

#0 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(448): Doctrine\ORM\Query\QueryException::syntaxError('line 0, col 103...', Object(Doctrine\ORM\Query\QueryException))
#1 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(3325): Doctrine\ORM\Query\Parser->syntaxError('=, <, <=, <>, >...')
#2 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(3040): Doctrine\ORM\Query\Parser->ComparisonOperator()
#3 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2564): Doctrine\ORM\Query\Parser->ComparisonExpression()
#4 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2447): Doctrine\ORM\Query\Parser->SimpleConditionalExpression()
#5 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2423): Doctrine\ORM\Query\Parser->ConditionalPrimary()
#6 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2391): Doctrine\ORM\Query\Parser->ConditionalFactor()
#7 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2366): Doctrine\ORM\Query\Parser->ConditionalTerm()
#8 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2464): Doctrine\ORM\Query\Parser->ConditionalExpression()
#9 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2423): Doctrine\ORM\Query\Parser->ConditionalPrimary()
#10 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2396): Doctrine\ORM\Query\Parser->ConditionalFactor()
#11 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2366): Doctrine\ORM\Query\Parser->ConditionalTerm()
#12 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1333): Doctrine\ORM\Query\Parser->ConditionalExpression()
#13 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(876): Doctrine\ORM\Query\Parser->WhereClause()
#14 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(843): Doctrine\ORM\Query\Parser->SelectStatement()
#15 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(268): Doctrine\ORM\Query\Parser->QueryLanguage()
#16 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(351): Doctrine\ORM\Query\Parser->getAST()
#17 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(281): Doctrine\ORM\Query\Parser->parse()
#18 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(205): Doctrine\ORM\Query->_parse()
#19 /X/Y/Z/Dao.php: Doctrine\ORM\Query->getSQL()
@holtkamp commented on GitHub (May 18, 2017): @lcobucci thanks for the swift response. For me it results in an error when using something like: ```php $queryBuilder->andWhere( $expressionBuilder->orX( $expressionBuilder->isNull('entity.someProperty'), $expressionBuilder->between('entity.someProperty', 1, 10), ); ); ``` When assembling the SQL, this results in an exception: ``` [Syntax Error] line 0, col 1039: Error: Expected =, <, <=, <>, >, >=, !=, got 'BETWEEN' Stack trace: #0 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(448): Doctrine\ORM\Query\QueryException::syntaxError('line 0, col 103...', Object(Doctrine\ORM\Query\QueryException)) #1 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(3325): Doctrine\ORM\Query\Parser->syntaxError('=, <, <=, <>, >...') #2 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(3040): Doctrine\ORM\Query\Parser->ComparisonOperator() #3 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2564): Doctrine\ORM\Query\Parser->ComparisonExpression() #4 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2447): Doctrine\ORM\Query\Parser->SimpleConditionalExpression() #5 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2423): Doctrine\ORM\Query\Parser->ConditionalPrimary() #6 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2391): Doctrine\ORM\Query\Parser->ConditionalFactor() #7 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2366): Doctrine\ORM\Query\Parser->ConditionalTerm() #8 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2464): Doctrine\ORM\Query\Parser->ConditionalExpression() #9 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2423): Doctrine\ORM\Query\Parser->ConditionalPrimary() #10 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2396): Doctrine\ORM\Query\Parser->ConditionalFactor() #11 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(2366): Doctrine\ORM\Query\Parser->ConditionalTerm() #12 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(1333): Doctrine\ORM\Query\Parser->ConditionalExpression() #13 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(876): Doctrine\ORM\Query\Parser->WhereClause() #14 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(843): Doctrine\ORM\Query\Parser->SelectStatement() #15 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(268): Doctrine\ORM\Query\Parser->QueryLanguage() #16 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php(351): Doctrine\ORM\Query\Parser->getAST() #17 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(281): Doctrine\ORM\Query\Parser->parse() #18 /app/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(205): Doctrine\ORM\Query->_parse() #19 /X/Y/Z/Dao.php: Doctrine\ORM\Query->getSQL() ```
Author
Owner

@lcobucci commented on GitHub (May 19, 2017):

@holtkamp I've just tried to reproduce that on a test case (on master) and that doesn't happen...

Mapping:

/**
 * @Entity
 */
class GH6450Person
{
    /**
     * @Id
     * @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     */
    public $id;

    /**
     * @Column(type="integer", nullable=true)
     *
     * @var int
     */
    public $age;

    public function __construct(?int $age)
    {
        $this->age = $age;
    }
}

Test:

public function testBetweenQuery(): void
{
    $this->_em->persist(new GH6450Person(null));
    $this->_em->persist(new GH6450Person(10));
    $this->_em->persist(new GH6450Person(18));
    $this->_em->persist(new GH6450Person(25));
    $this->_em->persist(new GH6450Person(50));
    $this->_em->flush();

    $queryBuilder = $this->_em->getRepository(GH6450Person::class)
                              ->createQueryBuilder('person');

    $expressionBuilder = $queryBuilder->expr();

    $queryBuilder->andWhere(
        $expressionBuilder->orX(
            $expressionBuilder->isNull('person.age'),
            $expressionBuilder->between('person.age', 12, 30)
        )
    );

    self::assertCount(3, $queryBuilder->getQuery()->getResult());
}

Am I doing something different than you?

@lcobucci commented on GitHub (May 19, 2017): @holtkamp I've just tried to reproduce that on a test case (on `master`) and that doesn't happen... Mapping: ```php /** * @Entity */ class GH6450Person { /** * @Id * @Column(type="integer") * @GeneratedValue(strategy="AUTO") */ public $id; /** * @Column(type="integer", nullable=true) * * @var int */ public $age; public function __construct(?int $age) { $this->age = $age; } } ``` Test: ```php public function testBetweenQuery(): void { $this->_em->persist(new GH6450Person(null)); $this->_em->persist(new GH6450Person(10)); $this->_em->persist(new GH6450Person(18)); $this->_em->persist(new GH6450Person(25)); $this->_em->persist(new GH6450Person(50)); $this->_em->flush(); $queryBuilder = $this->_em->getRepository(GH6450Person::class) ->createQueryBuilder('person'); $expressionBuilder = $queryBuilder->expr(); $queryBuilder->andWhere( $expressionBuilder->orX( $expressionBuilder->isNull('person.age'), $expressionBuilder->between('person.age', 12, 30) ) ); self::assertCount(3, $queryBuilder->getQuery()->getResult()); } ``` Am I doing something different than you?
Author
Owner

@holtkamp commented on GitHub (May 22, 2017):

@lcobucci you are right, with such an isolated case the problem does not occur, I tried to pinpoint the problem and it seems that something like this fails on 2.5.6:

$queryBuilder->andWhere(
    $expressionBuilder->between(20, 12, 30)
);

//Or with a custom DQL function that returns the WeekDay number
$queryBuilder->andWhere(
    $expressionBuilder->between('WeekDay(CURRENT_TIMESTAMP()) + 1', 12, 30)
);

The query I am building checks whether a provided weekday is within a range, so:

$queryBuilder->andWhere(
    $expressionBuilder->between($weekday, 1, 7)
);

Where $weekday is a string and either:

  • WeekDay(CURRENT_TIMESTAMP()) + 1 => default: the current weekday
  • WeekDay(:weekdayParameter) => specific, a specific date is used as parameter

However, this succeeds:

$queryBuilder->andWhere(
    $expressionBuilder->between('CURRENT_TIMESTAMP()', 12, 30)
);

So it has to do with the first parameters...

@holtkamp commented on GitHub (May 22, 2017): @lcobucci you are right, with such an isolated case the problem does not occur, I tried to pinpoint the problem and it seems that something like this fails on `2.5.6`: ```php $queryBuilder->andWhere( $expressionBuilder->between(20, 12, 30) ); //Or with a custom DQL function that returns the WeekDay number $queryBuilder->andWhere( $expressionBuilder->between('WeekDay(CURRENT_TIMESTAMP()) + 1', 12, 30) ); ``` The query I am building checks whether a provided weekday is within a range, so: ```php $queryBuilder->andWhere( $expressionBuilder->between($weekday, 1, 7) ); ``` Where `$weekday` is a string and either: - `WeekDay(CURRENT_TIMESTAMP()) + 1` => default: the current weekday - `WeekDay(:weekdayParameter)` => specific, a specific date is used as parameter However, this succeeds: ```php $queryBuilder->andWhere( $expressionBuilder->between('CURRENT_TIMESTAMP()', 12, 30) ); ``` So it has to do with the first parameters...
Author
Owner

@holtkamp commented on GitHub (May 23, 2017):

@lcobucci can you push that testcase to master or a branch on your fork so I can fiddle with it as well?

@holtkamp commented on GitHub (May 23, 2017): @lcobucci can you push that testcase to master or a branch on your fork so I can fiddle with it as well?
Author
Owner

@holtkamp commented on GitHub (Jun 24, 2017):

@lcobucci I had a look and came up with a failing test. For the second scenario depicted below, DQL generation seems to succeed and SQL generation fails on the current master branch.

//Doctrine\Tests\ORM\QueryBuilderTest.php
public function testBetween()
{
        $qb = $this->_em->createQueryBuilder();
        $qb->select('g')
            ->from(CmsGroup::class, 'g')
            ->where($qb->expr()->between('g.id', 1, 10));
        $this->assertValidQueryBuilder($qb, 'SELECT g FROM Doctrine\Tests\Models\CMS\CmsGroup g WHERE g.id BETWEEN 1 AND 10');
        $this->assertEquals($qb->getQuery()->getSQL(), 'SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_groups c0_ WHERE c0_.id BETWEEN 1 AND 10');

        $qb = $this->_em->createQueryBuilder();
        $qb->select('g')
            ->from(CmsGroup::class, 'g')
            ->where($qb->expr()->between(5, 1, 10));
        $this->assertValidQueryBuilder($qb, 'SELECT g FROM Doctrine\Tests\Models\CMS\CmsGroup g WHERE 5 BETWEEN 1 AND 10');
        $this->assertEquals($qb->getQuery()->getSQL(), 'SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_groups c0_ WHERE 5 BETWEEN 1 AND 10');
}

Results in

1) Doctrine\Tests\ORM\QueryBuilderTest::testBetween
Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 59: Error: Expected =, <, <=, <>, >, >=, !=, got 'BETWEEN'
@holtkamp commented on GitHub (Jun 24, 2017): @lcobucci I had a look and came up with a failing test. For the second scenario depicted below, DQL generation seems to succeed and SQL generation fails on the current `master` branch. ```php //Doctrine\Tests\ORM\QueryBuilderTest.php public function testBetween() { $qb = $this->_em->createQueryBuilder(); $qb->select('g') ->from(CmsGroup::class, 'g') ->where($qb->expr()->between('g.id', 1, 10)); $this->assertValidQueryBuilder($qb, 'SELECT g FROM Doctrine\Tests\Models\CMS\CmsGroup g WHERE g.id BETWEEN 1 AND 10'); $this->assertEquals($qb->getQuery()->getSQL(), 'SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_groups c0_ WHERE c0_.id BETWEEN 1 AND 10'); $qb = $this->_em->createQueryBuilder(); $qb->select('g') ->from(CmsGroup::class, 'g') ->where($qb->expr()->between(5, 1, 10)); $this->assertValidQueryBuilder($qb, 'SELECT g FROM Doctrine\Tests\Models\CMS\CmsGroup g WHERE 5 BETWEEN 1 AND 10'); $this->assertEquals($qb->getQuery()->getSQL(), 'SELECT c0_.id AS id_0, c0_.name AS name_1 FROM cms_groups c0_ WHERE 5 BETWEEN 1 AND 10'); } ``` Results in ``` 1) Doctrine\Tests\ORM\QueryBuilderTest::testBetween Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 59: Error: Expected =, <, <=, <>, >, >=, !=, got 'BETWEEN' ```
Author
Owner

@holtkamp commented on GitHub (Jul 12, 2017):

@lcobucci anything else I can do to help on this issue? My knowledge is too limited to come up with a PR to suggest a fix, but if someone gives me a pointer I can give it a try...

@holtkamp commented on GitHub (Jul 12, 2017): @lcobucci anything else I can do to help on this issue? My knowledge is too limited to come up with a PR to suggest a fix, but if someone gives me a pointer I can give it a try...
Author
Owner

@lcobucci commented on GitHub (Jul 22, 2017):

@holtkamp sorry for not getting back to you, let's start PR with a failing test case (based on master) and we try to fix it there. Can you please send it?

@lcobucci commented on GitHub (Jul 22, 2017): @holtkamp sorry for not getting back to you, let's start PR with a failing test case (based on `master`) and we try to fix it there. Can you please send it?
Author
Owner

@holtkamp commented on GitHub (Sep 19, 2017):

@lcobucci FYI: I added the requested PR some time ago, hopefully this properly points out the issue...

@holtkamp commented on GitHub (Sep 19, 2017): @lcobucci FYI: I added the requested PR some time ago, hopefully this properly points out the issue...
Author
Owner

@lcobucci commented on GitHub (Sep 19, 2017):

@holtkamp sorry about my delay (again).

I saw the PR but I didn't manage to check it properly, it does seem to cover the issue though. Thanks!

@lcobucci commented on GitHub (Sep 19, 2017): @holtkamp sorry about my delay (again). I saw the PR but I didn't manage to check it properly, it does seem to cover the issue though. Thanks!
Author
Owner

@holtkamp commented on GitHub (Sep 19, 2017):

Ok, thanks for the update! Standing by 😄

@holtkamp commented on GitHub (Sep 19, 2017): Ok, thanks for the update! Standing by 😄
Author
Owner

@holtkamp commented on GitHub (Aug 14, 2019):

Minor bump on this? This issue is about a "bug".

So I am not sure whether this bug issue can/should be closed?

@holtkamp commented on GitHub (Aug 14, 2019): Minor bump on this? This issue is about a "bug". - not sure about https://github.com/doctrine/orm/pull/7184 - to have landed as a new feature targeted at `3.0` in https://github.com/doctrine/orm/pull/7184 So I am not sure whether this bug issue can/should be closed?
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5541