Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 2.3.1
    • Fix Version/s: 2.4
    • Component/s: DQL
    • Security Level: All
    • 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 '=' 
      

        Activity

        Hide
        Miha Vrhovnik added a comment -

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

        Show
        Miha Vrhovnik added a comment - I've added two more cases where the parsing fails. Do you want a separate tickets for that?
        Hide
        Fabio B. Silva added a comment -

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

        Show
        Fabio B. Silva added a comment - Don't worry, I'll spend some time over this... But I'm not sure about the last one.
        Hide
        Miha Vrhovnik added a comment - - edited

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

        Show
        Miha Vrhovnik added a comment - - edited 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 ...
        Hide
        Miha Vrhovnik added a comment - - edited

        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

        Show
        Miha Vrhovnik added a comment - - edited 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
        Hide
        Miha Vrhovnik added a comment -

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

        Show
        Miha Vrhovnik added a comment - addon: well the subquery part can be full query with joins ....
        Hide
        Guilherme Blanco added a comment -

        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.

        Show
        Guilherme Blanco added a comment - 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.
        Hide
        Miha Vrhovnik added a comment -

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

        Show
        Miha Vrhovnik added a comment - 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...

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Miha Vrhovnik
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: