MYSQL "Division by zero" warnings, strange behavior-CodePudding

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?

CodePudding user response：

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.