DDC-1858: LIKE and IS NULL operators not supported in HAVING clause #2519

Closed
doctrinebot opened this Issue Jun 7, 2012 · 18 comments

2 participants

@doctrinebot

Jira issue originally created by user abhoryo:

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

@doctrinebot

Comment created by @ocramius:

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

@doctrinebot

Comment created by abhoryo:

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

@doctrinebot

Comment created by bdiang:

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

@doctrinebot

Comment created by @beberlei:

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

[~guilhermeblanco] Is this something we should support or not?

@doctrinebot

Comment created by stof:

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

I would vote +1 for supporting it

@doctrinebot

Comment created by bitone:

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

@doctrinebot

Comment created by @guilhermeblanco:

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

Cheers,

@doctrinebot

Comment created by @guilhermeblanco:

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

4e99c5c

Closing the ticket.

@doctrinebot

Comment created by litz:

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.

@doctrinebot

Comment created by @guilhermeblanco:

I implemented support for ResultVariable in LikeExpression as of 43fc8ba

@doctrinebot

Comment created by litz:

It works. Thanks !

@doctrinebot

Comment created by @beberlei:

Assigned fix version

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by the_storm:

[~guilhermeblanco] Please backport to released version. This is very serious issue, especially when you make search queries.

@doctrinebot

Comment created by @ocramius:

[~the_storm] this is an improvement, not a bug fix, therefore there will be no backporting.

@doctrinebot

Comment created by the_storm:

[~ocramius] 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?

@doctrinebot

Comment created by @ocramius:

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.

@doctrinebot

Comment created by the_storm:

[~ocramius] 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 );

@doctrinebot doctrinebot added this to the 2.5 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment