[DDC-1858] LIKE and IS NULL operators not supported in HAVING clause Created: 07/Jun/12  Updated: 19/Oct/14  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

Comment by Nikolay Baklicharov [ 06/Oct/14 ]

Guilherme Blanco Please backport to released version. This is very serious issue, especially when you make search queries.

Comment by Marco Pivetta [ 06/Oct/14 ]

Nikolay Baklicharov this is an improvement, not a bug fix, therefore there will be no backporting.

Comment by Nikolay Baklicharov [ 07/Oct/14 ]

Marco Pivetta Then what workaround I can use? Lets say I do leftJoin on table and do AVG(object.value) > 3 OR object IS NULL. I.e. I want all results that have avg value higher than 3 and also results that do not have object at all?

Comment by Marco Pivetta [ 07/Oct/14 ]

The current workaround is bumping the ORM version, which may or may not be a problem depending on your stability policies and how much you need this feature.

Otherwise, NativeSQL until the functionality is available in a stable release.

Comment by Nikolay Baklicharov [ 07/Oct/14 ]

Marco Pivetta Not an option for the project. However I found workaround that I will share here in case someone come here from google search like me.

$queryBuilder->addSelect( 'COALESCE(AVG(ra.value), 0) AS HIDDEN averageRating' );
$queryBuilder->leftJoin( 'c.ratings', 'ra' );
$queryBuilder->andHaving( 'averageRating >= :avgRating' );
$queryBuilder->orHaving( 'averageRating = 0' );
$queryBuilder->setParameter( ':avgRating', $ratingFilter );
Generated at Tue Oct 21 02:10:08 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.