Bug #85883 Wrong data returned with ndb join pushdown
Submitted: 10 Apr 2017 11:39 Modified: 22 Nov 2017 0:55
Reporter: Magnus Blåudd Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.2 OS:Any
Assigned to: CPU Architecture:Any

[10 Apr 2017 11:39] Magnus Blåudd
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"
[22 Nov 2017 0:55] Jon Stephens
Fixed in MySQL 8.0.2 -> NDB 8.0.1.

Doesn't affect any current Server or NDBCLUSTER releases. 

No changelog entry needed.

Closed.