Shorthand CASE with additional conditions

Question

Up until today I held as true the notion that shorthand CASE doesn't accept additional conditions, as in

CASE evaluate_this
WHEN TRUE AND another_field ... THEN ...

would never work.

Today, as I was distractingly writing some code, I violated this rule just to realise that one CASE expression still worked as intended, the other didn't. Assuming the following data:

id	boolean_one	boolean_two	field_one	field_two
1	TRUE	TRUE	NULL	'a'
2	TRUE	FALSE	2	'a'
3	FALSE	TRUE	8	NULL
4	FALSE	FALSE	NULL	NULL

The one that did not work looked like this:

CASE boolean_one
  WHEN TRUE AND field_one IS NOT NULL THEN field_one * UNIFORM(1, 10, RANDOM())
  ELSE field_one
END AS new_field_one

The idea is that in most circumstances the value for new_field_one should match field_one, the only exception being when boolean_one = TRUE AND field_one IS NOT NULL in which case it should be assigned the product of field_one and a random integer.

Below the desired and actual values for new_field_one based on the above

id	desired	actual
1	NULL	NULL
2	20	20
3	8	8
4	NULL	5

So it applies the randomisation also when boolean_one = FALSE AND field_one IS NULL. Fixing the expression to

CASE
  WHEN boolean_one = TRUE AND field_one IS NOT NULL THEN...

solves the issue. What I find more interesting, and confusing, is that the second expression still did what it was meant to do:

CASE boolean_two
  WHEN TRUE AND field_two = 'b' THEN 'a'
  WHEN TRUE AND field_two = 'a' THEN 'b'
  ELSE field_two
END AS new_field_two

which in this case means that new_field_two = 'b' for the row with ID = 1, and all other remain equal to field_two.

So, this doesn't seem to be an issue of adding an extra condition to a shorthand CASE per se, I would guess it must have something to do specifically with the fact that the second condition is a NULL check. Am I on the right track?

Answer 1

Shawnt00 answer is good,

I like to think of the two forms as

chained If expression

CASE
   WHEN check1 THEN r1
   WHEN check2 THEN r2
   WHEN check3 THEN r3
END

if the same as

IF(check1) 
   r1
ELSE IF (check2)
   r2
...

where-as this "shortcut" version is the Switch statement form:

CASE expression
   WHEN val1 THEN r1
   WHEN val2 THEN r2
   WHEN val3 THEN r2
END

is the same as

SWITCH(expression){
    CASE val1: r1
    CASE val2: r2
    CASE val3: r3
}

which is to say the VAL things need to evaluate down, thus for you stumbled upon cases, you are writing extra boolean logic, which resolves down to a boolean answer. And as the doc's note, for a expression to match a value they need to be of the same type of can be auto converted.

thus this gives a SQL error:

select 
    case false
    when true then 1
    when 'bob' then 2
end;

Boolean value 'bob' is not recognized

because the second value cannot be auto converted to the same type as the case expression, which is a boolean value.