Description:
Recetn changes in MySQL Server 8.0 has caused queries using the ndb join pushdown feature to return wrong data. Several queries in the ndb regression suite fails and it looks like there is a problem with returning "no such row", this is since several joins which previously returned NULL (from one of the tables) now have values. There are also some queries which previously didn't return any rows which now have a row appearing. If no queries are run earlier in the session it looks like garbage data else you get old rows.
Have compared the queries output with Innodb and the result differs.
How to repeat:
mysql> set ndb_join_pushdown = true;
Query OK, 0 rows affected (0,00 sec)
mysql> use test
Database changed
mysql> create table t1 (
-> a int not null,
-> b int not null,
-> c int not null,
-> d int not null,
-> primary key (`a`,`b`)
-> ) engine=ndb;
Query OK, 0 rows affected (0,22 sec)
mysql> insert into t1 values
-> (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
-> (1,2,5,1), (1,3,1,2), (1,4,2,3),
-> (2,1,3,4), (2,3,4,5), (2,4,5,1),
-> (3,1,1,2), (3,2,2,3), (3,4,3,4),
-> (4,1,4,5), (4,2,5,1), (4,3,1,2);
Query OK, 16 rows affected (0,02 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> select *
-> from t1
-> join t1 as t2 on t2.a = t1.c and t2.b = t1.d
-> where t1.a = 2 and t1.b = 3;
+---+---+---+---+------------+------------+--------+---+
| a | b | c | d | a | b | c | d |
+---+---+---+---+------------+------------+--------+---+
| 2 | 3 | 4 | 5 | -223342318 | 1744830591 | 274064 | 0 |
+---+---+---+---+------------+------------+--------+---+
1 row in set (0,01 sec)
The last part of output is apparently random garbage. Same table and query with InnoDb returns no row.
Suggested fix:
Properly return "no such row"