bug: The subquery inner join the define variables will return wrong result.

[haitaoguan]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

mysql> create table ttt(id int,name varchar(5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ttt values(1,'AAA'),(2,'BBB');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into ttt values(1,'AAA'),(2,'BBB');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from ttt;
+------+------+
| id   | name |
+------+------+
|    1 | AAA  |
|    2 | BBB  |
|    1 | AAA  |
|    2 | BBB  |
+------+------+
4 rows in set (0.00 sec)

mysql> SELECT @rownum1 := @rownum1 + 1 inde
    ->   FROM (SELECT a.*
    ->           FROM ttt a) a,
    ->        (SELECT @rownum1 := 0, @pxydm1 := NULL, @rankno1 := 0) b;
+------+
| inde |
+------+
|    2 |
|    4 |
|    6 |
|    8 |
+------+
4 rows in set (0.01 sec)

Expected behavior

+------+
| inde |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

How To Reproduce

No response

Environment

./mysqld Ver 5.7.36-StoneDB-v1.0.1 for Linux on x86_64 (build-) build information as follow: Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev Branch name: stonedb-5.7-dev Last commit ID: f180323 Last commit time: Date: Wed Jun 14 15:44:47 2023 +0800 Build time: Date: Thu Jun 15 06:37:29 UTC 2023

Are you interested in submitting a PR to solve the problem?

• [X] Yes, I will!