Parsing CASE expressions throws an exception when it contains NULL as a result value #5115

New Issue

admin · 2026-01-22T14:58:48+01:00

admin commented

2026-01-22 14:58:48 +01:00

Originally created by @mkruk-u2 on GitHub (Apr 28, 2016).

Originally assigned to: @mkruk-u2 on GitHub.

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

Originally created by @mkruk-u2 on GitHub (Apr 28, 2016). Originally assigned to: @mkruk-u2 on GitHub. 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](http://sqlfiddle.com/#!2/9eecb7d/1216/0), [MySQL 5.6](http://sqlfiddle.com/#!9/9eecb7d/56887/0), [PostgreSQL 9.3](http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/7996/0), [SQLite](http://sqlfiddle.com/#!5/9eecb7/2091/0)).

admin commented

2026-01-22 14:58:48 +01:00

@buffcode commented on GitHub (Oct 11, 2017):

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

@buffcode commented on GitHub (Oct 11, 2017): You can try working around this issue by using a parameter and binding it to `NULL`, though it not working in all cases.

admin commented

2026-01-22 14:58:48 +01:00

@mkruk-u2 commented on GitHub (Oct 12, 2017):

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

@mkruk-u2 commented on GitHub (Oct 12, 2017): @buffcode True. But as you noticed yourself - it's just a workaround. This can also be "hacked" by using `NULLIF` (which [is supported](https://github.com/doctrine/doctrine2/blob/v2.5.4/lib/Doctrine/ORM/Query/Parser.php#L1936)): ``` $ 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](https://github.com/doctrine/doctrine2/blob/v2.5.4/lib/Doctrine/ORM/Query/Parser.php#L1907) 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.

admin commented

2026-01-22 14:58:49 +01:00

@lcobucci commented on GitHub (Nov 26, 2017):

@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 388afb46d0/tests/Doctrine/Tests/ORM/Functional/Ticket

@lcobucci commented on GitHub (Nov 26, 2017): @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

admin commented

2026-01-22 14:58:49 +01:00

@mkruk-u2 commented on GitHub (Dec 29, 2017):

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

@mkruk-u2 commented on GitHub (Dec 29, 2017): @lcobucci I've added a failing test case (https://github.com/doctrine/doctrine2/pull/6944)

admin commented

2026-01-22 14:58:50 +01:00

@Flyrell commented on GitHub (Aug 8, 2018):

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

@Flyrell commented on GitHub (Aug 8, 2018): Hey guys, what's the status on this one?

admin commented

2026-01-22 14:58:50 +01:00