[DDC-2208] CASE WHEN ... WHEN doesn't work Created: 19/Dec/12  Updated: 22/May/13  Resolved: 22/May/13

Status: Closed
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3.1
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Miha Vrhovnik Assignee: Guilherme Blanco
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

Having the following part in select DQL throws an exception.

SUM(CASE
            WHEN c.startDate <= :start THEN c.endDate - :start
            WHEN c.endDate >= :end THEN :end - c.startDate
            ELSE 0
            END)

exception:

[Syntax Error] line 0, col 124: Error: Expected Doctrine\ORM\Query\Lexer::T_ELSE, got '-'

It seems that it's failing inside the second THEN

This one also seems to fail:

SUM(CASE
            WHEN c.startDate <= :start THEN (c.endDate - :start)
            WHEN c.endDate >= :end THEN (:end - c.startDate)
            ELSE 0
            END)

exception:

[Syntax Error] line 0, col 60: Error: Unexpected '('

Another one:

SUM(CASE
                WHEN c.startDate <= :start THEN c.endDate - :start
                WHEN c.endDate >= :end THEN :end - c.startDate
                ELSE 0
                END) = :result FROM ...

exception:

[Syntax Error] line 0, col 60: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got '='


 Comments   
Comment by Miha Vrhovnik [ 20/Dec/12 ]

I've added two more cases where the parsing fails. Do you want a separate tickets for that?

Comment by Fabio B. Silva [ 20/Dec/12 ]

Don't worry, I'll spend some time over this...
But I'm not sure about the last one.

Comment by Miha Vrhovnik [ 20/Dec/12 ]

The 3rd case seems work just fine as a part of a HAVING clause.
I haven't tried it but It might be that it fails with something simpler like SELECT COUNT( * ) = :foo FROM ... or SELECT COUNT( * ) = 2 FROM ...

Comment by Miha Vrhovnik [ 08/Jan/13 ]

Fabio I have two more...
It doesn't like NULL and subselect after then part

 
->addSelect('CASE
    WHEN po.quantity IS NULL THEN NULL
    ELSE po.quantity -
            COALESCE(0, (
                SELECT COUNT(rd.product) FROM xxxx rd
                    WHERE (rd.startDate <= :end) AND (rd.endDate >= :start) AND
                        rd.product = c.product)))
    END
    AS po.quantity
')

:edit replaced with real query

Comment by Miha Vrhovnik [ 08/Jan/13 ]

addon: well the subquery part can be full query with joins ....

Comment by Guilherme Blanco [ 22/May/13 ]

After further investigation, JPA 2.0 and 2.1 do not support NULL as part of ScalarExpression.
There are many underlying problems by adding this straight to ScalarExpression, such as the example I showed.
I don't think supporting this will bring benefits, but too many headaches.
As a workaround, create your own function that generates "NULL" as SQL. It would work perfectly here.
Closing the PR as we will not support it.

Comment by Miha Vrhovnik [ 22/May/13 ]

Not to sound rude but, the answer is far fetched. So what if JPA is not supporting it. Yes I understand that the Doctrine is modeled after JPA but this shouldn't mean that it's not better in some regards.

This is really a low blow especially if there is a need to use a query builder to build the queries. And as I said it's not only the IS NULL but the CASE statement can contain a whole subquery with it's own CASE statements etc...

Generated at Fri May 24 20:50:44 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.