Is this a known bug in SQL Server?

Question

whatever it is it looks like it was introduced in 2017

2016 gives the correct results https://dbfiddle.uk/xbeHGGKn

every version after 2016 is giving incorrect results unless you comment out line 9

here's the setup:

drop table if exists x;
with x(id,s,pid) as (
	select 1, null, null union all
	select 2, 'L', 1 union all
	select 3, 'R', 1 union all
	select 4, 'L', 3 union all
	select 5, 'R', 3
)
select * into x from x -- try with and without this line commented out

select l.id, r.id,
	a = iif(exists(select * from x where pid = l.id), 1, 0),
	b = iif(exists(select * from x where pid = r.id), 1, 0)
from x m
join x l on l.pid = m.id and l.s = 'L'
join x r on r.pid = m.id and r.s = 'R'

Answer 1

Hi @GordonHave tested on my side, same issue occurred.

Also, I found that if you keep only one iif function and comment out the other, the result is correct.

My guess is a bug when using exists inside iif with multiple iif functions.

For this bug issue, you can post it in the feedback site.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.