Orm: Parsing CASE expressions throws an exception when it contains NULL as a result value

[mkruk-u2]

Case expressions (GeneralCaseExpression, SimpleCaseExpression) doesn't allow to use NULL value in CASE SQL statement:

$ app/console doctrine:query:dql "SELECT u.id from UserBundle:User u" --show-sql
string 'SELECT u0_.id AS id_0 FROM user u0_' (length=35)

$ app/console doctrine:query:dql "SELECT u.id, CASE WHEN u.id > 0 THEN u.id ELSE 0 END from UserBundle:User u" --show-sql
string 'SELECT u0_.id AS id_0, CASE WHEN u0_.id > 0 THEN u0_.id ELSE 0 END AS sclr_1 FROM user u0_' (length=90)

$ app/console doctrine:query:dql "SELECT u.id, CASE WHEN u.id > 0 THEN u.id ELSE NULL END from UserBundle:User u" --show-sql

  [Doctrine\ORM\Query\QueryException]                      
  [Syntax Error] line 0, col 47: Error: Unexpected 'NULL'  

  [Doctrine\ORM\Query\QueryException]                                               
  SELECT u.id, CASE WHEN u.id > 0 THEN u.id ELSE NULL END from UserBundle:User u  

These expressions expect ScalarExpression, which do not handle NULL values: https://github.com/doctrine/doctrine2/blob/v2.5.4/lib/Doctrine/ORM/Query/Parser.php#L1936.

Is this expected and correct behaviour?
It seems that this syntax is valid SQL syntax (at least in MySQL 5.5, MySQL 5.6, PostgreSQL 9.3, SQLite).

[buffcode]

You can try working around this issue by using a parameter and binding it to NULL, though it not working in all cases.

[mkruk-u2]

@buffcode True. But as you noticed yourself - it's just a workaround.

This can also be "hacked" by using NULLIF (which is supported):

$ app/console doctrine:query:dql "SELECT u.id, CASE WHEN u.id > 0 THEN u.id ELSE NULLIF(1,1) END from UserBundle:User u" --show-sql
string 'SELECT u0_.id AS id_0, CASE WHEN u0_.id > 0 THEN u0_.id ELSE NULLIF(1, 1) END AS sclr_1 FROM user u0_' (length=100)

But the question was more about why NULL is not supported in the first place?
It seems that extending this case statement with case ($lookahead === Lexer::T_NULL): (and probably adding few constants here and there) should do the job. But maybe there is a reason why it's not done already.

[lcobucci]

@mkruk-u2 could you please send us a failing test case that reproduces that behaviour? It would help us a lot to identify and fix the issue you're describing.

You can find examples on https://github.com/doctrine/doctrine2/tree/388afb46d0cb3ed0c51332e8df0de9e942c2690b/tests/Doctrine/Tests/ORM/Functional/Ticket

[mkruk-u2]

@lcobucci I've added a failing test case (https://github.com/doctrine/doctrine2/pull/6944)

[Flyrell]

Hey guys, what's the status on this one?

[Ocramius]

@Flyrell give #6944 a stab