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?
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?