Skip to content

Loading…

DDC-2208: CASE WHEN ... WHEN doesn't work #2904

Closed
doctrinebot opened this Issue · 8 comments

2 participants

@doctrinebot

Jira issue originally created by user mvrhov:

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 '=' 
@doctrinebot

Comment created by mvrhov:

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

@doctrinebot

Comment created by @FabioBatSilva:

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

@doctrinebot

Comment created by mvrhov:

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 ...

@doctrinebot

Comment created by mvrhov:

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

@doctrinebot

Comment created by mvrhov:

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

@doctrinebot

Comment created by @guilhermeblanco:

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.

@doctrinebot

Issue was closed with resolution "Won't Fix"

@doctrinebot

Comment created by mvrhov:

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...

@doctrinebot doctrinebot added this to the 2.4 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.