Bug #99807 natural join returns wrong result when expression index is used
Submitted: 8 Jun 2020 17:35 Modified: 25 Jun 2020 15:32
Reporter: wj huang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[8 Jun 2020 17:35] wj huang
Description:
Consider the following statements:

mysql> create table t1(a int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> create index idx on t1((a+1));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx on t2((a+2));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

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

mysql> select * from t1 natural join t2;
Empty set (0.01 sec)

Unexpectedly, the query returns empty. If we drop the index, we will get the correct result: 
mysql> alter table t1 drop index idx;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1 natural join t2;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

How to repeat:
create table t1(a int);
create table t2(a int);
create index idx on t1((a+1));
create index idx on t2((a+2));
insert into t1 values (1);
insert into t2 values (1);
select * from t1 natural join t2;
[8 Jun 2020 21:09] MySQL Verification Team
Thank you for the bug report.
[25 Jun 2020 15:32] Paul DuBois
Posted by developer:
 
Fixed in 8.0.22.

NATURAL JOIN evaluation could inadvertantly match hidden virtual
columns created by functional indexes.