Bug #104883 ResultSet is different between first and second query
Submitted: 9 Sep 9:54 Modified: 9 Sep 11:20
Reporter: wei yao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.35 OS:Any
Assigned to: CPU Architecture:Any

[9 Sep 9:54] wei yao
Description:
Hi, i met a strange behavior as when sql is first execueted in one session,
the resultset is not expected,
but when you exeucted the sql second,third, the resultset is ok.
This case can be repeated in mysql 5.6.35 and 5.7.33
but on mysql 8.0.18 can not be repeated.

How to repeat:
Prepare data:
drop table if exists a1;
create table a1(
  task_id bigint unsigned not null,
  oper_id varchar(64) null,
  retention_time double null)
  ;
insert into a1 values(17463778019812451578,'a',0);
commit;
drop table if exists b1;
create table b1(
  task_id bigint unsigned not null,
  consult_oper_id varchar(64) not null
  );
insert into b1 values
(17463778019812451578,'a'),
(17463778019812451578,'a'),
(17463778019812451578,'a'),
(17463778019812451578,'a')
;
commit;

Login with new session, execute the sql:
select @row :=
 IF( @task_id = task_id, @row + 1, 1 ) num1,
  aa.*,
  @task_id := task_id  from (
select task_id,num from(
select a.task_id, @r := @r+1 num
from(
SELECT t.task_id
  FROM
   (
   SELECT
    task.task_id,
    task.oper_id,
    task.retention_time 
   FROM
    a1 task 
   WHERE
    1 = 1 
   ) AS t
   LEFT JOIN ( SELECT task_id, consult_oper_id FROM b1 WHERE 1 = 1 ) AS c ON t.task_id = c.task_id 
  ORDER BY
    t.retention_time DESC 
     )a, ( SELECT  @r := 0 ) b    
) gg
order by task_id asc, num asc)aa, ( SELECT @row := 0 ) bb ,(select @task_id := 0) ccc
limit 0,20;

the first resultset:
+------+----------------------+------+----------------------+
| num1 | task_id              | num  | @task_id := task_id  |
+------+----------------------+------+----------------------+
|    1 | 17463778019812451578 |    1 | 17463778019812451578 |
|    1 | 17463778019812451578 |    2 | 17463778019812451578 |
|    1 | 17463778019812451578 |    3 | 17463778019812451578 |
|    1 | 17463778019812451578 |    4 | 17463778019812451578 |
+------+----------------------+------+----------------------+

execute again:
+------+----------------------+------+----------------------+
| num1 | task_id              | num  | @task_id := task_id  |
+------+----------------------+------+----------------------+
|    1 | 17463778019812451578 |    1 | 17463778019812451578 |
|    2 | 17463778019812451578 |    2 | 17463778019812451578 |
|    3 | 17463778019812451578 |    3 | 17463778019812451578 |
|    4 | 17463778019812451578 |    4 | 17463778019812451578 |
+------+----------------------+------+----------------------+

notice the first column.
i tried to find the possible reason, but failed.
seems bug?
[9 Sep 10:51] MySQL Verification Team
Hello wei yao,

Thank you for the report and test case.
Issue seen in 5.7.35(and earlier versions).

regards,
Umesh
[9 Sep 11:20] wei yao
Thanks.
Is there any information about the issue?
such as how to trigger,
how to workaround?