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

Status: Closed
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.2.2
Fix Version/s: None
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.

Generated at Tue Jun 18 22:45:32 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.