Bug #117716 Inconsistent result when using ndb cluster
Submitted: 15 Mar 19:05 Modified: 18 Mar 17:42
Reporter: Jiyuan Li Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version: 9.1.0-cluster OS:Ubuntu (18.04)
Assigned to: MySQL Verification Team CPU Architecture:Any

[15 Mar 19:05] Jiyuan Li 
Description:
I created a table and run a query on mysql ndb cluster 9.1.0 and got a result, then I run same query with mysql 8.0 but not deployed as cluster, and nothing output 

How to repeat:
create table `t0` (`c1` int,`c2` varchar(255));
insert into `t0` values (1,'a'),(2,'d'),(3,'c');

select `t01`.`c1`,`t01`.`c2`,`t01`.`c3` from (select `t1`.*,@`c3`:=@`c3`+1 as `c3` from (select  `t0`.*,@`c3`:=0 from `t0` order by  `t0`.`c1`) as `t1`) as `t01` where `t01`.`c3`=2 and `t01`.`c2`='d';
[18 Mar 13:40] MySQL Verification Team 
Hi,

I am not sure I understand.

mysql> drop table t0;
Query OK, 0 rows affected (0.01 sec)

mysql> create table `t0` (`c1` int,`c2` varchar(255));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into `t0` values (1,'a'),(2,'d'),(3,'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select `t01`.`c1`,`t01`.`c2`,`t01`.`c3` from (select `t1`.*,@`c3`:=@`c3`+1 as `c3` from (select  `t0`.*,@`c3`:=0 from `t0` order by  `t0`.`c1`) as `t1`) as `t01` where `t01`.`c3`=2 and `t01`.`c2`='d';
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    2 | d    |    2 |
+------+------+------+
1 row in set, 2 warnings (0.00 sec)

So I deployed this on a 'non cluster' (innodb) system and it works as expected ?
[18 Mar 14:15] Jiyuan Li 
I found that the result depends on mysql version;

In mysql 8.0.27, 8.0.22 it outputs nothing. 

In mysql 5.7.44, it reports syntax error. 

In mysql 5.7.42, 8.3.0, 8.0.41, 8.0.39, 8.2.0 it gives same result as mysql ndb cluster.
[18 Mar 15:55] MySQL Verification Team 
> In mysql 5.7.42, 8.3.0, 8.0.41, 8.0.39, 8.2.0 it gives same result as mysql ndb cluster.

So all latest versions work as expected?
[18 Mar 15:59] Jiyuan Li 
At least on those versions I tested, yes. Maybe it appears in 8.0.x temporarily.
[18 Mar 17:03] MySQL Verification Team 
Hi,

So we can close this bug then?

We can't fix behavior on already released releases and as far as I see all current releases work as expected? I apologize if I'm missing something.
[18 Mar 17:34] Jiyuan Li 
Yes, thanks for your patience!
[18 Mar 17:42] MySQL Verification Team 
All great. Thanks for using MySQL.

kind regards