Why CASE WHEN always return 0 and 1 in mysql? [closed]

Question

I use a table where there are two integer fields right_score and left_score. When CASE WHEN is used it outputs incorrect result :

SELECT 
    right_score,
    left_score,
    CASE
        WHEN right_score < 3 THEN right_score = 1
        WHEN right_score >= 3 THEN right_score = 0
    END AS r_result,
    CASE
        WHEN left_score < 3 THEN left_score = 1
        WHEN left_score >= 3 THEN left_score = 0
    END AS l_result
FROM
    de_data
WHERE
    id = 037629

The output shows right_score=0 , left_score=0, r_result=0 and l_result=0

But i expect the result as

The output shows right_score=0 , left_score=0, r_result=1 and l_result=1

Where did i went wrong ?

Answer 1

Everything is running as programmed.

So for right_score = 0

WHEN right_score < 3 THEN right_score = 1

First MySQL checks if right_score is smaller than three and in the next and second step MySQL checks if right_score is equal to 1 what is false which mysql tells by sending a 0

So in that line you have two comparisons and the result is what you have programmed a bunch of true(1) and false (0)

If you remove all the second comparisons, you get what you expected.

CREATE TABLE de_data(id varchar(6),right_score int ,    left_score int)

INSERT INTO de_data VALUES ('037629', 1,1),('037629', 3,1),('037629', 1,3),('037629', 3,3),('037629', 2,2)
,('037629', 1,1),('037629', 2,2)

SELECT 
    right_score,
    left_score,
    CASE
        WHEN right_score < 3 THEN right_score = 1
        WHEN right_score >= 3 THEN right_score = 0
    END AS r_result,
    CASE
        WHEN left_score < 3 THEN left_score = 1
        WHEN left_score >= 3 THEN left_score = 0
    END AS l_result
FROM
    de_data
WHERE
    id = 037629

right_score | left_score | r_result | l_result
----------: | ---------: | -------: | -------:
          1 |          1 |        1 |        1
          3 |          1 |        0 |        1
          1 |          3 |        1 |        0
          3 |          3 |        0 |        0
          2 |          2 |        0 |        0
          1 |          1 |        1 |        1
          2 |          2 |        0 |        0

SELECT 
    right_score,
    left_score,
    CASE
        WHEN right_score < 3 THEN  1
        WHEN right_score >= 3 THEN  0
    END AS r_result,
    CASE
        WHEN left_score < 3 THEN  1
        WHEN left_score >= 3 THEN  0
    END AS l_result
FROM
    de_data
WHERE
    id = 037629

right_score | left_score | r_result | l_result
----------: | ---------: | -------: | -------:
          1 |          1 |        1 |        1
          3 |          1 |        0 |        1
          1 |          3 |        1 |        0
          3 |          3 |        0 |        0
          2 |          2 |        1 |        1
          1 |          1 |        1 |        1
          2 |          2 |        1 |        1

db<>fiddle here