DDC-2787: COALESCE() doesn't work with NOT IN() #3486

New Issue

admin · 2026-01-22T14:20:46+01:00

admin commented

2026-01-22 14:20:46 +01:00

Originally created by @doctrinebot on GitHub (Nov 9, 2013).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user flack:

When I try running the following query:

SELECT count(c.id) FROM my\model c WHERE COALESCE(c.up, 0) NOT IN( :parent_ids)

I get

Doctrine\ORM\Query\QueryException: [Semantical Error] line 0, col 118 near 'up, 0) NOT IN(': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

When I run the query as straight SQL against the database, it works as expected. Is this something that can be fixed in Doctrine or is this syntax unsupported?

Originally created by @doctrinebot on GitHub (Nov 9, 2013). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user flack: When I try running the following query: ``` SELECT count(c.id) FROM my\model c WHERE COALESCE(c.up, 0) NOT IN( :parent_ids) ``` I get ``` Doctrine\ORM\Query\QueryException: [Semantical Error] line 0, col 118 near 'up, 0) NOT IN(': Error: Invalid PathExpression. Must be a StateFieldPathExpression. ``` When I run the query as straight SQL against the database, it works as expected. Is this something that can be fixed in Doctrine or is this syntax unsupported?

admin added the Bug label 2026-01-22 14:20:46 +01:00

admin closed this issue

2026-01-22 14:20:47 +01:00

admin commented

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

@astepin commented on GitHub (Jun 6, 2017):

This bug can be closed. It is not an issue.

I assume that "c.up" is a reference to another table. To solve this you just have to wrap "c.up" with the IDENTITY() function like this:

SELECT count(c.id) FROM my\model c WHERE COALESCE(IDENTITY(c.up), 0) NOT IN( :parent_ids)

@astepin commented on GitHub (Jun 6, 2017): This bug can be closed. It is not an issue. I assume that "c.up" is a reference to another table. To solve this you just have to wrap "c.up" with the IDENTITY() function like this: > SELECT count(c.id) FROM my\model c WHERE COALESCE(**IDENTITY(c.up)**, 0) NOT IN( :parent_ids)

admin commented

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

@lcobucci commented on GitHub (Jun 23, 2017):

@astepin thanks! Closing.

@lcobucci commented on GitHub (Jun 23, 2017): @astepin thanks! Closing.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#3486