expression: short-cut expressions cause unnecessary warnings, which even causes errors in update statements.

[fzhedu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> use test;
Database changed
mysql> create table t (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert t values (1);
Query OK, 1 row affected (0.01 sec)

mysql> select case when a=1 then 1 else 1/(a-1) end from t;
+---------------------------------------+
| case when a=1 then 1 else 1/(a-1) end |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)

mysql> select if(a=1,1,1/(a-1)) from t;
+-------------------+
| if(a=1,1,1/(a-1)) |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)

 select ifnull(1,1/0);
+---------------+
| ifnull(1,1/0) |
+---------------+
|             1 |
+---------------+
1 row in set, 1 warning (0.00 sec)

select case  when 1/1 then 1/1 when 1/0 then 1/0 end;
+-----------------------------------------------+
| case  when 1/1 then 1/1 when 1/0 then 1/0 end |
+-----------------------------------------------+
|                                        1.0000 |
+-----------------------------------------------+
1 row in set, 2 warnings (0.01 sec)

We may also handle AND and OR.

mysql> select 0 and 1/0;
+-----------+
| 0 and 1/0 |
+-----------+
|         0 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)

mysql> select COALESCE(1, 1/0);
+------------------+
| COALESCE(1, 1/0) |
+------------------+
|                1 |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)

mysql> select * from t0;
+------+
| c0   |
+------+
|    0 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> select interval(t0.c0, -1,0,1,2,1/0) from t0;
+-------------------------------+
| interval(t0.c0, -1,0,1,2,1/0) |
+-------------------------------+
|                             2 |
|                             5 |
+-------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)

2. What did you expect to see? (Required)

no warnings like in MySQL

3. What did you see instead (Required)

a warning

4. Affected version (Required)

v4.0.0

5. Root Cause Analysis

1. Bug from the vectorization run all execution paths,
2. constant folding folds all paths.

[SunRunAway]

We may also handle AND and OR.

mysql> select 0 and 1/0;
+-----------+
| 0 and 1/0 |
+-----------+
|         0 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)

[SunRunAway]

cc @breeswish @TennyZhuang @skyzh
TiKV coprocessor should handle this issue too.

[skyzh]

cc @breeswish @TennyZhuang @skyzh
TiKV coprocessor should handle this issue too.

I'll check how TiKV will handle case / if later.