doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-23 22:42:18 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

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

New Issue
Closed
opened 2026-01-22 14:03:11 +01:00 by admin · 8 comments
admin commented 2026-01-22 14:03:11 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Dec 19, 2012).

Originally assigned to: @guilhermeblanco on GitHub.

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 '='
Originally created by @doctrinebot on GitHub (Dec 19, 2012). Originally assigned to: @guilhermeblanco on GitHub. Jira issue originally created by user mvrhov: Having the following part in select DQL throws an exception. ``` sql 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: ``` sql 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: ``` sql 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 '=' ```
admin added the Bug label 2026-01-22 14:03:11 +01:00
admin closed this issue 2026-01-22 14:03:11 +01:00
admin commented 2026-01-22 14:03:13 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Dec 20, 2012):

Comment created by mvrhov:

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

@doctrinebot commented on GitHub (Dec 20, 2012): Comment created by mvrhov: I've added two more cases where the parsing fails. Do you want a separate tickets for that?
admin commented 2026-01-22 14:03:13 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Dec 20, 2012):

Comment created by @FabioBatSilva:

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

@doctrinebot commented on GitHub (Dec 20, 2012): Comment created by @FabioBatSilva: Don't worry, I'll spend some time over this... But I'm not sure about the last one.
admin commented 2026-01-22 14:03:13 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Dec 20, 2012):

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 commented on GitHub (Dec 20, 2012): 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 ...
admin commented 2026-01-22 14:03:15 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jan 8, 2013):

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 commented on GitHub (Jan 8, 2013): 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
admin commented 2026-01-22 14:03:15 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jan 8, 2013):

Comment created by mvrhov:

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

@doctrinebot commented on GitHub (Jan 8, 2013): Comment created by mvrhov: addon: well the subquery part can be full query with joins ....
admin commented 2026-01-22 14:03:15 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (May 22, 2013):

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 commented on GitHub (May 22, 2013): 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.
admin commented 2026-01-22 14:03:17 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (May 22, 2013):

Issue was closed with resolution "Won't Fix"

@doctrinebot commented on GitHub (May 22, 2013): Issue was closed with resolution "Won't Fix"
admin commented 2026-01-22 14:03:18 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (May 22, 2013):

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 commented on GitHub (May 22, 2013): 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...
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#2776
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?