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




[DDC-2458] [GH-671] [DDC-2435] Fix column name with numbers and non alphanumeric characters. Created: 17/May/13  Updated: 17/May/13  Resolved: 17/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: None
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of FabioBatSilva:

Url: https://github.com/doctrine/doctrine2/pull/671

Message:

http://www.doctrine-project.org/jira/browse/DDC-2435

 Comments   
Comment by Doctrine Bot [ 17/May/13 ]

A related Github Pull-Request [GH-671] was closed:
https://github.com/doctrine/doctrine2/pull/671

Comment by Fabio B. Silva [ 17/May/13 ]

Merged : https://github.com/doctrine/doctrine2/commit/c9d9b68fa9937218aad05dfca4b3f96b409cfc8e




[DDC-2435] Column name with number and reserved character creates invalid query Created: 08/May/13  Updated: 17/May/13  Resolved: 17/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers
Affects Version/s: 2.3.3
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Dane Lipscombe Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

I have a column name called 1:1 which i map like so
@Column(type="string", name="`1:1`")
Unfortunately, the SQL that get generated does not work in mysql:
SELECT t0.`1:1` AS 112 FROM `table` t0
the problem is with the 'AS 112', if it contained a letter eg. 11a, or was escaped eg. `112` it wound work.

 Comments   
Comment by Fabio B. Silva [ 17/May/13 ]

Fixed : https://github.com/doctrine/doctrine2/commit/c9d9b68fa9937218aad05dfca4b3f96b409cfc8e




[DDC-2457] [GH-670] [DDC-2451] Fix entity listeners serialization Created: 17/May/13  Updated: 17/May/13  Resolved: 17/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: None
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

This issue is created automatically through a Github pull request on behalf of FabioBatSilva:

Url: https://github.com/doctrine/doctrine2/pull/670

Message:

http://www.doctrine-project.org/jira/browse/DDC-2451

 Comments   
Comment by Doctrine Bot [ 17/May/13 ]

A related Github Pull-Request [GH-670] was closed:
https://github.com/doctrine/doctrine2/pull/670

Comment by Fabio B. Silva [ 17/May/13 ]

merged : https://github.com/doctrine/doctrine2/commit/65886fdfeaf38692be5196a59530f56fc3e6ab56




[DDC-2451] Entity listeners class don't work when metadatas are stored in cache Created: 16/May/13  Updated: 17/May/13  Resolved: 17/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers, ORM
Affects Version/s: 2.4
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Bruno CHALOPIN Assignee: Fabio B. Silva
Resolution: Fixed Votes: 0
Labels: event, orm
Environment:

Ubuntu / PHP 5.4.9


 Description   

I'm using entity listeners class to make PostPersist and PostLoad actions per entity and use memcached as the metadata cache. Before the metadatas are in cache, the methods of the entity listeners are properly called but when the metadatas are in cache, they are no longer called. I've also made the test with APC and the result is the same.

 Comments   
Comment by Fabio B. Silva [ 17/May/13 ]

Fixed : https://github.com/doctrine/doctrine2/commit/65886fdfeaf38692be5196a59530f56fc3e6ab56




Generated at Thu May 23 18:59:17 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.