Query result is incorrect when WHERE clause is "where userID-2>2 || (userID+spID)/3<>0 && score MOD 2 > 0"

[anitajjx]

Can be reproduced ?

Yes

Steps:

Run SQL below:

CREATE TABLE t1 (spID int,userID int,score smallint);
INSERT INTO t1 VALUES (1,1,1);
INSERT INTO t1 VALUES (2,2,2);
INSERT INTO t1 VALUES (2,1,4);
INSERT INTO t1 VALUES (3,3,3);
INSERT INTO t1 VALUES (1,1,5);
INSERT INTO t1 VALUES (4,6,10);
INSERT INTO t1 VALUES (5,11,99);

select * from t1 where userID-2>2 || (userID+spID)/3<>0 && score MOD 2 > 0;

Expected behavior:

mysql> select * from t1 where userID-2>2 || (userID+spID)/3<>0 && score MOD 2 > 0;
+------+--------+-------+
| spID | userID | score |
+------+--------+-------+
| 1 | 1 | 1 |
| 3 | 3 | 3 |
| 1 | 1 | 5 |
| 4 | 6 | 10 |
| 5 | 11 | 99 |
+------+--------+-------+
5 rows in set, 2 warnings (0.00 sec)

Actual behavior:

mysql> select * from t1 where userID-2>2 || (userID+spID)/3<>0 && score MOD 2 > 0;
+--------+------+-------+
| userID | spID | score |
+--------+------+-------+
| 3 | 3 | 3 |
| 6 | 4 | 10 |
| 11 | 5 | 99 |
+--------+------+-------+
3 rows in set (0.01 sec)

Environment:

• Version or commit-id (e.g. v0.1.0 or 8b23a93):commit 758c531
• Hardware parameters:
• OS type:
• Others:

Configuration file:

Additional context:

• Error message from client:
• Server log:
• Other information:

[nnsgmsone]

The result of mod is wrong

[zzl200012]

The result of mod is wrong

It's due to the precision loss of (userID+spID)/3, cause MySQL treats the result of integer calculation as float.
So we need to discuss if we should be compatible with MySQL on this behavior.

[lignay]

Fixed. The case added in BVT already.