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:

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

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?

CodePudding user response：

With the shortcut syntax the "when expression" is evaluated as a whole and then that value is compared against the "case expression". It's not matching the value as true and then running an extra check

In fact your true and is essentially ignored because true and X is just equivalent to X in Boolean logic. The test then is whether boolean_one matches the non-null state of field_one. The second and fourth rows correspond to true = true and false = false which is when you're seeing the random result values.

You could certainly nest case if there were a real benefit to legibility:

case boolean_one
    when true then
        case field_one is not null ... end
    else field_one
end

CodePudding user response：

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.