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
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.19
• OS: Any
• 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.