MYSQL "Division by zero" warnings, strange behavior

Question

I'm using MySQL 8.0.

I created the following tables

SET SESSION sql_mode='ERROR_FOR_DIVISION_BY_ZERO';

create table Test(
col1 integer,
col2 integer
);

create table Test1(
colA integer,
colB varchar(45)
);

I populated them with these values:

Test     Test1
1 2      2 a          
3 4      4 b
1 5
1 2
0 4
0 2

Now, from other answers, I know that, in general, I cannot rely on short-circuit because SQL is a declarative language so you specify what you want not how: that's left to rdbms optimizer which is free to rearrange the order of conditions.

I executed the following query against Test table:

SELECT *
FROM Test 
WHERE  col2/col1>0 AND col1<>0;

SHOW WARNINGS;

I have two warnings regarding the two divisions by zero (as expected) and this matches with the order of execution reported in the execution plan of the above query:

"attached_condition": "(((`prova`.`test`.`col2` / `prova`.`test`.`col1`) > 0) and 
(`prova`.`test`.`col1` <> 0))"

which clearly shows that col2/col1>0 is checked before col1<>0

Then I ran the following query against the above tables:

SELECT *
FROM Test T JOIN Test1 T1 ON T.col2=T1.colA
WHERE  T.col2/T.col1>0 AND T.col1<>0;

Also this time I would have expected the warnings about divisions by zero be raised but, surprisingly for me, they didn't despite the fact that the query execution plan shows that the condition with division is perfomed before the check of col1<>0:

"attached_condition": "((`prova`.`t`.`col2` = `prova`.`t1`.`colA`) and 
((`prova`.`t1`.`colA` / `prova`.`t`.`col1`) > 0) and (`prova`.`t`.`col1` <> 0))"

How is it possible?

user_5459839 · Answer

When executing an EXPLAIN ANALYSE on the last query, this is the output:

-> Filter: ((T1.colA / T.col1) > 0)  (cost=1.00 rows=0) (actual time=0.047..0.061 rows=3 loops=1)
    -> Inner hash join (T.col2 = T1.colA)  (cost=1.00 rows=0) (actual time=0.039..0.051 rows=3 loops=1)
        -> Filter: (T.col1 <> 0)  (cost=0.23 rows=1) (actual time=0.006..0.015 rows=4 loops=1)
            -> Table scan on T  (cost=0.23 rows=6) (actual time=0.004..0.012 rows=6 loops=1)
        -> Hash
            -> Table scan on T1  (cost=0.45 rows=2) (actual time=0.015..0.019 rows=2 loops=1)

The inner actions are executed first. Here we see that the records from T are first filtered by T.col1 <> 0 and only then they are joined with the records from T1. This explains why no division by zero occurs. It also makes sense that this filter is applied before the join, as that potentially makes the join operation involve fewer records.

user_1766831 · Answer

Some general rules in MySQL:

AND'd clauses in WHERE are usually performed left to right.
MATCH and SPATIAL tests will be performed before other tests.
The Optimizer has limited smarts.
The Optimizer may choose to start with any of the JOINed tables first.
LEFT JOIN may or may not force the 'left' table to read first.
Subqueries and function calls -- who knows.
AND has precedence over OR. That is, a OR b AND c means a OR (b AND c) but that does not say which will be performed first.
Some of the above items can be determined by EXPLAIN, EXPLAIN FORMAT=JSON and the Optimizer Trace.

Write your SQL defensively!

MYSQL "Division by zero" warnings, strange behavior

2 Answers