Weird behavior of MySQL Server when ordering by 2 conditions

Say a database table my_table has the following structure:

| id      | positive_votes | total_votes | ... |
|---------|----------------|-------------|-----|
| integer | integer        | integer     | ... |

and some of the rows in the database have total_votes == 0.

I want to get the rows ordered like this, except I also want to include the rows that have total_votes == 0. I tried using the following query, and it worked like a charm:

SELECT *
FROM my_table
ORDER BY
    total_votes = 0,
    ((positive_votes + 1.9208) / total_votes - 1.96 * SQRT((positive_votes * (total_votes - positive_votes)) / total_votes + 0.9604) / total_votes) / (1 + 3.8416 / total_votes) DESC

My question is why don’t I ever get a Divide by zero error?

I’m using 8.0.25 MySQL Community Server.

Answer

This is controlled by the SQL mode. As described here, ERROR_FOR_DIVISION_BY_ZERO behaves as:

• If this mode is not enabled, division by zero inserts NULL and produces no warning.

• If this mode is enabled, division by zero inserts NULL and produces a warning.

• If this mode and strict mode are enabled, division by zero produces an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, division by zero inserts NULL and produces a warning.

You can see this behavior using a direct 1/0.