compare NULL in int column with float got incorrect

[you06]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

mysql> create table t(c int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values(1), (NULL);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

2. What did you expect to see?

• MySQL 5.7.28

mysql> select c, c = 0.5 from t;
+------+---------+
| c    | c = 0.5 |
+------+---------+
|    1 |       0 |
| NULL |    NULL |
+------+---------+
2 rows in set (0.00 sec)

mysql> select c, c = "0.5" from t;
+------+-----------+
| c    | c = "0.5" |
+------+-----------+
|    1 |         0 |
| NULL |      NULL |
+------+-----------+
2 rows in set (0.00 sec)

3. What did you see instead?

mysql> select c, c = 0.5 from t;
+------+---------+
| c    | c = 0.5 |
+------+---------+
|    1 |       0 |
| NULL |       0 |
+------+---------+
2 rows in set (0.01 sec)

mysql> select c, c = "0.5" from t;
+------+-----------+
| c    | c = "0.5" |
+------+-----------+
|    1 |         0 |
| NULL |         0 |
+------+-----------+
2 rows in set, 1 warning (0.00 sec)

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

Release Version: v4.0.0-beta-789-ge607157ef
Git Commit Hash: e607157efef0f76878d18a667b233061ce43545a
Git Branch: master
UTC Build Time: 2020-04-24 07:26:28
GoVersion: go1.13.3
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @SunRunAway

[Illyrix]

I found it has similar error when null on a int column comparing with a string:

create table t0 (id int not null primary key auto_increment, c0 int);
insert into t0 values(null, 1),(null, null);

select * from t0 where c0 is null;
+----+------+
| id | c0   |
+----+------+
|  2 | NULL |
+----+------+
select * from t0 where (c0 >= "68903428378947589475") is null;
Empty set (0.00 sec)

[youwithouto]

/pick-up

[ti-challenge-bot]

Pick up success.

[youwithouto]

The cause of this issue is that the when doing a comparison between a column and a constant value with a type different from the column type, the comparison is getting short-circuited by refineArgs.

• The result is always a fixed value, i.e. true or false, and the actual column value, which can possibly be NULL, is never used in the evaluation.

There are two possible ways to fix this issue:

1. Add contextual information to the comparison function signatures, such as builtinEQIntSig, to keep track of whether the evaluation should always be true or always be false, unless the column value involved in the comparison is NULL
2. Add a new operator in the Parser project and have a similar logic as 1.

Either way changes the way SQL statements are evaluated.

[ti-challenge-bot]

@youwithouto You did not submit PR within 7 days, so give up automatically.

[youwithouto]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@youwithouto You did not submit PR within 7 days, so give up automatically.

[dentiscalprum]

/assign

[Defined2014]

It has the same result as MySQL in master branch. Could you close this issue? @you06 @tisonkun

[Defined2014]

It has the same result as MySQL in master branch. Could you close this issue? @you06 @tisonkun

It's fixed by #24049. @tisonkun @you06

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.