incompatible query result with MySQL

[eurekaka]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

mysql> create table t(col_double double);
Query OK, 0 rows affected (0.00 sec)

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

mysql> set session tidb_enable_vectorized_expression = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into mysql.expr_pushdown_blacklist values('cast');
Query OK, 1 row affected (0.00 sec)

mysql> admin reload expr_pushdown_blacklist;
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from "SELECT * FROM (SELECT col_double AS c0 FROM t) t WHERE (ABS((REPEAT(?, ?) OR 5617780767323292672)) < LN(EXP(c0)) + (? ^ ?))";
Query OK, 0 rows affected (0.01 sec)

mysql> set @a1 = "JuvkBX7ykVux20zQlkwDK2DFelgn7";
Query OK, 0 rows affected (0.00 sec)

mysql> set @a2 = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @a3 = -112990.35179796701;
Query OK, 0 rows affected (0.00 sec)

mysql> set @a4 = 87997.92704840179;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a1, @a2, @a3, @a4;
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set, 3 warnings (0.00 sec)

2. What did you expect to see?

In MySQL:

mysql> create table t(col_double double);
Query OK, 0 rows affected (0.02 sec)

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

mysql> prepare stmt from "SELECT * FROM (SELECT col_double AS c0 FROM t) t WHERE (ABS((REPEAT(?, ?) OR 5617780767323292672)) < LN(EXP(c0)) + (? ^ ?))";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a1 = "JuvkBX7ykVux20zQlkwDK2DFelgn7";
Query OK, 0 rows affected (0.01 sec)

mysql> set @a2 = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @a3 = -112990.35179796701;
Query OK, 0 rows affected (0.00 sec)

mysql> set @a4 = 87997.92704840179;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a1, @a2, @a3, @a4;
Empty set (0.00 sec)

3. What did you see instead?

Different result for execute.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

57bb931a273d37a1d59e3f7b6a09dd94ff8e2326

[eurekaka]

@qw4990 Should this issue be assigned to expression SIG?

[ghost]

This behaves different in MySQL 5.7 / MySQL 8.0 / TiDB. I am not smart enough to figure out which one is most correct :(

Testcase:

drop table if exists t;
create table t(col_double double);
insert into t values(1);
prepare stmt from "SELECT * FROM (SELECT col_double AS c0 FROM t) t WHERE (ABS((REPEAT(?, ?) OR 5617780767323292672)) < LN(EXP(c0)) + (? ^ ?))";
SET @a1 = "JuvkBX7ykVux20zQlkwDK2DFelgn7", @a2 = 1, @a3 = -112990.35179796701, @a4 = 87997.92704840179;
execute stmt using @a1, @a2, @a3, @a4;

MySQL 5.7:

mysql [localhost:5731] {msandbox} (test) > execute stmt using @a1, @a2, @a3, @a4;
Empty set (0.00 sec)

MySQL 8.0:

mysql [localhost:8021] {msandbox} (test) > execute stmt using @a1, @a2, @a3, @a4;
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

TiDB (master):

mysql> execute stmt using @a1, @a2, @a3, @a4;
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set, 23 warnings (0.01 sec)

[guo-shaoge]

Simplified testcase:

drop table t;
create table t(col_double double);
insert into t values(1);
select * from t where  0 < (col_double + (-1 ^ 8));
select 0 < (col_double + (-1 ^ 8)) from t; 

MySQL 5.6 result:

Server version: 5.6.51 Source distribution
mysql> select * from t where  0 < (col_double + (-1 ^ 8));
Empty set (0.00 sec)

mysql> select 0 < (col_double + (-1 ^ 8)) from t;
+-----------------------------+
| 0 < (col_double + (-1 ^ 8)) |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

MySQL 8.0 result:

Server version: 8.0.23 MySQL Community Server - GPL
mysql> select * from t where  0 < (col_double + (-1 ^ 8));
+------------+
| col_double |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select 0 < (col_double + (-1 ^ 8)) from t;
+-----------------------------+
| 0 < (col_double + (-1 ^ 8)) |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

"0 < (col_double + (-1 ^ 8))": result of this expression should be true. TiDB's result is OK.

Implementation of MySQL 5.6 got bug, because it didn't convert the result of "-1 ^ 8" to unsigned longlong(see Item_cache_int::val_real(), it shoud check Item::unsigned_flag, then convert value to unsigned, just like Item_int_func::val_real() does.)

Maybe we can close this issue?