[DDC-1858] LIKE and IS NULL operators not supported in HAVING clause Created: 07/Jun/12  Updated: 08/Sep/13  Resolved: 08/Sep/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.2.2
Fix Version/s: 2.5
Security Level: All

Type: Improvement Priority: Major
Reporter: PETIT Yoann Assignee: Guilherme Blanco
Resolution: Fixed Votes: 4
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



 Comments   
Comment by Marco Pivetta [ 08/Jun/12 ]

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

Comment by PETIT Yoann [ 08/Jun/12 ]

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

Comment by Bdiang [ 04/Jul/12 ]

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'"

Comment by Benjamin Eberlei [ 29/Aug/12 ]

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

Guilherme Blanco Is this something we should support or not?

Comment by Christophe Coevoet [ 29/Aug/12 ]

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

I would vote +1 for supporting it

Comment by Benoit Jacquemont [ 10/Jun/13 ]

@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 ?

Comment by Guilherme Blanco [ 11/Jun/13 ]

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,

Comment by Guilherme Blanco [ 11/Jun/13 ]

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

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

Closing the ticket.

Comment by Litz Ouille [ 14/Aug/13 ]

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.

Comment by Guilherme Blanco [ 19/Aug/13 ]

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

Comment by Litz Ouille [ 19/Aug/13 ]

It works. Thanks !

Comment by Benjamin Eberlei [ 08/Sep/13 ]

Assigned fix version

Generated at Wed Jul 23 04:05:37 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.