COALESCE not working with array parameters
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 18.4k
- Forks
- 3.7k
- Avg merge
- 5h 8m
- Merged PRs (30d)
- 1
Description
Hi,
I try to check if my array is empty or NULL, so I have test that :
SELECT U.* FROM Users U WHERE (COALESCE @UserIds IS NULL OR U.Id IN @UserIds)
It's work perfectly when my array UserIds contain data, but when it is empty or null I have an SQL syntaxe error.
Dapper seems to replace my empty array by (SELECT NULL WHERE 1 = 0)
I understand this for a IN statement but it makes usage of COALESCE impossible with empty array parameters. (NULL) should be the default value for COALESCE.
Thank you by advance if you can fix this or give me a clean workaround.
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the reported SQL query with empty and null UserIds, then trace Dapper's array-parameter expansion that produces (SELECT NULL WHERE 1 = 0). Done means COALESCE accepts the empty or null parameter without a syntax error while the IN behavior for populated arrays remains correct, with regression coverage for these cases.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100