Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1858

LIKE and IS NULL operators not supported in HAVING clause

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.2
    • Fix Version/s: 2.5
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      Win7, Mysql

      Description

      The LIKE and IS NULL operators are not supported in HAVING clause.

      Work:
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu in (3,6)
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu = 3
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu >= 3
      ...

      Don't work:
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu LIKE 3
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu IS NULL
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu IS NOT NULL

        Activity

        Hide
        Marco Pivetta added a comment -

        I think this has already been fixed in latest master and 2.1.7. Could you just give it a try and eventually confirm?

        Show
        Marco Pivetta added a comment - I think this has already been fixed in latest master and 2.1.7. Could you just give it a try and eventually confirm?
        Hide
        PETIT Yoann added a comment -

        Already try with 2.17, 2.20 and 2.2.2. This hasn't been fixed.

        Show
        PETIT Yoann added a comment - Already try with 2.17, 2.20 and 2.2.2. This hasn't been fixed.
        Hide
        Bdiang added a comment -

        I'm also having this issue (2.2.2). Is there any workaround for this?

        Column aliases also are not supported in HAVING clause:

        $qb->select('p', 'COUNT(p.field) as FieldCount')
                    ->from('Entity', 'p')
                    ->groupBy('p.id')
           ->having('FieldCount IS NULL')
        

        Above code causes error "FieldCount is not pointing to class" and IS NULL causes "Expected =, <, <=, <>, >, >=, !=, got 'IS'"

        Show
        Bdiang added a comment - I'm also having this issue (2.2.2). Is there any workaround for this? Column aliases also are not supported in HAVING clause: $qb->select('p', 'COUNT(p.field) as FieldCount') ->from('Entity', 'p') ->groupBy('p.id') ->having('FieldCount IS NULL') Above code causes error "FieldCount is not pointing to class" and IS NULL causes "Expected =, <, <=, <>, >, >=, !=, got 'IS'"
        Hide
        Benjamin Eberlei added a comment -

        Its not a bug as the EBNF says that this is not possible.

        Guilherme Blanco Is this something we should support or not?

        Show
        Benjamin Eberlei added a comment - Its not a bug as the EBNF says that this is not possible. Guilherme Blanco Is this something we should support or not?
        Hide
        Christophe Coevoet added a comment -

        Another place where it is not supported is in the CASE clause.

        I would vote +1 for supporting it

        Show
        Christophe Coevoet added a comment - Another place where it is not supported is in the CASE clause. I would vote +1 for supporting it
        Hide
        Benoit Jacquemont added a comment - - edited

        @Benjamin Eberlei
        The EBNF seems to indicate that any search condition is valid on the HAVING clause:
        http://savage.net.au/SQL/sql-99.bnf.html#having%20clause

        If you look at the Search condition ( http://savage.net.au/SQL/sql-99.bnf.html#search%20condition ), the NULL predictate ( http://savage.net.au/SQL/sql-99.bnf.html#null%20predicate ) seems to be a part of it.

        Maybe I'm misinterpreting the BNF ?

        And if not, any idea of a targeted release for the fix ?

        Show
        Benoit Jacquemont added a comment - - edited @Benjamin Eberlei The EBNF seems to indicate that any search condition is valid on the HAVING clause: http://savage.net.au/SQL/sql-99.bnf.html#having%20clause If you look at the Search condition ( http://savage.net.au/SQL/sql-99.bnf.html#search%20condition ), the NULL predictate ( http://savage.net.au/SQL/sql-99.bnf.html#null%20predicate ) seems to be a part of it. Maybe I'm misinterpreting the BNF ? And if not, any idea of a targeted release for the fix ?
        Hide
        Guilherme Blanco added a comment -

        Benjamin Eberlei it seems to be SQL-92 compatible. The improvement is valid.
        We should support it. I'll take a look into this. =)

        Cheers,

        Show
        Guilherme Blanco added a comment - Benjamin Eberlei it seems to be SQL-92 compatible. The improvement is valid. We should support it. I'll take a look into this. =) Cheers,
        Hide
        Guilherme Blanco added a comment -

        Functionality is already implemented in master as per coverage added in here:

        https://github.com/doctrine/doctrine2/commit/4e99c5c127c810bf63c9a371f87f0ff5c82e3e79

        Closing the ticket.

        Show
        Guilherme Blanco added a comment - Functionality is already implemented in master as per coverage added in here: https://github.com/doctrine/doctrine2/commit/4e99c5c127c810bf63c9a371f87f0ff5c82e3e79 Closing the ticket.
        Hide
        Litz Ouille added a comment -

        At the moment, only the `HAVING field IS [NOT] NULL` is working.

        The `HAVING field LIKE` as in
        (SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu LIKE 3)
        still does not work.

        Show
        Litz Ouille added a comment - At the moment, only the `HAVING field IS [NOT] NULL` is working. The `HAVING field LIKE` as in (SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu LIKE 3) still does not work.
        Hide
        Guilherme Blanco added a comment -

        I implemented support for ResultVariable in LikeExpression as of https://github.com/doctrine/doctrine2/commit/43fc8bafa766b4e924b05c74825dd30393a17f06

        Show
        Guilherme Blanco added a comment - I implemented support for ResultVariable in LikeExpression as of https://github.com/doctrine/doctrine2/commit/43fc8bafa766b4e924b05c74825dd30393a17f06
        Hide
        Litz Ouille added a comment -

        It works. Thanks !

        Show
        Litz Ouille added a comment - It works. Thanks !
        Hide
        Benjamin Eberlei added a comment -

        Assigned fix version

        Show
        Benjamin Eberlei added a comment - Assigned fix version

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            PETIT Yoann
          • Votes:
            4 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: