【Bug?】The behavior of the UPDATE statement using the CASE clause is wrong

[hiroyuki0415]

I have the following data:

	crf3c_rank0_3id	crf3c_integer_column
	2f0384fb-5b14-ee11-9cbe-002248627e12	1
	63aecf0d-5c14-ee11-9cbe-002248627e12	1
	13a93ef8-5b14-ee11-9cbe-002248627ef1	1
	b51e0406-5c14-ee11-9cbe-002248627ef1	1
	08a15f0d-5c14-ee11-9cbe-002248627ef1	1

After executing the following UPDATE, all the values ​​in the [crf3c_integer_column] column are NULL.
Isn't this a bug?

UPDATE r
SET r.crf3c_integer_column = (
		CASE 
			WHEN r.crf3c_integer_column IS NULL
				THEN NULL
			ELSE r.crf3c_integer_column + 1
			END
		)
FROM crf3c_rank0_3 AS r
WHERE r.crf3c_rank0_3id IS NOT NULL;

The result is below:

	crf3c_rank0_3id	crf3c_integer_column
	2f0384fb-5b14-ee11-9cbe-002248627e12	NULL
	63aecf0d-5c14-ee11-9cbe-002248627e12	NULL
	13a93ef8-5b14-ee11-9cbe-002248627ef1	NULL
	b51e0406-5c14-ee11-9cbe-002248627ef1	NULL
	08a15f0d-5c14-ee11-9cbe-002248627ef1	NULL

Thanks in advance for your support.

[MarkMpn]

Thanks for the details. Yes, I can confirm this is a bug, I'll try to get this fixed ASAP. In the meantime you can get the correct results either by reversing the order of the CASE statement:

UPDATE r
SET r.crf3c_integer_column = (
		CASE 
			WHEN r.crf3c_integer_column IS NOT NULL
				THEN r.crf3c_integer_column + 1
			ELSE NULL
			END
		)
FROM crf3c_rank0_3 AS r
WHERE r.crf3c_rank0_3id IS NOT NULL;

or by removing it entirely (NULL + 1 is still NULL):

UPDATE r
SET r.crf3c_integer_column = r.crf3c_integer_column + 1
FROM crf3c_rank0_3 AS r
WHERE r.crf3c_rank0_3id IS NOT NULL;

I've moved this to issue #314 to track it until it's resolved.

[hiroyuki0415]

Thank you for your prompt reply.