Why do I need "OR NULL" in MySQL when counting rows with a condition

This should reveal all

SELECT 4=4, 3=4, 1 or null, 0 or null

Output

1   |   0   |   1   |   NULL

Facts

1. COUNT adds up the columns / expressions that evaluate to NOT NULL. Anything will increment by 1, as long as it is not null. Exception is COUNT(DISTINCT) where it increments only if it is not already counted.

2. When a BOOLEAN expression is used on its own, it returns either 1 or 0.

3. When a boolean is OR-ed with NULL, it is NULL only when it is 0 (false)

To others

Yes if the count is the ONLY column desired, one could use WHERE value=4 but if it is a query that wants to count the 4's as well as retrieving other counts/aggregates, then the filter doesn't work. An alternative would have been SUM(value=4), e.g.

SELECT sum(value=4)
  FROM test

COUNT() function accepts an argument, that is treated as NULL or NOT NULL. If it is NOT NULL - then it increments the value, and doesn't do anything otherwise.

In your case expression value=4 is either TRUE or FALSE, obviously both true and false are not null, that is why you get 10.

but I am interested in a COUNT(condition) based solution.

The count-based solution will be always slower (much slower), because it will cause table fullscan and iterative comparison of each value.

COUNT(expression) counts the number of rows for which the expression is not NULL. The expression value=4 is only NULL if value is NULL, otherwise it is either TRUE (1) or FALSE (0), both of which are counted.

1 = 4         | FALSE
4 = 4         | TRUE
1 = 4 OR NULL | NULL
4 = 4 OR NULL | TRUE

You could use SUM instead:

SELECT SUM(value=4) FROM test

This is not particularly useful in your specific example but it can be useful if you want to count rows satisfying multiple different predicates using a single table scan such as in the following query:

SELECT
    SUM(a>b) AS foo,
    SUM(b>c) AS bar,
    COUNT(*) AS total_rows
FROM test