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
THEN 1
instead ofTHEN right_score = 1
. AndTHEN 0
instead ofTHEN right_score = 0
left_score=1
is a boolean expression that returns 0 (false) since the value ofleft_score
is not equal 1, as it is 0.